建立複雜計算

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 報表製作