Athenaで基礎からしっかり入門 分析SQL（Python・Pandasコード付き） #2

今まで複雑なデータ操作・分析などはPythonでやっており、SQLは普通のアプリ開発程度のライトなものしか触って来なかったのですが、やはり分析用の長いSQLなども書けた方がやりとり等で便利・・・という印象なので、復習も兼ねて記事にしておきます。

また、SQLに加えて検算も兼ねてPythonやPandasなどを使ったコードもSQLと併記していきます（Pythonで書くとどういった記述が該当するのかの比較用として使います）。

※長くなるのでいくつかの記事に分割します。本記事は2記事目となります。

他のシリーズ記事

Athenaとはなんぞやという方はこちらをご確認ください:

前回（#1）

用語の説明・SELECT、WHERE、ORDER BY、LIMIT、AS、DISTINCT、基本的な集計関係（COUNTやAVGなど）、Athenaのパーティション、型、CAST、JOIN、UNION（INTERSECTなど含む）などについてはこちらの記事で既に説明済みなので本記事では触れません。

本記事で触れる点

• GROUP BY
• HAVING
• サブクエリ
• CASE
• COALESCE
• NULLIF
• LEAST
• GREATEST
• 四則演算などの基本的な計算
• 日付と日時の各操作

※ボリューム的には最後の日付と日時の操作関係で半分以上を占めています。

環境の準備

以下の#1の記事でS3へのAthena用のデータの配置やテーブルのCREATE文などのGitHubに公開しているものに関しての情報を記載していますのでそちらをご参照ください。

特記実行

• お仕事がAWSなので合わせてDBはAWSのAthena（Presto）を利用していきます。BigQueryやRedshift、MySQLやPostgreSQLなどではある程度方言や使える関数の差などがあると思いますがご了承ください。
• 同様にお仕事がゲーム業界なので、用意するデータセットはモバイルゲームなどを意識した形（データ・テーブル）で進めます。
• エンジニア以外の方（プランナーさんやマーケの方など）も少し読者として想定しています。ある程度技術的なところで煩雑な記述もありますがご容赦ください。
• 長くなるのでいくつかの記事に分割して執筆を進めています。

同じ値をグループ化する: GROUP BY

GROUP BYを使うと各カラムで同じ値のものをグループ化することができます。これによって例えば「ユーザーごとの売り上げを計算したい」とか「日付ごとの売り上げを出したい」といったように、一度のSQLでいくつものグループ（ユーザー単位であったり日付ごとであったりのグループ）を作って計算なとを行うことができます。

使い方はGROUP BY <グループ化で使いたい対象のカラム名>といった具合に書きます。例えばユーザーごとにグループ化したければユーザーIDのカラムを指定する形でGROUP BY user_idといった記述になります。

例として、2021-01-01と2021-01-02の2日間で各ユーザーが何回ログインしているのかを集計してみましょう。

各ユーザーのログインを扱うloginというテーブルを使っていきます。loginテーブルは以下のように各ユーザーのID（user_id）や日時などのカラムを持っています。

SELECT * FROM athena_workshop.login LIMIT 10;

ユーザーごとにグループ化してそれぞれのユーザーでのログイン回数を出したいのでGROUP BY user_idという記述を使います。

また、テーブルの性質上各ユーザーごとの行数がそのまま各ユーザーのログイン回数となるので、COUNT(*)の記述を使って行数をカウントしています。そのままだとCOUNTで指定したカラムが_col1といった名前で表示されてしまうので、AS login_countと指定してなんのカラムなのかが分かりやすくしています。

SELECT user_id, COUNT(*) AS login_count
FROM athena_workshop.login
WHERE dt IN ('2021-01-01', '2021-01-02')
GROUP BY user_id

これで特定の期間におけるログイン回数を出すことができました。

Pythonでの書き方

Pandasにgroupbyメソッドがあるのでそちらを使います。by引数でグループ化で使いたいカラム名を指定します。このメソッドではDataFrameGroupByやSeriesGroupByといったインスタンスの型が返却され、そちらに対してcountやsumなどの集計関数（メソッド）を実行することで通常のデータフレームなどが返ってきます。by引数で指定したカラムはインデックスに設定された状態で返ってきます。もしSQLのようにインデックスではなくカラムに設定したい場合には別途reset_indexなどのメソッドを利用する必要があります。

import pandas as pd

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/login/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df = df[['user_id', 'time']]
df = df.groupby(by='user_id').count()
df.rename(columns={'time': 'login_count'}, inplace=True)
print(df.head(5))

         login_count
user_id
1                  1
3                  2
5                  1
6                  3
9                  2

他の句との順番に注意

WHERE句やLIMIT句などをGROUP BYと一緒に使う場合にはそれぞれの句の順番を意識する必要があります。順番が正しくないとエラーになってしまいます。

まずはWHERE句ですが、これはGROUP BYよりも前に書かないといけません。

以下のようにGROUP BYの後に書くとエラーになります。

SELECT user_id, COUNT(*) AS login_count
FROM athena_workshop.login
GROUP BY user_id
WHERE dt IN ('2021-01-01', '2021-01-02')

ORDER BYに関してはGROUP BYの直後に書きます。

SELECT user_id, COUNT(*) AS login_count
FROM athena_workshop.login
WHERE dt IN ('2021-01-01', '2021-01-02')
GROUP BY user_id
ORDER BY user_id

GROUP BYの前に書くとエラーになります。

LIMIT句に関しては最後に書きます。

SELECT user_id, COUNT(*) AS login_count
FROM athena_workshop.login
WHERE dt IN ('2021-01-01', '2021-01-02')
GROUP BY user_id
ORDER BY user_id
LIMIT 3

複数のカラムを指定する

GROUP BYには複数のカラムを指定することができます。この場合グループ化の条件が増えるので、1つ辺りのグループの行数は基本的に減少します。例えばAカラムとBカラムという2つのカラムを指定した場合、「A且つB」という2つの条件を満たした行同士が同じグループとなります。

例えば「日別」且つ「ユーザーごと」といったように日付やユーザーIDといった複数の条件を付けて集計をやりたいケースなどはよく発生します。

書き方はORDER BYなどで複数のカラムを指定するときと同様に、半角のコンマ区切りで複数のカラムを指定します。

以下のSQLではユーザーID（user_id）と日付（dt）のパーティションをGROUP BYに指定しています。分かりやすいようにユーザーはIDが1と3のユーザーのみに絞ってあります。

SELECT user_id, dt, COUNT(*) AS login_count
FROM athena_workshop.login
WHERE user_id IN(1, 3)
GROUP BY user_id, dt
ORDER BY dt, user_id

Pythonでの書き方

groupbyメソッドのby引数にはリストなども指定できるので、リスト等で複数のカラム名を指定すれば対応ができます。

import pandas as pd

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/login/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df = df[['user_id', 'device_type', 'time']]
df = df.groupby(by=['user_id', 'device_type']).count()
df.rename(columns={'time': 'login_count'}, inplace=True)
print(df.head(5))

                     login_count
user_id device_type
1       2                      1
3       2                      2
5       1                      1
6       2                      3
9       2                      2

基本的には利用できるカラムはGROUP BYで指定したカラムもしくは集計関数が利用できる

GROUP BYを使った場合、SELECT ... FROMの...のカラムの指定部分は以下の2つの条件を満たすカラムのみ指定ができます。

• GROUP BYで指定したカラム
• GROUP BYで指定していないカラムで、且つ集計関数（SUMやCOUNTなど）を使う場合
• COUNTなどの集計関数であればアスタリスクの全カラム指定も使える

それぞれ試していきましょう。まずはGROUP BYで指定されたカラムのケースです。以下のように通常通りSQLを投げることができます。

SELECT user_id
FROM athena_workshop.login
WHERE user_id IN(1, 3)
GROUP BY user_id

続いて集計関数を挟む形のSQLです。サンプルとしてCOUNTの集計関数を使います。カラムはGROUP BYで指定していないdtのパーティションを指定しています。こちらも通常通りSQLが流れます。

SELECT user_id, COUNT(dt) AS login_count
FROM athena_workshop.login
WHERE user_id IN(1, 3)
GROUP BY user_id

続いて指定できない条件を試してみます。GROUP BYに指定されておらず、且つ集計関数も挟まない形のdtのパーティションを指定してみます。

SELECT user_id, dt
FROM athena_workshop.login
WHERE user_id IN(1, 3)
GROUP BY user_id

以下のようにエラーになります。

エラーメッセージに以下のように出ています。

SYNTAX_ERROR: line 1:17: 'dt' must be an aggregate expression or appear in GROUP BY clause

dtのカラム（or パーティション）は集計（aggregate）表現（集計関数）もしくはGROUP BY句（clause）でしか使えませんよ、といったことが表示されています。

このようにGROUP BYを使った場合には使えるカラムの挙動が特殊になります。

GROUP BYした集計関数の値に対して条件を設定する: HAVING

GROUP BYでグループ化し、集計関数を指定したカラムに対して条件を指定したくなることも結構出てきます。例えば以下のユーザーごとのログイン回数を集計するSQLで、ログイン回数が3回以上のユーザーの行のみを抽出したいとします。

SELECT user_id, COUNT(*) AS login_count
FROM athena_workshop.login
WHERE dt IN ('2021-01-01', '2021-01-02')
GROUP BY user_id

しかしながらGROUP BYを使った場合にはWHERE句でそのカラムを指定することはできません。以下のようにWHERE login_count >= 3としてみてもエラーになります。

SELECT user_id, COUNT(*) AS login_count
FROM athena_workshop.login
WHERE dt IN ('2021-01-01', '2021-01-02')
AND login_count >= 3
GROUP BY user_id

そういった場合はHAVINGを使うとグループ化し集計関数を挟んだ値に対して条件を設定することができます。HAVINGはGROUP BYの後に書きます。また、左辺などに集計関数と対象のカラムの指定を行います。残りと等値や以下・超過などの条件の書き方はWHERE句と同じような書き方となります。

以下のSQLでは先ほどWHERE句関係でエラーになったものを対応でき、ログイン回数が3回以上の行のみが抽出できています。

SELECT user_id, COUNT(*) AS login_count
FROM athena_workshop.login
WHERE dt IN ('2021-01-01', '2021-01-02')
GROUP BY user_id
HAVING COUNT(*) >= 3

