MySQLでインデックスヒントを使う

※ MySQLって書いてますがその互換の Aurora で検証しています。

MySQLのレコード数が大きなテーブルからデータを検索するとき、インデックスは処理時間の短縮において重要な役割を果たしてくれます。

ただ、一個のテーブルにインデックスを何個も作成しているとSQLによっては最適なインデックスが使われないことがあります。このような場合、インデックスヒントを使うことでクエリを最適化できる可能性があります。

参考: MySQL :: MySQL 8.0 リファレンスマニュアル :: 8.9.4 インデックスヒント

自分の事例で言うと、昔仕事で扱っていたDBでインデックスが10個以上張られているテーブルがあり、きちんと指定してあげないと正しく使ってくれないものがあったことがあります。

また、最近は私用で個人的に集めているデータを溜めているテーブルにおいても、レコード数が増えるにれて正しいインデックスを使ってくれない事例が発生するようになりました。

自分の事例で言うと、どうもMySQLによってインデックスによる絞り込み効果が低いと判断されるとそれが使われないってことがあるようですね。半年分のデータを取得しようとすると正しく実行されないが、1ヶ月分のデータを取得しようとすると正しく使われる、といった事例をよく見ます。

テーブルに貼られているインデックスの確認方法

まずはどんなインデックスが使いうるのか知らないと話にならないのでその確認方法です。

SHOW CREATE TABLE {テーブル名};
でテーブル定義を丸ごと確認するか、インデックス情報取得専用の構文である、
SHOW INDEX FROM {テーブル名};
を使うことで確認できます。

僕個人が分析用に溜めている株価のテーブルだと次のようになります。code(証券コード)とdate(日付)を主キーとしていて、code, date 個別にもインデックスを貼っています。

select
SHOW INDEX FROM price
-- 結果
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
0	price	0	PRIMARY	1	code	A	4561	None	None		BTREE		
1	price	0	PRIMARY	2	date	A	7336473	None	None		BTREE		
2	price	1	code	1	code	A	4383	None	None		BTREE		
3	price	1	date	1	date	A	3166	None	None		BTREE		

key_name列で、primary, code, date の3種類のインデックスがあることが確認できますね。

クエリが利用しようとしているインデックスの確認方法

おそらく処理時間とかを計測したりてこのクエリは正しくインデクス使ってないな、って気づくことになると思うのですが、実際にどのインデックスを使っているかは、そのクエリの先頭に EXPLAIN をつけて実行することで確認できます。

例えば、次のような感じです。possible_keys が利用可能なインデックスで、key が実際に使うインデックスです。

EXPLAIN SELECT
    *
FROM
    price
WHERE
    date >= '2023-01-01'

-- 結果 (これはkeyがNoneなのでインデックスを使ってない)
	id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
0	1	SIMPLE	price	None	ALL	date	None	None	None	7336473	25.53	Using where

EXPLAIN SELECT
    *
FROM
    price
WHERE
    date >= '2023-12-01'

-- 結果 (これはdateをインデックスとして使っている)
	id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
0	1	SIMPLE	price	None	range	date	date	3	None	176076	100.0	Using index condition

さて、上記の上の例でもdateをインデックスとして使って欲しい場合にインデックスヒントを使います。

インデックスヒントの使い方

インデックスヒントを使う場合、特定のインデックスを強制する USE INDEX, 特定のインデックスの利用を防ぐ IGNORE INDEX, USE INDEXよりも強力に指定したインデックスを強制する FORCE INDEX の3種類があります。

使い方は同じで、FROM句のテーブル名の後ろに、
テーブル名 USE INDEX (インデックス名)
のように書きます。カッコを忘れないように注意してください。

FORCE IDNEXでやってみます。

EXPLAIN SELECT
    *
FROM
    price FORCE INDEX(date)
WHERE
    date >= '2023-01-01'

-- 結果 (FORCEしたインデックスが使われている)

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
0	1	SIMPLE	price	None	range	date	date	3	None	176076	100.0	Using index condition

最後に

インデックスヒントを使うことでMySQLが利用するインデックスをある程度制御できるようにあります。ただ、実際にパフォーマスが改善するかどうかはまた別の問題です。というのもMySQLが選んだキーの方が効率的である可能性というのも十分あり得るからです。

このクエリはチューニングが必要だなと感じることがあった場合にインデックスヒントを使うことになると思いますが、処理時間が本当に改善してるかどうかはきちんと計測するようにしましょう。

PythonでSQLite3を使う

前回の記事に引き続き、SQLite3の話です。

今回はPythonのコードの中でSQLite3を使っていきます。Pythonでは標準ライブラリにSQLite3用のインターフェースがあり、特に何か追加でインストールしなくてもSQLite3を使えます。MySQL等と比較したときの大きなアドバンテージですね。
参考: sqlite3 — DB-API 2.0 interface for SQLite databases — Python 3.11.3 documentation

使い方はドキュメントにあるので早速使っていきましょう。

DB(SQLiter3のDBはただのファイル)は前回の記事で作ったのがあるのでそれに接続します。存在しなかったら勝手に生成されるのでここはあまり難しいところではありません。

前回の記事を少しおさらいしておくと、DBファイル名は、mydatabase.dbで、userってテーブルができています。これの全レコードをSELECTするコードは次の様になります。

import sqlite3


# データベースに接続
con = sqlite3.connect('mydatabase.db')
cursor = con.cursor()
# SQLの実行と結果の表示
cursor.execute("SELECT * FROM user")
row = cursor.fetchall()
print(row)
# 以下結果
# [(1, 'Alice', 20), (2, 'Bob', 30), (3, 'Charlie', 40)]

簡単ですね。

結果はタプルのリストで得られました。列名(SELECT文ではアスタリスク指定にしている)の一覧が欲しいと思われますが、それは、coursor.descriptionという属性に入ってます。ただ、入り方がちょっとクセがあって、DB API の仕様との整合性のために、不要なNone が入ってます。

# 列名 + 6個の None という値が入ってる
print(cursor.description)
"""
(
    ('id', None, None, None, None, None, None),
    ('name', None, None, None, None, None, None),
    ('age', None, None, None, None, None, None)
)
"""

# 列名の一覧を得るコードの例
print([row[0] for row in cursor.description])
# ['id', 'name', 'age']

これだとちょっと使いにくいということもあるともうのですが、row_factory というのを使うと、列名: 値 の辞書で結果を得ることができます。ドキュメントに「How to create and use row factories」ってしょうがあるのでそれを参考にやってみます。

con = sqlite3.connect('mydatabase.db')
# row_factoryを指定
con.row_factory = sqlite3.Row
cursor = con.cursor()
cursor.execute("SELECT * FROM user")
row = cursor.fetchall()
print(row)
# [<sqlite3.Row object at 0x109b4de70>, <sqlite3.Row object at 0x109b4dae0>, <sqlite3.Row object at 0x109b4fc70>]

