Windows関数やROLLUP Modifierでの集計

ROLLUP

Group byやWindows関数でも、勿論集計出来ますが。ROLLUPを利用する事でグループ毎に集計した値を取得すると同時に集計した結果も合わせて取得する事が出来ます。データ集計を簡素化する上で、Windows関数やGroup byを使い慣れておくと非常に便利です。MySQLでも8.0からWindows関数が利用出来る様になっています。Group by + Rollupは古いMySQL5.6等でも利用する事が可能です。

PostgreSQL

  • Windows Functionで集計
POC=# WITH Sales AS (
  SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
  SELECT 123, 1, 8.99 UNION ALL
  SELECT 456, 1, 4.56 UNION ALL
  SELECT 123, 2, 9.99 UNION ALL
  SELECT 789, 3, 1.00 UNION ALL
  SELECT 456, 3, 4.25 UNION ALL
  SELECT 789, 3, 0.99
)
SELECT
sku,
day,
SUM(price) over (partition by sku,day) as "total" from Sales;
 sku | day | total
-----+-----+-------
 123 |   1 | 18.98
 123 |   1 | 18.98
 123 |   2 |  9.99
 456 |   1 |  4.56
 456 |   3 |  4.25
 789 |   3 |  1.99
 789 |   3 |  1.99
(7 rows)

Time: 1.744 ms
  • Group by +Rollupで小計を追加
POC=# WITH Sales AS (
  SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
  SELECT 123, 1, 8.99 UNION ALL
  SELECT 456, 1, 4.56 UNION ALL
  SELECT 123, 2, 9.99 UNION ALL
  SELECT 789, 3, 1.00 UNION ALL
  SELECT 456, 3, 4.25 UNION ALL
  SELECT 789, 3, 0.99
)
SELECT
  sku,
  day,
  SUM(price) AS total
FROM Sales
GROUP BY ROLLUP(sku, day)
ORDER BY sku, day;
 sku | day | total
-----+-----+-------
 123 |   1 | 18.98
 123 |   2 |  9.99
 123 |     | 28.97
 456 |   1 |  4.56
 456 |   3 |  4.25
 456 |     |  8.81
 789 |   3 |  1.99
 789 |     |  1.99
     |     | 39.77
(9 rows)

Time: 1.350 ms
POC=#

上記の例ではテーブル自体をバッファー上に作成しているので以下の様な結果になっています。

Explain on PostgreSQL

参照: 9.22. ウィンドウ関数 / 7.2. テーブル式

備考:PostgreSQLにおけるWindows関数の処理

POC=#  select * from members limit 10;
 id | name | age | salary | description |   regist
----+------+-----+--------+-------------+------------
  1 | Mr.T |  20 |    100 |             | 2022-03-18
  2 | Mr.U |  21 |    101 |             | 2022-03-18
  3 | Mr.V |  22 |    102 |             | 2022-03-18
  5 | Mr.X |  22 |    100 |             | 2022-04-01
(4 rows)

Time: 1.052 ms
POC=# select id,name,age,salary,Row_Number() over(partition by age order by salary desc) as "row_number",
rank() over(partition by age order by salary desc) as "rank",
dense_rank() over(partition by age order by salary desc) as "dense_rank"
from members;
 id | name | age | salary | row_number | rank | dense_rank
----+------+-----+--------+------------+------+------------
  1 | Mr.T |  20 |    100 |          1 |    1 |          1
  2 | Mr.U |  21 |    101 |          1 |    1 |          1
  3 | Mr.V |  22 |    102 |          1 |    1 |          1
  5 | Mr.X |  22 |    100 |          2 |    2 |          2
(4 rows)

Time: 0.737 ms

POC=# select age,salary,
POC-# AVG(salary) over(partition by age) as "年齢毎の平均給与"
POC-# from members;
 age | salary |   年齢毎の平均給与
-----+--------+----------------------
  20 |    100 | 100.0000000000000000
  21 |    101 | 101.0000000000000000
  22 |    102 | 101.0000000000000000
  22 |    100 | 101.0000000000000000
(4 rows)

Time: 1.150 ms

MySQL

  • Windows Functionで集計