クエリ結果を参照する: サブクエリ

この節以降ではメインのSQLとは別のSQLを同時に使い、結果をメインのSQLで使う形となるサブクエリについて触れていきます。SQL内で複数のSELECT文などが出現する形となります。

サブクエリ使用時の前提

サブクエリ使用時の前提と留意点として以下のようなものがあります。

• 基本的にはWHERE句やJOINなどに直接サブクエリを書かずにWITH句などを使う方がSQLがシンプルで読みやすくなることが多めです。どうしてもサブクエリで複数のクエリを使うと複雑になりがちで、且つWHERE句などにぎっしり詰まると他の人がそのSQLを読んだりが難しくなったりして好ましくないケースがあり注意が必要です。
• WHERE句などでのサブクエリはJOINなどで代替できればそちらの方がシンプルになることが多いように感じます。参考 : WHERE句のサブクエリは大抵の場合テーブルJOINで代替できる
• サブクエリが増えれば増えるほどSQLの内容が遅くなったりスキャンサイズが増えたりしてきます。繰り返し参照するものや過程の検算などをしたい場合には後々触れる一時テーブル（中間テーブル）を作成する対応（CTAS）の方が好ましいケースも結構あるかもしれません。

クエリ結果を条件指定（WHERE）で利用する

まずはサブクエリをWHERE句で使う方法について見ていきます。サブクエリ側は一つのカラムしか選択はできません。

且つ一番基本的な使い方としては、サブクエリ側の行は1行のみとする形となります。

説明のため、以前の記事でJOINの説明時に使ったサンプルテーブルを使っていこうと思います。join_sample_left_tableとjoin_sample_right_tableという2つのテーブルを使います。

join_sample_left_tableテーブルは以下のように武器のIDと名前を格納したテーブルになります。

SELECT * FROM athena_workshop.join_sample_left_table

join_sample_right_tableテーブルは以下のように武器のIDと攻撃力を持ったテーブルになります。

SELECT * FROM athena_workshop.join_sample_right_table

join_sample_right_tableテーブル側（攻撃力のデータを持つテーブル）で、武器の名前がグングニルになっている行の攻撃力を確認したいとします。もちろんIDを確認してIDの方をWHERE句に使ったりもできますが、今回はIDを見ずにSQLを投げたいとします。

その場合以下のようなサブクエリを使ったSQLを書くことでWHERE句に武器名を指定しつつSQLを実行することができます。

SELECT * FROM athena_workshop.join_sample_right_table
WHERE id = (SELECT id FROM athena_workshop.join_sample_left_table WHERE name = 'グングニル')

ポイントとしてはWHERE id = (<サブクエリ>)となっている点です。WHERE句部分のカラム名の指定とサブクエリで指定しているカラム名は別の名前でも動きますが、型が同一で且つサブクエリ側は1つのカラムのみ、且つ今回の基本的なサブクエリではサブクエリの結果の行も1行のみにする必要があります。

サブクエリ側で別のテーブルを参照して名前に対してWHERE句で条件を指定し、結果のIDがメインのSQLのWHERE句の条件として使われる・・・という形になります。

他にも色々機能はあるのですが、前述の通りWITH句を使った方が読みやすかったりJOINで代替できるケースも多かったりで仕事でもあまりこのサブクエリは使う機会が無いので本記事ではあまり触れずにいこうと思います（読んだ分析のSQL本でも基本的にWITH句や中間テーブルなどがメインに使われており、WHERE句のサブクエリは軽く触れる程度だったため）。

クエリ結果の列を連結（JOIN）する

こちらもWHERE句と同様、WITH句で対応したり普通にJOINしたりすることが仕事では多いのですが一応軽く触れておきます。

以下のようにJOINするテーブルの指定部分にサブクエリで別のSELECT文を流しています。サブクエリ側の結果はWHERE句の指定で武器名がグングニルとなっている行のみとなるのと、内部結合（INNER JOIN）させているので結果的にグングニルの1行のみ残ります。

これだけだとサブクエリ使わずに連結してからWHERE句とかで制御すれば同じ結果が得られるのですが、もっと複雑な加工をサブクエリ側でやる場合などに役立ったりするケースがあります（ただし、それらの場合でもWITH句などを使う方が読みやすくなるかもしれません）。

SELECT * FROM athena_workshop.join_sample_right_table
INNER JOIN (
  SELECT * FROM athena_workshop.join_sample_left_table
  WHERE name = 'グングニル'
)
USING(id)

WITHを使うと読みやすいSQLになる

WITH句というものを使ってサブクエリを書くこともできます。クエリ結果を一時的なテーブルとして任意の名前を付けることができ、そのテーブルをメインのSQL内で利用することができます。

WITH <設定する一時テーブル名> AS (サブクエリのSELECT文) メインのSELECT文といったように書きます。サブクエリの結果の一時テーブル名に名前を付けるフォーマットになっていることから分かる通り、サブクエリの結果に変数や定数のように名前を付けられます。つまりサブクエリの結果を一時的なテーブルとして、その名前を使ってメインのSQL内の各所で利用することができます。

プログラミングでいうとWITH句を使わないサブクエリがハードコーディング、WITH句を使ったものが変数などを利用したようなものに近くなります。WITH句を使うことで以下のようなメリットがあります。

• 複数個所でサブクエリの結果が必要になった場合にも毎回サブクエリを書かずに済みます（サブクエリの記述の重複を避けれます）。
• 結果の一時テーブルに名前を付けられるので、なんの内容のサブクエリなのかが分かりやすくなります。
• サブクエリとメインのSQLの各SELECT文を分離できるので、メインのSQLがごちゃっとしにくくなります（WHERE句やJOIN部分に詰め込まれず、分かれる形になります）。

WITH句を使わずに書いていたサブクエリの内容をWITH句を使って同様の内容を記述すると以下のようになります。

WITH target_weapon AS (
  SELECT * FROM athena_workshop.join_sample_left_table
  WHERE name = 'グングニル'
)

SELECT * FROM athena_workshop.join_sample_right_table
INNER JOIN target_weapon
USING(id)

条件に応じた制御を行う

この節以降では条件に応じた制御について触れていきます。

条件に応じた値の置換を行う: CASE

CASEを使うと条件に応じた特定の列の値を別のカラムの値として結果に設定することができます。プログラミングでいうところのif文みたいなことができます。

基本的な書き方としてはSELECT ... FROMのカラム指定部分にCASE WHEN <対象のカラム> = <対象値> THEN <条件を満たした場合に設定される値> ENDといったように書きます。

Pythonのif文で書くと

if <対象のカラムの値> == <対象値>:
    <条件を満たした場合に値を設定する処理>

みたいなものに近くなります。

また、ENDの後にはASを使って結果の値のカラム名を設定することができます。

説明のため引き続きJOINなどの節で使ったjoin_sample_right_tableテーブルを利用していきます。以下のようなテーブルとなっています。武器の攻撃力を想定したテーブルですね。

SELECT * FROM athena_workshop.join_sample_right_table

例として、このテーブルでIDはあるものの武器名などが設定されているマスタのテーブルはAthenaの環境に無くJOINができない・・・しかし武器名などを設定したいといったケースを想定してみましょう（マスタではなく種別値などのラベルなどはテーブルが存在しないケースなどは結構あると思います）。

こういったケースでは数が少なければCASEで対応することができます。例えば以下のSQLではCASEを使ってIDが2の武器の行に対してグングニルという武器名を設定しています。そちらの武器名のカラム名にはweapon_nameという名前をASを使って設定しています。

SELECT *,
CASE WHEN id = 2 THEN 'グングニル'
END AS weapon_name
FROM athena_workshop.join_sample_right_table

Pythonでの書き方

データフレームに対する条件分岐的な制御は色々書き方はあります。maskを使ったりwhereを使ったりapplyを使ったり、もしくはリストに直してループで回してしまうなど様々です。

今回はmaskメソッドを使います。特定のシリーズ（カラム）でmaskメソッドを使い、第一引数にスライスなどで使う時と同じように条件（今回はdf['id'] == 2）を指定し、第二引数に設定する値（今回は'グングニル'）を指定します。

import pandas as pd

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/join_sample_right_table/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df['weapon_name'] = df['id'].mask(df['id'] == 2, 'グングニル')
print(df)

   id  attack weapon_name
0   2    1000       グングニル
1   3     800           3

複数の条件の値の置換を行う

複数の条件を設定したい場合にはENDの前にWHEN ... THEN ...の記述を追加していきます。Pythonで言うところのelifのような処理になります。

SELECT *,
CASE WHEN id = 2 THEN 'グングニル'
WHEN id = 3 THEN 'トライデント'
END AS weapon_name
FROM athena_workshop.join_sample_right_table

どの条件にも該当しない場合の設定

いくつかWHEN ... THEN ...で条件を設定したとして、「その他」のケースで値を設定したいケースにはELSEを使います。どの条件にも該当しない場合にはその行がELSEで指定された値にて置換されます。ELSE <設定する値>といったように書きます。WHEN ... THEN ...の条件の後、且つENDの前に書きます。

SELECT *,
CASE WHEN id = 1 THEN 'エクスカリバー'
WHEN id = 2 THEN 'グングニル'
ELSE '不明な武器'
END AS weapon_name
FROM athena_workshop.join_sample_right_table

欠損値（NULL）の場合に別の値に置き換える: COALESCE

欠損値（NULL）の行の値を別の値に置き換えたい場合にはCALESCE関数を使います。COALESCEは「 癒合(ゆごう)する、合体する、合同する」といった意味を持つ単語のようです。発音的にはコウアレスとかに近いようです。

coalesceの読み方はカタカナで書くと「コウアレス」に近いようです。
SQL関数coalesceの使い方と読み方

以下のように前記事で扱った、欠損値が一部で存在するdevice_unique_idを使っていきます。

SELECT * FROM athena_workshop.device_unique_id
WHERE unique_id IS NULL
LIMIT 10

COALESCE関数を使うと欠損している値を別の値に置換できます。第一引数には対象のカラム名、第二引数には欠損値に対して設定する値を指定します。以下では分かりやすいように欠損している箇所に対して不明な値という値を設定しています。

SELECT *, COALESCE(unique_id, '不明な値') AS label  FROM athena_workshop.device_unique_id
WHERE unique_id IS NULL
LIMIT 10