# これらはそれぞれ辞書形でデータが入っているので次の様にして値を取り出せる。
print(row[0]["name"])
# Alice
print(row[0]["age"])
# 20


# 上記の状態だと、DataFrameへの変換が簡単
import pandas as pd


print(pd.DataFrame(row))
"""
   0        1   2
0  1    Alice  20
1  2      Bob  30
2  3  Charlie  40
"""

これでSELECTはできましたね。

次はINSERT等のデータの編集の話です。INSERTだけ取り上げますが、UPDATEもDELETEも同様です。

基本的にexecuteでクエリを実行できるのですが、端末でSQLite3を使う時と違って、明示的にコミットしないと、セッションを切って繋ぎ直したらデータが消えてしまいます。commit()を忘れない様にしましょう。

con = sqlite3.connect('mydatabase.db')
cursor = con.cursor()
# 1行挿入
cursor.execute("INSERT INTO user (name, age) VALUES (?, ?)", ["Daniel", 35])
# コミット
con.commit()
# レコードが1レコード増えてる
cursor.execute("SELECT * FROM user")
row = cursor.fetchall()
print(row)
# [(1, 'Alice', 20), (2, 'Bob', 30), (3, 'Charlie', 40), (4, 'Daniel', 35)]

この他 CREATE TABLE等々も実行できますので以上の知識で通常の使用はできると思います。

ただ、Pythonで使う場合と端末で直接使う場合で異なる点もあります。それが、.(ドット)で始まるSQLite3の固有コマンドたちが使えないってことです。

.table が使えないのでテーブルの一覧を得るには代わりに次の様にします。

# テーブル名の一覧を取得するSQL文を実行
cursor.execute("SELECT name FROM sqlite_master")
print(cursor.fetchall())
# [('user',)]

そして、特定のテーブルのスキーマを確認するクエリはこちらです。userテーブルしか作ってないので、userテーブルで実験します。

# スキーマを取得するSQL
cursor.execute("SELECT sql FROM sqlite_master WHERE name = 'user'")
print(cursor.fetchone()[0])
"""
CREATE TABLE user (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
)
"""

どちらも sqlite_master ってテーブルからデータを引っ張ってきてることからわかる通り、メタデータ的なテーブルが存在してその中に情報があるということです。

ちなみに、この sqlite_master に含まれる列名の一覧は次の様になってます。 sqlite_master の中に sqlite_master の情報はなさそうなので、*で全列SELECTして列名の一覧を取得しています。

cursor.execute("SELECT * FROM sqlite_master WHERE name = 'user'")
print([row[0] for row in cursor.description])
# ['type', 'name', 'tbl_name', 'rootpage', 'sql']

以上で、PythonでSQLite3を使える様になると思います。

SQLite3入門

SQLite3の概要

SQLite3というのは軽量なRDBMSです。C言語で書かれてます。特徴としては、データベースをファイルとして扱い、サーバーが不要って点が挙げられます。

リレーショナルデータベースをちょっと使いたいけど、わざわざMySQLを導入するほどではないとか既存のDB環境は汚したくないってときに重宝します。

ただ、当然ですがMySQLではないので、SQL以外のコマンド群、MySQLで言えばSHOWで始まる各種コマンドなどが無く、その代わりにSQLite3専用のコマンドが用意されていたりして、最初は少し戸惑ったので記事としてまとめておきます。今回はとりあえず端末で直接使う場面を想定して書きます。次回の記事で、Pythonライブラリから使う方法を書く予定です。

インストール方法

macの場合。最初からインストールされています。
バージョンを上げたい場合は、Homebrewでもインストールできるようです。

$ brew install sqlite3

Linux (EC2のAmazon Linux2 を想定) の場合、こちらも最初から入っていますが、yumで入れることもできます。

$ sudo yum update
$ sudo yum install sqlite

ディストリビューションによってはyumでは無く、apt-get等別のコマンドのことがあるので確認して入れたほうが良いでしょう。

Windowsの場合、公式サイトのダウンロードページでバイナリファイルが配布されているのでダンロードして使います。

SQLite3の起動と終了

MySQLとかだと接続情報(ユーザー名やパスワード、外部であればホスト名など)を含む長いコマンドを打ち込んで接続しますが、SQLite3はDBファイル名を指定して実行するだけでそのファイルに接続してDBとして使えるようになります。1ファイルが1データベースで、その中にテーブルを作っていくイメージです。MySQLは一つのRDBMSに複数のデータベース(スキーマ)を作れるのでこの点が違いますね。

mydatabase.db というファイルで起動する場合のコマンドは下記です。既存ファイルを指定しても良いし、無ければ新規に作成されます。
sqlite3 を起動するとプロンプトが sqlite> に変わります。

$ sqlite3 mydatabase.db
SQLite version 3.39.5 2022-10-14 20:58:05
Enter ".help" for usage hints.
sqlite>

終了するには、 .exit か .quit を入力します。 .exitの方はリターンコードを一緒に指定することもできます。先頭に.(ドット)が必要なので注意してください。

sqlite> -- 終了コマンド2種類。どちらかで閉じる。
sqlite> .quit
sqlite> .exit

個人的なぼやきですが、sqlite3の入門記事を書く人はSELECT文とかより先に終了方法を書くべきだと思っています。MySQLのノリでquitとかexitとか入れると syntax error が起きます。vimの終了方法並みに重要な知識です。

SQLite3におけるSQLの実行

SQLの実行は特に変わったことがありません。MySQLとほぼ同じように実行できます。

テーブルを作って、データを入れて、それを選択してみましょう。

-- テーブルの作成
sqlite> CREATE TABLE user (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
);

-- データの挿入
sqlite> INSERT INTO user (name, age) VALUES ('Alice', 20);
sqlite> INSERT INTO user (name, age) VALUES ('Bob', 30);
sqlite> INSERT INTO user (name, age) VALUES ('Charlie', 40);

-- データの検索
sqlite> SELECT * FROM user;
-- 以下結果
1|Alice|20
2|Bob|30
3|Charlie|40

データ挿入時に id は指定しませんでしたが勝手に連番入れてくれてます。これはsqlite3の特徴の一つで、すべてのテーブルにはrowidっていう一意の行idが振られていて、 INTEGER PRIMARY KEY の列を作るとその列はrowid列の別名となり、自動的に振られたidが格納されるようになるのです。

DELETE や UPDATEなども他のRDBMSと同様の構文で行えるのですがこの記事では省略します。

SQLite3のコマンド

冒頭に少し書いた通り、SQLite3では、SQL以外にも独自のコマンドがあります。その代わり他のRDBMS固有のコマンドは使えません。SHOWなんとか、と打ちたくなって動かないことに気づいたら思い出しましょう。

