Go to Qiita Advent Calendar Top
LoginSignup
3
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@9ryuuuuu(クリュウ)

window関数の説明

Last updated at Posted at 2019-03-09

window関数の説明

概要

  • 機械学習の前処理やデータ分析において便利なwindow関数の使用方法を説明する。

準備

  • 使用するテーブル

    • book(購買対象物テーブル)
      • データ
book_id book_name price
1 1000のバグを持つ男 600
2 あなたもなれるスーパーエンジニア 1100
3 顧客をダマス極意 1200
4 バグか仕様か 1150
5 今こそ学ぶC++ 900
6 スーパーエンジニアを育てる方法 1100
7 上司に好かれる77の戦術 700
8 コードを書かないエンジニア 600
9 javaとjavascriptの違いを説明するには 900
10 僕は帰宅してからコーディングする 600

  • テーブル作成

    CREATE TABLE book
(
  book_id SERIAL PRIMARY KEY,
  book_name varchar(255) not null,
  price DECIMAL (11, 2) not null
);

  • データインポート

    INSERT INTO book (book_name, price)VALUES
  ('1000のバグを持つ男',600),
  ('あなたもなれるスーパーエンジニア',1100),
  ('顧客をダマス極意',1200),
  ('バグか仕様か',1150),
  ('今こそ学ぶC++',900),
  ('スーパーエンジニアを育てる方法',1100),
  ('上司に好かれる77の戦術',700),
  ('コードを書かないエンジニア',600),
  ('javaとjavascriptの違いを説明するには',900),
  ('帰宅後やっとコードを書き始める',600);

    • purchase(購買履歴テーブル)

      • データ
purchase_id user_id book_id
1 1 1
2 1 4
3 1 8
4 1 9
5 2 1
6 2 2
7 2 6
8 3 1
9 3 7
10 3 8
11 3 3
12 3 5
13 3 10

  • テーブル作成

    CREATE TABLE purchase
(
  purchase_id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  book_id INT NOT NULL,
  FOREIGN KEY (book_id) REFERENCES book (book_id)
);

  • データ挿入

    INSERT INTO purchase (user_id, book_id)VALUES
(1,1),
(1,4),
(1,8),
(1,9),
(2,1),
(2,2),
(2,6),
(3,1),
(3,7),
(3,8),
(3,3),
(3,5),
(3,10);

window関数の概要

  • 概要

    • window関数は集約関数と似ているが、集約関数と異なりレコードを削除しない。
    • 以下では、集約関数と比較しながらwindow関数の説明をする。

  • 集約関数を用いた平均額の算出

    • 集約関数を用いて、購入額の平均を算出する処理は以下になる。

    • AVG関数の引数にpriceを指定するだけ。

    • sql

      SELECT
  AVG(price)
FROM
  purchase
INNER JOIN book USING (book_id);

    • 結果

avg
819.2307692307692308

  • 集約関数を用いたユーザーごとの平均額の算出

    • 集約関数を用いて、ユーザー毎の購入額の平均を算出する処理は以下になる。

    • group byを用いて、ユーザーごとにグループ化した上で平均を計算するだけ。

    • sql

      SELECT
  user_id,
  AVG(price)
FROM
  purchase
INNER JOIN book USING (book_id)
GROUP BY
  user_id;

    • 結果

user_id avg
3 766.6666666666666667
2 933.3333333333333333
1 812.5000000000000000

  • window関数を用いたユーザーごとの平均額の算出

    • window関数を用いると以下のようになる。

    • 集約関数と同様にユーザーごとにグループ化した上で平均が算出されるが、集約関数とは異なり、レコードが削除されない。

    • sql

      SELECT
  user_id,
  price,
  AVG (price) OVER (PARTITION BY user_id)
FROM
  purchase
INNER JOIN book USING (book_id);

    • 結果

user_id price avg
1 600.00 812.5000000000000000
1 1150.00 812.5000000000000000
1 600.00 812.5000000000000000
1 900.00 812.5000000000000000
2 600.00 933.3333333333333333
2 1100.00 933.3333333333333333
2 1100.00 933.3333333333333333
3 600.00 766.6666666666666667
3 700.00 766.6666666666666667
3 600.00 766.6666666666666667
3 1200.00 766.6666666666666667
3 900.00 766.6666666666666667
3 600.00 766.6666666666666667
  • 動作順序
    • window関数は、JOIN, WHERE, GROUP BY と HAVING 句のあとに動作する。
    • 加えて、ORDER BY句の前に動作する。

window関数の書き方

  • 基本構文

    window関数 (引数1, 引数2) OVER (PARTITION BY expression ORDER BY expression)

  • window関数

    • 集約関数をwindow関数として使用できる。
    • 加えて、以下のようなビルトインのwindow関数を使用できる。
      • row_number(), rank(), dense_rank()

  • PARTITION BY

    • 指定したものでパーティーションの設定(グループ化)を行う。

  • ORDER BY

    • パーティーション内での並び方を指定する。

window関数の使用例

  • レコードの番号

    • パーティーション内でのレコードの番号を取得する処理を以下のように書ける。

    • ROW_NUMBER

      • ユーザーごとに購入額の高い順にレコードに番号を振る。

      • ROW_NUMBER関数は、同一の値でも別の番号が振られる。

      • sql

        SELECT
  user_id,
  price,
  ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY price)