ログが欠損値を含む場合に別のラベルに置換しておきたい場合であったり、他の不正なケースの値と統一したい（例 : 空文字に統一したり、何らかの欠損値を示す文字列と統一したり）といった場合に役立ちます。

Pythonでの書き方

fillnaメソッドで欠損値補完が効くのでそちらを使います。第一引数に設定する値を指定します。

import pandas as pd

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/device_unique_id/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df['unique_id'].fillna('不明な値', inplace=True)
print(df[df['unique_id'] == '不明な値'].head())

      user_id       date  device_type unique_id
1065     7367 2021-01-01            2      不明な値
1201     6101 2021-01-01            1      不明な値
1759    17509 2021-01-01            1      不明な値
1830    20110 2021-01-01            2      不明な値
1858    20459 2021-01-01            1      不明な値

条件を満たした値を欠損値（NULL）に置き換える: NULLIF

NULLIF関数はCOALESCEと逆のような挙動をします。特定の条件を満たす行を欠損値に置換します。

第一引数に対象のカラム名、第二引数に該当する条件の値を指定します。

以下のSQLではdevice_typeが2の行の値を欠損値にしています。

SELECT user_id, device_type, NULLIF(device_type, 2) AS nullif_column
FROM athena_workshop.device_unique_id
LIMIT 10

Pythonでの書き方

replaceメソッドで置換が利くので置換の値でNumPyの欠損値（np.nan）を指定すれば同じようなことができます。

import pandas as pd
import numpy as np

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/device_unique_id/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df['device_type'].replace(to_replace=2, value=np.nan, inplace=True)
print(df.head(10))

   user_id       date  device_type                                          unique_id
0     9958 2021-01-01          1.0  80dd469bc6fc1ac5c51bfca801c605d6cb296868a64471...
1    26025 2021-01-01          1.0  d302032f6b82abaf18f9df6bbde6ba999b901b7a8def00...
2    28390 2021-01-01          NaN  4ebfcc61825f46ca981eb56041ed837b1ff5bd08c495e4...
3    11977 2021-01-01          1.0  de24cf852f0270fe864de27b633e888d57ab55278b0030...
4    10732 2021-01-01          1.0  3420f6554809274949fab197b685f549e2b7d94ccb58f0...
5    16670 2021-01-01          NaN  0a2e7ebfa13f583361d4dc51a7ddab03acaeb1700c90d5...
6    17426 2021-01-01          NaN  60c4893828c76fb134247d773c02b8bf0c4801c95d392d...
7    26623 2021-01-01          1.0  eeb5d2235bb2db0c87f016d0bbe1050e9f08d7767dd5fc...
8     2083 2021-01-01          NaN  4e7a0668e1de434a88c26ae248ab80e71f3dc5ec603421...
9     9079 2021-01-01          NaN  fae26219b0f6340c86bb7440576dbef94afa7ca5809d0b...

値が小さい方を設定する: LEAST

LEAST関数を使うと、指定されたカラムや数値などの中から最小の値が選択されます。以下のSQLではtotal_salesというカラムの値と100という値の2つの中で小さい方が結果に残っていることを確認できます。特定のカラムに対して上限値などを設定する時などに便利かもしれません。

SELECT user_id, total_sales, LEAST(total_sales, 100) AS least_result
FROM athena_workshop.user_total_sales_and_power LIMIT 50;

なお、この関数は3つ以上の引数も受け付けてくれます。複数のカラムなどを指定して、その中から一番小さい値を残す・・・といった制御ができます。

SELECT user_id, total_sales, power, LEAST(total_sales, power, 50000) AS least_result
FROM athena_workshop.user_total_sales_and_power
LIMIT 50;

Pythonでの書き方

minメソッドでaxis=1と指定すれば列方向での最小値が取れます。既存のカラム以外にも固定値も必要な場合にはそのカラムを追加しておくことで対応ができます（以下のコードではdf['fixed_value'] = 50000として固定値のカラムを追加しています）。

import pandas as pd
import numpy as np

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/user_total_sales_and_power/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df['fixed_value'] = 50000
df['least_result'] = df[['total_sales', 'power', 'fixed_value']].min(axis=1)
print(df.head(10))

   user_id       date  device_type  total_sales   power  fixed_value  least_result
0    19914 2021-01-01            1            0   39203        50000             0
1    19005 2021-01-01            2            0   42748        50000             0
2     5741 2021-01-01            2            0   69826        50000             0
3    14028 2021-01-01            2            0  102833        50000             0
4    18434 2021-01-01            2            0   72106        50000             0
5     6231 2021-01-01            1            0   68877        50000             0
6     7403 2021-01-01            1        82900  223433        50000         50000
7    23378 2021-01-01            2            0  110646        50000             0
8     9597 2021-01-01            1        30300   70400        50000         30300
9     1553 2021-01-01            2            0   62792        50000             0

値が大きい方を設定する: GREATEST

GREATEST関数はLEAST関数の逆の挙動をします。つまり指定されたカラムや値の中で一番大きな値が選択されます。

LEASTとは逆に最小値を固定値で設定したい場合などに便利です。

SELECT user_id, total_sales, power, GREATEST(total_sales, power, 50000) AS least_result
FROM athena_workshop.user_total_sales_and_power
LIMIT 50

Pythonでの書き方

LEASTの時にminメソッドを使っていた箇所をmaxメソッドにするだけです。axis=1などの引数設定は変わりません。

import pandas as pd
import numpy as np

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/user_total_sales_and_power/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df['fixed_value'] = 50000
df['least_result'] = df[['total_sales', 'power', 'fixed_value']].max(axis=1)
print(df.head(10))

   user_id       date  device_type  total_sales   power  fixed_value  least_result
0    19914 2021-01-01            1            0   39203        50000         50000
1    19005 2021-01-01            2            0   42748        50000         50000
2     5741 2021-01-01            2            0   69826        50000         69826
3    14028 2021-01-01            2            0  102833        50000        102833
4    18434 2021-01-01            2            0   72106        50000         72106
5     6231 2021-01-01            1            0   68877        50000         68877
6     7403 2021-01-01            1        82900  223433        50000        223433
7    23378 2021-01-01            2            0  110646        50000        110646
8     9597 2021-01-01            1        30300   70400        50000         70400
9     1553 2021-01-01            2            0   62792        50000         62792

基本的な演算

この節以降では四則演算などについて触れていきます。

加算

特定のカラムに対して+の記号を使って加算（足し算）を行うことができます。例えば<特定のカラム> + 10000とすると指定したカラムに1万足された状態で各行が返ってきます。

以下のSQLではadded_valueというカラム名を付ける形で元のカラムよりも1万足された値を表示しています。

SELECT user_id, total_sales, total_sales + 10000 AS added_value
FROM athena_workshop.user_total_sales_and_power
LIMIT 50

また、固定値だけでなくカラム同士の足し算なども可能です。以下のSQLではtotal_salesカラムとpowerカラム同士を足し算した結果を表示しています。

SELECT total_sales, power, total_sales + power AS added_value
FROM athena_workshop.user_total_sales_and_power
LIMIT 50

3つ以上の複数の加算を繋げることも可能です。以下のSQLでは2つのカラムに加えて固定値の1万をさらに加えています。

SELECT total_sales, power, total_sales + power + 10000 AS added_value
FROM athena_workshop.user_total_sales_and_power
LIMIT 50

このようにカラム同士を計算したり複数の演算子を繋げたりすることは他の計算（減算や乗算など）でも可能です。

Pythonでの書き方

そのまま+や+=などの演算子で計算ができます。

import pandas as pd
import numpy as np

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/user_total_sales_and_power/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df['added_value'] = df['total_sales'] + 10000
print(df.head(10))

   user_id       date  device_type  total_sales   power  added_value
0    19914 2021-01-01            1            0   39203        10000
1    19005 2021-01-01            2            0   42748        10000
2     5741 2021-01-01            2            0   69826        10000
3    14028 2021-01-01            2            0  102833        10000
4    18434 2021-01-01            2            0   72106        10000
5     6231 2021-01-01            1            0   68877        10000
6     7403 2021-01-01            1        82900  223433        92900
7    23378 2021-01-01            2            0  110646        10000
8     9597 2021-01-01            1        30300   70400        40300
9     1553 2021-01-01            2            0   62792        10000

減算

-の記号を使うと減算（引き算）できます。

SELECT total_sales, total_sales - 5000 AS subtracted_value
FROM athena_workshop.user_total_sales_and_power
LIMIT 50

Pythonでの書き方

こちらも-や-=などの演算子を使って対応ができます。

import pandas as pd
import numpy as np

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/user_total_sales_and_power/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df['subtracted_value'] = df['total_sales'] - 5000
print(df.head(10))

   user_id       date  device_type  total_sales   power  subtracted_value
0    19914 2021-01-01            1            0   39203             -5000
1    19005 2021-01-01            2            0   42748             -5000
2     5741 2021-01-01            2            0   69826             -5000
3    14028 2021-01-01            2            0  102833             -5000
4    18434 2021-01-01            2            0   72106             -5000
5     6231 2021-01-01            1            0   68877             -5000
6     7403 2021-01-01            1        82900  223433             77900
7    23378 2021-01-01            2            0  110646             -5000
8     9597 2021-01-01            1        30300   70400             25300
9     1553 2021-01-01            2            0   62792             -5000

乗算

*の演算子で乗算（掛け算）できます。

SELECT total_sales, total_sales * 3 AS multiplied_value
FROM athena_workshop.user_total_sales_and_power
LIMIT 50

Pythonでの書き方

*や*=の演算子で対応ができます。

import pandas as pd
import numpy as np

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/user_total_sales_and_power/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df['multiplied_value'] = df['total_sales'] * 3
print(df.head(10))

   user_id       date  device_type  total_sales   power  multiplied_value
0    19914 2021-01-01            1            0   39203                 0
1    19005 2021-01-01            2            0   42748                 0
2     5741 2021-01-01            2            0   69826                 0
3    14028 2021-01-01            2            0  102833                 0
4    18434 2021-01-01            2            0   72106                 0
5     6231 2021-01-01            1            0   68877                 0
6     7403 2021-01-01            1        82900  223433            248700
7    23378 2021-01-01            2            0  110646                 0
8     9597 2021-01-01            1        30300   70400             90900
9     1553 2021-01-01            2            0   62792                 0