そのコマンド群は . (ドット) 始まりになっています。代表的なものをいつくか挙げておきます。

  • .help: コマンドの一覧と使用方法を表示
  • .tables: データベース内のテーブル一覧を表示
  • .schema: テーブルのスキーマを表示 (指定したテーブルのCREATE TABLE文が見れる)
  • .backup: データベースをバックアップする
  • .import: CSVファイルなどの外部ファイルからデータを読み込み、テーブルに挿入する
  • .quitまたは.exit: SQLite3コマンドラインツールを終了する
  • .headers on|off: Selectした結果のヘッダーとして列名を表示するかどうか切り替える。初期設定はoff

一番最初に書いた .help で コマンドを一覧表示できるので一度一通りみておくと良いでしょう。(helpの起動方法が独自コマンドってこれもなかなか初心者に不親切な設計ですよ。)

まとめ

以上の内容を把握しておけば、最低限の用途でSQLite3を利用できるではと思います。

そして、最低限の用途で済まなくなったらSQLite3について詳しくなるよりも他のRDBMSへの移行を検討していくのが良いかなと考えています。簡易版RDBMSですからね。

トレジャーデータ(Presto)でアクセスログをセッションごとにまとめる方法

前回の記事で紹介したテクニックの応用として、最後の方にちょろっとユーザーのアクセスログデータをセッションごとにまとめたりもできるって話を書きました。
参考: DataFrameを特定列の値が連続してる行ごとにグルーピングする方法

ただ、僕は普段アクセスを分析するときは、Pythonでななくて、トレジャーデータからデータを取ってくる時点でセッションIDを振っているので、自分がいつもやっている方法を紹介しておこうという記事です。トレジャーデータのウィンドウ関数をまとめて紹介したことがあったのでこれも紹介したつもりになってましたがまだでしたね。

使う関数は、 TD_SESSIONIZE_WINDOW というUDFです。名前がTD_で始まっていることから分かる通り、トレジャーデータ専用の関数です。
ドキュメント: Supported Presto and TD Functions – Product Documentation – Treasure Data Product Documentation

例がわかりやすいので、そのまま引用します。アクセスログにタイムスタンプ(time列)とIPアドレス(ip_address列)、アクセスされたパス(path列)があるデータに対して、IPアドレスごとに分けて、60分(=3600秒)間隔が空いてたら別セッションとしてセッションidをふるって操作をやりたい場合次のクエリになります。

SELECT
    TD_SESSIONIZE_WINDOW(time, 3600)
        OVER (PARTITION BY ip_address ORDER BY time)
    AS session_id,
    time,
    ip_address,
    path
FROM
    web_logs

TD_SESSIONIZE_WINDOW 関数に直接渡す引数は、セッションを区切るtimeスタンプの列(トレジャーデータなのでほぼ確実にtime列を使うことになると思います)と、セッションを区切る時間です。そして、ウィンドウ関数なので、OVERを使って、区切りやソート順を指定できます。区切りはIPアドレスだけでなくユーザーIDやデバイス情報はど複数指定することもできます。ソート順はほぼ自動的にtimeを使うことになるでしょうね。

結果として振られるsession_idはUUIDになるので、実行するたびに結果がわかります。ちょっとVALUEを使ってダミーデータ作ってお見せします。
結果がタイムスタンプになると説明しにくかったので、time_formatとして読めるようにした時刻列持つかしました。

-- 実行したクエリ
SELECT
    TD_SESSIONIZE_WINDOW(time, 3600)
        OVER (PARTITION BY ip_address ORDER BY time)
    AS session_id,
    time,
    TD_TIME_FORMAT(time, 'yyyy-MM-dd HH:mm:ss', 'JST') AS time_format,
    ip_address,
    path
FROM
-- 以下ダミーデータ
    (
        VALUES
            (TD_TIME_PARSE('2022-12-12 12:00:00', 'JST'), '127.0.0.x', './hoge1.html'),
            (TD_TIME_PARSE('2022-12-12 12:30:00', 'JST'), '127.0.0.x', './hoge2.html'),
            (TD_TIME_PARSE('2022-12-12 13:30:00', 'JST'), '127.0.0.x', './hoge3.html'),
            (TD_TIME_PARSE('2022-12-12 12:10:00', 'JST'), '127.0.0.y', './hoge1.html'),
            (TD_TIME_PARSE('2022-12-12 12:20:00', 'JST'), '127.0.0.y', './hoge2.html'),
            (TD_TIME_PARSE('2022-12-12 13:19:59', 'JST'), '127.0.0.y', './hoge3.html')
  ) AS t(time, ip_address, path)


-- 以下が出力
f6d83ca3-6f3b-4af8-be10-197a38074cd7	1670814600	2022-12-12 12:10:00	127.0.0.y	./hoge1.html
f6d83ca3-6f3b-4af8-be10-197a38074cd7	1670815200	2022-12-12 12:20:00	127.0.0.y	./hoge2.html
f6d83ca3-6f3b-4af8-be10-197a38074cd7	1670818799	2022-12-12 13:19:59	127.0.0.y	./hoge3.html
7c9f176f-950c-4b5e-a997-eaa0d8ed77ec	1670814000	2022-12-12 12:00:00	127.0.0.x	./hoge1.html
7c9f176f-950c-4b5e-a997-eaa0d8ed77ec	1670815800	2022-12-12 12:30:00	127.0.0.x	./hoge2.html
fa9cb3f0-0c3f-4dbd-9976-b10ea12d653e	1670819400	2022-12-12 13:30:00	127.0.0.x	./hoge3.html

まず、127.0.0.xからのアクセスと127.0.0.yからのアクセスには別のid振られていまね。yの方は間隔が最大でも3599秒しか離れていないので3アクセスが1セッションとして同じIDになっています。
一方で、xの方は、2回目と3回目のアクセスが3600秒離れているのでこれは別セッションとして扱われて、idが2種類になっています。
細かいですがこれは結構重要で、引数で指定した3600ってのは、3600未満までの感覚までしか同一セッションとは見なさないということになります。

さて、ここから応用です。

これ、Webページの個々のアクセスのような動作をセッションかする関数なのですが、少し工夫したら前回の記事で書いたようなタイムスタンプに限らないただの連番とか、あと、日付が連続してるかどうかによるグルーピングとかもできます。

ある特定のユーザーが何日連続で訪問してくれたかって集計とか、特定のコンテンツが何日連続で掲載されていたかといった集計にも使えますね。

例えば、WITH句か何かでユーザーさんがアクセスしてくれた日付のデータを作って、それをTD_TIME_PARSEでタイムスタンプに戻し、60*60*24+1=86401 を区切りにするとできます。

例えばこんな感じです。

-- 実行したクエリ
SELECT
    TD_SESSIONIZE_WINDOW(TD_TIME_PARSE(date), 86401)
        OVER (PARTITION BY user_id ORDER BY date)
    AS session_id,
    date,
    user_id
