날짜/시간 시계열로 집계

SQL로 하는 탐색적 데이터 분석

Christina Maimone

Data Scientist

시계열 생성

SELECT generate_series(from, to, interval);

SELECT generate_series('2018-01-01',
                       '2018-01-15',
                       '2 days'::interval);

   generate_series   
---------------------
 2018-01-01 00:00:00
 2018-01-03 00:00:00
 2018-01-05 00:00:00
 2018-01-07 00:00:00
 2018-01-09 00:00:00
 2018-01-11 00:00:00
 2018-01-13 00:00:00
 2018-01-15 00:00:00
(8 rows)

시계열 생성

SELECT generate_series('2018-01-01',
                       '2018-01-02',
                       '5 hours'::interval);

    generate_series     
----------------------
 2018-01-01 00:00:00
 2018-01-01 05:00:00
 2018-01-01 10:00:00
 2018-01-01 15:00:00
 2018-01-01 20:00:00
(5 rows)

처음부터 시계열 생성

SELECT generate_series('2018-01-31',
                       '2018-12-31',
                       '1 month'::interval);

   generate_series     
---------------------
 2018-01-31 00:00:00
 2018-02-28 00:00:00
 2018-03-28 00:00:00
 2018-04-28 00:00:00
 2018-05-28 00:00:00
 2018-06-28 00:00:00
 2018-07-28 00:00:00
 2018-08-28 00:00:00
 2018-09-28 00:00:00
 2018-10-28 00:00:00
 2018-11-28 00:00:00
 2018-12-28 00:00:00
(12 rows)

처음부터 시계열 생성

-- 말일을 얻기 위해 하루 빼기
SELECT generate_series('2018-02-01',  -- 시작을 1개월 늦춤
                       '2019-01-01',
                       '1 month'::interval) - '1 day'::interval;

   generate_series
---------------------
 2018-01-31 00:00:00
 2018-02-28 00:00:00
 2018-03-31 00:00:00
 2018-04-30 00:00:00
 2018-05-31 00:00:00
 2018-06-30 00:00:00
 2018-07-31 00:00:00
 2018-08-31 00:00:00
 2018-09-30 00:00:00
 2018-10-31 00:00:00
 2018-11-30 00:00:00
 2018-12-31 00:00:00
(12 rows)

일반 집계

SELECT * FROM sales;

        date         | amount 
---------------------+--------
 2018-04-23 09:13:14 |     12
 2018-04-23 13:57:53 |     41
 2018-04-23 12:05:44 |     23
 2018-04-23 09:07:33 |     31
 2018-04-23 10:31:40 |      5
 2018-04-23 09:35:16 |     18
 2018-04-23 12:17:43 |     19
 2018-04-23 12:57:49 |     32
 2018-04-23 10:12:35 |     13
 2018-04-23 13:21:30 |      6
(10 rows)

SELECT date_trunc('hour', date) 
          AS hour, 
       count(*) 
  FROM sales 
 GROUP BY hour
 ORDER BY hour;

        hour         | count 
---------------------+-------
 2018-04-23 09:00:00 |     3
 2018-04-23 10:00:00 |     2
 2018-04-23 12:00:00 |     3
 2018-04-23 13:00:00 |     2
(4 rows)

시계열로 집계

-- series를 hour_series 테이블로 생성
WITH hour_series AS (
     SELECT generate_series('2018-04-23 09:00:00',          -- 오전 9시
                            '2018-04-23 14:00:00',          -- 오후 2시
                            '1 hour'::interval) AS hours)

-- series의 시각과, NULL 제외를 위해 date 개수(count(* ) 아님)
SELECT hours, count(date) 

  -- series를 매출 데이터에 조인
  FROM hour_series
       LEFT JOIN sales
              ON hours=date_trunc('hour', date) 

 GROUP BY hours
 ORDER BY hours;

시계열 집계: 결과

         hours          | count 
------------------------+-------
 2018-04-23 09:00:00-05 |     3
 2018-04-23 10:00:00-05 |     2
 2018-04-23 11:00:00-05 |     0
 2018-04-23 12:00:00-05 |     3
 2018-04-23 13:00:00-05 |     2
 2018-04-23 14:00:00-05 |     0
(6 rows)

구간(bin)으로 집계

-- 구간(bin) 생성
WITH bins AS (
      SELECT generate_series('2018-04-23 09:00:00', 
                             '2018-04-23 15:00:00',
                             '3 hours'::interval) AS lower,
             generate_series('2018-04-23 12:00:00', 
                             '2018-04-23 18:00:00',
                             '3 hours'::interval) AS upper)

-- 각 구간의 개수 세기
SELECT lower, upper, count(date) 

  -- LEFT JOIN으로 모든 구간 유지
  FROM bins
       LEFT JOIN sales
              ON date >= lower
             AND date < upper

 -- 구간 경계로 그룹화
 GROUP BY lower, upper
 ORDER BY lower;

구간 결과

        lower        |        upper        | count 
---------------------+---------------------+-------
 2018-04-23 09:00:00 | 2018-04-23 12:00:00 |     5
 2018-04-23 12:00:00 | 2018-04-23 15:00:00 |     5
 2018-04-23 15:00:00 | 2018-04-23 18:00:00 |     0
(3 rows)

시계열 생성 연습

SQL로 하는 탐색적 데이터 분석