除算

/の記号を使うと除算（割り算）することができます。

SELECT power, power / 2 AS divided_value
FROM athena_workshop.user_total_sales_and_power
LIMIT 50

気を付けないといけない点として、整数の型のカラム（intやbigintなど）に対して除算を行うと結果も整数になります。つまり小数点以下が切り捨てとなります。

前述したSQLの結果で102833という値の2での除算結果が51416となっている点に注目してください。51416.5といったように浮動小数点数にはなりません。Python2系のような挙動ですね。これはAthena（Presto）特有の挙動というものでもなく、他の例えばPostgreSQLなどでも同様に切り捨ての挙動となります。

結果を浮動小数点数（floatやdoubleなど）で取得したい場合には前回の記事で触れたように対象のカラムを浮動小数点数に型変換（キャスト）してから除算する必要があります。

以下のSQLでは事前に浮動小数点数の型であるdoubleにキャストしています。

SELECT power, CAST(power AS double) / 2 AS divided_value
FROM athena_workshop.user_total_sales_and_power
LIMIT 50

Pythonでの書き方

/や/=の記号などで対応ができます。Python（Pandas）側は除算しても切り捨てにはならずに浮動小数点数の値となります。Pandasなどを使わずに且つPython2系などを使って除算をすると切り捨てにはなります。

import pandas as pd

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/user_total_sales_and_power/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df['divided_value'] = df['power'] / 2
print(df.head(10))

   user_id       date  device_type  total_sales   power  divided_value
0    19914 2021-01-01            1            0   39203        19601.5
1    19005 2021-01-01            2            0   42748        21374.0
2     5741 2021-01-01            2            0   69826        34913.0
3    14028 2021-01-01            2            0  102833        51416.5
4    18434 2021-01-01            2            0   72106        36053.0
5     6231 2021-01-01            1            0   68877        34438.5
6     7403 2021-01-01            1        82900  223433       111716.5
7    23378 2021-01-01            2            0  110646        55323.0
8     9597 2021-01-01            1        30300   70400        35200.0
9     1553 2021-01-01            2            0   62792        31396.0

SQL側と合わせて除算結果を切り捨てにしたい場合にはastypeメソッドを使ってintにキャストすれば切り捨てが実行されます。

import pandas as pd
import numpy as np

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/user_total_sales_and_power/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df['divided_value'] = df['power'] / 2
df['divided_value'] = df['divided_value'].astype(int, copy=False)
print(df.head(10))

0    19914 2021-01-01            1            0   39203          19601
1    19005 2021-01-01            2            0   42748          21374
2     5741 2021-01-01            2            0   69826          34913
3    14028 2021-01-01            2            0  102833          51416
4    18434 2021-01-01            2            0   72106          36053
5     6231 2021-01-01            1            0   68877          34438
6     7403 2021-01-01            1        82900  223433         111716
7    23378 2021-01-01            2            0  110646          55323
8     9597 2021-01-01            1        30300   70400          35200
9     1553 2021-01-01            2            0   62792          31396

剰余

剰余（余り）を計算したい場合には%の記号を使います。

SELECT power, power % 100 as modulo_value
FROM athena_workshop.user_total_sales_and_power
LIMIT 50

Pythonでの書き方

%の記号を使ったりなどで対応ができます。

import pandas as pd
import numpy as np

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/user_total_sales_and_power/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df['divided_value'] = df['power'] % 100
print(df.head(10))

   user_id       date  device_type  total_sales   power  divided_value
0    19914 2021-01-01            1            0   39203              3
1    19005 2021-01-01            2            0   42748             48
2     5741 2021-01-01            2            0   69826             26
3    14028 2021-01-01            2            0  102833             33
4    18434 2021-01-01            2            0   72106              6
5     6231 2021-01-01            1            0   68877             77
6     7403 2021-01-01            1        82900  223433             33
7    23378 2021-01-01            2            0  110646             46
8     9597 2021-01-01            1        30300   70400              0
9     1553 2021-01-01            2            0   62792             92

日付と日時の操作

この節以降では日付（date）と日時（timestamp）関係の操作について触れていきます。

必要なキャスト処理

ここまでの節で触れてきた各テーブルは日付も日時も文字列のカラムとして扱ってきました。そのため日付や日時に対する操作を行う場合には以下のようにCAST関数を使って型をDATEやTIMESTAMPなどに変換してから扱う必要があります。

日付に対するキャスト例 :

SELECT date, sales, CAST(date AS DATE)
FROM athena_workshop.total_sales_daily LIMIT 10;

日時に対するキャスト例 :

SELECT user_id, time, CAST(time AS TIMESTAMP)
FROM athena_workshop.login LIMIT 10;

Pythonでの書き方

pd.to_datetime関数に特定の日付や日時になっている文字列のカラムのシリーズなどを引数に渡すとPandasのTimestamp型の値が取れます。

import pandas as pd

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_daily/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df['date'] = pd.to_datetime(df['date'])
print(df)
print(type(df.at[0, 'date']))

        date   sales
0 2021-01-01  768500
<class 'pandas._libs.tslibs.timestamps.Timestamp'>

日数を加算する

<日付もしくは日時> + INTERVAL '<日数>' DAYとすると対象のカラムの日付もしくは日時から指定された日数分あとの日付や日時が取得できます。例えばdateという日付のカラムに対してCAST(date AS DATE) + INTERVAL '2' DAYとすると2日後の日付が取れます。日付と日時問わずに利用できます。

2日後の日付を取得する例

SELECT date, sales, CAST(date AS date) + INTERVAL '2' DAY AS two_days_after
FROM athena_workshop.total_sales_daily LIMIT 10;

2日後の日時を取得する例

SELECT user_id, time, CAST(time AS TIMESTAMP) + INTERVAL '2' DAY AS two_days_after
FROM athena_workshop.login LIMIT 10;

Pythonでの書き方

PandasのDayクラスを使うと日付の加算ができます。引数を省略すると+1日されます。

import pandas as pd
from pandas.tseries.offsets import Day

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_daily/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df['date'] = pd.to_datetime(df['date'])
df['date'] += Day()
print(df)

        date   sales
0 2021-01-02  768500

引数に日数を指定した場合はその日数分加算を行うことができます。

import pandas as pd
from pandas.tseries.offsets import Day

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_daily/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df['date'] = pd.to_datetime(df['date'])
df['date'] += Day(3)
print(df)

        date   sales
0 2021-01-04  768500

時間を加算する

<日時> + INTERVAL '時間' HOURとすると対象の日時から指定された時間分加算した日時が取得できます。例えばtimeという日時のカラムに対してCAST(time AS TIMESTAMP) + INTERVAL '2' HOURとすると2時間後の時間が取れます。

SELECT user_id, time, CAST(time AS TIMESTAMP) + INTERVAL '2' HOUR AS two_hours_after
FROM athena_workshop.login LIMIT 10;

Pythonでの書き方

PandasでHourクラスがあるのでそちらを加算することで対応ができます。

import pandas as pd
from pandas.tseries.offsets import Hour

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/login/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df['time'] = pd.to_datetime(df['time'])
df['time'] += Hour(2)
print(df.head())

   user_id                time  device_type
0     8590 2021-01-01 02:00:01            1
1      568 2021-01-01 02:00:02            1
2    17543 2021-01-01 02:00:04            2
3    15924 2021-01-01 02:00:07            1
4     5243 2021-01-01 02:00:09            2

年の加算

<対象の日付もしくは日時> + INTERVAL '<年数> YEAR'とすると年の加算ができます。

SELECT date, sales, CAST(date AS date) + INTERVAL '1' YEAR AS next_year
FROM athena_workshop.total_sales_daily LIMIT 10;

Pythonでの書き方

そういえば年での加算をPythonで扱ったことがない・・・気がしたのですが、調べたらYearクラスは存在しない？ようです。しかしDateOffsetクラスにyears引数を指定することで同じようなことができるようです（DateOffsetクラスを初めて使いました・・・）。

import pandas as pd
from pandas.tseries.offsets import DateOffset

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_daily/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df['date'] = pd.to_datetime(df['date'])
df['date'] += DateOffset(years=1)
print(df)

        date   sales
0 2022-01-01  768500

月の加算

<日付もしくは日時> + INTERVAL '<月数>' MONTHとすると月の加算処理を扱えます。

SELECT date, sales, CAST(date AS date) + INTERVAL '1' MONTH AS next_month
FROM athena_workshop.total_sales_daily LIMIT 10;

Pythonでの書き方

こちらも年と同様、MonthというクラスはPandasにはありませんがDateOffsetクラスのmonths引数を指定することで同じような対応を行うことができます。

import pandas as pd
from pandas.tseries.offsets import DateOffset

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_daily/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df['date'] = pd.to_datetime(df['date'])
df['date'] += DateOffset(months=1)
print(df)

        date   sales
0 2021-02-01  768500

複数の加算を同時に行う

INTERVAL ...の記述は1つのカラムに対して複数の設定を同時に行えます。例えば1か月と2日後・・・といった設定が可能です。

SELECT date, sales, CAST(date AS date) + INTERVAL '1' MONTH + INTERVAL '2' DAY AS future_date
FROM athena_workshop.total_sales_daily LIMIT 10

特定の部分（年や月など）の取得処理

※この節で触れる方法以外にも、後々の節で触れる各種関数でも整数の形で取得することができます。

文字列の状態のカラムであればSUBSTRという関数で文字列の特定の位置の値が抽出できます。第一引数には対象のカラム、第二引数に文字列の開始位置、第三引数に抽出する文字列を指定します。文字列の開始位置の整数は1からスタートするので注意してください。また、文字列用の関数なので日付や日時にキャストしていない点にも注意してください。

例えばYYYY-MM-DD HH:MM:SSの形式の日時の文字列であれば年部分は1文字目以降・4文字分なのでSUBSTR(time, 1, 4)となります。月であれば6文字目・2文字なのでSUBSTR(time, 6, 2)となります。

SELECT user_id, time,
SUBSTR(time, 1, 4) AS year,
SUBSTR(time, 6, 2) AS month,
SUBSTR(time, 9, 2) AS day,
SUBSTR(time, 12, 2) AS hour,
SUBSTR(time, 15, 2) AS minute,
SUBSTR(time, 18, 2) AS second
FROM athena_workshop.login LIMIT 10;