FROM
    (
        VALUES
            ('2022-12-04', 1),
            ('2022-12-05', 1),
            ('2022-12-06', 1),
            ('2022-12-08', 1),
            ('2022-12-09', 1),
            ('2022-12-05', 2),
            ('2022-12-06', 2)
  ) AS t(date, user_id)

-- 以下出力
321b325b-36eb-43c1-afcd-155cfe7fff8d	2022-12-05	2
321b325b-36eb-43c1-afcd-155cfe7fff8d	2022-12-06	2
2738c31e-79ca-4830-b20f-c48a1b14ef72	2022-12-04	1
2738c31e-79ca-4830-b20f-c48a1b14ef72	2022-12-05	1
2738c31e-79ca-4830-b20f-c48a1b14ef72	2022-12-06	1
5447acfb-0718-43a4-9d0a-4d714b79a7d1	2022-12-08	1
5447acfb-0718-43a4-9d0a-4d714b79a7d1	2022-12-09	1

ユーザーidが1の方を見ると、4,5,6日と8,9日で別のidが振られていますね。
86401 が重要で、ここを86400にすると全部バラバラのidになるので注意してください。

この、TD_SESSIONIZE_WINDOWを通常のWebアクセスのセッション化意外に使う使い方をトレジャーデータさんがどの程度想定してるのかが不明なので、なかなか推奨しにくいところではあるのですが、知っておくと便利な場面は結構あるので頭の片隅にでも置いといてください。

Treasure Data でハッシュ関数計算(MD5/SHA1)

諸事情ありまして、先日 Treasure Data (Presto) で 文字列のSHA1を計算する必要が発生しました。(滅多に無いことなのですが。)
その時に方法を検証したので紹介します。ついでにMD5のやり方も紹介します。PythonやMySQLでやる場合は、MD5もSHA1もたいして変わらないのですが、Treasure Data はこの二つは事情がかなり違います。その辺も書いていきたいと思います。

まず、他のDBではどうなっているかということで、MySQLの場合を見ていきます。(僕はAWS RDSのMySQL互換のインスタンスで試してます。)
MySQLには md5 と sha1 という関数が実装されており、これらが「文字列」を受け取り、結果を「16進法表記の文字列」で返してくれます。コメントでつけてるのは結果です。
ドキュメントはこちら: 12.13 暗号化関数と圧縮関数

SELECT
    MD5('abcde'), -- ab56b4d92b40713acc5af89985d4b786
    SHA1('abcde') -- 03de6c570bfe24bfc328ccd7ca46b76eadaf4334

文字列をそのまま受け取ってくれるし、結果も文字列なので簡単ですね。

Pythonで行う方法は昔記事にしました。
参考: pythonでMD5
文字列をバイト型に変換して、専用の関数を呼び出し、結果がバイト型なのでそれを文字列に変換するのでしたね。

さて、いよいよ本題のTreasure Dataです。まず、MD5の方なのですが、実はTD_MD5というUDFが実装されており、MySQLのMD5と同じように動作します。
参考: Supported Presto and TD Functions – Product Documentation

SELECT
    TD_MD5('abcde') -- ab56b4d92b40713acc5af89985d4b786

簡単に使えて結果もMySQLと同じですね。

さて、TD_MD5の存在を知っていたので僕は SHA1についても TD_SHA1が存在すると勘違いしていました。しかし、ドキュメントの先ほどのUDFのページを見ると TD_SHA1はありません。無理矢理書いて実行しても、「Function td_sha1 not registered」と言われます。

では、Treasure Data で SHA1は計算できないのかとも思ったのですが、実はPresto自体に、SHA1という関数が定義さてれいます。(もちろんMD5もあります。)
参考: Binary Functions and Operators — Presto 0.263.1 Documentation

しかし、これつかえばMySQLと同じように動かせる!と思って試したところエラーになりました。

SELECT
    SHA1('abcde')
-- 以下エラーメッセージ
-- Unexpected parameters (varchar(5)) for function sha1. Expected: sha1(varbinary) 

ドキュメントを見ていただけるとわかる通り、PrestoのMD5やSHA1はバイナリを受け取ってバイナリを返します。要するにPythonのハッシュ関数と似たような動きをするのです。

md5(binary) → varbinary
Computes the md5 hash of binary.

sha1(binary) → varbinary
Computes the sha1 hash of binary.

ということで、文字列は受け取ってくれないで事前にキャストしましょう。

SELECT
    SHA1(CAST('abcde' AS VARBINARY)) -- A95sVwv+JL/DKMzXyka3bq2vQzQ=

CASTしてから渡すと無事に動きました。でも結果が違いますね(実は表記方法が違うだけで中身は違わないのですが。) 結果がバイナリで戻り、(たぶん)BASE64表記で帰ってきたようです。

TO_HEXっていうバイナリを16進法表記の文字列に変換する関数もあるので、それも追加します。

SELECT
    TO_HEX(SHA1(CAST('abcde' AS VARBINARY))) -- 03DE6C570BFE24BFC328CCD7CA46B76EADAF4334

MySQLの結果に近づいてきました。ただ、A-Fのアルファベットが大文字になっていますね。LOWERで小文字にしましょう。

SELECT
    LOWER(TO_HEX(SHA1(CAST('abcde' AS VARBINARY)))) -- 03de6c570bfe24bfc328ccd7ca46b76eadaf4334

これでMySQLのSHA1と同じ結果が得られました。
ちなみに、MD5の方もこれと同じ流儀で計算できます。

SELECT
    LOWER(TO_HEX(MD5(CAST('abcde' AS VARBINARY)))) -- ab56b4d92b40713acc5af89985d4b786

どう考えても TD_MD5 一発で済ませる方が簡単ですね。
将来的にTD_SHA1も実装されることを期待したいです。

VALUESを使ったダミーデータの生成

WINDOW関数の話はひと段落しましたが、今回もPrestoの話です。例によって僕はトレジャーデータで検証しています。(今回の記事で紹介する内容はMySQLでは動かないという話も見かけました。)

ここ最近の記事で掲載したサンプルのSQLでは、テーブルに保存されたデータではなくSQLで生成したデータを使っていました。事前にこういうデータを用意してますとか書くのが面倒だったので。その際、ARRAY関数で作った配列を並べて、UNNESTでテーブル型に変換していたのですが、実はVALUES関数をつかってダミーデータを作ることもできるそうです。

この機会に、VALUES関数について調べたので、わかったことを記事にまとめておきます。PrestoのVALUES関数のドキュメントはこちらです。
参考: VALUES — Presto 0.261 Documentation

Description を読むと、VALUESは問い合わせ(query)が使用できる場所ならどこでも使用できる、トップレベルでさえ使える、といきなり衝撃的なことが書かれています。僕は、INSERT文などの特定の構文の中でしか使えないと思っていました。

試しにトップレベルで使ってみましょう。

VALUES 1, 2, 3

-- 出力結果
_col0
1
2
3

確かに、無名列(_col0)に3行のレコードが生成されました。

複数列のデータを作ることもできます。

