Share
Embed
君はWindow関数を知っているか
Window関数について解説するスライドです。
2019/01/16
発表イベント
- テクテクテック #7 DB勉強会(新機能や運営でのあれこれ)https://spookies.connpass.com/event/104684/
- KyotoLT 第23回 https://kyotolt.connpass.com/event/118256/
Masa
January 16, 2019
Transcript
56 slides
-
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 君はWindow関数 を知っている
か!? テクテクテック#7 DB勉強会 2019/01/16 @masayuki14 -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 目次 自己紹介
✓ Window関数とは ✓ 実践Window関数 ✓ まとめ ✓ -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 自己紹介 もりさきまさゆき
✓ @masayuki14 ✓ Follow me !! -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 自己紹介 プロ主夫
妻1人 (フルタイム) 幼児2人 ✓ フリーランス (Web系パートタイム) ✓ データベーススペシャリスト ✓ ✓ コミュニティ はんなりPython (第3金曜開催@京都) ✓ OSS Gate (京都, 大阪, 東京) ✓ ✓ スプーキーズアンバサダー ✓ -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 スプーキーズ@京都 Web系システム
✓ ソーシャルゲーム開発 ✓ ボードゲーム制作 ✓ Webエンジニア積極採用中!! -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 スプーキーズ@京都 勉強会
テクテクテック 2019/04/10 サーバ監視や負荷テストどうやって るの? ✓ ✓ もくもく会はじめました 2/28(木) 19:00〜 ✓ いつでも来てね!! ✓ ✓ -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 Window関数とは 標準SQL規格
SQL:2003にて追加された 標準機能 -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 Window関数とは MySQL8.0でサポート
主要RDBMSで利用可能 Oracle, SQL Server ✓ PostgreSQL, MySQL ✓ -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 Window関数とは Window
の意味は? × 窓 ✓ ◦ 範囲 幅 ✓ -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 Window関数とは 範囲を指定して
違う行を自分の行に もってくる ✓ 集約結果を自分の行に もってくる ✓ -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 Window関数とは Window関数を使ったSQLの例
SELECT customer_id, amount, RANK() OVER ( PARTITION BY customer_id ORDER BY amount DESC) AS rk FROM payment; -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 Window関数とは 実はこれの省略形(名前付き構文)
SELECT customer_id, amount, RANK() OVER w AS rk FROM payment WINDOW w AS (PARTITION BY customer_id ORDER BY amount DESC); -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 Window関数とは Windowの使い回し
SELECT customer_id, amount, RANK() OVER w AS rk, MAX(amount) OVER w AS max FROM payment WINDOW w AS (PARTITION BY customer_id ORDER BY amount DESC); -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 Window関数とは Window関数の書き方
SELECT 集約関数() OVER (範囲指定) FROM table OVER() があればWindow関数 -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 Window関数とは Window関数の書き方
集約関数 ROW_NUMBER(), FIRST_VALUE() ✓ MAX(), MIN(), AVG() ✓ 範囲指定 PARTITION BY, ORDER BY ✓ フレーム句 ✓ -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 Window関数とは 1枚でわかるWindow関数
- https://codezine.jp/article/detail/ 11115 -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 Window関数とは Window関数の3つの機能
PARTITION BY 句によるレ コード集合の分割 ✓ ORDER BY 句によるレコード の順序づけ ✓ フレーム句によるカレントレ コードを中心としたサブセット 定義 ✓ -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 まずは
PARTITION BY を おさえよう -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 単純な例
teams table +-----------+------+------+ | member | team | age | +-----------+------+------+ | 富士崎 | A | 39 | | 西塚 | A | 45 | | 西崎 | A | 24 | | 逸見 | B | 26 | | 岡村 | B | 18 | | 東野 | C | 50 | | 各務原 | D | 27 | | 犬山 | D | 28 | | 鳥羽 | D | 33 | | 桃山 | D | 28 | +-----------+------+------+ -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 team
ごとに分割して行番号を取得する SELECT member, team, age, ROW_NUMBER() OVER ( PARTITION BY team ORDER BY age) AS num FROM teams; -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 team
ごとに分割して行番号を取得する +-----------+------+------+-----+ | member | team | age | num | +-----------+------+------+-----+ | 西崎 | A | 24 | 1 | | 富士崎 | A | 39 | 2 | | 西塚 | A | 45 | 3 | | 岡村 | B | 18 | 1 | | 逸見 | B | 26 | 2 | | 東野 | C | 50 | 1 | | 各務原 | D | 27 | 1 | | 桃山 | D | 28 | 2 | | 犬山 | D | 28 | 3 | | 鳥羽 | D | 33 | 4 | +-----------+------+------+-----+ -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 team
ごとに分割して順位を取得する SELECT member, team, age, RANK() OVER ( PARTITION BY team ORDER BY age) AS rk FROM teams; -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 team
ごとに分割して順位を取得する +-----------+------+------+----+ | member | team | age | rk | +-----------+------+------+----+ | 西崎 | A | 24 | 1 | | 富士崎 | A | 39 | 2 | | 西塚 | A | 45 | 3 | | 岡村 | B | 18 | 1 | | 逸見 | B | 26 | 2 | | 東野 | C | 50 | 1 | | 各務原 | D | 27 | 1 | | 犬山 | D | 28 | 2 | | 桃山 | D | 28 | 2 | | 鳥羽 | D | 33 | 4 | +-----------+------+------+----+ -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 PARTITION
BY で部分集合を 作って その中で集約関数使う -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 少し込み入った例
records table +------+------------+---------+--------+------------+ | team | competitor | score | is_win | played_at | +------+------------+---------+--------+------------+ | A | B | 10 - 3 | 1 | 2018-11-20 | | A | C | 10 - 8 | 1 | 2018-11-24 | | A | B | 8 - 5 | 1 | 2018-10-05 | | A | D | 3 - 9 | 0 | 2018-09-28 | | A | C | 4 - 5 | 0 | 2018-12-04 | | A | B | 7 - 9 | 0 | 2018-11-05 | | A | D | 20 - 17 | 1 | 2018-12-06 | | A | D | 12 - 11 | 0 | 2018-10-14 | | A | B | 8 - 4 | 1 | 2018-09-23 | | A | E | 8 - 0 | 1 | 2018-12-28 | | A | E | 6 - 0 | 1 | 2019-01-06 | +------+------------+---------+--------+------------+ -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 チームごとに対戦成績をみたい
SELECT * FROM records ORDER BY competitor, played_at; -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 チームごとに対戦成績をみたい
+------+------------+---------+--------+------------+ | team | competitor | score | is_win | played_at | +------+------------+---------+--------+------------+ | A | B | 8 - 4 | 1 | 2018-09-23 | | A | B | 8 - 5 | 1 | 2018-10-05 | | A | B | 7 - 9 | 0 | 2018-11-05 | | A | B | 10 - 3 | 1 | 2018-11-20 | | A | C | 10 - 8 | 1 | 2018-11-24 | | A | C | 4 - 5 | 0 | 2018-12-04 | | A | D | 3 - 9 | 0 | 2018-09-28 | | A | D | 12 - 11 | 0 | 2018-10-14 | | A | D | 20 - 17 | 1 | 2018-12-06 | | A | E | 8 - 0 | 1 | 2018-12-28 | | A | E | 6 - 0 | 1 | 2019-01-06 | +------+------------+---------+--------+------------+ -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 チームごとに最新の対戦成績をみたい
SELECT team, competitor, score, is_win, played_at, ROW_NUMBER() OVER ( PARTITION BY competitor ORDER BY played_at DESC ) AS rn FROM records; -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 チームごとに最新の対戦成績をみたい
+------+------------+---------+--------+------------+----+ | team | competitor | score | is_win | played_at | rn | +------+------------+---------+--------+------------+----+ | A | B | 10 - 3 | 1 | 2018-11-20 | 1 | | A | B | 7 - 9 | 0 | 2018-11-05 | 2 | | A | B | 8 - 5 | 1 | 2018-10-05 | 3 | | A | B | 8 - 4 | 1 | 2018-09-23 | 4 | | A | C | 4 - 5 | 0 | 2018-12-04 | 1 | | A | C | 10 - 8 | 1 | 2018-11-24 | 2 | | A | D | 20 - 17 | 1 | 2018-12-06 | 1 | | A | D | 12 - 11 | 0 | 2018-10-14 | 2 | | A | D | 3 - 9 | 0 | 2018-09-28 | 3 | | A | E | 6 - 0 | 1 | 2019-01-06 | 1 | | A | E | 8 - 0 | 1 | 2018-12-28 | 2 | +------+------------+---------+--------+------------+----+ -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 チームごとに最新の対戦成績をみたい
SELECT * FROM ( SELECT team, competitor, score, is_win, played_at, ROW_NUMBER() OVER ( PARTITION BY competitor ORDER BY played_at DESC ) AS rn FROM records ) tbl WHERE rn = 1; -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 チームごとに最新の対戦成績をみたい
+------+------------+---------+--------+------------+----+ | team | competitor | score | is_win | played_at | rn | +------+------------+---------+--------+------------+----+ | A | B | 10 - 3 | 1 | 2018-11-20 | 1 | | A | C | 4 - 5 | 0 | 2018-12-04 | 1 | | A | D | 20 - 17 | 1 | 2018-12-06 | 1 | | A | E | 6 - 0 | 1 | 2019-01-06 | 1 | +------+------------+---------+--------+------------+----+ -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 Window関数を使うことで
部分集合の結果を 自分の行に持って来るこ とができる -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践window関数 売上データ
sales table +------------+-------+ | date | sales | +------------+-------+ | 2019-01-01 | 1930 | | 2019-01-02 | 2030 | | 2019-01-05 | 1720 | | 2019-01-07 | 2290 | | 2019-01-08 | 1660 | | 2019-01-10 | 2450 | +------------+-------+ -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 過去の直近の日付を取得する
SELECT date AS cur_date, sales AS cur_sales, MIN(date) OVER ( ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) AS latest_date FROM sales; -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 過去の直近の日付を取得する
+------------+-----------+-------------+ | cur_date | cur_sales | latest_date | +------------+-----------+-------------+ | 2019-01-01 | 1930 | NULL | | 2019-01-02 | 2030 | 2019-01-01 | | 2019-01-05 | 1720 | 2019-01-02 | | 2019-01-07 | 2290 | 2019-01-05 | | 2019-01-08 | 1660 | 2019-01-07 | | 2019-01-10 | 2450 | 2019-01-08 | +------------+-----------+-------------+ -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 過去の直近の日付と売上を取得する
SELECT date AS cur_date, sales AS cur_sales, MIN(date) OVER W AS latest_date, MIN(sales) OVER W AS latest_sales FROM sales WINDOW W AS ( ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ); -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 過去の直近の日付と売上を取得する
+------------+-----------+-------------+--------------+ | cur_date | cur_sales | latest_date | latest_sales | +------------+-----------+-------------+--------------+ | 2019-01-01 | 1930 | NULL | NULL | | 2019-01-02 | 2030 | 2019-01-01 | 1930 | | 2019-01-05 | 1720 | 2019-01-02 | 2030 | | 2019-01-07 | 2290 | 2019-01-05 | 1720 | | 2019-01-08 | 1660 | 2019-01-07 | 2290 | | 2019-01-10 | 2450 | 2019-01-08 | 1660 | +------------+-----------+-------------+--------------+ -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 1日前の売上を取得する
SELECT date AS cur_date, sales AS cur_sales, MIN(date) OVER W AS latest_date, MIN(sales) OVER W AS latest_sales FROM sales WINDOW W AS ( ORDER BY date RANGE BETWEEN INTERVAL 1 day PRECEDING AND INTERVAL 1 day PRECEDING ); -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 1日前の売上を取得する
+------------+-----------+-------------+--------------+ | cur_date | cur_sales | latest_date | latest_sales | +------------+-----------+-------------+--------------+ | 2019-01-01 | 1930 | NULL | NULL | | 2019-01-02 | 2030 | 2019-01-01 | 1930 | | 2019-01-05 | 1720 | NULL | NULL | | 2019-01-07 | 2290 | NULL | NULL | | 2019-01-08 | 1660 | 2019-01-07 | 2290 | | 2019-01-10 | 2450 | NULL | NULL | +------------+-----------+-------------+--------------+ -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 2日後の売上を取得する
SELECT date AS cur_date, sales AS cur_sales, MIN(date) OVER W AS latest_date, MIN(sales) OVER W AS latest_sales FROM sales WINDOW W AS ( ORDER BY date RANGE BETWEEN INTERVAL 2 day FOLLOWING AND INTERVAL 2 day FOLLOWING ); -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 2日後の売上を取得する
+------------+-----------+-------------+--------------+ | cur_date | cur_sales | latest_date | latest_sales | +------------+-----------+-------------+--------------+ | 2019-01-01 | 1930 | NULL | NULL | | 2019-01-02 | 2030 | NULL | NULL | | 2019-01-05 | 1720 | 2019-01-07 | 2290 | | 2019-01-07 | 2290 | NULL | NULL | | 2019-01-08 | 1660 | 2019-01-10 | 2450 | | 2019-01-10 | 2450 | NULL | NULL | +------------+-----------+-------------+--------------+ -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 フレーム句を使うと
違う行を現在の行にもっ てくることができる -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 フレーム句で利用するキーワード
ROWS: 行単位で移動する ✓ RANGE: 列の値で移動する ORDER BY 句の列 ✓ n PRECEDING: n だけ前へ移動 ✓ n FOLLOWING: n だけ後ろへ移動 ✓ CURRENT ROW: 現在行 ✓ -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 実践Window関数 応用例
前日との売上を比較 ✓ 3日間の移動平均 ✓ チームごとの平均勝率 ✓ -
君はWindow関数を知っているか!? - テクテクテック#7 DB勉強会 Powered by Rabbit 2.2.1 まとめ Window
関数でできること 部分集合の値を持ってくる ✓ 違う行の値を持ってくる ✓ わかりにくいサブクエリーにさよ なら