Pythonでの書き方

日付もしくは日時に変換する前の文字列のカラムに対してPandasのシリーズのstr属性でスライスの記法（[0:4]など）をすることで対応ができます。SQLのSUBSTR関数と異なりインデックスのスタートは1からではなく0からとなります。

import pandas as pd
from pandas.tseries.offsets import DateOffset

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/login/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df['year'] = df['time'].str[0:4]
df['month'] = df['time'].str[5:7]
df['date'] = df['time'].str[8:10]
df['hour'] = df['time'].str[11:13]
df['minute'] = df['time'].str[14:16]
df['second'] = df['time'].str[17:19]
print(df.head())

   user_id                 time  device_type  year month date hour minute second
0     8590  2021-01-01 00:00:01            1  2021    01   01   00     00     01
1      568  2021-01-01 00:00:02            1  2021    01   01   00     00     02
2    17543  2021-01-01 00:00:04            2  2021    01   01   00     00     04
3    15924  2021-01-01 00:00:07            1  2021    01   01   00     00     07
4     5243  2021-01-01 00:00:09            2  2021    01   01   00     00     09

各種減算処理

減算も-の記号を使うことで加算と同様に行えます。加算と同様にYEAR, MONTH, DAY, HOURの指定に対応しており、使い方は加算と同じです。

SELECT date, sales, CAST(date AS date) - INTERVAL '1' MONTH - INTERVAL '2' DAY AS future_date
FROM athena_workshop.total_sales_daily LIMIT 10

Pythonでの書き方

各種クラス（Dayなど）はそのまま減算のオペレーター（-や-=など）が使えます。

import pandas as pd
from pandas.tseries.offsets import Day

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_daily/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df['date'] = pd.to_datetime(df['date'])
df['date'] -= Day(3)
print(df)

        date   sales
0 2020-12-29  768500

もしくはクラスの引数の値に負の値を指定しても同じ挙動になります。

import pandas as pd
from pandas.tseries.offsets import Day

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_daily/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df['date'] = pd.to_datetime(df['date'])
df['date'] += Day(-3)
print(df)

        date   sales
0 2020-12-29  768500

特定の部分の連結処理

例えばYYYY-MMの形式での年月部分だったり、MM/DDの月日だったりを抽出したいとします。YYYY-MMの年月の形式であれば前節で触れたSUBSTR関数だけでも対応ができます。ただしフォーマットが変わった場合（例えばYYYY年MM月といったような日本語表記が必要な場合など）には別の対応が必要になります。

そういった場合にはSUBSTR関数での抽出処理とCONCAT関数での連結処理を組み合わせることで対応ができます（英語でconcatenateは「連結する」という意味になります）。

CONCAT関数ではコンマ区切りで複数の文字列のカラムもしくは固定の文字列を指定することで、それぞれの値を連結した文字列を返してくれます。このCONCAT関数の中にSUBSTR関数のものなどを入れることで任意の部分・任意のフォーマットで文字列を出力することができます。

以下のSQLではYYYY年MM月というフォーマットで結果を出力しています。

SELECT date, sales, CONCAT(SUBSTR(date, 1, 4), '年', SUBSTR(date, 6, 2), '月') as year_and_month
FROM athena_workshop.total_sales_daily LIMIT 10

もしくは||の記号を使っても文字列を連結することができます。

SELECT date, sales, SUBSTR(date, 1, 4) || '年' || SUBSTR(date, 6, 2) || '月' as year_and_month
FROM athena_workshop.total_sales_daily LIMIT 10

Pythonでの書き方

シンプルに+の演算子だけで各カラムや特定の文字列を連結できます。

import pandas as pd
from pandas.tseries.offsets import Day

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_daily/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')

df['date'] = df['date'].astype(str, copy=False)
df['year'] = df['date'].str[0:4]
df['month'] = df['date'].str[5:7]
df['year_and_month'] = df['year'] + '年' + df['month'] + '月'
print(df)

         date   sales  year month year_and_month
0  2021-01-01  768500  2021    01       2021年01月

日時の特定単位の切り落とし : DATE_TRUNC

DATE_TRUNC 関数を使うと特定の単位での日付もしくは日時の値の切り捨て処理が行えます。TRUNCATEで「切り捨てる」といったような意味になります。

例えば月の単位で指定すれば日付以降が切り捨てられ、月初の日付などが得られます。日付の単位で指定すれば1日の始まりの日時が得られて時間や分などは切り捨てられます。

DATE_TRUNC('<対象の単位>', <日付もしくは日時>)という形で、第一引数に単位、第二引数に対象のカラム名などを指定します。

単位には以下のいずれかの文字列が指定できます。

• 'SECOND': 秒までが残ってミリ秒以下が切り捨てとなります。
• 'MINUTE': 分までが残って秒以下が切り捨てとなります。
• 'HOUR': 時までが残って分以下が切り捨てとなります。
• 'DAY': 日までが残って時以下が切り捨てとなります。
• 'WEEK': 月曜の開始時点の日付・日時になるように切り捨てとなります。
• 'MONTH': 月までが残って日以下が切り捨てとなります。
• 'QUARTER': 四半期ごとの開始の日付・日時になるように切り捨てとなります。
• 'YEAR': 年だけが残り、他は切り捨てられます。

以下のSQLではYEARを指定しているため年のみが残り月と日は01に、時分秒は00に切り捨てられています。

SELECT user_id, time, DATE_TRUNC('YEAR', CAST(time AS TIMESTAMP)) AS month_begin
FROM athena_workshop.login LIMIT 10

月初の日付の算出

Pandasで言うところのMonthBeginクラスで扱うような、月初を得たりは結構お仕事で必要になったりします。そういった場合は前述のDATE_TRUNC関数で'MONTH'の単位を指定することで月初が得られます。

SELECT date, sales, DATE_TRUNC('MONTH', CAST(date AS DATE)) AS month_begin
FROM athena_workshop.total_sales_daily
LIMIT 10

Pythonでの書き方

PandasにMonthBeginクラスがあるのでそちらを使えます。使い方はDayなどの他のクラスと同様です。引数に整数を指定した場合はその月数分で計算がされます。

ただし注意しない点として、対象が既に月初の日付になっている場合前月の月初になってしまいます。例えば2021-01-01の日付の行に対して計算がされると2020-12-01といったように前月の日付になってしまいます。一方で月初以外の2021-01-05などの日付は2021-01-01の日付になります。

2021-01-01の日付指定時

import pandas as pd
from pandas.tseries.offsets import MonthBegin

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_daily/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')
df['date'] = pd.to_datetime(df['date'])
df['date'] -= MonthBegin()
print(df)

        date   sales
0 2020-12-01  768500

2021-01-05の日付指定時

import pandas as pd
from pandas.tseries.offsets import MonthBegin

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_daily/dt%3D2021-01-05/data.json.gz?raw=true',
    lines=True, compression='gzip')
df['date'] = pd.to_datetime(df['date'])
df['date'] -= MonthBegin()
print(df)

        date   sales
0 2021-01-01  687100

1つの解決策として、一度MonthBeginで加算して翌月にしてからMonthBeginで1か月分減算するという方法があります。これなら元の日付が月初かどうかに関係なくその月の月初になります。

import pandas as pd
from pandas.tseries.offsets import MonthBegin

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_daily/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')
df['date'] = pd.to_datetime(df['date'])
df['date'] += MonthBegin()
df['date'] -= MonthBegin()
print(df)

        date   sales
0 2021-01-01  768500

月末の日付の算出

月初と同様に月末の日付の算出なども必要になることが結構あります。HIVEとかだとそれ専用の関数なども用意されていますしPandasだとMonthEndクラスなどがありますが、Athena（Presto）ではこの記事を書いている時点では（UDFなどを除いて）対応するものが無い？感じがしますので少しステップを踏む必要があります。

色々やり方はあると思いますが、今回は以下のようにして月末を計算しています。

• 月初を計算します（DATE_TRUNC('MONTH', ...)）。
• 翌月の月初に変換します（+ INTERVAL '1' MONTH）。
• 翌月月初の前日を取得します（- INTERVAL '1' DAY as month_end）。

SELECT date, sales,
DATE_TRUNC('MONTH', CAST(date AS DATE)) + INTERVAL '1' MONTH - INTERVAL '1' DAY as month_end
FROM athena_workshop.total_sales_daily
LIMIT 10

Pythonでの書き方

PandasにMonthEndクラスなどがありそちらで計算すると月末の日付が取れます。ただし月初のMonthBeginと同様に、対象が既に月末の日付の場合翌月の月末になってしまいます。

2021-01-31の月末の日付に対してMonthEndを加算している例

import pandas as pd
from pandas.tseries.offsets import MonthEnd

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_daily/dt%3D2021-01-31/data.json.gz?raw=true',
    lines=True, compression='gzip')
df['date'] = pd.to_datetime(df['date'])
df['date'] += MonthEnd()
print(df)

        date   sales
0 2021-02-28  747700

その月の月末などに統一したい場合の解決策の一つとしては、一度MonthBeginを加算して翌月月初にしてからDayもしくはMonthEndを減算するという方法が取れます。

import pandas as pd
from pandas.tseries.offsets import MonthBegin, MonthEnd

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/total_sales_daily/dt%3D2021-01-31/data.json.gz?raw=true',
    lines=True, compression='gzip')
df['date'] = pd.to_datetime(df['date'])
df['date'] += MonthBegin()
df['date'] -= MonthEnd()
print(df)

        date   sales
0 2021-01-31  747700

WEEKの単位について試す

DATE_TRUNCのWEEKについて少し実際に動かして詳細を確認してみます。ドキュメントを読んでいた感じ、月曜に変換されるようです。

SELECT date, sales,
DATE_TRUNC('WEEK', CAST(date AS DATE)) AS week_start
FROM athena_workshop.total_sales_daily
LIMIT 50

以下のように変換がされていることが分かります。

• 2021-01-01（金） -> 2020-12-28（月）
• 2021-03-02（火） -> 2021-03-01（月）
• ...
• 2021-02-22（月） -> 2021-02-22（月）

確かに月曜に変換されています。