VALUES
  (1, 'a'),
  (2, 'b'),
  (3, 'c')

-- 出力結果
_col0, _col1
1,     'a'
2,     'b'
3,     'c'

ドキュメントには、ASを使えばテーブルと列に名前も付けれる、と書かれているので、色々試したのですが、トップレベルでVALUES を使った場合にASで列名をつける方法は結局見つけられませんでした。いろんな位置にカッコをつけたりして検証したのですが。

無名テーブルではなく、テーブル名と列名をつけたい場合は、次のようにSELECT文のFROM句の中で使うことで実現できます。

SELECT
  id,
  name
FROM
  (
    VALUES
      (1, 'a'),
      (2, 'b'),
      (3, 'c')
  ) AS t(id, name)

-- 出力結果
id, name
1,  'a'
2,  'b'
3,  'c'

この結果は、UNNESTを使って書いた下のクエリの結果と同じになります。

SELECT
  id,
  name
FROM
  UNNEST(
    ARRAY[1, 2, 3],
    ARRAY['a', 'b', 'c']
  ) AS t(id, name)

UNNEST と VALUES のどちらがいいか、という話ですが 読みやすさというか、直感的でぱっと見で結果の予想がつきやすいのは VALUESの方なんじゃないかなと思います。ただ、10行くらいのダミーデータを生成しようとすると、UNNESTの方が行数が少なくなりやすく、このブログのサンプルコードとしてはそちらの方が使いやすかったりします。非本質的な部分なのであまりスペースを取りたくないので。

処理速度等の性能面の比較は行っていません。性能が気になるほどの莫大なデータをこのような方法でSQLに直接書くべきではないと思いますので、もしそのような大規模データをサンプルとして用意したい場合は、VALUESを使うとか言ってないで、素直にどの下のテーブルに格納してそこからSELECTした方が良いと思います。

Prestoの残りのWINDOW関数について紹介

6記事連続ですが今回もPrestoのWINDOW関数の記事です。例によって僕はトレジャーデータで検証しています。

実は前回のframeの記事でWINDOW関数シリーズは終わりにしようかと思っていたのですが、ドキュメントを見ると、まだ紹介してない関数はあと3つだけになっており、試しに動かしてみたら予想とちょっと違う動きも見られたので切りよくこれらまで紹介することにしました。

ドキュメントはこの記事もここです。
参考: Window Functions

この記事で紹介する関数は次の3つです。
- first_value(x)
- last_value(x)
- nth_value(xoffset)

first_value の説明は、 Returns the first value of the window. とあり、
last_value の説明は、Returns the last value of the window. とどちらも簡潔に書かれています。WINDOW内の最初の値と最後の値を返してくれると読めますね。

そして、 nth_value は offset で指定した数値番目(数値は1以上)の値を返してくれます。

さて、いつもみたいにダミーデータを使ってやってみましょう。
試したコードは下記です。

WITH
    sample_table AS(
        SELECT
            id,
            category,
            value
        FROM
            UNNEST(
                ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
                ARRAY['A', 'A', 'A', 'A', 'A', 'A', 'B', 'B', 'B', 'B'],
                ARRAY[20, 10, 20, 30, 5, 50, 15, 30, 20, 40]
            ) AS t(
                id,
                category,
                value
            )
    )
SELECT
    id,
    category,
    value,
    first_value(value) OVER(PARTITION BY category ORDER BY value) AS first_value_,
    last_value(value) OVER(PARTITION BY category ORDER BY value) AS last_value_,
    nth_value(value, 4) OVER(PARTITION BY category ORDER BY value) AS nth_value_4
FROM
    sample_table

さて、この結果はどうなるでしょうか?3関数とも、OVER(PARTITION BY category ORDER BY value) をつけたので、 categoryの値で、’A’ と ‘B’ に分けられ、 value 列の値でソートされた上で、関数が実行されそうですね。

僕は、 first_value(value) の結果は、category ‘A’と’B’ でそれぞれ一番小さい値である、5と15が返され、 last_value(value) の結果は、それぞれのカテゴリで一番大きい、50と40が返されると予想していました。
そして、nth_value(value, 4) は’A’,’B’それぞれの小さい方から4番目の値である、20と40が帰ってくると思っていました。

ところが実行結果は以下の通りです。

id, category, value, first_value_, last_value_, nth_value_4
5,  'A',      5,     5,            5,           NULL
2,  'A',      10,    5,            10,          NULL
1,  'A',      20,    5,            20,          20
3,  'A',      20,    5,            20,          20
4,  'A',      30,    5,            30,          20
6,  'A',      50,    5,            50,          20
7,  'B',      15,    15,           15,          NULL
9,  'B',      20,    15,           20,          NULL
8,  'B',      30,    15,           30,          NULL
10, 'B',      40,    15,           40,          40

いかがでしょうか。この結果が予想通りだった!って人は(元々仕様を正確に理解していた人以外に)いらっしゃいますか?

first_value_ 列はいいですね。行をcategory列でグループ分けして、valueでソートした後、それぞれのcategoryのグループの最初の値である 5と15 を返してくれています。

問題は、last_value_ 列と、 nth_value_4 列です。
last_value_ の方なんて、同じ行の value 列の値をそのまま返してきていて、関数を使った意味がないです。

なぜこのような挙動になるのかを理解するには、前回の記事で紹介したframeについて知る必要があります。
参考: WINDOW関数のframeのROWSモードについて

WINDOW関数には frameというオプションがあり、何も指定しないとこれが、
RANGE UNBOUNDED PRECEDING
になるのでした。

つまり、PARTITION BY で区切ったグループ内の、最初の行からその行と同じ値の行までを関数の適用範囲とするわけです。これにより、 last_value が指し示す最後の行とは、その行と(ORDER BYで指定した行の値が)同じ値の行まで、の範囲で見た場合の最後の行となり、その結果として同じ行のvalue列の値をそのまま返してきてきたわけです。

ちなみに、 last_value(value) ではなく、 last_value(id) とするとまた微妙に結果が変わります。
category Aで、value列が20の行が二つありますが、2行とも 3が返ってきます。

nth_value_4 の方も原理は同じです。最初の行からその行と同じ値の行まで、の範囲に含まれる行数が4行未満の場合、返す値がないのでNULLになってしまうのです。
未指定の場合 frame は ROWSモードではなくRANGEモードなので、 その行までに4行含まなくても、その行と同じ値の行まで含めて4行確保できれば値を返してくれます。
だから、結果の3行目(id:1の行)は20が返ってきているのです。

ここまでの説明で、 last_value と nth_value って使えない、もしくは使いにくいなと感じられた人も多いかと思います。
これらの関数でイメージ通りに PARTITION BY で区切った範囲の最終行や、 n番目の行の値を素直に出力したい場合は、 合わせて frameを指定してあげることで可能になります。
PARTITION BY で区切った範囲の最初から最後までなので、
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
ですね。

