데이터 분석을 위한 SQL 레시피 - 4장 시계열 기반으로 데이터 집계하기 (1)

1
2
3
4
5
6
7
8
9

SELECT
    dt
  , COUNT(*)                        AS purchase_count
  , SUM(purchase_amount)            AS total_amount
  , AVG(purchase_amount)            AS avg_amount
FROM purchase_log
GROUP BY dt
ORDER BY dt
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT
    dt
  , SUM(purchase_amount) AS total_amount
  -- 최근 최대 7일 동안의 평균 계산하기
  , AVG(SUM(purchase_amount)) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) as seven_day_avg
  -- 7일 데이터가 모두 있는 경우에만 계산
  , CASE WHEN 7 = COUNT(*) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) THEN AVG(SUM(purchase_amount)) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) END as seven_day_avg_strict
FROM purchase_log
GROUP BY dt
ORDER BY dt
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

SELECT
  dt
  -- PostgreSQL, Hive, Redshift, SparkSQL의 경우 substring로 연-월 추출
  , substring(dt, 1, 7) AS year_month
  -- PostgreSQL, Hive, Bigquery, SparkSQL의 경우 substr 함수 사용
  , substr(dt, 1, 7) AS year_month

  , SUM(purchase_amount) AS total_amount

  -- PostgreSQL, Hive, Redshift, SparksQL
  , SUM(SUM(purchase_amount)) OVER(PARTITION BY substring(dt, 1, 7) ORDER BY dt ROWS UNBOUNDED PRECEDING) AS agg_amount

  -- BigQuery의 경우 substring을 substr로 수정
  , SUM(SUM(purchase_amount)) OVER(PARTITION BY substr(dt, 1, 7) ORDER BY dt ROWS UNBOUNDED PRECEDING) AS agg_amount
    
  FROM purchase_log
GROUP BY dt
ORDER BY dt
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

WITH daily_purchase AS (
    SELECT
        dt
        -- BigQuery의 경우 substring -> substr  
      , substring(dt, 1, 4) as year
      , substring(dt, 6, 2) as month
      , substring(dt, 9, 2) as date
      , SUM(purchase_amount) as purchase_amount
      , COUNT(order_id) AS orders
    FROM purchase_log
    GROUP BY dt
) SELECT * FROM daily_purchase ORDER BY dt
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

WITH daily_purchase AS (
    SELECT
        dt
        -- BigQuery의 경우 substring -> substr  
      , substring(dt, 1, 4) as year
      , substring(dt, 6, 2) as month
      , substring(dt, 9, 2) as date
      , SUM(purchase_amount) as purchase_amount
      , COUNT(order_id) AS orders
    FROM purchase_log
    GROUP BY dt
) SELECT
    dt

  -- Redshift의 경우, concat 함수를 조합해서 사용하거나 || 연산자 사용  
  , CONCAT(year, '-', month) AS year_month
  , year || '-' || month AS year_month

  , purchase_amount
  , SUM(purchase_amount) OVER(PARTITION BY year, month ORDER BY dt ROWS UNBOUNDED PRECEDING) as agg_amount

FROM daily_purchase
ORDER BY dt;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

WITH daily_purchase AS (
    SELECT
        dt
        -- BigQuery의 경우 substring -> substr  
      , substring(dt, 1, 4) as year
      , substring(dt, 6, 2) as month
      , substring(dt, 9, 2) as date
      , SUM(purchase_amount) as purchase_amount
      , COUNT(order_id) AS orders
    FROM purchase_log
    GROUP BY dt
) SELECT
    month
  , SUM(CASE year WHEN '2014' THEN purchase_amount END) as amount_2014
  , SUM(CASE year WHEN '2015' THEN purchase_amount END) as amount_2015
  , 100.0 * (SUM(CASE year WHEN '2015' THEN purchase_amount END) / SUM(CASE year WHEN '2014' THEN purchase_amount END)) as rate
FROM daily_purchase
GROUP BY month
ORDER BY month
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

WITH daily_purchase AS (
    SELECT
        dt
      , substring(dt, 1, 4) as year
      , substring(dt, 6, 2) as month
      , substring(dt, 9, 2) as date
      , SUM(purchase_amount) as purchase_amount
      , COUNT(order_id) AS orders
    FROM purchase_log
    GROUP BY dt
), 
monthly_amount AS (
    SELECT year, month, SUM(purchase_amount) as amount
    FROM daily_purchase
    GROUP BY year, month
), 
alc_index AS (
    SELECT
        year
      , month
      , amount
      -- 2015년의 누계 매출 집계하기
      , SUM(CASE WHEN year = '2015' THEN amount END) OVER (ORDER BY year, month ROWS UNBOUNDED PRECEDING) as agg_amount
      -- 당월부터 11개월 이전까지의 매출 합계(이동년계) 집계하기
      , SUM(amount) OVER (ORDER BY year, month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW )  as year_avg_amount
    FROM monthly_amount 
    ORDER BY year, month
)
SELECT
    concat(year, '-', month) as year_month

   -- Redshift의 경우 concat 함수를 조합하거나, || 연산자 사용
   , concat(concat(year, '-'), month) AS year_month
 
   , amount
   , agg_amount
   , year_avg_amount

FROM calc_index
WHERE year = '2015' -- 2015년 데이터만 압축하기
ORDER BY year_month
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

WITH
daily_purchase AS (
  SELECT
      dt
    , substring(dt, 1, 4) as year
    , substring(dt, 6, 2) as month
    , substring(dt, 9, 2) as date
    , SUM(purchase_amount) as purchase_amount
    , COUNT(order_id) AS orders
  FROM purchase_log
  GROUP BY dt
)
, monthly_purchase AS (
  SELECT
    year
    , month
    , SUM(orders) AS orders
    , AVG(purchase_amount) AS avg_amount
    , SUM(purchase_amount) AS monthly
  FROM daily_purchase
  GROUP BY year, month
)
SELECT
  concat(year, '-', month) AS year_month
  -- Redshift의 경우 concat 함수를 조합하거나, || 연산자 사용
  -- year || '-' || month AS year_month

  , orders
  , avg_amount
  , monthly
  , SUM(monthly) OVER(PARTITION BY year ORDER BY month ROWS UNBOUNDED PRECEDING) AS agg_amount

  -- 12개월 전의 매출 구하기
  , LAG(monthly, 12) OVER(ORDER BY year, month) as last_year
  -- sparkSQL의 경우 다음과 같이 사용 
  --, LAG(monthly, 12) OVER(ORDER BY year, month ROWS BETWEEN 12 PRECEDING AND 12 PRECEDING) AS last_year

  , 100.0 * monthly / LAG(monthly, 12) OVER(ORDER BY year, month)  as rate
  -- sparkSQL의 경우 다음과 같이 사용
  --, 100.0 * monthly / LAG(monthly, 12) OVER(ORDER BY year, month ROWS BETWEEN 12 PRECEDING AND 12 PRECEDING) as rate

FROM monthly_purchase
ORDER BY year, month
;