集約関数のウィンドウ化難易度 ★★★★☆
各行に部署平均を添える
部署に所属している社員それぞれについて、氏名・給与と、その社員が所属する部署の平均給与を取り出してください。退職者も含めます。平均は小数を四捨五入して整数にします。行はまとめず、社員 1 人につき 1 行にしてください。列名は平均が heikin で、列は「name, salary, heikin」の順、社員番号の小さい順に並べてください。
この書き方が使えない製品があります そのまま使える: PostgreSQL / MySQL / MariaDB / SQLite / Oracle Database
- SQL Server では使えません（round(x)（引数 1 つ））
- IBM Db2 では使えません（集約関数の OVER（累計など）・ウィンドウ関数の PARTITION BY・round(x)（引数 1 つ））
- 集約関数に OVER を付けると、行をまとめずに集計値を各行へ付けられます。ここが GROUP BY との決定的な違いです。
- ⚠️ round(x) は SQL Server と Db2 では桁数の指定が必須です。round(x, 0) と書きます。
● 起動中…
この問題で使えるテーブル（名前をタップすると入力できます）
7 行
| 列 | 型 | NULL |
|---|---|---|
| 🔑 | int | 不可 |
| text | 不可 | |
| int | 可 |
データを見る（先頭 5 行）
| busho_id | busho_name | parent_busho_id |
|---|---|---|
| 1 | 経営 | NULL |
| 2 | 開発 | 1 |
| 3 | 営業 | 1 |
| 4 | 管理 | 1 |
| 5 | 基盤 | 2 |
18 行
| 列 | 型 | NULL |
|---|---|---|
| 🔑 | int | 不可 |
| text | 不可 | |
| int | 可 | |
| int | 可 | |
| numeric(10,0) | 不可 | |
| date | 不可 | |
| date | 可 |
データを見る（先頭 5 行）
| shain_id | name | busho_id | joushi_id | salary | hired_on | taishoku_on |
|---|---|---|---|---|---|---|
| 1 | 鈴木 | 1 | NULL | 980000 | 2014-04-01 | NULL |
| 2 | 佐藤 | 2 | 1 | 720000 | 2016-10-01 | NULL |
| 3 | 田中 | 2 | 2 | 520000 | 2019-04-01 | NULL |
| 4 | 中村 | 2 | 2 | 530000 | 2020-04-01 | NULL |
| 5 | 小林 | 2 | 2 | 445050 | 2024-04-01 | NULL |
解説
結論
集約関数に OVER を付けると、行をまとめずに集計値を添えられます。
なぜ
GROUP BY は 1 グループ 1 行にまとめるので、個々の行の情報が失われます。ウィンドウ関数は元の行を保ったまま、指定した範囲での集計値を各行に付けます。「自分の値とグループの平均を並べて比べる」ことが 1 回の走査でできます。
同じことは相関副問い合わせでも書けますが、行ごとに副問い合わせを評価するため、行数が増えると差が出ます。
よくある間違い
①PARTITION BY を書かず全体平均になる ②GROUP BY と混同して行がまとまってしまう。
根拠（一次情報）
- PostgreSQL 18 マニュアル: ウィンドウ関数一次情報・確認 2026-08-01
- PostgreSQL 18 マニュアル: ウィンドウ関数一覧一次情報・確認 2026-08-01