ウィンドウ関数を使用してグループごとに複数の値を返す方法 - PolarDB

GROUP BY 関数は、クエリ対象のデータをグループ化し、各グループのクエリ結果を集計します。この関数は、グループごとに 1 行だけを返します。Group BY 関数とは異なり、ウィンドウ関数はグループごとに複数の行を返すことができます。出力の行数は、関数の入力の行数と同じです。ウィンドウ関数は、オンライン分析処理（OLAP）関数として知られています。このトピックでは、ウィンドウ関数の使用方法について説明します。

制限事項

ウィンドウ関数は、選択リストにのみ表示できます。
ウィンドウ関数は、集計関数が句に含まれている場合にのみ、集計関数と組み合わせて使用できます。
次のサンプル文では、SUM 関数は OVER 句を含まない集計関数です。この場合、文は実行できません。
```
SELECT SUM(NAME),COUNT() OVER(...) FROM <table_name>
```
上記のクエリ結果を取得する必要がある場合は、次の文を実行します。
```
SELECT SUM(NAME),WIN1 FROM (SELECT NAME,COUNT() OVER(...) AS WIN1 FROM <table_name>) alias
```

構文

FUNCTION OVER ([[PARTITION BY column_some1] [ORDER BY column_some2] [RANGE|ROWS BETWEEN start AND end]])

パラメータ	説明
`FUNCTION`	指定できるウィンドウ関数。有効値： OVER 句を含む次の集計関数 `SUM()` `COUNT()` `AVG()` `MAX()` `MIN()` 次の特殊なウィンドウ関数： `ROW_NUMBER()` `RANK()` `DENSE_RANK()` `PERCENT_RANK()` `CUME_DIST()` `FIRST_VALUE()` `LAST_VALUE()` `LAG()` `LEAD()` `NTH_VALUE()` 説明 `RANK()` または `DENSE_RANK()` を使用する場合は、`order by` がウィンドウ関数に含まれていることを確認してください。特殊な関数の詳細については、「ウィンドウ関数の説明」をご参照ください。次の関数は、特殊なウィンドウ関数としてのみ使用できます。 `PERCENT_RANK()` `CUME_DIST()` `FIRST_VALUE()` `LAST_VALUE()` `LAG()` `LEAD()` `NTH_VALUE()`
`[PARTITION BY column_some1]`	クエリ結果セットをパーティション分割する列を指定します。この句は、ウィンドウ関数が適用される入力行を異なるパーティションに分割するために使用されます。この句は、GROUP BY 句と同様の方法で機能します。説明 PARTITION BY 句では、複雑な式を参照できません。たとえば、`column_some1` は参照できますが、column_some1 + 1 は参照できません。
`[ORDER BY column_some2]`	ウィンドウ関数の入力行が計算される順序を指定します。この句は、ウィンドウ関数で入力行が計算される順序を定義します。説明 ORDER BY 句では、複雑な式を参照できません。たとえば、`column_some2` は参照できますが、column_some2 + 1 は参照できません。
`[RANGE\|ROWS BETWEEN start AND end]`	ウィンドウ関数のウィンドウフレーム。フレームを定義するには、RANGE または ROWS を使用できます。RANGE は、計算列の値の範囲内の行によってフレームが定義されることを示します。ROWS は、計算列の最初と最後の行の位置によってフレームが定義されることを示します。 BETWEEN start AND end 句を使用して、ウィンドウフレームの最初と最後の行を指定できます。 `の有効値：` `: フレームは現在の行から始まります。` `: フレームは N 行前から始まります。` `: フレームは最初の行から始まります。` `の有効値：` `: フレームは現在の行で終わります。` `: フレームは N 行後で終わります。` `: フレームは最後の行で終わります。`

例

次の生データは例で使用できます。

CREATE TABLE test_window (
    year INT NOT NULL,
    country VARCHAR(50) NOT NULL,
    product VARCHAR(100) NOT NULL,
    profit INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO test_window (year, country, product, profit) VALUES
(2001, 'Finland', 'Phone', 10),
(2000, 'Finland', 'Computer', 1500),
(2001, 'USA', 'Calculator', 50),
(2001, 'USA', 'Computer', 1500),
(2000, 'Singapore', 'Calculator', 75),
(2000, 'Singapore', 'Calculator', 75),
(2001, 'Singapore', 'Calculator', 79);

次の集計関数を使用して、各国の総利益を計算します。

SELECT
    country,
    SUM(profit) OVER (PARTITION BY country) sum_profit
FROM test_window;

次の結果が返されます。

+-----------+------------+
| country   | sum_profit |
+-----------+------------+
| Finland   |       1510 |
| Finland   |       1510 |
| USA       |       1550 |
| USA       |       1550 |
| Singapore |        229 |
| Singapore |        229 |
| Singapore |        229 |
+-----------+------------+

ウィンドウ関数としてのみ使用できる次の関数を使用して、国別にデータをグループ化し、各国の製品を利益の昇順でランク付けします。

SELECT
    year,
    country,
    product,
    profit,
    RANK() OVER (PARTITION BY country ORDER BY profit) AS rank
FROM test_window;

次の結果が返されます。

+------+-----------+------------+--------+------+
| year | country   | product    | profit | rank |
+------+-----------+------------+--------+------+
| 2001 | Finland   | Phone      |     10 |    1 |
| 2000 | Finland   | Computer   |   1500 |    2 |
| 2000 | Singapore | Calculator |     75 |    1 |
| 2000 | Singapore | Calculator |     75 |    1 |
| 2001 | Singapore | Calculator |     79 |    3 |
| 2001 | USA       | Calculator |     50 |    1 |
| 2001 | USA       | Computer   |   1500 |    2 |
+------+-----------+------------+--------+------+

ROWS 句を含む次の文を実行して、現在のウィンドウの profit 列の値の合計を計算します。

SELECT 
    year,
    country,
    profit,
    SUM(profit) OVER (PARTITION BY country ORDER BY 'year' ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_win 
FROM test_window;

次の結果が返されます。

+------+-----------+--------+---------+
| year | country   | profit | sum_win |
+------+-----------+--------+---------+
| 2001 | Finland   |     10 |      10 |
| 2000 | Finland   |   1500 |    1510 |
| 2000 | Singapore |     75 |      75 |
| 2000 | Singapore |     75 |     150 |
| 2001 | Singapore |     79 |     229 |
| 2001 | USA       |     50 |      50 |
| 2001 | USA       |   1500 |    1550 |
+------+-----------+--------+---------+