QUARTERの単位について試す

QUARTER（四半期）に関しても同様に少し実行してみます。

SELECT date, sales,
DATE_TRUNC('QUARTER', CAST(date AS DATE)) AS quarter_start
FROM athena_workshop.total_sales_daily
LIMIT 10

各日付が2021-01-01の日付になっていることが分かります。ドキュメントではサンプルが2000-07-01という結果になっているので、以下のように変換される感じだと思われます。

• 1月月初から3月月末 -> 1月の月初
• 4月月初から6月月末 -> 4月の月初
• 7月月初から9月月末 -> 7月の月初
• 10月月初から12月月末 -> 10月の月初

DATE_ADDでもINTERVALと同じようなことができる

INTERVALの方を使えばこちらはあまり使う機会が無さそう・・・な気もしますが、DATE_ADD関数でも似たような制御を行えます。

SELECT date, sales, DATE_ADD('DAY', 1, CAST(date AS date)) AS future_date
FROM athena_workshop.total_sales_daily LIMIT 10

第一引数に単位、第二引数に値（DAYを指定した場合は日数）、第三引数に日付のカラムなどを指定します。単位の部分にはINTERVALと同様の値が指定できます。たとえばDAYの代わりにYEARを指定すれば年単位で加算などがされます。

SELECT date, sales, DATE_ADD('YEAR', 1, CAST(date AS date)) AS future_date
FROM athena_workshop.total_sales_daily LIMIT 10

第二引数には負の値も指定できます。負の値を指定すると減算の挙動になります。

SELECT date, sales, DATE_ADD('YEAR', -1, CAST(date AS date)) AS future_date
FROM athena_workshop.total_sales_daily LIMIT 10

現在の時間を取得する: CURRENT_TIME

カラムの指定部分などにCURRENT_TIMEと指定すると現在の時間が取れます。後述するCURRENT_TIMESTAMPやCURRENT_DATEなども同じなのですが、関数と違って()の括弧が不要な点にはご注意ください。

SELECT date, sales, CURRENT_TIME
FROM athena_workshop.total_sales_daily
LIMIT 10

他の関数などと同様にASなども指定できます（以下のサンプルではcurrent_timeは予約語となりASで使えないのでcurrent_time_とサフィックスにアンダースコアを付けています）。

SELECT date, sales, CURRENT_TIME AS current_time_
FROM athena_workshop.total_sales_daily
LIMIT 10

現在の日時を取得する: CURRENT_TIMESTAMPとNOW

CURRENT_TIMEの代わりにCURRENT_TIMESTAMPを指定すると日付付きで現在の日時が取得できます。

SELECT date, sales, CURRENT_TIMESTAMP
FROM athena_workshop.total_sales_daily
LIMIT 10

ちなみにこのCURRENT_TIMESTAMPの代わりにNOW関数も使うことができます。挙動は同じですが記述が短くなります。

SELECT date, sales, NOW() AS now
FROM athena_workshop.total_sales_daily
LIMIT 10

Pythonでの書き方

ビルトインのdatetimeのnow関数で現在日時を取れます。

from datetime import datetime

import pandas as pd

df: pd.DataFrame = pd.DataFrame(data={'a': [1, 2]})
df['now'] = datetime.now()
print(df)

   a                        now
0  1 2021-07-13 08:49:58.254533
1  2 2021-07-13 08:49:58.254533

データフレームに設定すると値はPandasのTimestampとなります。そちらはdatetime型と同じようにstrftimeメソッドが使えるので%H:%M:%Sなどの任意のフォーマットを指定すれば時間部分などのみ取得できます。

from datetime import datetime

import pandas as pd

df: pd.DataFrame = pd.DataFrame(data={'a': [1, 2]})
df['now'] = datetime.now()
timestamp: pd.Timestamp = df.at[0, 'now']
print(timestamp.strftime('%H:%M:%S'))

08:53:11

現在の日付を取得する: CURRENT_DATE

CURRENT_DATEを指定すると現在の日付（時間は含みません）を取得できます。

SELECT date, sales, CURRENT_DATE
FROM athena_workshop.total_sales_daily
LIMIT 10

ただし、この値後述するタイムゾーン関係がエラーとなり設定できません（？）。日本環境で扱うと場合によっては日付がずれうるので注意が必要かもしれません（後でもう少し試してみようと思いますが、タイムゾーン付きでCURRENT_TIMESTAMPなどで算出してから日付部分を抽出した方が無難な気もちょっとしています）。

タイムゾーンの設定

前節のCURRENT_TIMEではUTCのタイムゾーンが設定されていました。ただし日本でお仕事する上では日本のタイムゾーンの値が取りたくなることが多くなると思います。

そういった場合にはAT TIME ZONE 'Asia/Tokyo'などと設定すると日本の時間となります。

SELECT date, sales, CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Tokyo' AS current_time_
FROM athena_workshop.total_sales_daily
LIMIT 10

日付へのキャストのエイリアス（短縮系の記述）

前節で触れてきたように、文字列などを日付にキャスト（型変換）したい場合にはCAST(<対象のカラム> AS DATE)といった記述が必要になりました。

Athena（Presto）ではこのキャストの記述を短くするためのDATE(<対象のカラム>)という関数が別途用意されています。こちらを使うとCASTの記述を省略できます（挙動はDATEへCASTした時と同じものになります）。

SELECT date AS date_str, DATE(date) AS date, sales
FROM athena_workshop.total_sales_daily
LIMIT 10

UNIXTIMEを日時に変換する

※この節以降、login_unixtimeというログの日時がUNIXTIMEになっているテーブルを利用します。loginテーブルと同様に、ユーザーのログイン時間を扱う想定のデータとしてあります。

unixtimeカラムは整数の型で以下のようなデータになっています。

SELECT * FROM athena_workshop.login_unixtime LIMIT 10;

---

たまにログでUNIXTIMを扱う必要が出で来るときがあります。そういった場合にはFROM_UNIXTIME関数でUNIXTIMEから通常の日時に変換することができます。第一引数に変換したいカラム名（今回はunixtimeカラム）などを指定します。

SELECT user_id, unixtime, FROM_UNIXTIME(unixtime) AS time
FROM athena_workshop.login_unixtime LIMIT 10;

ただしこの値はタイムゾーンが加味されていません。日本時間で扱いたい場合には他と同様にAT TIME ZONE 'Asia/Tokyo'とタイムゾーンの指定を追加する必要があります。

SELECT user_id, unixtime, FROM_UNIXTIME(unixtime) AT TIME ZONE 'Asia/Tokyo' AS time
FROM athena_workshop.login_unixtime LIMIT 10;

Pythonでの書き方

Pandasだとまずはdt.tz_localize('UTC')でUTCに変換してからさらに特定のタイムゾーン（今回は日本時間が欲しいのでAsia/Tokyoをdt.tz_convert）を指定する必要があるようです。少し煩雑には感じます。

import pandas as pd

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/login_unixtime/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')
df['time'] = pd.to_datetime(df['unixtime'], unit='s')
df['time'] = df['time'].dt.tz_localize('UTC').dt.tz_convert('Asia/Tokyo')
print(df.head())

   user_id    unixtime  device_type                      time
0    17447  1609426803            2 2021-01-01 00:00:03+09:00
1    10800  1609426805            1 2021-01-01 00:00:05+09:00
2    15188  1609426807            1 2021-01-01 00:00:07+09:00
3    18304  1609426807            1 2021-01-01 00:00:07+09:00
4    22496  1609426810            2 2021-01-01 00:00:10+09:00

日本時間に変換した後にタイムゾーン部分の表記を消したい場合にはdt.tz_localize(None)とさらに繋げると対応できるようです。

import pandas as pd

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/login_unixtime/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')
df['time'] = pd.to_datetime(df['unixtime'], unit='s')
df['time'] = df['time'].dt.tz_localize('UTC').dt.tz_convert('Asia/Tokyo')
df['time'] = df['time'].dt.tz_localize(None)
print(df.head())

   user_id    unixtime  device_type                time
0    17447  1609426803            2 2021-01-01 00:00:03
1    10800  1609426805            1 2021-01-01 00:00:05
2    15188  1609426807            1 2021-01-01 00:00:07
3    18304  1609426807            1 2021-01-01 00:00:07
4    22496  1609426810            2 2021-01-01 00:00:10

日時からUNIXTIMEに変換する

逆に日時からUNIXTIMEにしたい場合にはTO_UNIXTIME関数を使います。第一引数に対象の時間のカラムなどを指定します。

ただし元の日時などが日本時間の場合にはこの関数でも時差を加味する必要があります。
関数自体には時差関係の引数などは無いようで、且つAT TIME ZONEなどで調整していてもちょっとうまくいかなかったので以下のSQLでは普通に日本時間を想定して9時間減算しています。

SELECT user_id, time,
TO_UNIXTIME(CAST(time AS TIMESTAMP) - INTERVAL '9' HOUR) AS unixtime
FROM athena_workshop.login LIMIT 10

結果は浮動小数点数で返ってくるので指数表記になっています（例 : 1.613919601E9）。整数の形式のものが必要なケースも多いでしょうから、そういった場合にはCAST(... AS BIGINT)と指定して整数変換（キャスト）すると必要な値が取れます。

SELECT user_id, time,
CAST(TO_UNIXTIME(CAST(time AS TIMESTAMP) - INTERVAL '9' HOUR) AS BIGINT) AS unixtime
FROM athena_workshop.login LIMIT 10

Pythonでの書き方

以下の手順で対応します。

• pd.to_datetimeでまずは文字列から日時の型（Timestamp）に変換。
• .dt.tz_localize('Asia/Tokyo')でタイムゾーンを日本時間のものに変換。
• タイムゾーン変換結果は2021-01-01 00:00:01+09:00といったような値になるので、これをastype(int)でキャストするとナノ秒まで含めた1609426801000000000といった値になるので、それを// 10 ** 9と除算することで結果の整数を取得。

としています（//は切り捨て除算なので整数になります）。

import pandas as pd

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/login/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')
df['timestamp'] = pd.to_datetime(df['time'])
df['timestamp'] = df['timestamp'].dt.tz_localize('Asia/Tokyo')
df['timestamp'] = df['timestamp'].astype(int) // 10 ** 9
print(df.head())

   user_id                 time  device_type   timestamp
