sql - 为最长的用户连胜获得适当的计数

Question

我很难获得最长用户连续使用次数的正确计数。 Streaks 是每个用户连续签到的天数。

如有任何帮助，我们将不胜感激。这是我的脚本和示例数据的 fiddle :http://sqlfiddle.com/#!17/d2825/1/0

check_ins 表:

user_id  goal_id   check_in_date
------------------------------------------      
| colt | 40365fa0 | 2019-01-07 15:35:53
| colt | d31efe70 | 2019-01-11 15:35:52
| berry| be2fcd50 | 2019-01-12 15:35:51
| colt | e754d050 | 2019-01-13 15:17:16
| colt | 9c87a7f0 | 2019-01-14 15:35:54
| colt | ucgtdes0 | 2019-01-15 12:30:59

PostgreSQL 脚本:

    WITH dates(DATE) AS
      (SELECT DISTINCT Cast(check_in_date AS DATE),
                       user_id
       FROM check_ins),
         GROUPS AS
      (SELECT Row_number() OVER (
                                ORDER BY DATE) AS rn, DATE - (Row_number() OVER (ORDER BY DATE) * interval '1' DAY) AS grp, DATE, user_id
       FROM dates)
    SELECT Count(*) AS streak,
           user_id
    FROM GROUPS
    GROUP BY grp,
             user_id
    ORDER BY 1 DESC; 

这是我运行上面的代码时得到的结果:

 streak user_id
 --------------
 4      colt
 1      colt
 1      berry

它应该是什么。我还想只为每个用户获得最长的连续记录。

 streak user_id
 --------------
 3      colt
 1      berry

Answer 1

在 Postgres 中，你可以这样写:

select distinct on (user_id) user_id, count(distinct check_in_date::date) as num_days
from (select ci.*,
             dense_rank() over (partition by user_id order by check_in_date::date) as seq
      from check_ins ci
     ) ci
group by user_id, check_in_date::date - seq * interval '1 day'
order by user_id, num_days desc;

Here是一个数据库<> fiddle 。

这遵循与您的方法类似的逻辑，但您的查询似乎比必要的更复杂。这确实使用了 Postgres distinct on 功能，这可以很方便地避免额外的子查询。