[SQL 패턴] Row 간 Timestamp 주기가 1초이고, 5분 이상 떨어진 Row 군집 별로 묶어서, StartTime과 EndTime을 계산해야 하는 경우 (LEAD 함수 활용)

개요

---

해당 쿼리는 Row 간 Timestamp 컬럼값의 주기가 1초이고, 특정 시간(ex. 5분) 이상 떨어진 Row 군집 별로 묶어서, StartTime과 EndTime을 계산해야 하는 경우에 사용할 수 있습니다.

LEAD 함수를 활용할 수 있는 좋은 사례 중에 하나입니다.

User 또는 Device 별로 StartTime과 EndTime을 계산해야 하는 경우

WITH cte AS (
  SELECT 
    userOrDeviceName, 
    MIN(local_datetime) AS first_start_tm, 
    MAX(local_datetime) AS last_stop_tm 
  FROM 
    "<<table name>>"
  WHERE 1 = 1 
  // <<filter condition>>
  GROUP BY 
    userOrDeviceName
), 
cte1 AS (
  SELECT 
    userOrDeviceName, 
    g.start_tm AS between_start_tm, 
    g.stop_tm AS between_stop_tm 
  FROM 
    (
      SELECT 
        userOrDeviceName, 
        local_datetime AS stop_tm, 
        LEAD(local_datetime) OVER (
          PARTITION BY userOrDeviceName 
          ORDER BY 
            local_datetime ASC
        ) AS start_tm 
      FROM 
        "<<table name>>"
      WHERE 1 = 1 
      // <<filter condition>>
    ) g 
  WHERE 
    DATE_DIFF(
      'second', 
      CAST(SUBSTR(g.stop_tm, 1, 19) AS timestamp), 
      CAST(SUBSTR(g.start_tm, 1, 19) AS timestamp)
    ) > (60 * 10)
) 
SELECT 
  userOrDeviceName, 
  start_tm AS startDatetime, 
  stop_tm AS endDatetime 
FROM 
  (
    SELECT 
      userOrDeviceName, 
      a.start_tm, 
      LEAD(a.stop_tm) OVER (
        PARTITION BY userOrDeviceName 
        ORDER BY a.start_tm ASC
      ) AS stop_tm 
    FROM 
      (
        SELECT 
          userOrDeviceName, 
          first_start_tm AS start_tm, 
          NULL AS stop_tm 
        FROM 
          cte 
        UNION ALL 
        SELECT 
          userOrDeviceName, 
          between_start_tm AS start_tm, 
          between_stop_tm AS stop_tm 
        FROM 
          cte1 
        UNION ALL 
        SELECT 
          userOrDeviceName, 
          NULL AS start_tm, 
          last_stop_tm AS stop_tm 
        FROM 
          cte
      ) a
  ) b 
WHERE 
  b.start_tm IS NOT NULL 
ORDER BY 
  userOrDeviceName, 
  stop_tm DESC;

User 또는 Device 별로 StartTime과 EndTime을 계산해야 하는 경우(축약)

SELECT userOrDeviceName
     , start_tm AS startDatetime
     , stop_tm AS endDatetime
        FROM (                                                                                
               SELECT a.userOrDeviceName
                      , a.start_tm
                      , LEAD(a.stop_tm) OVER (PARTITION BY a.userOrDeviceName ORDER BY a.start_tm ASC) AS stop_tm   
                 FROM (                                                                       
                        SELECT g.userOrDeviceName
                             , g.start_tm
                             , g.stop_tm                                          
                          FROM (                                                              
                                 SELECT userOrDeviceName
                                       ,local_datetime AS stop_tm                    
                                       ,LEAD(local_datetime) OVER                    
                                        (PARTITION BY userOrDeviceName ORDER BY local_datetime ASC) AS start_tm
                                   FROM << TABLE 명 >>                             
                                  WHERE 1=1
                                   --  조건 추가
                                   AND local_datetime  >= DATE_FORMAT(CAST('2023-01-01' AS timestamp), '%Y%m%d')
                                   AND userOrDeviceName IN ('John', 'Kim') 
                               ) g                                                             
                          WHERE DATE_DIFF('second', CAST(SUBSTR(g.stop_tm, 1, 19) AS timestamp), CAST(SUBSTR(g.start_tm, 1, 19) AS timestamp)) > (60 * 10)        
                          UNION ALL                                                              
                         SELECT userOrDeviceName
                                ,NULL AS start_tm
                                ,MAX(local_datetime) AS stop_tm
                           FROM << TABLE 명 >>                                        
                          WHERE 1=1
                            --  조건 추가(위와 동일)
                            AND local_datetime  >= DATE_FORMAT(CAST('2023-01-01' AS timestamp), '%Y%m%d')                      
                            AND userOrDeviceName IN ('John', 'Kim') 
                          GROUP BY userOrDeviceName
                          UNION ALL                                                              
                         SELECT userOrDeviceName
                                ,MIN(local_datetime) AS start_tm
                                ,NULL AS stop_tm
                           FROM << TABLE 명 >>                                        
                          WHERE 1=1
                            --  조건 추가(위와 동일)
                            AND local_datetime  >= DATE_FORMAT(CAST('2023-01-01' AS timestamp), '%Y%m%d')                   
                            AND userOrDeviceName IN ('John', 'Kim') 
                          GROUP BY userOrDeviceName
                      ) a                                                                     
             ) b                                                                              
       WHERE b.start_tm IS NOT NULL                                                           
ORDER BY userOrDeviceName, stop_tm DESC                                                                           ;

 

전체 StartTime과 EndTime을 계산해야 하는 경우

WITH cte AS (
  SELECT
    MIN(local_datetime) AS first_start_tm, 
    MAX(local_datetime) AS last_stop_tm 
  FROM 
    "<<table name>>"
  WHERE 1 = 1 
  // <<filter condition>>
), 
cte1 AS (
  SELECT 
    g.start_tm AS between_start_tm, 
    g.stop_tm AS between_stop_tm 
  FROM 
    (
      SELECT 
        local_datetime AS stop_tm, 
        LEAD(local_datetime) OVER (
          ORDER BY 
            local_datetime ASC
        ) AS start_tm 
      FROM 
        "<<table name>>"
      WHERE 1 = 1 
      // <<filter condition>>
    ) g 
  WHERE 
    DATE_DIFF(
      'second', 
      CAST(SUBSTR(g.stop_tm, 1, 19) AS timestamp), 
      CAST(SUBSTR(g.start_tm, 1, 19) AS timestamp)
    ) > (60 * 10)
) 
SELECT 
  start_tm AS startDatetime, 
  stop_tm AS endDatetime 
FROM 
  (
    SELECT 
      a.start_tm, 
      LEAD(a.stop_tm) OVER (
        ORDER BY a.start_tm ASC
      ) AS stop_tm 
    FROM 
      (
        SELECT 
          first_start_tm AS start_tm, 
          NULL AS stop_tm 
        FROM 
          cte 
        UNION ALL 
        SELECT 
          between_start_tm AS start_tm, 
          between_stop_tm AS stop_tm 
        FROM 
          cte1 
        UNION ALL 
        SELECT 
          NULL AS start_tm, 
          last_stop_tm AS stop_tm 
        FROM 
          cte
      ) a
  ) b 
WHERE 
  b.start_tm IS NOT NULL 
ORDER BY stop_tm DESC;

 

참조

---

[SQL] ROW_NUMBER(), LEAD(), LAG() (tistory.com)