mysql> WITH Sales AS (
    -> SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
    -> SELECT 123, 1, 8.99 UNION ALL
    -> SELECT 123, 2, 9.99 UNION ALL
    -> SELECT 789, 3, 1.00 UNION ALL
    -> SELECT 456, 3, 4.25 UNION ALL
    -> SELECT 789, 3, 0.99
    -> )
    -> SELECT
    -> sku,
    -> day,
    -> SUM(price) over (partition by sku,day) as "total" from Sales;
+-----+-----+-------+
| sku | day | total |
+-----+-----+-------+
| 123 |   1 | 18.98 |
| 123 |   1 | 18.98 |
| 123 |   2 |  9.99 |
| 456 |   3 |  4.25 |
| 789 |   3 |  1.99 |
| 789 |   3 |  1.99 |
+-----+-----+-------+
6 rows in set (0.00 sec)
  • Group by +Rollupで小計を追加
mysql> WITH Sales AS (
  SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
  SELECT 123, 1, 8.99 UNION ALL
  SELECT 456, 1, 4.56 UNION ALL
  SELECT 123, 2, 9.99 UNION ALL
  SELECT 789, 3, 1.00 UNION ALL
  SELECT 456, 3, 4.25 UNION ALL
  SELECT 789, 3, 0.99
)
SELECT
  sku,
  day,
  SUM(price) AS total
FROM Sales GROUP by sku, day WITH ROLLUP
ORDER BY sku, day;

+------+------+-------+
| sku  | day  | total |
+------+------+-------+
|  123 |    1 | 18.98 |
|  123 |    2 |  9.99 |
|  123 | NULL | 28.97 |
|  456 |    1 |  4.56 |
|  456 |    3 |  4.25 |
|  456 | NULL |  8.81 |
|  789 |    3 |  1.99 |
|  789 | NULL |  1.99 |
| NULL | NULL | 39.77 |
+------+------+-------+
9 rows in set (0.01 sec)

mysql>

Explain

Explain Plan on MySQL

参照:12.21.1 Window 関数の説明 / 12.20.2 GROUP BY 修飾子

備考:MySQLにおけるWindows関数の処理1

mysql> select * from members limit 10;
+----+------+-----+--------+------------+
| id | name | age | salary | regist     |
+----+------+-----+--------+------------+
|  1 | Mr.T |  20 |    100 | 2022-03-03 |
|  2 | Mr.U |  21 |    101 | 2022-03-03 |
|  3 | Mr.V |  22 |    102 | 2022-03-03 |
|  4 | Mr.W |  23 |     90 | 2022-03-03 |
|  5 | Mr.X |  22 |    100 | 2022-04-01 |
+----+------+-----+--------+------------+
5 rows in set (0.00 sec)

mysql> select id,name,age,salary,Row_Number() over(partition by age order by salary desc) as "row_number",
    -> rank() over(partition by age order by salary desc) as "rank",
    -> dense_rank() over(partition by age order by salary desc) as "dense_rank"
    -> from members;
+----+------+-----+--------+------------+------+------------+
| id | name | age | salary | row_number | rank | dense_rank |
+----+------+-----+--------+------------+------+------------+
|  1 | Mr.T |  20 |    100 |          1 |    1 |          1 |
|  2 | Mr.U |  21 |    101 |          1 |    1 |          1 |
|  3 | Mr.V |  22 |    102 |          1 |    1 |          1 |
|  5 | Mr.X |  22 |    100 |          2 |    2 |          2 |
|  4 | Mr.W |  23 |     90 |          1 |    1 |          1 |
+----+------+-----+--------+------------+------+------------+
5 rows in set (0.00 sec)

mysql> select age,salary,
    -> AVG(salary) over(partition by age) as "年齢毎の平均給与"
    -> from members;
+-----+--------+--------------------------+
| age | salary | 年齢毎の平均給与        |
+-----+--------+--------------------------+
|  20 |    100 |                 100.0000 |
|  21 |    101 |                 101.0000 |
|  22 |    102 |                 101.0000 |
|  22 |    100 |                 101.0000 |
|  23 |     90 |                  90.0000 |
+-----+--------+--------------------------+
5 rows in set (0.00 sec)

備考:MySQLにおけるWindows関数の処理2

mysql> WITH RECURSIVE CTE AS
    -> (SELECT 1 AS a UNION ALL SELECT 1+a FROM CTE WHERE a<10)
    -> SELECT * FROM CTE;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
10 rows in set (0.00 sec)

Windows Functionに関して

参照:grouping function for Group by rollup in bigquery standard

カテゴリー: