Postgres 用户连续登录收益计算

• 遇到一个有意思的问题（原始题目来自newcoder sql167，此处有改动）

• 已知一个用户登录表（已简化）

• DROP TABLE IF EXISTS user_login_log;
  CREATE TABLE user_login_log (
      id serial PRIMARY KEY,
      uid INT NOT NULL,
      login_time timestamp
  );
  
  -- 插入适量测试数据
  insert into user_login_log(uid, login_time) select (random()*50+1000)::int, 
  date('2022-8-1') + (random()*100)::int from generate_series(1,10000);
  

• 问题描述

• 用户每天登录会收益一个金币，连续登录3天（当天会额外收益2个金币），连续登录7天（当天会额外收益6个金币）
  求用户历史以来的全部收益
  

• 我平时写PLSQL比较多，我就用匿名函数试了下思路效果

• do language plpgsql
  $$
      declare
          uid_arr int[] := (select array_agg(distinct uid order by uid asc) from user_login_log);
          tmp_uid int;
          date_arr date[];
          total int := 0;
          serial_days int := 0;
          res jsonb := '{}'::jsonb;
      begin
          foreach tmp_uid in array uid_arr
          loop
              date_arr := (select array_agg(distinct login_time::date order by login_time::date asc) from user_login_log where uid = tmp_uid);
              for i in 1..array_length(date_arr, 1)
              loop
                  if i = 1 then serial_days := serial_days +1; total := total + 1;
                  else
                      if date_arr[i] - date_arr[i-1] = 1 then serial_days := serial_days + 1 ; total := total + 1;
                          if serial_days = 3 then total := total + 2;
                          elseif serial_days = 7 then total := total + 6; serial_days := 0;
                          end if;
                      else serial_days := 0 ; total := total + 1;
                      end if;
                  end if;
              end loop;
              res := jsonb_set(res, ('{'||tmp_uid||'}')::text[], to_jsonb(total));
          end loop;
          raise notice '%', res;
      end;
      $$;
  

• 这是常规思路，遍历每个用户的每个日期来做判断，然后我对连续的判定逻辑做一下改进

• -- 用窗口函数求出每个uid每天登录的当前连续登录天数
  with t1 as (
      select distinct uid, login_time::date login_time from user_login_log
  ),
  t2 as (
  select
      uid,
      login_time::date srd,
      login_time::date - (row_number() over (partition by uid order by login_time::date))::int dst
  from t1)
  select uid, srd login_time,
  row_number() over (partition by uid, dst order by srd asc) serial_days
  from t2;
  
  -- 针对上述sql，按照连续登录的规则，在连续登录对7取模，逢3加2，逢7加6，即可以求出当天的收益值
  with t1 as (
      select distinct uid, login_time::date login_time from user_login_log
  ),
  t2 as (
  select
      uid,
      login_time::date srd,
      login_time::date - (row_number() over (partition by uid order by login_time::date))::int dst
  from t1)
  select uid, srd,
  row_number() over (partition by uid, dst order by srd asc) serial_days,
  case row_number() over (partition by uid, dst order by srd asc) % 7
      when 3 then 1+2
      when 0 then 1+6
      else 1
  end daily_coin
  from t2;
  
  -- 再提升一下数据粒度，即可求得每个用户的总收益
  with t1 as (
      select distinct uid, login_time::date login_time from user_login_log
  ),
  t2 as (
  select
      uid,
      login_time::date srd,
      login_time::date - (row_number() over (partition by uid order by login_time::date))::int dst
  from t1),
  t3 as (select uid,
  case row_number() over (partition by uid, dst order by srd asc) % 7
      when 3 then 3
      when 0 then 7
      else 1
  end daily_coin
  from t2)
  select uid, sum(daily_coin) from t3 group by uid;
  

• 对于改进后的逻辑，可以嵌入函数中可以做成求出任一时间段的收益，整体执行效率和拓展性也会提升