[數據分析#53] SQL 連續日期、連續名單日期寫法：場景及程式碼

今天分享在 Presto SQL 下，常用的練續日期產生方式！

什麼情境下會需要寫 SQL 連續日期？

• 當你需要產出完整日期及對應的資料時

連續日期怎麼寫？

• Presto SQL 在產出連續日期時，會用到：sequence 跟 unnest

情境一：產出一段期間內的連續日期

• 在 sequence 裡面放入日期的範圍，他會變成一個 array (陣列形式)
• 再用 unnest 把陣列形式裡的資料，拆解成一欄資料

SELECT t.grass_date
FROM unnest(sequence(date('2022-01-01'), date('2022-12-31'))) AS t(grass_date)
ORDER BY 1

情境二：產出設定條件內的連續日期

• 在 sequence 裡面放入日期的條件範圍
• 再用 unnest 把陣列形式裡的資料，拆解成一欄資料

SELECT t.grass_date
FROM unnest(sequence(date('2022-01-01'), current_date - interval '1' day)) AS t(grass_date)
ORDER BY 1

連續名單跟日期怎麼寫？

• Presto SQL 在產出連續日期時，會用到：sequence 跟 unnest
• Presto SQL 在產出連續日期跟連續名單時，假設日期 (10 天) x 連續名單 (5 個名單) = 50 列，需要用到 cross join

with aggr_days AS (
	SELECT t.grass_date
	FROM unnest(sequence(date('2022-01-01'), date('2022-01-10'))) AS t(grass_date)
	ORDER BY 1
)

, user_list (user_id) AS (VALUES
(1)
, (2)
, (3)
, (4)
, (5)
)

SELECT
user.user_id
, aggr_days.grass_date
FROM (SELECT user_id FROM user_list) AS user
CROSS JOIN aggr_days

• 所以就產出的資料而言：一個 user_id 會對到一串完整的日期，有幾個 user_id (範例為 5 個)，就會分別對到幾個日期 (範例中的日期為 10 天)，5*10=50 (50 行)

連續日期及名單的常見商業用法？

• 通常用戶不一定會在每個日期都下單，因此沒下單的那一天就不會有資料
• 但是當我們想要計算有多少用戶在那一天沒下單時，raw data 就會需要沒下單的用戶也出現，只是 order = 0 或是 null (空白)，好讓我們計算 order = 0 或是 null 的人有多少；就知道當天沒下單有多少人，或是誰沒下單

結論

• 有些小技巧雖然在一般 SQL 教學時不會提及
• 但是在商業應用上卻很常見！
• 可以知道有這些小技巧，如果需要時再回來看 SQL code 怎麼寫 (不一定要背下來)