制作管理层报告

使用 SQL 分析业务数据

Michel Semaan

Data Scientist

可读性

日期：使用易读格式（August 2018，而非 2018-08-01）
数字：最多保留两位小数（98.76，非 98.761234）
表形：尽量将长表转为宽表，按日期透视
排序：别忘了排序！

管理层报告 - 查询

查询

SELECT
  eatery,
  TO_CHAR(order_date, 'MM-Mon YYYY') AS delivr_month,
  COUNT(DISTINCT order_id) AS count_orders
FROM meals
JOIN orders ON meals.meal_id = orders.meal_id
WHERE order_date >= '2018-10-01'
GROUP BY eatery, delivr_month
ORDER BY eatery, delivr_month;

结果

eatery               delivr_month  count_orders
-----------------  ------------  ------------
Bean Me Up Scotty  10-Oct 2018   709         
Bean Me Up Scotty  11-Nov 2018   1143        
Bean Me Up Scotty  12-Dec 2018   2168        
Burgatorio         10-Oct 2018   679         
...                ...           ...

管理层报告（II）

WITH eatery_orders AS (
  SELECT
    eatery,
    TO_CHAR(order_date, 'MM-Mon YYYY') AS delivr_month,
    COUNT(DISTINCT order_id) AS count_orders
  FROM meals
  WHERE order_date >= '2018-10-01'
  JOIN orders ON meals.meal_id = orders.meal_id
  GROUP BY eatery, delivr_month)

SELECT
  eatery,
  delivr_month,
  RANK() OVER
    (PARTITION BY delivr_month
     ORDER BY count_orders DESC) :: INT AS orders_rank
FROM eatery_orders
ORDER BY eatery, delivr_month;

eatery               delivr_month  orders_rank
-----------------  ------------  -----------
Bean Me Up Scotty  10-Oct 2018     2          
Bean Me Up Scotty  11-Nov 2018     4          
Bean Me Up Scotty  12-Dec 2018   2          
Burgatorio         10-Oct 2018     4            
...                ...           ...

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM CROSSTAB($$
  ...
$$) AS ct (eatery TEXT,
           "10-Oct 2018" INT,
           "11-Nov 2018" INT,
           "12-Dec 2018" INT)
ORDER BY eatery ASC;

管理层报告 - 结果

eatery                  Q2 2018  Q3 2018  Q4 2018
----------------------  -------  -------  -------
The Moon Wok            1        1        1      
Bean Me Up Scotty       3        2        2      
Leaning Tower of Pizza  4        4        3      
Burgatorio              2        3        4      
Life of Pie             5        5        5

制作管理层报告

使用 SQL 分析业务数据