MySQL データベースの基礎 SQL ウィンドウ関数の例の分析チュートリアル

導入

以前、Huang は「MySQL ウィンドウの実践」というタイトルの記事を書きました (記事の内容は次のとおりです)。しかし、その内容のほとんどは、詳細な説明がなく、実践的な演習に基づいていました。

ポータル: MySQL 実践ウィンドウ関数 クラスの生徒のテストの成績と生活費の SQL 分析

そこで、私は美しい少女Yueyaにプライベートメッセージを送り、前回の記事をうまく補完する「ウィンドウ関数の基礎」というタイトルの記事を書いてもらえないかと尋ねました。ほら、彼女はそれをとても早く書き終えて、今日はそれを皆さんと共有して、皆さんとコミュニケーションを取り、学ぶことを目指しています!

以下は、大きな画像を添付した、Crescent Moon のジョーク紹介です。

導入

ウィンドウ関数 (「ウィンドウ オープニング関数」とも呼ばれます) は、MySQL 8.0 以降で使用できます。

Likou で問題を解いていると、より難しい問題ではウィンドウ関数の適用が含まれることが多いことに気づきます。ウィンドウ関数は、SQL レベルが熟練レベルに達しているかどうかをテストするための基準であると言えます。

ウィンドウ関数の形式は、集計関数 + over() です。

ウィンドウは、over() 括弧内に区切られた内容を表します。この内容はウィンドウ関数のスコープであり、つまり、操作されるデータは over() のスコープ内にあります。

ウィンドウ関数の私の個人的な理解は、ソース データに対してスライディング ウィンドウを開くことです。ウィンドウが移動すると、移動平均、グループ ソートなど、ウィンドウ内のデータに対して追加の計算を実行できます。ウィンドウは 1 行、複数行、またはすべての行にすることができます。

ウィンドウ関数は、複数のデータ グループに対して同期ソートや集計などの操作を実行したり、group by 句または where 処理後の結果を操作したりすることもできます。これらは、select 句にのみ記述できます。

Soul Painter はオンラインで、Excel を使用してウィンドウ関数の簡単なデモが作成されています。

集計関数 + over()

基本的な構文:

sum/avg(処理されたフィールドの名前) over(グループ化されたフィールドの名前によるパーティション、ソートされたフィールドの名前による順序、…と…の間の行)

意味: パーティションごとにグループ化した後、各グループを合計または平均することを示します。

--前の6行と現在の行の間の行を含む
--現在の行と次の3行の間の行が含まれます:現在の行と次の3行の間の行
--この行と前のすべての行が含まれます: 無制限の前の行と現在の行の間の行
--この行とそれ以降のすべての行が含まれます: 現在の行と次の無制限の行の間の行
--最初の3行から次の行まで（合計5行のデータ）：前の3行と次の1行の間

同様の文法構造を持つ、max、min、count などの集計関数は他にもあります。

ソート関数 + over()

row_number()、rank()、dense_rank() の 3 つの関数はすべて、選択クエリの結果を並べ替えます。それらの違いを見てみましょう。

row_number() : 繰り返しのない連続ソートの場合、1から始まり、ソートのためにクエリされたデータに対して繰り返しのないシリアル番号が生成されます。

基本構文 - row_number() over (昇順/降順に並べ替えるフィールドによる順序)

rank() : ジャンプソートです。同じ結果の2つのデータを並べて、次のデータが占めていた順位を空けます。つまり、同じ順位が1つの場所を占めることになります。

基本構文 - rank() over (昇順/降順に並べ替えるフィールドによる順序)

連続ソートを繰り返し行う場合、同じ結果の2つのデータが隣り合って配置され、次のデータが占める順位は空けられない、つまり同じ順位dense_rank()場所を占めない。

基本構文 - dense_rank() over (昇順/降順に並べ替えるフィールドによる順序)

これら 3 つの関係を図で示します。

ntile() 関数 + over()

基本構文: ntile(n) over(partition by…order by…)。ここで、n は分割するセグメントの数を表します。

ntile(n) は、グループ化されたデータを n 個に均等に分割するために使用されます。分割されたグループの数が等しくない場合、最初のグループにより多くのデータが割り当てられます。

ntile() 関数は通常、たとえば、学年内の成績上位 10% の生徒を検索するために使用されます。次に、n は 10 とみなされ、where は最初のデータ グループをフィルター処理するために使用されます。

オフセット関数 + over()

基本構文 1: 最初の N 行: lead(str, n, default) over(partition by …order by …)

基本構文 2: N 行後: lag(str, n, default) over(partition by …order by …)

str はフィールド名を表し、n はデータの最初/最後の n 行を表します。デフォルト値は 1 です。default は、値の範囲がテーブル全体の戻り値を超える場合は空白のままにできることを意味します。空白のままにすると、デフォルトで N/A が返されます。

オフセット関数は、同じフィールドの最初の N 行または最後の N 行のデータを別の列として抽出するために使用されます。ここで、リードは最初の N 行を表し、ラグは最後の N 行を表すことに注意してください。

上記は、MySQL データベースの基礎ウィンドウ関数例分析チュートリアルの詳細な内容です。MySQL ウィンドウ関数の基礎の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。