복붙노트

[SQL] MySQL의 GROUP BY 날짜 시간 +/- 삼초

SQL

MySQL의 GROUP BY 날짜 시간 +/- 삼초

내가 3 열이있는 테이블이 있다고 가정 :

나는 다음과 같은 기록이있다 :

1, 2010-01-01 15:00:00, Some Title
2, 2010-01-01 15:00:02, Some Title
3, 2010-01-02 15:00:00, Some Title

나는 서로 후 3 초 이내에있는 기록에 GROUP BY 할 필요가있다. 이 테이블, 행 1 및 2가 함께 그룹화 될 것이다.

15 분에 의해 MySQL의 날짜 시간 그룹 : 비슷한 여기에 의문의 여지가

나는 또한이 발견 : http://www.artfulsoftware.com/infotree/queries.php#106

나는 초 동안 작동합니다 뭔가에 이러한 방법을 변환하는 방법을 모르겠어요. SO 질문에 방법에 문제는 단지 시간의 빈에 속하는 기록을 위해 일하는 것이 나에게 보인다입니다 알려진 시점에서 시작됩니다. 나는 5 초 간격으로, 초 작업에 FLOOR ()을 얻을 수 있다면 예를 들어, 15시 0분 4초의 시간이 15시 0분 1초와 함께 분류 할 것이지만, 15시 0분 6초로 그룹화되지 .

이게 말이 되요? 더 설명이 필요한 경우 알려 주시기 바랍니다.

EDIT : 숫자의 집합 {1, 2, 3, 4, 5, 6, 7, 50, 51, 60}의 경우, 그것은 그룹들을 {1, 2, 3, 4, 5에 가장있을 것 6,7}, {50, 51} {60}, 각 그룹의 행의 행이 이전 3 초 이내 인 경우에 따라되도록. 나는이에 wishywashy 것에 대해 미안 해요,이 일을 조금 변경 알고있다.

나는 다른 서버에서 퍼지 일치 로그에 노력하고 있습니다. 서버 # 1 서버 # 1의 몇 초 이내에, 같은 항목 "항목 # 1"을 기록합니다 항목 "항목 # 1"및 서버 # 2를 기록 할 수 있습니다. 난 둘 다 로그 라인에서 일부 집계 함수를 할 필요가있다. 불행하게도, 난 단지 인해 서버 소프트웨어의 특성에 가서 제목을 가지고있다.

해결법

  1. ==============================

    1.톰 H.의 우수한 아이디어를 사용하지만 다르게 여기 좀하고 있어요 :

    톰 H.의 우수한 아이디어를 사용하지만 다르게 여기 좀하고 있어요 :

    대신 체인의 시작있는 모든 행을 찾는, 우리는, 체인의 시작 모든 시간을 찾아 다시 가서 시간을 일치하는 행을 ifnd 수 있습니다.

    여기에 검색어 # 1은 배 시간이 그 아래에 있지만, 3 초 내에 모든 시간이 없어 찾아 체인의 시작은 당신을 말해야한다 :

    SELECT DISTINCT Timestamp
FROM Table a
LEFT JOIN Table b
ON (b.Timestamp >= a.TimeStamp - INTERVAL 3 SECONDS
    AND b.Timestamp < a.Timestamp)
WHERE b.Timestamp IS NULL

    그리고 각 행에 대해, 우리는 쿼리 # 2 우리의 타임 스탬프보다 가장 큰 체인 시작 타임 스탬프를 찾을 수 있습니다 :

    SELECT Table.id, MAX(StartOfChains.TimeStamp) AS ChainStartTime
