前回の記事で紹介したテクニックの応用として、最後の方にちょろっとユーザーのアクセスログデータをセッションごとにまとめたりもできるって話を書きました。
参考: 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_logsTD_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アクセスのセッション化意外に使う使い方をトレジャーデータさんがどの程度想定してるのかが不明なので、なかなか推奨しにくいところではあるのですが、知っておくと便利な場面は結構あるので頭の片隅にでも置いといてください。