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

1. 背景

本日、ニセ大学3年生の月例試験の結果が出ました。ここで各生徒の試験結果をお知らせします。

次に、各学生の生活費についてお知らせします。

次に、上記のテストのスコアと生活費の記録を使用して、MySQL で簡単な分析を行います。

もちろん、この記事のタイトルからもそれがわかります。この記事では、このデータを使用して、SQL の「ウィンドウ関数」の使用方法を説明します。

これは、将来 Hive または Oracle データベースを学習する場合や、データ分析の面接を受ける場合に非常に重要な知識ポイントになります。

2. テーブル作成ステートメントとデータ挿入

テーブルを作成する

テーブル exam_score を作成します(
    sname varchar(20)、
    年齢 int、
    件名varchar(20)、
    スコアvarchar(20)
)文字セット=utf8;

# ----------------------- #

テーブルcost_fee(を作成する
    sname varchar(20)、
    購入日付varchar(20),
    購入コスト int
)文字セット=utf8;

データの挿入

exam_score値に挿入
（「張三」18歳、「中国人」90歳）
（『張三』18歳、『数学』80歳）
（「張三」18歳、「英語」70歳）
（「李思」、21歳、「中国人」、88歳）、
（『李斯』21歳、『数学』78歳）
（「李思」、21歳、「英語」、71歳）、
（「王武」18歳、「中国人」95歳）
（『王武』18歳、『数学』83歳）
（「王武」18歳、「英語」71歳）
（「趙劉」、19歳、「中国人」、98歳）、
（『趙劉』19歳、『数学』90歳）
（「趙劉」、19歳、「英語」、80歳）
# ----------------------- #
cost_fee値に挿入
('張三','2019-01-01',10),
('張三','2019-03-03',23),
('張三','2019-02-05',46),
('李思','2019-02-02',15),
('李思','2019-01-07',50),
('李思','2019-03-04',29),
('王武','2019-03-08',62),
('王武','2019-02-09',68),
('王武','2019-01-11',75),
('趙劉','2019-02-08',55),
('趙劉','2019-03-10',12),
('趙劉','2019-01-12',80);

3. ウィンドウ関数分類の概要

「ウィンドウ関数」の応用について正式に議論する前に、まず「ウィンドウ関数」の基本を確認します。ウィンドウ関数は次のカテゴリに分類できます。

集計関数 + over() の組み合わせ。

ソート関数 + over() の組み合わせ。

ntile() 関数 + over() の組み合わせ。

オフセット関数 + over() の組み合わせ。

各カテゴリーの機能は何ですか?下のマインドマップをご覧ください。

over() には、説明が必要なよく使われるキーワードが 2 つあります。次のように：

パーティション by + フィールド: これは、 「グループ化」に使用されるキーワードである group by キーワードと考えることができます。

order by + フィールド: これは理解しやすく、「並べ替え」に使用されるキーワードです。

4. ウィンドウ関数の適用

上記では、よく使われる「ウィンドウ関数」をいくつか紹介してきました。ここでは、記事の冒頭で作成したデータを使用して、「ウィンドウ関数」の応用についてお話します。

各機能の意味については、それぞれのケースを通してまとめてもらえればと思うので、ここでは詳しく書きません。

1. 集計関数 + over()

①各生徒の得点と平均点を計算する

選択 
	名前
    、主題
    、スコア
    ,avg(スコア) over(sname によるパーティション) as avg_score
から
	試験スコア

結果は次のとおりです。

②1月から3月までの各生徒の消費量と総消費量を計算する

選択
	名前
    、購入日
    、購入コスト
    ,sum(buycost) over(partition by sname) as sum_cost
から
	費用

結果は次のとおりです。

③1月から3月までの各生徒の消費量と累計消費量を計算する

選択
	名前
    、購入日
    、購入コスト
    ,sum(buycost) over(partition by sname order by buydate) as sum_cost