0     8590  2021-01-01 00:00:01            1  1609426801
1      568  2021-01-01 00:00:02            1  1609426802
2    17543  2021-01-01 00:00:04            2  1609426804
3    15924  2021-01-01 00:00:07            1  1609426807
4     5243  2021-01-01 00:00:09            2  1609426809

特定単位の時間（間隔値）への変換処理

Athena（Presto）にはPARSE_DURATIONという関数があります。第一引数に特定の単位付きの文字列を指定すると特殊な型（interval day to second）の時間の値が返ってきます。例えばminuteを表すmと一緒に10mという文字列を引数に指定すると10分のinterval day to secondの時間が返ってきます。0 00:10:00.000といったような特殊な表記になります。

SELECT user_id, time, PARSE_DURATION('10m') AS ten_minutes
FROM athena_workshop.login LIMIT 10

Prestoのドキュメントにはあるものの、他の記事とかはほとんどこの関数関係がヒットせずに「何に使うのだろう？」と少し考えていたのですが、1つの使い道として浮動小数点数もサポートされているという点がメリットかなと思いました。

例えば10.5mといった用に書くと10分と30秒といった値が返ってきます。

SELECT user_id, time, PARSE_DURATION('10.5m') AS minute_val
FROM athena_workshop.login LIMIT 10

この値はTO_MILLISECONDS関数でミリ秒変換できます。/ 1000で除算すれば秒単位の値にできます。

SELECT user_id, time, TO_MILLISECONDS(PARSE_DURATION('10.5m')) / 1000 AS second_val
FROM athena_workshop.login LIMIT 10

この値をDATE_ADD関数に指定すれば10.5分後みたいな制御ができるようになります（INTERVALにはこの値は直接指定するとエラーになるようで、DATE_ADD関数の方を使いました。また、INTERVALの記述の場合は浮動小数点数を指定するとエラーになります）。

SELECT user_id, time,
DATE_ADD('SECOND', TO_MILLISECONDS(PARSE_DURATION('10.5m')) / 1000, CAST(time AS TIMESTAMP)) AS future_time
FROM athena_workshop.login LIMIT 10

10.5分後みたいなシンプルなケースであればINTERVALの記述を2回するなり、630秒後とかで計算してもシンプルに対応できますが、計算が面倒な場合やもっと直観的ではない値（例えば10.3日など）の場合の計算はこういった浮動小数点数付きの表記の方が可読性が高くなる（意図が伝わりやすくなる）ケースがあるかもしれません。

分のm以外にも以下のような色々な単位がサポートされています。

単位	内容
ns	ナノ秒（1 / 1000 / 1000 / 1000秒）
us	マイクロ秒（1 / 1000 / 1000秒）
ms	ミリ秒（1 / 1000秒）
s	秒
m	分
h	時
d	日

MySQL互換の日時のフォーマット処理

普段この辺りは仕事だとPython上で扱うケースが大半なのでMySQL上でほぼ使ったことはないのですが、任意のフォーマットの文字列 → 時間の変換やその逆の時間 → 任意のフォーマットの文字列に変換する関数が存在します。Pythonだとstrftimeとかstrptimeと同じようなものに該当します。

strftime的に日時 → 任意のフォーマットの文字列に変換したい場合にはDATE_FORMAT関数を使います。strptime的に任意のフォーマットの文字列 → 日時に変換したい場合にはDATE_PARSE関数を使います。それぞれの関数やサポートされているフォーマットの指定などは以降の節で順番に触れていきます。

日時から任意のフォーマットの文字列を生成する: DATE_FORMAT

DATE_FORMAT関数を使うと日時のカラムなどを特定のフォーマットの文字列に変換できます。第一引数に日時などのカラム、第二引数にはフォーマットの文字列を指定します。

第二引数のフォーマットに関しては後々の節で詳しく触れます。以下のSQLでは'%m/%d'というフォーマットを指定しています。%mが2文字の月の文字列、%dが2文字の日の文字列なので'%m/%d'で01/05みたいな表記になります。

SELECT user_id, time,
DATE_FORMAT(CAST(time AS TIMESTAMP), '%m/%d')
FROM athena_workshop.login LIMIT 10

Pythonでの書き方

Pandasのシリーズのdt属性でビルトインのdatetimeみたいなインターフェイスにアクセスできるようになっているのでそちらのstrftimeメソッドで対応ができます。今回の例ではフォーマットの文字列も一致していますが、他のフォーマットは一致していないものも多いのでご注意ください。

import pandas as pd

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/login/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')
df['time'] = pd.to_datetime(df['time'])
df['formatted_time'] = df['time'].dt.strftime('%m/%d')
print(df.head())

   user_id                time  device_type formatted_time
0     8590 2021-01-01 00:00:01            1          01/01
1      568 2021-01-01 00:00:02            1          01/01
2    17543 2021-01-01 00:00:04            2          01/01
3    15924 2021-01-01 00:00:07            1          01/01
4     5243 2021-01-01 00:00:09            2          01/01

任意の日時のフォーマットの文字列から日時を生成する: DATE_PARSE

※この節から説明のためにno_symbol_loginという名前のテーブルを扱っていきます。日時のtimeカラムがハイフンやコロンなどの記号を含まない形で保存されているログインデータのテーブルとなります。

SQLで確認してみると以下のようになっています。

SELECT * FROM athena_workshop.no_symbol_login LIMIT 10;

---

今度は逆に特定のフォーマットの文字列から日時（TIMESTAMP）を取得する処理について触れていきます。たとえば20210101103050みたいにハイフンなどの記号が省かれているデータがあるかもしれませんし、もしくは2021年01月01日10時30分50秒みたいな日本語表記になっているデータもあるかもしれません。そういった特殊なフォーマットの場合に便利です。

DATE_PARSE関数を使います。第一引数に該当のフォーマットの文字列のカラムなどを指定し、第二引数に想定されるフォーマットを指定します。

今回のSQL例では20210101103050といったように記号やスペースなどを含まない年月日時分秒のフォーマットの文字列を日時に変更します。フォーマットは%Y%m%d%H%i%Sとなります。

SELECT user_id, time,
DATE_PARSE(time, '%Y%m%d%H%i%S') AS parsed_time
FROM athena_workshop.no_symbol_login LIMIT 10;

Pythonでの書き方

Pandas側ではpd.to_datetime関数を使う際にformat引数でフォーマットを指定する必要があります。

import pandas as pd

df: pd.DataFrame = pd.read_json(
    'https://github.com/simon-ritchie/athena_workshop/blob/main/workshop/no_symbol_login/dt%3D2021-01-01/data.json.gz?raw=true',
    lines=True, compression='gzip')
df['parsed_time'] = pd.to_datetime(df['time'], format='%Y%m%d%H%M%S')
print(df.head())

   user_id            time  device_type         parsed_time
0     9093  20210101000000            1 2021-01-01 00:00:00
1    23210  20210101000007            1 2021-01-01 00:00:07
2     9560  20210101000027            2 2021-01-01 00:00:27
3    23197  20210101000028            2 2021-01-01 00:00:28
4     2569  20210101000032            1 2021-01-01 00:00:32

日時の各フォーマット種別

以降の節ではフォーマットの各文字列の使い方や主だったもののフォーマットの定義について触れていきます。

※全てのフォーマットは触れません。また、Prestoのドキュメントには載っているフォーマットでもAthena上で利用しようとするとエラーになるものもあるようです（Athena側で裏側で使われているPrestoのバージョンが最新のドキュメントと比べて少し古い・・・とかでしょうか？）。

フォーマットの指定の基本的な使い方

• 各フォーマット（年や月など）を表す部分には半角の%の記号が付けられます。
• フォーマットの部分はシングルクォーテーションで囲む必要があります（例 : '%d'）。
• 複数のフォーマットの文字列を組み合わせて使うことができます（例 : %m%d）。
• 任意の文字や記号をフォーマットの文字列の間に挟むことができます（例 : %m月%d日, %m-%d）。

4桁の西暦のフォーマット: %Y

%Yのフォーマットで4桁の西暦の年が取れます（例 : 2021）。

SELECT user_id, time,
DATE_FORMAT(CAST(time AS TIMESTAMP), '%Y') AS year
FROM athena_workshop.login LIMIT 10

2桁の西暦のフォーマット: %y

%yのフォーマットで下2桁の西暦の年が取れます（例 : 21）。

SELECT user_id, time,
DATE_FORMAT(CAST(time AS TIMESTAMP), '%y') AS year
FROM athena_workshop.login LIMIT 10

ゼロ埋めされた形の月のフォーマット: %m

%mのフォーマットで左にゼロ埋めがされた状態の月の値が取れます。結果はゼロ埋めされて必ず2桁となります（例 : 03）。

SELECT user_id, time,
DATE_FORMAT(CAST(time AS TIMESTAMP), '%m') AS month
FROM athena_workshop.login LIMIT 10

ゼロ埋めしない形の月のフォーマット: %c

%cのフォーマットでゼロ埋めがされていない状態の月の値が取れます（例 : 3）。結果は月に応じて1桁もしくは2桁になります。

SELECT user_id, time,
DATE_FORMAT(CAST(time AS TIMESTAMP), '%c') AS month
FROM athena_workshop.login LIMIT 10

月の文字列のフォーマット: %M

英語表記となりますが、%Mの表記で月のラベルを取得することができます（例 : February）。

SELECT user_id, time,
DATE_FORMAT(CAST(time AS TIMESTAMP), '%M') AS month
FROM athena_workshop.login LIMIT 10

ゼロ埋めされた形の日のフォーマット: %d

%dのフォーマットでゼロ埋めされた状態の日が取れます（例 : 05）。結果は必ず2桁となります。

SELECT user_id, time,
DATE_FORMAT(CAST(time AS TIMESTAMP), '%d') AS day
FROM athena_workshop.login LIMIT 10

ゼロ埋めしない形の日のフォーマット: %e

%eのフォーマットでゼロ埋めされていない形の日の値が取れます（例 : 6）。結果は日付に応じて1桁もしくは2桁となります。

SELECT user_id, time,
DATE_FORMAT(CAST(time AS TIMESTAMP), '%e') AS day
FROM athena_workshop.login LIMIT 10

曜日の英語の文字列のフォーマット: %aと%W