FROM Table
JOIN ([query #1]) StartofChains
ON Table.Timestamp >= StartOfChains.TimeStamp
GROUP BY Table.id

    우리가이 있으면, 우리는 GROUP BY 그것은 당신이 원 수.

    SELECT COUNT(*) --or whatever
FROM Table
JOIN ([query #2]) GroupingQuery
ON Table.id = GroupingQuery.id
GROUP BY GroupingQuery.ChainStartTime

    나는 전적으로 확인이 별도로 게시하는 톰 H의 대답 구별 충분이 있어요,하지만 당신은 구현에 문제가되었고, 나는 그것에 대해 생각처럼 내가 다시 게시 할 줄 알았는데, 그래서 그것은 소리가났다. 행운을 빕니다!

  2. ==============================

    2.지금은 내가 OMG 조랑말에 대한 귀하의 코멘트 응답에 따라 문제를 이해한다는 생각, 내가 집합 기반 솔루션을 가지고 있다고 생각합니다. 아이디어는 먼저 제목에 기반한 체인의 시작을 찾을 수 있습니다. 체인의 시작은 그 행에 앞서 삼초 내에서 일치가없는 모든 행으로 정의 될 것입니다 :

    지금은 내가 OMG 조랑말에 대한 귀하의 코멘트 응답에 따라 문제를 이해한다는 생각, 내가 집합 기반 솔루션을 가지고 있다고 생각합니다. 아이디어는 먼저 제목에 기반한 체인의 시작을 찾을 수 있습니다. 체인의 시작은 그 행에 앞서 삼초 내에서 일치가없는 모든 행으로 정의 될 것입니다 :

    SELECT
    MT1.my_id,
    MT1.title,
    MT1.my_time
FROM
    My_Table MT1
LEFT OUTER JOIN My_Table MT2 ON
    MT2.title = MT1.title AND
    (
        MT2.my_time < MT1.my_time OR
        (MT2.my_time = MT1.my_time AND MT2.my_id < MT1.my_id)
    ) AND
    MT2.my_time >= MT1.my_time - INTERVAL 3 SECONDS
WHERE
    MT2.my_id IS NULL

    이제 우리는 비 체인 선발이 그들 앞에 나타나있는 체인 스타터에 속한다는 것을 가정 할 수 있습니다. MySQL은 CTE를 지원하지 않기 때문에, 당신은이 다수의 아래 같은 하위 쿼리에 조인을 저장하는 것처럼, 임시 테이블에 위의 결과가 발생 할 수 있습니다.

    SELECT
    SQ1.my_id,
    COUNT(*)  -- You didn't say what you were trying to calculate, just that you needed to group them
FROM
(
    SELECT
        MT1.my_id,
        MT1.title,
        MT1.my_time
    FROM
        My_Table MT1
    LEFT OUTER JOIN My_Table MT2 ON
        MT2.title = MT1.title AND
        (
            MT2.my_time < MT1.my_time OR
            (MT2.my_time = MT1.my_time AND MT2.my_id < MT1.my_id)
        ) AND
        MT2.my_time >= MT1.my_time - INTERVAL 3 SECONDS
    WHERE
        MT2.my_id IS NULL
) SQ1
INNER JOIN My_Table MT3 ON
    MT3.title = SQ1.title AND
    MT3.my_time >= SQ1.my_time
LEFT OUTER JOIN
(
    SELECT
        MT1.my_id,
        MT1.title,
        MT1.my_time
    FROM
        My_Table MT1
    LEFT OUTER JOIN My_Table MT2 ON
        MT2.title = MT1.title AND
        (
            MT2.my_time < MT1.my_time OR
            (MT2.my_time = MT1.my_time AND MT2.my_id < MT1.my_id)
        ) AND
        MT2.my_time >= MT1.my_time - INTERVAL 3 SECONDS
    WHERE
        MT2.my_id IS NULL
) SQ2 ON
    SQ2.title = SQ1.title AND
    SQ2.my_time > SQ1.my_time AND
    SQ2.my_time <= MT3.my_time
WHERE
    SQ2.my_id IS NULL

    임시 테이블을 사용하는 경우는 열팽창 계수를 사용할 수 있다면 훨씬 더 간단 보거나 것이다. 또한 성능에 도움이 될 임시 테이블을 사용.

    당신이 정확히 일치 타임 스탬프를 가질 수 경우이 문제가있을 것입니다. 그런 경우 당신은 타임 스탬프 값을 일치하는 행을 구분하는 ID와 타임 스탬프의 조합을 사용 약간 쿼리를 조정할 필요가있을 것이다.

    편집 : 타임 스탬프에 의해 정확하게 일치를 처리하기 위해 쿼리를 변경.

  3. ==============================

    3.경고 : 긴 대답. 이 일을, 당신은 이상 INSERT 문을 실행하는 데 기꺼이해야 중간에 한 단계를 제외하고, 상당히 깔끔해야하며, 우리는 MySQL의에서 재귀 CTE의 일을 할 수 없기 때문에이 아무것도하지 않는 이상까지.

    경고 : 긴 대답. 이 일을, 당신은 이상 INSERT 문을 실행하는 데 기꺼이해야 중간에 한 단계를 제외하고, 상당히 깔끔해야하며, 우리는 MySQL의에서 재귀 CTE의 일을 할 수 없기 때문에이 아무것도하지 않는 이상까지.

    내가 대신 당신의 예로서이 데이터를 사용하려고 해요 :

    id    Timestamp
1     1:00:00
2     1:00:03
3     1:00:06
4     1:00:10

    여기에 쓰기로 첫 번째 쿼리는 다음과 같습니다

    SELECT a.id as aid, b.id as bid
FROM Table a
JOIN Table b 
ON (a.Timestamp is within 3 seconds of b.Timestamp)

    그것은 반환 :

    aid     bid
1       1
1       2
2       1
2       2
2       3
3       2
3       3
4       4

    하자의 중복을 허용하지 않습니다 것들을 유지하기 위해 좋은 테이블을 만들 :

    CREATE TABLE
Adjacency
( aid INT(11)
, bid INT(11)
, PRIMARY KEY (aid, bid) --important for later
)

    이제 문제는 그 관계의 전이 폐쇄 같은 것을 찾을 수 있습니다.

    이렇게하려면의 링크의 다음 수준을 찾을 수 있습니다. 우리가 인접 테이블 1 2 2 3이 있기 때문에 그에 의해 내 말은, 우리는 추가해야합니다 1 3 :

    INSERT IGNORE INTO Adjacency(aid,bid)
SELECT adj1.aid, adj2.bid
FROM Adjacency adj1
JOIN Adjacency adj2
ON (adj1.bid = adj2.aid)

    이 비 우아한 부분이다 : 그것은 테이블에 행을 추가하지 않습니다 때까지 반복해서 위의 INSERT 문을 실행해야합니다. 그렇게 할 수있는 깔끔한 방법이 있는지 모르겠어요.

    이 끝나면,이 같은 전 이적 폐쇄와 관련이있을 것이다 :

    aid     bid
1       1
1       2
1       3     --added
2       1
2       2
2       3
3       1     --added
3       2
3       3
4       4

    그리고 이제 뒤통수 때리는 웃긴 위해 :

    SELECT aid, GROUP_CONCAT( bid ) AS Neighbors
FROM Adjacency
GROUP BY aid

    보고:

    aid     Neighbors
1       1,2,3
2       1,2,3
3       1,2,3
4       4

    그래서

    SELECT DISTINCT Neighbors
FROM (
     SELECT aid, GROUP_CONCAT( bid ) AS Neighbors
     FROM Adjacency
     GROUP BY aid
     ) Groupings

    보고

    Neighbors
1,2,3
4

    아휴!

  4. ==============================

    4.I @ 크리스 커닝햄의 대답처럼,하지만 여기에 또 다른 걸릴입니다.

    I @ 크리스 커닝햄의 대답처럼,하지만 여기에 또 다른 걸릴입니다.

    먼저, 문제 설명에 대한 이해는 (내가 틀렸다면 정정 해줘) :

    그래서 내가 SQL 서버 구문을 사용하고, SQL 서버에서 주로 일한다. MySQL의 SQL로 번역하기 너무 어렵지 않을 것이다.

    그래서, 먼저 우리의 이벤트 로그 테이블 :

    --
-- our event log table
--
create table dbo.eventLog
(
  id       int          not null ,
  dtLogged datetime     not null ,
  title    varchar(200) not null ,

  primary key nonclustered ( id ) ,
  unique clustered ( dtLogged , id ) ,

)

    하여 문제 설명의 이해 이상을 감안할 때, 다음 쿼리는 상부 및 하부 경계 당신에게 당신의 그룹을 제공해야합니다. 그것은 붕괴 것들에 의해 2 그룹과 간단한 중첩 SELECT 문의 :

    테이블의 모든 행은 이렇게 정의 된 그룹 중 하나에 해당해야하고, 특정 그룹이 아니라 하나의 날짜 / 시간 값으로 구성 될 수있다.

    [편집 : 상한은 간격이 3 초 이상이며 최저 일자 / 시간 값이다]

    select dtFrom = min( t.dtFrom ) ,
       dtThru =      t.dtThru
from ( select dtFrom = t1.dtLogged ,
              dtThru = min( t2.dtLogged )
       from      dbo.EventLog t1
       left join dbo.EventLog t2 on t2.dtLogged >= t1.dtLogged
                                and datediff(second,t1.dtLogged,t2.dtLogged) > 3
       group by t1.dtLogged
     ) t
group by t.dtThru

    그런 다음 그들은 따라서, 소속하는 그룹과 이벤트 로그 및 태그를 행을 당겨 수 :

    select *
from ( select dtFrom = min( t.dtFrom ) ,
              dtThru =      t.dtThru
       from ( select dtFrom = t1.dtLogged ,
                     dtThru = min( t2.dtLogged )
              from      dbo.EventLog t1
              left join dbo.EventLog t2 on t2.dtLogged >= t1.dtLogged
                                       and datediff(second,t1.dtLogged,t2.dtLogged) > 3
              group by t1.dtLogged
            ) t
       group by t.dtThru
     ) period
join dbo.EventLog t on t.dtLogged >=           period.dtFrom
                   and t.dtLogged <= coalesce( period.dtThru , t.dtLogged )
order by period.dtFrom , period.dtThru , t.dtLogged

    dtFrom 및 dtThru 열이 반환을 통해 각 행의 그룹과 태그합니다. 당신은 공상을 얻을 당신이 원하는 경우 각 그룹에 필수적인 행 번호를 할당 할 수 있습니다.

  5. ==============================

    5.간단한 쿼리 :

    간단한 쿼리 :

    SELECT * FROM time_history GROUP BY ROUND(UNIX_TIMESTAMP(time_stamp)/3);

    6. from https://stackoverflow.com/questions/6551214/mysql-group-by-datetime-3-seconds by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] ORDER BY는 먼저 숫자에 따라 알파벳  (0) 2020.06.09
[SQL] 어떻게 열 및 업데이트 테이블의 값을 정렬하려면?  (0) 2020.06.09
[SQL] 어떻게 JPA 명명 된 쿼리의 IN 절에서 동적 매개 변수를 사용하는 방법?  (0) 2020.06.09
[SQL] SQL 서버는 웹 요청을 보낼 수 있습니까?  (0) 2020.06.09
[SQL] MS SQL Server에 RODBC 임시 테이블 문제를 연결할 때  (0) 2020.06.09
,

티스토리툴바