PL/pgSQLを何となく動かしてみる

--テーブル作成
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');

create or replace procedure placcesslog()
as $$

--宣言
declare
 --変数
 strCtid varchar(100); --アクセスログのキー。アクセルログにキーがなかったため、ctidを無理やり利用
 strUserid varchar(1); --ユーザーID
 strConversion varchar(1); --コンバージョン判定（１＝コンバージョン）

 intCount int := 1; --レコードカウント用

 strOldConversionKey varchar(2); --旧コンバージョン判定
 strNewConversionKey varchar(2); --新コンバージョン判定
 strOldUserKey varchar(1); --旧ユーザー
 strNewUserKey varchar(1); --新ユーザー

 intGroupid int := 1; --グループID採番用。初期は１
 
 --カーソル
 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;
   
   --１レコード目の処理
   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
      --コンバージョン判定が続くケースがあるため、コンバージョン判定＝１かどうかチェック
      if strNewConversionKey = '1' then --D
       --コンバージョン判定が１の場合はグループ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;

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