常用函数概览

使用 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'

模式	说明
`FMDay`	完整星期名（`Monday`、`Tuesday` 等）
`MM`	月（`01` - `12`）
`Mon`	月份缩写（`Jan`、`Feb` 等）
`FMMonth`	完整月份名（`January`、`February` 等）
`YY`	年份后两位（`18`、`19` 等）
`YYYY`	四位年份（`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 分析业务数据