FROM
  purchase
INNER JOIN book USING (book_id);

      • 結果

user_id price row_number
1 600.00 1
1 600.00 2
1 900.00 3
1 1150.00 4
2 600.00 1
2 1100.00 2
2 1100.00 3
3 600.00 1
3 600.00 2
3 600.00 3
3 700.00 4
3 900.00 5
3 1200.00 6
  • RANK

    • ROW_NUMBER関数の例と同様に、ユーザーごとに購入額の高い順にレコードに番号を振る。

    • ただし同じ値があるときは同じ番号を振り、次の値はその分スキップする。

    • sql

      SELECT
  user_id,
  price,
  RANK() OVER ( PARTITION BY user_id ORDER BY price)
FROM
  purchase
INNER JOIN book USING (book_id);

    • 結果

user_id price rank
1 600.00 1
1 600.00 1
1 900.00 3
1 1150.00 4
2 600.00 1
2 1100.00 2
2 1100.00 2
3 600.00 1
3 600.00 1
3 600.00 1
3 700.00 4
3 900.00 5
3 1200.00 6
  • DENSE_RANK

    • RANK関数と似ているが、同じ値があるときに次の値をスキップしない。

    • sql

      SELECT
  user_id,
  price,
  DENSE_RANK() OVER ( PARTITION BY user_id ORDER BY price)
FROM
  purchase
INNER JOIN book USING (book_id);

    • 結果

user_id price dense_rank
1 600.00 1
1 600.00 1
1 900.00 2
1 1150.00 3
2 600.00 1
2 1100.00 2
2 1100.00 2
3 600.00 1
3 600.00 1
3 600.00 1
3 700.00 2
3 900.00 3
3 1200.00 4

  • パーティーション内での最初と最後の値

    • FIRST_VALUE

      • ユーザー毎の購入額の最小値をレコードに追加する。

      • sql

        SELECT
  user_id,
  price,
  FIRST_VALUE (price) OVER ( PARTITION BY user_id ORDER BY price) as user_lowest_price
FROM
  purchase
INNER JOIN book USING (book_id);

      • 結果

user_id price user_lowest_price
1 600.00 600.00
1 600.00 600.00
1 900.00 600.00
1 1150.00 600.00
2 600.00 600.00
2 1100.00 600.00
2 1100.00 600.00
3 600.00 600.00
3 600.00 600.00
3 600.00 600.00
3 700.00 600.00
3 900.00 600.00
3 1200.00 600.00
  • LAST_VALUE

    • ユーザー毎の購入額の最大値をレコードに追加する。

    • sql

      SELECT
  user_id,
  price,
  LAST_VALUE (price) OVER ( PARTITION BY user_id ORDER BY price RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as user_highest_price
FROM
  purchase
INNER JOIN book USING (book_id);

    • PARTITION BY user_id ORDER BY price RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

      • パーティション全体に渡る集約処理を行うための設定
      • デフォルト設定は(RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)で、これはパーティションの先頭から現在の行を集約処理の対象とする。(そのため最小値を出すときは、この指定がなくても正しく動作した)

    • 結果

user_id price user_highest_price
1 600.00 1150.00
1 600.00 1150.00
1 900.00 1150.00
1 1150.00 1150.00
2 600.00 1100.00
2 1100.00 1100.00
2 1100.00 1100.00
3 600.00 1200.00
3 600.00 1200.00
3 600.00 1200.00
3 700.00 1200.00
3 900.00 1200.00
3 1200.00 1200.00

  • 前後のレコード

    • LAG関数やLEAD関数を使用すると、レコードの前後にあるレコードの値を取得できる。

    • LAG関数やLEAD関数の書き方

      LAG  (expression [,offset] [,default])
LEAD (expression [,offset] [,default])

      • offset

        • 何行ずらした位置のレコードを取得するか
        • デフォルト値： 1

      • default

        • ずらした位置にレコードが存在しない場合、設定する値
        • デフォルト値： NULL

    • LAG

      • ユーザーごとに、対象のレコードの価格よりも、１つ低い価格を取得する。

      • sql

        SELECT
  user_id,
  price,
  LAG(price, 1) OVER(PARTITION BY user_id ORDER BY price) AS prev_price
FROM
  purchase
INNER JOIN book USING (book_id)
GROUP BY user_id, price;

      • 結果

user_id price prev_price
1 600.00
1 900.00 600.00
1 1150.00 900.00
2 600.00
2 1100.00 600.00
3 600.00
3 700.00 600.00
3 900.00 700.00
3 1200.00 900.00

  • LEAD

    • ユーザーごとに、対象のレコードの価格よりも、１つ高い価格を取得する。

    • sql

      SELECT
  user_id,
  price,
  LEAD(price, 1) OVER(PARTITION BY user_id ORDER BY price) AS next_price
FROM
  purchase
INNER JOIN book USING (book_id)
GROUP BY user_id, price;

    • 結果

user_id price next_price
1 600.00 900.00
1 900.00 1150.00
1 1150.00
2 600.00 1100.00
2 1100.00
3 600.00 700.00
3 700.00 900.00
3 900.00 1200.00
3 1200.00

参考

3
5
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?