ウィンドウ関数
ダウンロード
フォーカスモード
フォントサイズ
この記事では、ウィンドウ関数の基本構文と例を紹介します。
ウィンドウ関数は、指定された複数行のデータに対して計算を実行し、計算結果を返すために使用されます。GROUP BY との違いは、計算結果を各行に付加するだけで、行自体を結合しない点にあります。
構文
window_function (expression) OVER ([ PARTITION BY part_key ][ ORDER BY order_key ][ { ROWS | RANGE } BETWEEN frame_start AND frame_end ] )
パラメータ説明
パラメータ | 説明 |
window_function | ウィンドウ値の計算方法です。一般的な集計関数、ソート関数、値取得関数をサポートします。 |
PARTITION BY | ウィンドウパーティションの基準です。 |
ORDER BY | ウィンドウパーティション内の複数行データのソート基準です。 |
{ ROWS ｜RANGE } BETWEEN frame_start AND frame_end | ウィンドウフレーム、つまりウィンドウパーティション内で各行のデータを計算する際に使用されるデータ範囲（行）です。未指定の場合は、ウィンドウパーティション内のすべての行を表します。 使用例： rows between current row and 1 following：現在の行および次の1行。 rows between 1 preceding and current row：現在の行および前の1行。 rows between 1 preceding and 1 following：前の1行から次の1行まで（合計3行）。 rows between current row and unbounded following：現在の行および後続のすべての行。 rows between unbounded preceding and current row：現在の行および前のすべての行。 |
汎用集計関数
ソート関数
ソート関数ではウィンドウフレームを使用できません。
関数 | 説明 |
rank() | 各ウィンドウパーティションに対してそれぞれランキングを実行し、値を返します。同じ値は同じランキングを持つため、ランキングは連続しない場合があります。例えば、同じ値が2つありランキングが1の場合、次の値のランキングは3になります。 |
dense_rank() | rank() と似ていますが、違いはこの関数のランキングが連続していることです。例えば、同じ値が2つありランキングが1の場合、次の値のランキングは2になります。 |
cume_dist() | ウィンドウパーティション内の各値の累積分布を計算します。これは、ウィンドウパーティション内で現在の値以下の行数がウィンドウ内の総行数に占める割合です。 |
ntile(n) | ウィンドウパーティション内のデータを順序に従ってnグループに分割します。パーティション内の行数がnグループに均等に分割されない場合、最初のグループから順に、各グループに残りの値を1つずつ割り当てます。例えば、データが6行あり、4グループに分割する必要がある場合、各行データのグループ番号は1、1、2、2、3、4になります。 |
percent_rank() | 各行データのウィンドウパーティション内におけるパーセントランクを計算します。計算式は(r - 1) / (n - 1)です。ここで、rはrank()で取得したランキング値、nはウィンドウパーティション内の総行数です。 |
row_number() | ウィンドウパーティション内の各行データ（ソート規則に従ってソート後）のシーケンス番号を返します。1から始まり、各行は一意の値です。 |
値取得関数
関数 | 説明 |
first_value(key) | ウィンドウパーティション内の最初の値を返します。 |
last_value(key) | ウィンドウパーティション内の最後の値を返します。 |
nth_value(key, offset) | ウィンドウパーティション内で指定したオフセット後の値を返します。offsetは1から計算します。offsetがnullの場合、またはウィンドウパーティション内の行数を超えた場合はnullを返します。offsetに0または負の数を指定することはできません。 |
lead(key[, offset[, default_value]]) | ウィンドウパーティション内の現在行から後方に指定したオフセット分移動した行の値を返します。offsetは0からカウントされ、0は現在行を指します。offsetのデフォルト値は1です。offsetがnullの場合はnullを返します。オフセット後の行がウィンドウパーティションを超える場合はdefault_valueを返し、default_valueが指定されていない場合はnullを返します。 この関数を使用する際は、ウィンドウパーティション内のソート順序（ORDER BY）を指定する必要があります。また、ウィンドウフレームは使用できません。 |
lag(key[, offset[, default_value]]) | lead(key[, offset[, default_value]])関数と似ていますが、この関数は現在行から前方に指定したオフセット後の値を返します。 |
例
例1：直近1時間における各インターフェースで最も遅い5つのリクエストとそのリクエストIDをクエリ
時間範囲として直近1時間を選択し、以下のクエリと分析ステートメントを実行します。ここで、actionはインターフェース名、timeCostはインターフェース応答時間、seqIdはリクエストIDです。
クエリおよび分析ステートメント
* | select * from (select action,timeCost,seqId,rank() over (partition by action order by timeCost desc) as ranking order by action,ranking,seqId) where ranking<=5 limit 10000
クエリおよび分析結果
action | timeCost | seqId | ranking |
ModifyXXX | 151 | d75427b3-c562-6d7a-354f-469963aab689 | 1 |
ModifyXXX | 104 | add0d353-1099-2c73-e9c9-19ad02480474 | 2 |
CreateXXX | 1254 | c7d591f0-2da6-292c-8abf-98a0716ff8c6 | 1 |
CreateXXX | 970 | d920cf7a-7e7b-524b-68e9-a957c454c328 | 2 |
CreateXXX | 812 | 16357f6d-33b3-83ea-0ae3-b1a2233d4858 | 3 |
CreateXXX | 795 | 0efdab5e-af5f-4a4a-0618-7961420d17a1 | 4 |
CreateXXX | 724 | fb0481f2-dcfc-9500-cb44-a139b774aceb | 5 |
DescribeXXX | 55242 | 4129dcda-46d7-9213-510e-f58cba29daf5 | 1 |
DescribeXXX | 17413 | e36cdeb0-cbc5-ce2b-dec7-f485818ab6c7 | 2 |
DescribeXXX | 10171 | cd6228f7-4644-ba45-f539-0fce7b09455b | 3 |
DescribeXXX | 9475 | 48b6f6e3-6d08-5a31-cd68-89006a346497 | 4 |
DescribeXXX | 9337 | 940b5398-e2ae-9141-801b-b7f0ca548875 | 5 |
例2：アプリケーションスループットの3日移動平均値の変化傾向をクエリ
查询和分析的时间范围为最近7天、并执行如下查询和分析语句、其中 pv 为每天的应用吞吐量、avg_pv_3 是3天移动平均后的应用吞吐量。
クエリおよび分析ステートメント
* | select avg(pv) over(order by analytic_time rows between 2 preceding and current row) as avg_pv_3,pv,analytic_time from (select histogram( cast(__TIMESTAMP__ as timestamp),interval 1 day) as analytic_time, count(*) as pv group by analytic_time order by analytic_time)
クエリおよび分析結果
フィードバック