要するに次のように書けば良いです。
(記述量が多くなるので、SELECT句内の該当行だけ書きました)

  last_value(VALUE) OVER(
    PARTITION BY
          category
      ORDER BY
      VALUE
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_value_,
  nth_value(VALUE, 4) OVER(
    PARTITION BY
      category
    ORDER BY
      VALUE
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS nth_value_4

これで出力結果を載せる前に書いてた、イメージしてた通りの結果が得られます。

WINDOW関数のframeのROWSモードについて

またWINDOW関数の記事です。例によって、SQLクエリエンジンはPrestoを仮定し、僕はトレジャーデータで検証して記事を書いています。
今回は前回の記事でチラチラと登場していたframeについて説明を試みます。
(正直、僕もこれまであまり使ってこなかったので詳しくはありません。)

ドキュメントは最近ずっと参照しているここ。
参考: Window Functions

さて、改めてWINDOW関数の説明を見ると、WINDOW関数は次の形で呼び出すと書かれています。

function(args) OVER (
    [PARTITION BY expression]
    [ORDER BY expression [ASC|DESC]]
    [frame]
)

function は SUMとか ROW_NUMBER などのことですね。
大括弧書きの部分はオプションなので省略可能です。よく見ると、ORDER BY の次に [frame] というのがあります。これが今回の記事のテーマです。

WINDOW関数で集計する時は、まず各レコードを、PARTITION BY で指定した列の値によってグループ分け、ORDER BY で指定した列と、指定したソート順(ASC/DESC)によって整列します。
そして、 「frame で指定した範囲」で集計するのです。

前回の記事で見た通りframe を何も指定しないと、ORDER BY がなければPARTITION BY で区切ったグループ全体を集計し、ORDER BYがあれば、PARTITION BYで区切ったグループ内の最初の行から、その行と同順位の行までを集計します。

これが、frameを指定することで、 ORDER BYを指定した上でも、「PARTITION BY で区切ったグループ全体」とか、「その行からPARTITION BY で区切った最後の行まで」とか、「その行の3行前から5行後ろまで」など、さまざまなレンジで集計ができます。これによって「直近7レコードの移動平均」みたいなこともできるわけですね。

frameの指定方法ですが、ドキュメントには次のように記載されています。(次の2行のどちらか)

{RANGE|ROWS} frame_start
{RANGE|ROWS} BETWEEN frame_start AND frame_end

最初に、RANGEモードかROWSモードをを指定し、1番目の記法では開始行(frame_start)のみ指定、2番目の記法では開始行(frame_start)と終了行(frame_end)を指定します。1番目の記法を採用した場合は、終了行はCURRENT ROW を指定するのと同じ動きになります。

そして、frame_start/ frame_start は 次の5種類の表記が使えます。 (ROWSモードでしかサポートされていないのが2種類あるのでRANGEモードでは3種類)

UNBOUNDED PRECEDING
expression PRECEDING  -- only allowed in ROWS mode
CURRENT ROW
expression FOLLOWING  -- only allowed in ROWS mode
UNBOUNDED FOLLOWING

UNBOUNDED PRECEDING は一番最初の行からで、UNBOUNDED FOLLOWINGは一番最後の行までの意味です。CURRENT ROW は ROWSモードであればその行まで、RANGEモードであれば、その行と同じ順位の行までです。(前回の記事で試してるのは未指定なのでデフォルトのRANGEモードの方です。)

expression PRECEDING と expression FOLLOWING は ROWSモードでしか使えませんが、expression に数字を入れて、 3 PRECEDING とか、 5 FOLLOWING のように書きます。それぞれ3行前、5行後ろの意味です。

さて、だらだら説明を書いてきたのでこの辺で一つやってみましょう。設定できるオプションが多くておそらく用途も多いROWS モードの方をやってみます。
例が無駄に複雑になるので次の実行例ではPARTITION BY は使いませんでしたが、もちろん本当は使えます。集計関数は挙動を確認しやすいSUMを例にしていますがこちらももちろん他の関数も使えます。

WITH
    sample_table AS(
        SELECT 
            id,
            value
        FROM
            UNNEST(
                ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
                ARRAY[20, 10, 40, 30, 50, 30, 20, 60, 10, 30]
            ) AS t(
                id,
                value
            )
)
SELECT 
    id,
    value,
    SUM(value) OVER() AS sum_1,
    SUM(value) OVER(ROWS UNBOUNDED PRECEDING) AS sum_2,
    SUM(value) OVER(ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS sum_3,
    SUM(value) OVER(ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_4,
    SUM(value) OVER(ORDER BY id ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS sum_5
FROM
    sample_table

-- 以下出力結果
id, value, sum_1, sum_2, sum_3, sum_4, sum_5
1,  20,    300,   20,    300,   30,    NULL
2,  10,    300,   30,    280,   70,    20
3,  40,    300,   70,    270,   80,    30
4,  30,    300,   100,   230,   120,   70
5,  50,    300,   150,   200,   110,   80
6,  30,    300,   180,   150,   100,   120
7,  20,    300,   200,   120,   110,   110
8,  60,    300,   260,   100,   90,    100
9,  10,    300,   270,   40,    100,   110
10, 30,    300,   300,   30,    40,    90

色々試したので順番に見ていきましょう。
sum_1 列はただの比較用です。前回の記事でも見ましたが、ORDER BY含めて何も指定しなければ全体の和になります。これは、デフォルトのRANGEモードが作動しており、行同士の間に順序が定義されていないので、全行が同一順位として扱われ、同じ順位の行まで足すという挙動により全行が足されたからです。

sum_2 以降が ROWSモードになります。 まず sum_2 ではROWSモードへの変更のみ行いました。(RANGEモードも未指定の場合の挙動は RANGE UNBOUNDED PRECEDING なので、本当にモードだけ変えています。)
ご覧の通り、ROWS モードでは ORDER BYがなくてもvalue列の累積和になっています。
これはROWSモードでは、「先頭の行から、その行まで」集計するという挙動になったからです。前回の記事でソートに指定した列に同じ値ががあった場合に少し不思議な挙動をするという話を書きましたが、ROWSモードではそれも発生せず、普通に累積和になります。

さて、この後のsum_3以降がframe機能の本番で初期設定ではなく、frameの記法を使って範囲を指定しています。
sum_3 では、 BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING として、その行から(例ではPARTITION BY使ってないけど、使ってればそのグループ内の)最後の行まで、という指定になっています。逆順の累積和っぽい挙動になっているのが見て取れると思います。

sum_4 は、 BETWEEN 1 PRECEDING AND 1 FOLLOWING と指定しており、これは1行前から1行後ろまで、合計3行の和をとっています。
例えば id 5 の行を見ると、 30+50+30 = 110 になっていますね。

sum_5 は、 BETWEEN 3 PRECEDING AND 1 PRECEDING と指定していて、これは3行前から1行前までの合計3行の和を取るものです。1行目(id:1の行)は集計対象のデータがないのでNULLになってますね。
ここで言いたかったことは、BETWEENで指定するレンジの中にその行自身を含める必要はないということです。
これを使うと、例えば日別の時系列データで、BETWEEN 7 PRECEDING AND 1 PRECEDING とすることで、前日までの過去1週間の集計値(平均など)とその日の値を比べる、といったことも可能になります。

さて、ここまでの説明で、 frameのROWSモードの使い方は概ね網羅できたのではないかと思います。集計の幅がグンと広がると思うのでぜひ使ってみてください。

一方で、 RANGE モードについてはほぼ放置しています。

Presto 以外の エンジンの中には、 実はRANGEモードをよりしっかりサポートしているものもあるそうです。その場合は、ORDER BYで指定した列の値に従って、値の差分が何以下の行を集計対象にするとか、そういう指定ができるそうです。(expression PRECEDING/ expression FOLLOWING がRANGEモードでもサポートされているとか。)

ただ、Prestoではサポートされておらず、トレジャーデータに試しに打ち込んでみても
Window frame RANGE PRECEDING is only supported with UNBOUNDED
などと、エラーが返ってくるだけなので試せていません。
(ではなぜRANGEモードをデフォルトにしているのかとか言いたいことはあるのですが)動かないものは仕方ないので、今回の記事はROWSモード中心に紹介させていただきました。
実際、ROWSモードが動けば大抵の要件には対応可能だと思います。

Prestoでは集計関数をWINDOW関数として扱える

今回もPrestoのWINDOW関数の話です。例によってトレジャーデータで動作を確認しています。参照するドキュメントは直近の数記事と同じこちら。
参考: Window Functions

All Aggregate Functions can be used as window functions by adding the OVER clause. The aggregate function is computed for each row over the rows within the current row’s window frame.

とドキュメントにある通り、Prestoの全ての集約関数はその後ろにOVER()をつけることで、WINDOW関数として動作させることができます。この場合、GROUP BY は要らないくなるので注意してください。

ちなみに、 Aggregate Functions (集約関数) の一覧はこちらのページにあります。
参考: Aggregate Functions

OVER() の中には他のWINDOW 関数と同じように、PARTITION BY と ORDER BY を指定できます。PARTITION BY を指定する場合はもちろん PARTITION BYで指定した列の値でグループ分けして、そのグループ内で集計が走ります。そしてさらに、 ORDER BY の有無によっても、集計される範囲が変わるので注意が必要です。この辺の概念を正しく理解するには、WINDOW関数の Frame という概念(というかオプション)について説明する必要があるので、別記事で紹介しようと思うのですが、今記事Frameを指定しない場合の挙動で説明します。

ORDER BY を指定しない場合、集計範囲は、PARTITION BYで指定されたグループ内の全行になります。PARTITION BYもORDER BYもどちらもしてしてない場合は単純にSELECTされた前レコードが集計範囲です。

ORDER BY を指定する場合、集計範囲は、PARTITION BYで指定されたグループ内のレコードをORDER BYでソートしたものの、「先頭からその行と同じ順位の行まで」になります。

このように言葉で書いてもわかりにくいと思うので、一番基本的な集約関数であるSUM() を使って、実際にやってみます。

WITH sample_table AS(
    SELECT 
        id,
        category,
        value
    FROM
        UNNEST(
            ARRAY[1, 2, 3, 4, 5, 6],
            ARRAY['A', 'A', 'A', 'A', 'B', 'B'],
            ARRAY[20, 10, 20, 30, 15, 30]
        ) AS t(
            id,
            category,
            value
        )
)
SELECT 
    id,
    category,
    value,
    SUM(value) OVER() AS sum_over_1,
    SUM(value) OVER(PARTITION BY category) AS sum_over_2,
    SUM(value) OVER(PARTITION BY category ORDER BY id) AS sum_over_3,
    SUM(value) OVER(PARTITION BY category ORDER BY value) AS sum_over_4
FROM
    sample_table

-- 以下出力結果
id, category, value, sum_over_1, sum_over_2, sum_over_3
1,  'A',      20,    125,        80,         20 
2,  'A',      10,    125,        80,         30
3,  'A',      20,    125,        80,         50
4,  'A',      30,    125,        80,         80
5,  'B',      15,    125,        45,         15
6,  'B',      30,    125,        45,         45

OVERの中に何も指定していないsum_over_1 の列の値は全行等しく6行のvalue 列の値の合計である125になりましたね。
そして、PARTITON BY を指定した sum_over_2 列の値は、category列の値が ‘A’なのか ‘B’なのかによって変わり、それぞれ該当する行のvalue列の値になっています。

そして、注目するのは ORDER BY も指定した sum_over_3 列です。
これは、 id でソートした後のvalue列の累積和が返されています。

どちらの値にも用途はたくさんありそうですね。目的に応じて使い分けていきましょう。
例えば累積和の方は、日々の売り上げから累積売り上げを算出するといったことができますし、全体の集計値の方はもう少し工夫して、そのグループ内のシェアを計算するなどの用途で使えます。要するにSELECT句に次のように入れたりです。(1.0を最初に掛けるのは型をDOUBLEに変換するため。CASTしても良し)
1.0 * value / SUM(value) OVER(PARTITION BY category)
次のように、グループ内の平均との差分を求めるというのも良いです。
value – AVG(value) OVER(PARTITION BY category)

さて、ORDER BYした時の挙動について、もう一点補足です。
先ほど説明の中で、「先頭からその行と同じ順位の行まで」などというまどろこしい表現を使いました。「先頭からその行まで」ではないんですね。
先ほどの例では ORDER BY で id列を指定し、 id列には重複した値がなかったのであまり気にしなくてよかったのですが、値の重複があり、ソートした順位が同順になる行が複数あると少し気をつける必要があります。 ということで、 id ではなくvalue 列でソートしてみたのが次の結果です。

WITH sample_table AS(
    SELECT 
        id,
        category,
        value
    FROM
        UNNEST(
            ARRAY[1, 2, 3, 4, 5, 6],
            ARRAY['A', 'A', 'A', 'A', 'B', 'B'],
            ARRAY[20, 10, 20, 30, 15, 30]
        ) AS t(
            id,
            category,
            value
        )
)
SELECT 
    id,
    category,
    value,
    SUM(value) OVER(PARTITION BY category ORDER BY value) AS sum_over_4
FROM
    sample_table

-- 以下出力結果
id, category, value, sum_over_4
2,  'A',      10,    10
1,  'A',      20,    50
3,  'A',      20,    50
4,  'A',      30,    80
5,  'B',      15,    15
6,  'B',      30,    30

さて、sum_over_4 列を順番に見ていきましょう。 1行目の結果はいいですね。 valueと同じ10です。そして、4行目の結果は1〜4行目の和で、 10+20+20+30=80 となります。
注目すべきは2行目と3行目です。これがどちらも50になっています。これは、10+20+20の結果です。

3行目が50なのはいいとして、2行目も50なのは少し違和感ありますね。
これが先ほど説明した、「先頭からその行と同じ順位の行まで」を集計するということです。

このような挙動になる理由を正しく理解するには、この記事の最初の方にも書きましたがFrameという概念を理解する必要があります。

別の記事でちゃんと書こうと思うのですが、Frameには行を基準に集計範囲を決めるROWモードと、値を基準に集計範囲を決めるRANGEモードというのがあります。
そして、Frameを指定しなかった場合の挙動は、値を基準とするRANGEモードなのです。

もう少し書くと、Frameを指定しないと、
RANGE UNBOUNDED PRECEDING
と指定するのと同じになり、これが「先頭からその行と同じ順位の行まで」を意味します。
もっと正確にいうと、 UNBOUNDED PRECEDING は、「先頭から」という意味で、「どこまで」は省略されており、「どこまで」を省略した時のデフォルトの挙動が「CURRENT ROW」を指定した場合に等しく、RANGEモードにおけるCURRENT ROWはその行ではなく、「その行と同じ値の行まで」を意味します。ややこしいですね。

とりあえず、Frameについては何も指定しなかった場合にどんな挙動になるのか、をしっかり理解しておくことが重要だと思います。想定と違った動きをしていた場合、SUMの場合は比較的気付きやすいですが平均(AVG)や分散(VAR_SAMP)の場合は見落としがちです。

Prestoで前後のレコードの値を参照する

3記事連続ですが、今回もPrestoのWindow関数の話です。例によって、TreasureDataで動かすことを想定しています。
Window関数の話なのでドキュメントの参照するページもこちら。
参考: Window Functions
今回は、Value Functionsのセクションですね。

さて、SQLで集計業務を行なっていて、同じ行内の別の列の値ではなく、別の行の値を参照する必要が出てくる場面は結構あります。特に前後隣(上下隣と呼ぶ方が直感的かも)の値を参照する場面は多々あります。

例えば、Webサイトのアクセス分析において、ユーザーが1つ前に見ていたページを取得してページ遷移を確認したり、次のページのアクセス時刻との差分からそのページの滞在時間を測ったりする場合です。そのほかにも日毎に集計された時系列データにおいて前日からの変化量を見る、といった用途もあるでしょう。

そのように、前後の値を取得したい時は、LAG() と LEAD() という関数を使用することができます。前の値を取得したい場合が LAG()で、後の値を取得したい場合に使うのがLEAD()です。

LEADの方を例に使い方を説明すると、次のようになります。

LEAD({値を取得したい列名}, {何行後のレコードを取得したいか}, {デフォルト値})
OVER(PARTITION BY {グループ分けに使う列名} ORDER BY {ソートに使う列名} {ASC/DESC})

{何行後のレコードを取得したいか} は 0以上の値が指定でき、省略した場合は1になります。
{デフォルト値}は、PARTITION BY で指定したグループ内に指定した行数だけ後のデータがなかった場合に返す値です。省略した場合はNULLになります。

PARTITION BY、ORDER BY は他の Window関数と同じですね。PARTITION BYを使わなかった場合は全レコードまとめて処理されます。ORDER BY も省略できますが、意図せぬ順番で結果が返ってくることがあるので、原則何か指定しましょう。

説明が長くなってきたので実際にやってみます。次の例は、A/B/C 3人のユーザーの架空のWEBページのアクセスログを集計するものです。(1時間おきにアクセスするWebサイトって現実的にあり得ないとか色々ツッコミどころはありますがあくまでも関数の動きを見やすくするための例なのでご了承ください。)
LAGとLEADを使って、ユーザーごとに、そのページの前後にアクセスしたページを出力しています。

WITH
    sample_table AS (
        SELECT 
            access_at,
            user,
            page
        FROM
            UNNEST(
                ARRAY['00:00:00', '01:00:00', '02:00:00', '03:00:00', '04:00:00',
                      '05:00:00', '06:00:00', '07:00:00', '08:00:00', '09:00:00'],
                ARRAY['A', 'A', 'A', 'A', 'B', 'B', 'B', 'C', 'C', 'C'],
                ARRAY['page0', 'page1', 'page2', 'page3', 'page4',
                      'page5', 'page6', 'page7', 'page8', 'page9']
            ) AS t(
                access_at,
                user,
                page
            )
  )
SELECT
    access_at,
    user,
    page,
    LAG(page) OVER (PARTITION BY user ORDER BY access_at) AS rag_page,
    LEAD(page) OVER (PARTITION BY user ORDER BY access_at) AS lead_page
FROM
    sample_table
ORDER BY
    access_at

-- 以下出力結果
access_at,  user, page,    rag_page, lead_page
'00:00:00', 'A',  'page0', NULL,     'page1'
'01:00:00', 'A',  'page1', 'page0',  'page2'
'02:00:00', 'A',  'page2', 'page1',  'page3'
'03:00:00', 'A',  'page3', 'page2',  NULL
'04:00:00', 'B',  'page4', NULL,     'page5'
'05:00:00', 'B',  'page5', 'page4',  'page6'
'06:00:00', 'B',  'page6', 'page5',  NULL
'07:00:00', 'C',  'page7', NULL,     'page8'
'08:00:00', 'C',  'page8', 'page7',  'page9'
'09:00:00', 'C',  'page9', 'page8',  NULL

{何行後のレコードを取得したいか} (OFFSET) は省略したので、RAGとLEADは、それぞれ1行前と後のpage列の値を取得しています。 page, rag_page, lead_pageの各列の結果を見比べるとわかりやすいと思います。

rag_pageで1つ前にアクセスしたページ、lead_pageで1つ後にアクセスしたページが取れていますね。

userでグループを切っているので、各ユーザーごとの最初のアクセスのLAGと、最後のアクセスのLEADは対象のレコードがないのでNULLになっています。

もし、関数を呼び出すときに3つ目の引数としてデフォルト値を指定していたら、NULLではなくそのデフォルト値が入った状態で戻されます。

ORDER BY で指定した、access_atに対しては、 ASC/DESCを指定しなかったので、デフォルトのASC(昇順)で動作しています。もし、それぞれDESCを指定したらaccess_atについて降順になり、前後が入れ替わるのでLAGとLEADの結果が入れ替わります。

ORDER BYで自分が何順を指定しているかと、 LAGとLEADのどちらを使っているかはセットで確認し、想定した結果が得られているかどうかは慎重に検証しましょう。

LAGとLEAD、わざわざ2関数使わなくても、LAGのOFFSET に -1 を入れたら LEADの動きになるんじゃないの?と思って試したのですが、これはエラーになりました。OFFSETは0以上の値しか受け付けないようです。 ちなみにOFFSETに0を入れるとその行の値をそのまま返すので、LAGやLEADの意味はなくなります。