内容

AWS Athenaへ用意されているテーブルのデータを、SQLを使って取得できるよう目指す方向け「Vol.3」です。

過去の記事

練習用データ

練習用データはこちらの3つのテーブルを用います。
Athenaコンソール上で実行すると作成できます。

Window関数

Window関数は、クエリ結果の行全体へ計算を実行する関数です。
OVER句を用いた特別な構文が必要です。

関数の結果をクエリの行それぞれに与えるようなことができて、Window関数()とOVER句それぞれの記載が必要です。
用途は色々ありますが、ユーザーごとの購入履歴に順序を付けたり、ひとつ前の履歴を持ってきたりできます。

【例】practice_salesテーブルから、ユーザーごとにsales_at昇順で番号を付与したい

sql

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関数を使う

sql

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を表示する

sql

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のデータを表示する

sql

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を月単位で切り捨てる

sql

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年を足す

sql

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年を足す

sql

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のデータを表示し、日数差を求める

sql

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の日数の差が出てきました。
余りは切り捨てされます。

【SQL】AWS Athena データ抽出入門 Vol.3【初心者向け】

内容