實用函式總覽
使用 SQL 分析商業資料
Michel Semaan
Data Scientist
處理日期
DATE_TRUNC('quarter', '2018-08-13')→'2018-07-01 00:00:00+00:00''2018-07-01 00:00:00+00:00' :: DATE→'2018-07-01'
報表中的日期
- 可讀性高的日期對報表很重要
- 預設格式
'2018-08-13'不夠直觀 - 如何從
'2018-08-13'變成'Friday 13, August 2018'？
解法
TO_CHAR('2018-08-13', 'FMDay DD, FMMonth YYYY')→'Friday 13, August 2018'
TO_CHAR()
TO_CHAR(DATE, TEXT) → TEXT（格式化後的日期字串）例：
Dy→ 星期縮寫（Mon、Tues等）TO_CHAR('2018-06-01', 'Dy') → 'Fri'TO_CHAR('2018-06-02', 'Dy') → 'Sat'
格式字串中的樣式會被對應的日期元素取代；其他字元原樣保留
- 例：
DD→ 日期數字（01-31）TO_CHAR('2018-06-01', 'Dy - DD') → 'Fri - 01'TO_CHAR('2018-06-02', 'Dy - DD') → 'Sat - 02'
| Pattern | Description |
|---|---|
FMDay |
星期全名（Monday、Tuesday 等） |
MM |
月份（01 - 12） |
Mon |
月份縮寫（Jan、Feb 等） |
FMMonth |
月份全名（January、February 等） |
YY |
年的後 2 位（18、19 等） |
YYYY |
4 位年份（2018、2019 等） |
文件: https://www.postgresql.org/docs/9.6/functions-formatting.html
查詢
SELECT DISTINCT
order_date,
TO_CHAR(order_date,
'FMDay DD, FMMonth YYYY') AS format_1,
TO_CHAR(order_date,
'Dy DD Mon/YYYY') AS format_2
FROM orders
ORDER BY order_date ASC
LIMIT 3;
結果
order_date format_1 format_2
---------- ---------------------- ---------------
2018-06-01 Friday 01, June 2018 Fri 01/Jun 2018
2018-06-02 Saturday 02, June 2018 Sat 02/Jun 2018
2018-06-02 Sunday 03, June 2018 Sun 03/Jun 2018
視窗函式再探
SUM(...) OVER (...)：計算欄位的累積總和- 例：
SUM(registrations) OVER (ORDER BY registration_month)會算出註冊數的累積總和
- 例：
LAG(...) OVER (...)：取得前一列的值- 例：
LAG(mau) OVER (ORDER BY active_month)會回傳上個月的活躍使用者（MAU）
- 例：
RANK() OVER (...)：依排序結果為每列給排名- 例：
RANK() OVER (ORDER BY revenue DESC)依營收為使用者、餐館或月份排名
- 例：
查詢
SELECT
user_id,
SUM(meal_price * order_quantity) AS revenue
FROM meals
JOIN orders ON meals.meal_id = orders.meal_id
GROUP BY user_id
ORDER BY revenue DESC
LIMIT 3;
結果
user_id revenue
------- -------
18 626
76 553.25
73 537
查詢
WITH user_revenues AS (
SELECT
user_id,
SUM(meal_price * order_quantity) AS revenue
FROM meals
JOIN orders ON meals.meal_id = orders.meal_id
GROUP BY user_id)
SELECT
user_id,
RANK() OVER (ORDER BY revenue DESC)
AS revenue_rank
FROM user_revenues
ORDER BY revenue_rank DESC
LIMIT 3;
結果
user_id revenue_rank
------- ------------
18 1
76 2
73 3
實用函式總覽
使用 SQL 分析商業資料
