ECサイト上のアクセスログのコンバージョンを起点に、過去のアクセスを順に遡ってシーケンスしたいと思ったが、なかなかうまくいかず。。。
そこで初めてアクセスしてから、コンバージョンまでのユーザーのログを1つのグループになるようなプログラムを作成し、
そのグループ内で最新の日付から降順に番号を採番できるように前処理しようと思います。
<処理イメージ>
Aさんが、2023年1月1日から2023年1月10日までセッションS1からS3でアクセスを実施し、セッションS2とS3で何らかのコンバージョンが発生した(本来こんなログはあり得ませんが、例として簡単にするために笑)
・アクセスログ
・グループIDをセットするプログラムをPL/pgSQLで作成する
アクセスログに初めて(もしくはコンバージョン後)からのアクセスからコンバージョンまでを一つのグループにする
・グループIDごとにコンバージョンから降順に採番
データの準備
--テーブル作成
create table accesslog(
userid varchar(1)
,date date
,sessionid varchar(2)
,conversion varchar(1)
,groupid varchar(100)
);
--データセット
insert into accesslog2
values
('A', '2023/1/1', 'S1', '0'),
('A', '2023/1/2', 'S2', '0'),
('A', '2023/1/3', 'S2', '0'),
('A', '2023/1/4', 'S2', '1'),
('A', '2023/1/5', 'S2', '0'),
('A', '2023/1/6', 'S3', '0'),
('A', '2023/1/7', 'S3', '0'),
('A', '2023/1/8', 'S3', '0'),
('A', '2023/1/9', 'S3', '1'),
('A', '2023/1/10', 'S3', '0'),
('B', '2023/1/1', 'S4', '0'),
('B', '2023/1/2', 'S4', '0'),
('B', '2023/1/3', 'S4', '0'),
('B', '2023/1/4', 'S4', '1'),
('B', '2023/1/5', 'S4', '0'),
('B', '2023/1/6', 'S4', '0'),
('B', '2023/1/7', 'S4', '0'),
('B', '2023/1/8', 'S4', '0'),
('B', '2023/1/9', 'S4', '1'),
('C', '2023/1/1', 'S5', '0'),
('C', '2023/1/2', 'S5', '0'),
('C', '2023/1/3', 'S5', '0'),
('C', '2023/1/4', 'S5', '0'),
('C', '2023/1/5', 'S5', '0'),
('C', '2023/1/6', 'S5', '0'),
('C', '2023/1/7', 'S5', '0'),
('C', '2023/1/8', 'S5', '0'),
('C', '2023/1/9', 'S5', '0'),
('D', '2023/1/7', 'S4', '0'),
('D', '2023/1/8', 'S4', '1'),
('D', '2023/1/9', 'S4', '1'),
('D', '2023/1/10', 'S4', '0'),
('E', '2023/1/8', 'S4', '0'),
('E', '2023/1/9', 'S4', '1'),
('E', '2023/1/10', 'S4', '1'),
('E', '2023/1/11', 'S4', '0');
PL/pgSQLサンプル
特に説明なしでサンプルコードです
create or replace procedure placcesslog()
as $$
--宣言
declare
--変数
strCtid varchar(100); --アクセスログのキー。アクセルログにキーがなかったため、ctidを無理やり利用
strUserid varchar(1); --ユーザーID
strConversion varchar(1); --コンバージョン判定(1=コンバージョン)
intCount int := 1; --レコードカウント用
strOldConversionKey varchar(2); --旧コンバージョン判定
strNewConversionKey varchar(2); --新コンバージョン判定
strOldUserKey varchar(1); --旧ユーザー
strNewUserKey varchar(1); --新ユーザー
intGroupid int := 1; --グループID採番用。初期は1
--カーソル
curAccess cursor for
select ctid
,userid
,conversion
from accesslog
order by userid
,date;
begin
open curAccess;
loop
--各変数に値をセット
fetch curAccess into strCtid,strUserid,strConversion;
exit when not found;
--1レコード目の処理
if intCount = 1 then --A
intCount := intCount + 1;
strOldUserKey := strUserid;
strNewUserKey := strUserid;
strOldConversionKey := strConversion;
strNewConversionKey := strConversion;
--2レコード目以降の処理
else --A
intCount := intCount + 1;
--参照しているレコードのユーザーIDとコンバージョン判定を「新」キーの変数にセット
strNewUserKey := strUserid;
strNewConversionKey := strConversion;
--参照しているレコードのユーザーIDと直前のレコードのユーザーIDを比べる
if strOldUserKey = strNewUserKey then --B
--参照しているレコードのコンバージョン判定と直前のレコードのコンバージョン判定を比べる
if strOldConversionKey = strNewConversionKey then --C
--参照しているレコードのコンバージョン判定と直前のレコードのコンバージョン判定が同じ場合はグループIDを改番させない
else --C
--コンバージョン判定が続くケースがあるため、コンバージョン判定=1かどうかチェック
if strNewConversionKey = '1' then --D
--コンバージョン判定が1の場合はグループIDを改番させない
else --D
--グループIDを改番
intGroupid := intGroupid + 1;
end if; --D
end if; --C
--参照しているレコードのユーザーIDと直前のレコードのユーザーIDが違う場合はグループIDを改番させる
else --B
--グループIDを改番
intGroupid := intGroupid + 1;
end if; --B
end if; --A
--グループIDを更新する
update accesslog set groupid = cast(intGroupid as varchar(100)) where cast(ctid as varchar(100)) = strCtid;
--次レコードのキーと比べる用のキーになる変数を準備
strOldConversionKey := strConversion;
strOldUserKey := strUserid;
end loop;
--カーソルを閉じる
close curAccess;
end
$$
language plpgsql;
実行
実行はpsqlでcallでできます
call placcesslog();
結果
select ctid
,userid
,date as accessdate
,sessionid
,conversion
,groupid --ここがPL/pgSQLで値をセットした項目
,row_number() over (partition by groupid order by date desc) as accessno --グループIDごとに採番
from accesslog
order by userid
,date
オレンジのレコードがコンバージョン発生したレコードで
そこを起点にグループIDごとに降順で番号を採番している
以上、ただのメモ記事でした