内容
AWS Athenaへ用意されているテーブルのデータを、SQLを使って取得できるよう目指す方向け「Vol.3」です。
過去の記事
練習用データ
練習用データはこちらの3つのテーブルを用います。
Athenaコンソール上で実行すると作成できます。
Window関数
Window関数は、クエリ結果の行全体へ計算を実行する関数です。
OVER句を用いた特別な構文が必要です。
関数の結果をクエリの行それぞれに与えるようなことができて、Window関数()とOVER句それぞれの記載が必要です。
用途は色々ありますが、ユーザーごとの購入履歴に順序を付けたり、ひとつ前の履歴を持ってきたりできます。
【例】practice_salesテーブルから、ユーザーごとにsales_at昇順で番号を付与したい
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY sales_at asc) as purchase_rank
FROM
practice_sales
ORDER BY
user_id, sales_at
結果
purchase_rank列を見ると、user_idごとにsales_at昇順で番号が付けられていることが分かります。
上記の例でいうROW_NUMBER()の部分がWindow関数です。
以降はよく使うWindow関数について説明します。
すべてのWindow関数については、マニュアルの7.15. Window Functionsに載っています。
RANK(),DENSE_RANK(),ROW_NUMBER()
| 関数名 | 説明 |
|---|---|
| RANK() | OVER(ORDER BY)で指定した順番に、順位を付けます。同率の場合は順位は同じになり、その次の順位は前の順位の同率の数だけ値を飛ばします。 |
| DENSE_RANK() | OVER(ORDER BY)で指定した順番に順位を付けます。同率の場合は順位は同じになり、その次の順位は+1されます。 |
| ROW_NUMBER() | OVER(ORDER BY)で指定した順番に順位を付けます。同率の場合でも順位は被らず、一意の連続した番号を返します。 |
上の３つを比べてみるとこんな感じです。
【例】practice_salesテーブルのquantity昇順で3つのWindow関数を使う
SELECT
quantity,
RANK() OVER(ORDER BY quantity asc) as purchase_rank,
DENSE_RANK() OVER(ORDER BY quantity asc) as purchase_dense_rank,
ROW_NUMBER() OVER(ORDER BY quantity asc) as purchase_row
FROM
practice_sales
結果
FIRST_VALUE()
| 関数名 | 説明 |
|---|---|
| FIRST_VALUE() | OVER()で指定した条件で文字列の最小値を返します |
FIRST_VALUE()はMIN()の文字列版のようなものです。文字列に対してWindow関数としてMIN()を用いると、期待した結果にならないことがあるので、FIRST_VALUE()を用います。
【例】practice_sales,practice_productテーブルからユーザーIDごとに最初に購入した(sales_atが昇順)時のproduct_nameを表示する
SELECT
user_id,
sales_at,
product_name,
MIN(product_name) OVER(PARTITION BY user_id ORDER BY sales_at) as min_window,
FIRST_VALUE(product_name) OVER(PARTITION BY user_id ORDER BY sales_at) as first_value_window
FROM
practice_sales, practice_product
WHERE
practice_sales.item_id = practice_product.item_id
ORDER BY
user_id, sales_at
結果
LEAD()
| 関数名 | 説明 |
|---|---|
| LEAD() | OVER()で指定した条件で、次の行となるデータを返します |
行ごとに順位付けした後、その次の順位を持ってくるようなイメージです。
【例】practice_salesテーブルのuser_id毎にsales_at昇順で、次のsales_atのデータを表示する
SELECT
user_id,
sales_at,
LEAD(sales_at) OVER(PARTITION BY user_id ORDER BY sales_at asc) as next_sales_at
FROM
practice_sales
ORDER BY
user_id, sales_at!
結果
日付/時刻データ操作
データ型のdate,timestampを対象として使える関数の一部を紹介します。
全ては、マニュアルの6.13. Date and Time Functions and Operatorsに載っています。
| 関数 | 説明 |
|---|---|
| DATE_TRUNC() | 時間データを任意の時間単位で切り捨てる |
| DATE_ADD() | 時間データに任意の時間単位、時間数を足す |
| DATE_DIFF() | 時間データと時間データの任意の時間単位での差を求める |
DATE_TRUNC()
説明の通り、時間データを任意の時間単位で切り捨てます。
週別や月別等で切り捨てて、集計したりするのに使います。
DATE_TRUNC(時間単位,対象列)のように使います。
【例】 practice_salesテーブルのsales_atを月単位で切り捨てる
SELECT
user_id,
sales_at,
DATE_TRUNC('MONTH', sales_at) as date_trunc_month
FROM
practice_sales
結果
date_trunc_month列は日にちが01に統一されて、月別に均されています。
DATE_TRUNC('MONTH', sales_at)をGROUP BYに用いて、月次集計・・・のようなことをよく行います。
DATE_ADD()
対象の時間データに、任意の時間単位、時間数を足します。
DATE_ADD(時間単位,時間数,対象列)のように使います。
【例】practice_salesテーブルのsales_atに3年を足す
SELECT
user_id,
sales_at,
DATE_ADD('YEAR', 3, sales_at) as date_add_sales
FROM
practice_sales
結果
見事に3年後になっています。
ちなみに、時間数をマイナスにすると時間を引くこともできます。
また、この操作は 対象列 + interval '時間数' 時間単位としても行うことができます。
【例】practice_salesテーブルのsales_atに3年を足す
SELECT
user_id,
sales_at,
sales_at + INTERVAL '3' YEAR as date_add_sales
FROM
practice_sales
結果
DATE_DIFF()
時間データと時間データの任意の時間単位での差を求めます。
DATE_DIFF(時間単位, 引く時間, 引かれる時間)のように使います。
LEAD()関数の例として用いたテーブルを使って、next_sales_at - sales_atの日数の差を求めてみます。
【例】practice_salesテーブルのuser_id毎にsales_at昇順で、次のsales_atのデータを表示し、日数差を求める
WITH lead_exsample_table AS (
SELECT
user_id,
sales_at,
LEAD(sales_at) OVER(PARTITION BY user_id ORDER BY sales_at asc) as next_sales_at
FROM
practice_sales
ORDER BY
user_id, sales_at
)
SELECT
user_id,
sales_at,
next_sales_at,
DATE_DIFF('DAY', sales_at, next_sales_at) as diff_days
FROM
lead_exsample_table
結果
next_sales_at - sales_atの日数の差が出てきました。
余りは切り捨てされます。