我有一些信息，如下表所示。
login_date | userid
-------------------------
2020-12-01 | 123
2020-12-01 | 456
2020-12-02 | 123
2020-12-02 | 456
2020-12-02 | 789
2020-12-03 | 123
2020-12-03 | 789
在login_date中发现的日期范围从2020-12-01到2020-12-12，并且每天的userid是唯一的。
我想要获得的东西有两个折叠：
在某个日期首次登录的用户数排除前一天登录的用户
对于在某个日期(例如2020-12-01(首次登录的用户，他们中有多少人在随后的几天也登录了？(即，在2020-12-01首次登录的批次，发现有多少人在2020-12:02、2020-12-03登录，依此类推(
对于上表，所需结果的示例如下：
| 2020-12-01 | 2020-12-02 | 2020-12-03 | ... (users' first login date)
----------------------------------------------------------------------------------------
| 2020-12-01 | 2 x x
users who continued | 2020-12-02 | 2 1 x
to log in on these | 2020-12-03 | 1 1 0
dates | ... |
推理：
- 第一天，有两个新用户登录，
123和456 - 第二天，同样的老用户
123和456也登录了。此外，还添加了一个新用户(首次登录(789 - 第三天，只有一个原始旧用户
123登录。(计数1(。新用户(从第二天起(789也已登录。(共1个(
我的尝试
实际上，我设法得到了一个分为两部分的(粗略(解决方案。在2012-12-01的第一天，我简单地过滤了第一天登录并在所有剩余日期执行左加入的用户：
select count(d1.userid) as d1_users, count(d2.userid) as d2_users, ... (repeated for all joined tables)
from table1 d1
left join (
select userid
from table1
where login_date = date('2020-12-02')
) d2
on d1.userid = d2.userid
... -- (10 more left joins, with each filtering by an incremented date value)
where d1.login_date = date('2020-12-01')
对于第二天之后的日期，我做了一些预处理，以排除在前一天登录的用户：
with d2_users as (
select userid
from table1 a
left join (
select userid
from table1
where login_date = date('2020-12-01')
) b
on a.userid = b.userid
where b.userid is null -- filtering out users who logged in on preceding day(s)
and a.login_date = date('2020-12-02')
)
select count(d2.userid) as d2_users, ... -- (repeated for all joined tables)
from d2_users d2
left join (
select userid
from table1
where login_date = date('2020-12-03')
) d3
on d2.userid = d3.userid
... -- (similar to the query for the 2020-12-01)
在编写和执行此查询的过程中，需要大量的手动编辑(删除不必要的左联接以备日后日期和计数(，最终仅两天的整个查询就占用了300多行SQL代码。我不确定是否有更有效的程序。
如有任何建议，我们将不胜感激！如果需要的话，我很乐意提供进一步的澄清，因为这个问题的解决方案的优化已经困扰了我一段时间。
我为所需结果的格式不好表示歉意，因为我目前只在电子表格中对其进行了表示，而不知道它作为SQL输出的样子。
编辑：
我意识到我可能没有正确地传达理想的结果。对于确定的每个min_login_date，我希望获得的是从前一日期开始继续登录的用户数量。例如：
- 10名用户于2020-12-01登录。因此，2020-12-01的计数=10
- 在之前的10个用户中，有8个用户在2020-12-02登录。因此，2020-12-02的计数=8
- 在8个用户(前一天(中，有6个用户在2020-12-03登录。因此，2020-12-03的计数=6
因此，对于每个min_login_date，后续日期的用户计数应为<=以前日期的用户计数。希望这能有所帮助！对于任何沟通错误，我深表歉意。
您可以使用窗口函数来获取最早的日期。然后聚合：
select min_login_date, count(*) as num_on_day,
sum(case when login_date = '2020-12-01' then 1 else 0 end) as login_20201201,
sum(case when login_date = '2020-12-02' then 1 else 0 end) as login_20201203,
. . .
from (select t.*,
min(login_date) over (partition by user_id) as min_login_date
from t
) t
group by min_login_date
我认为您需要使用分析函数和聚合函数进行一些调整，如下所示：
select login_date,
Count(case when min_login_date = '2020-12-01' then 1 end) as login_20201201,
Count(case when min_login_date = '2020-12-02' then 1 end) as login_20201202,
......
from (select t.*,
min(login_date) over (partition by user_id) as min_login_date,
Lag(login_date) over (partition by user_id) as lag_login_date,
from your_taeble t
Where t.login_date between '2020-12-01' and '2020-12-12'
) t
where (lag_login_date = login_date - interval '1 day' or lag_login_date is null)
group by login_date