複雑な計算の構築

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 は集計関数か、GROUP BY 句に含める必要があります。

多層計算

既存の集計に再集計
サブクエリを活用

多層計算の例

手順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;

複雑な計算の計画

ウィンドウ関数の並び順は?
多層計算で集計が2回必要か?

Let's practice!

SQLでのレポーティング