%aと%Wのフォーマットで曜日の英語のラベルを取ることができます。%aの方は短い表記となります（例 : Mon）。

SELECT user_id, time,
DATE_FORMAT(CAST(time AS TIMESTAMP), '%a') AS weekday
FROM athena_workshop.login LIMIT 10

%Wの方は長い表記となります（例 : Wednesday）。

SELECT user_id, time,
DATE_FORMAT(CAST(time AS TIMESTAMP), '%W') AS weekday
FROM athena_workshop.login LIMIT 10

ゼロ埋めされた形の時のフォーマット: %H

%Hのフォーマットでゼロ埋めされた時の値が取れます（例 : 05）。結果は必ず2桁となります。

SELECT user_id, time,
DATE_FORMAT(CAST(time AS TIMESTAMP), '%H') AS hour
FROM athena_workshop.login
WHERE time >= '2021-03-05 05:30:00'
AND dt = '2021-03-05'
LIMIT 10

ゼロ埋めされていない形の時のフォーマット: %k

%kのフォーマットを使うとゼロ埋めされていない時のフォーマットの値が取れます（例 : 5）。

SELECT user_id, time,
DATE_FORMAT(CAST(time AS TIMESTAMP), '%k') AS hour
FROM athena_workshop.login
WHERE time >= '2021-03-05 05:30:00'
AND dt = '2021-03-05'
LIMIT 10

午前か午後かの英語表記のフォーマット: %p

%pというフォーマットを使うと午前か午後かに応じてAMもしくはPMという文字列が取得できます。

午前に結果がなるケース例

SELECT user_id, time,
DATE_FORMAT(CAST(time AS TIMESTAMP), '%p') AS am_or_pm
FROM athena_workshop.login
WHERE time >= '2021-03-05 05:30:00'
AND dt = '2021-03-05'
LIMIT 10

午後に結果がなるケース例

SELECT user_id, time,
DATE_FORMAT(CAST(time AS TIMESTAMP), '%p')  AS am_or_pm
FROM athena_workshop.login
WHERE time >= '2021-03-05 15:30:00'
AND dt = '2021-03-05'
LIMIT 10

午前か午後かの値が続く時分秒のフォーマット: %r

%rのフォーマットを使うとAMもしくはPMが付く形で時分秒の値が取れます。24時間表記ではなくAMとPMごとの12時間表記となります。

SELECT user_id, time,
DATE_FORMAT(CAST(time AS TIMESTAMP), '%r') AS time
FROM athena_workshop.login
WHERE time >= '2021-03-05 15:30:00'
AND dt = '2021-03-05'
LIMIT 10

24時間表記での時分秒のフォーマット: %T

%Tのフォーマットでは、AMやPMなどの表記はなく24時間表記での時間が取れます。

SELECT user_id, time,
DATE_FORMAT(CAST(time AS TIMESTAMP), '%T') AS time
FROM athena_workshop.login
WHERE time >= '2021-03-05 15:30:00'
AND dt = '2021-03-05'
LIMIT 10

ゼロ埋めされた形の分のフォーマット: %i

%iのフォーマットでゼロ埋めされた分の値が取れます（00～59）。結果は必ず2桁となります。

SELECT user_id, time,
DATE_FORMAT(CAST(time AS TIMESTAMP), '%i') AS minute
FROM athena_workshop.login
WHERE time >= '2021-03-05 15:05:00'
AND dt = '2021-03-05'
LIMIT 10

秒とかもそうなのですが、ゼロ埋めされない形の分や秒のフォーマットはドキュメントに見当たらない？ようです。

ゼロ埋めされた形の秒のフォーマット: %sと%S

%sと%Sのフォーマットでゼロ埋めされた秒の値が取れます。大文字小文字両方同じ結果になるようです。

小文字のsを使った場合

SELECT user_id, time,
DATE_FORMAT(CAST(time AS TIMESTAMP), '%s') AS second
FROM athena_workshop.login
WHERE time >= '2021-03-05 15:05:00'
AND dt = '2021-03-05'
LIMIT 10

大文字のSを使った場合

SELECT user_id, time,
DATE_FORMAT(CAST(time AS TIMESTAMP), '%S') AS second
FROM athena_workshop.login
WHERE time >= '2021-03-05 15:05:00'
AND dt = '2021-03-05'
LIMIT 10

1年のうちで何日目なのかのフォーマット: %j

%jのフォーマットで1年で何日目に該当するのかの日数が取れます。三桁分にゼロ埋めされる形で返ってくるようです。

SELECT user_id, time,
DATE_FORMAT(CAST(time AS TIMESTAMP), '%j') AS second
FROM athena_workshop.login
WHERE time >= '2021-01-05 15:05:00'
AND dt = '2021-01-05'
LIMIT 10

日時の部分的な抽出処理を楽にする関数集

Athena（Presto）には特定部分の日時を抽出するのに便利な関数が色々用意されています。これらで完結するならこちらを使うとSQLの記述がシンプルになるケースがあります。

以降の節ではそれらの関数についてそれぞれ触れていきます。各関数の引数には日時（TIMESTAMP）に型変換（キャスト）したカラムなどが必要になります。また、各関数の結果はゼロ埋めなどはされず整数などで返ってきます。

YEAR関数

YEAR関数では年部分を抽出することができます。

SELECT user_id, time,
YEAR(CAST(time AS TIMESTAMP)) AS year
FROM athena_workshop.login
WHERE time >= '2021-01-05 15:05:00'
AND dt = '2021-01-05'
LIMIT 10

QUARTER関数

QUARTERでは各日時が第何四半期に該当するのかの値が取れます。値は1からスタートします（1～4の値となります）。

SELECT user_id, time,
QUARTER(CAST(time AS TIMESTAMP)) AS quarter
FROM athena_workshop.login
WHERE time >= '2021-01-05 15:05:00'
AND dt = '2021-01-05'
LIMIT 10

MONTH関数

MONTH関数では月部分の整数が取れます。

SELECT user_id, time,
MONTH(CAST(time AS TIMESTAMP)) AS month
FROM athena_workshop.login
WHERE time >= '2021-01-05 15:05:00'
AND dt = '2021-01-05'
LIMIT 10

WEEK関数

WEEK関数では該当の日時が1年の中で第何週に該当するのかの値が取れます。

SELECT user_id, time,
WEEK(CAST(time AS TIMESTAMP)) AS week
FROM athena_workshop.login
WHERE time >= '2021-02-10 15:05:00'
AND dt = '2021-02-10'
LIMIT 10

DAY関数

DAY関数では該当の日時の日部分が整数で返ってきます。

SELECT user_id, time,
DAY(CAST(time AS TIMESTAMP)) AS day
FROM athena_workshop.login
WHERE time >= '2021-02-05 15:05:00'
AND dt = '2021-02-05'
LIMIT 10

DAY_OF_WEEKとDOW関数

DAY_OF_WEEK関数では該当の日時の曜日の数値が返ってきます。月曜が1で日曜が7となります。

SELECT user_id, time,
DAY_OF_WEEK(CAST(time AS TIMESTAMP)) AS day_of_week
FROM athena_workshop.login
WHERE time >= '2021-02-05 15:05:00'
AND dt = '2021-02-05'
LIMIT 10

DOW関数はDAY_OF_WEEKの短縮系となります。こちらも同じ挙動をします。

SELECT user_id, time,
DOW(CAST(time AS TIMESTAMP)) AS day_of_week
FROM athena_workshop.login
WHERE time >= '2021-02-05 15:05:00'
AND dt = '2021-02-05'
LIMIT 10

DAY_OF_YEARとDOY関数

DAY_OF_YEAR関数では1年の中で何日目に該当するのかの値を取得できます。

SELECT user_id, time,
DAY_OF_YEAR(CAST(time AS TIMESTAMP)) AS day_of_year
FROM athena_workshop.login
WHERE time >= '2021-02-05 15:05:00'
AND dt = '2021-02-05'
LIMIT 10

DOY関数はDAY_OF_YEAR関数の短縮系です。こちらも同じ挙動をします。

SELECT user_id, time,
DOY(CAST(time AS TIMESTAMP)) AS day_of_year
FROM athena_workshop.login
WHERE time >= '2021-02-05 15:05:00'
AND dt = '2021-02-05'
LIMIT 10

HOUR関数

HOUR関数では時間が取れます（0～23）。

SELECT user_id, time,
HOUR(CAST(time AS TIMESTAMP)) AS hour
FROM athena_workshop.login
WHERE time >= '2021-02-05 05:05:00'
AND dt = '2021-02-05'
LIMIT 10

MINUTE関数

MINUTE関数では分の値が取れます（0～59）。

SELECT user_id, time,
MINUTE(CAST(time AS TIMESTAMP)) AS minute
FROM athena_workshop.login
WHERE time >= '2021-02-05 05:08:00'
AND dt = '2021-02-05'
LIMIT 10

SECOND関数

SECOND関数では秒の値が取れます（0～59）。

SELECT user_id, time,
SECOND(CAST(time AS TIMESTAMP)) AS second
FROM athena_workshop.login
WHERE time >= '2021-02-05 05:00:00'
AND dt = '2021-02-05'
LIMIT 10

参考文献・参考サイトまとめ

• The Applied SQL Data Analytics Workshop: Develop your practical skills and prepare to become a professional data analyst, 2nd Edition
• HAVINGの使い方 【SQL入門】
• サブクエリを使った検索条件の設定
• WHERE句のサブクエリは大抵の場合テーブルJOINで代替できる
• SQL関数coalesceの使い方と読み方
• coalesceとは
• pandas get the row-wise minimum value of two or more columns
• Comparison Functions and Operators
• Mathematical Functions and Operators
• Add months to a date in Pandas
• pandasで文字列にスライスを適用して任意の位置・長さの部分を抽出
• String Functions and Operators
• pandas.DataFrameの複数の列の文字列を結合して新たな列を生成
• Treasure Data(Presto/Hive)で月の最終日を取得する方法
• Date and Time Functions and Operators
• athena(presto)で、dateとtimeからJSTを得る
• using time zone in pandas to_datetime
• DataFrame からタイムゾーンを削除したいがうまくいかない
• pandasでunixtimeへの変換
• prestoの日付関数
• pandasデータフレームの日付型⇄文字型変換とdfply使用時のメモ