から
	費用

結果は次のとおりです。

注: ②③を組み合わせると、partition by を order by と組み合わせた場合と、order by なしで組み合わせた場合で、まったく異なる結果が生成されることがわかります。 1 つはグループの合計値を求める方法 (order by なし)、もう 1 つはグループの累積合計を求める方法 (order by あり) です。

2. ソート関数 + over()

① 各科目の順位を計算します。同じ得点でも順位が異なり、順番に順位が上がります。

選択
	名前
	、主題
	、スコア
    ,row_number() over(partition by subject order by score) rank1
から
	試験スコア

結果は次のとおりです。

② 各科目の順位を計算します。同点の場合は順位はそのまま、それ以外は順位が上がります。

選択
	名前
	、主題
	、スコア
    ,rank() over(主題によるパーティション スコアによる順序) rank1
から
	試験スコア

結果は次のとおりです。

③ 各科目の順位を計算します。同点のものは同順位、残りは昇順で順位付けします。

選択
	名前
	、主題
	、スコア
    ,dense_rank() over(件名によるパーティション、スコア順) rank1
から
	試験スコア

結果は次のとおりです。

3. ntile() 関数 + over() の組み合わせ

ntile() 関数は少し場違いな感じがして、どのカテゴリに置けばよいかわかりません。この機能は主に「データのセグメンテーション」に使用されます。この関数の用途があるとすれば、それは、前述の row_number() 関数と同様に、データを並べ替えることができることです。

① exam_scoreテーブル全体を分割する

選択
	名前
	、主題
	、スコア
    ,ntile(4) ランク1以上
から
	試験スコア

結果は次のとおりです。

信じられないなら試してみてください。ntile() にどんな数字を書いても動作するようです。

② exam_scoreテーブルを科目グループごとに分割する

選択
	名前
	、主題
    、スコア
    ,ntile(4) over(主題による分割) rank1
から
	試験スコア

結果は次のとおりです。

グループごとに分けたとしても、スコアがソートされていないため意味がないことがわかります。

③ exam_scoreテーブルでは、スコアをソートし、科目ごとにグループ化して分割します（最も便利です）

選択
	名前
	、主題
    、スコア
    ,ntile(4) over(主題によるパーティション スコア順) rank1
から
	試験スコア

結果は次のとおりです。

注: この使用法を注意深く観察すると、基本的には row_number() 関数と同等であり、効果も同じであることがわかります。

4. オフセット関数 + over() の組み合わせ

①生徒ごとの「前回購入時間」と「次回購入時間」を表示する

注: 最初の日には「最初の購入」と表示され、最後の日には「最後の購入」と表示されます。

選択
	名前
	、購入日
    ,lag(buydate,1,'first day') over(partition by sname order by buydate) は最終購入時間として、lead(buydate,1,'last day') over(partition by sname order by buydate) は次回購入時間として
	費用

結果は次のとおりです。

②本日時点の各生徒の「初回購入時刻」と「最終購入時刻」

選択
	名前
	、購入日
    、first_value(buydate) over(partition by sname order by buydate) は最初の購入日として、last_value(buydate) over(partition by sname order by buydate) は最後の購入日として
	費用

結果は次のとおりです。

③各生徒の「初回購入時間」と「最終購入時間」を表示する

注意：ここでは「現時点」とは書いていませんので、②③の違いに注意してください。ニーズが異なれば結果も異なります。

選択
	名前
	、購入日
    、first_value(buydate) over(partition by sname order by buydate) は最初の購入日として、last_value(buydate) over(partition by sname ) は最後の購入日として
	費用

結果は次のとおりです。

以上は、MySQLの実用的なウィンドウ関数SQLを使用して、クラスの生徒のテストの成績と生活費を分析する詳細です。スコアと消費のSQLウィンドウ関数分析の詳細については、123WORDPRESS.COMの他の関連記事に注目してください。