构建复杂计算

SQL 报表制作

Tyler Pernes

Learning & Development Consultant

方法

窗口函数
分层计算

窗口函数

引用表中的其他行。

窗口函数

引用表中的其他行。

窗口函数

引用表中的其他行。

窗口函数语法

SUM(value) OVER (PARTITION BY field ORDER BY field)

窗口函数语法

SUM(value) OVER (PARTITION BY field ORDER BY field)

窗口函数语法

SUM(value) OVER (PARTITION BY field ORDER BY field)

PARTITION BY = 计算范围
ORDER BY = 运行计算的行序

窗口函数示例

铜牌总数

SELECT 
    country_id, 
    athlete_id, 
    SUM(bronze) OVER () AS total_bronze
FROM summer_games;

+-------------+-------------+---------------+
| country_id  | athlete_id  | total_bronze  |
|-------------|-------------|---------------|
| 11          | 77505       | 141           |
| 11          | 11673       | 141           |
| 14          | 85554       | 141           |
| 14          | 76433       | 141           |
+-------------+-------------+---------------+

窗口函数示例

各国铜牌数

SELECT 
    country_id, 
    athlete_id, 
    SUM(bronze) OVER (PARTITION BY country_id) AS total_bronze
FROM summer_games

+-------------+-------------+---------------+
| country_id  | athlete_id  | total_bronze  |
|-------------|-------------|---------------|
| 11          | 77505       | 12            |
| 11          | 11673       | 12            |
| 14          | 85554       | 5             |
| 14          | 76433       | 5             |
+-------------+-------------+---------------+

窗口函数类型

SUM()
AVG()
MIN()
MAX()

窗口函数类型

LAG() 与 LEAD()

窗口函数类型

LAG() 与 LEAD()

窗口函数类型

ROW_NUMBER() 与 RANK()

聚合上的窗口函数

original_table
+----------+-----------+---------+ 
| team_id  | player_id | points  |
|----------|-----------|---------|    
| 1        | 4123      | 3       |
| 1        | 5231      | 6       |
| 2        | 8271      | 5       |
+----------+-----------+---------+

desired_report
+----------+-------------+---------------+ 
| team_id  | team_points | league_points |
|----------|-------------|---------------|    
| 1        | 9           | 43            |
| 2        | 12          | 43            |
| 3        | 22          | 43            |
+----------+-------------+---------------+

聚合上的窗口函数

最终查询

SELECT
    team_id,
    SUM(points) AS team_points,
    SUM(SUM(points)) OVER () AS league_points
FROM original_table
GROUP BY team_id;

聚合上的窗口函数

SELECT
    team_id,
    SUM(points) AS team_points,
    SUM(points) OVER () AS league_points
FROM original_table
GROUP BY team_id;

ERROR: points must be an aggregation or appear in a GROUP BY statement.

分层计算

聚合已有的聚合
利用子查询

分层计算示例

步骤 1：各国铜牌总数

SELECT country_id, SUM(bronze) as bronze_medals
FROM summer_games
GROUP BY country_id;

步骤 2：转为子查询并取最大值

SELECT MAX(bronze_medals)
FROM
  (SELECT country_id, SUM(bronze) as bronze_medals
  FROM summer_games
  GROUP BY country_id) AS subquery;

规划复杂计算

窗口函数的排序？
两次聚合与分层计算？

让我们来练习！

SQL 报表制作