SQL查询根据移动日期窗口有条件地求和

发布时间：2020-12-12 16:11:40 所属栏目：MsSql教程来源：网络整理

导读：我试图找出我的用户的滑动窗口统计信息.我有一个用户的表和列,如created_at和verified_at.每个月,我想知道有多少用户注册(一个简单的group by date_trunc的created_at),然后是那些人,在我的滑动窗口内验证了多少(称为60天). 我想做一个SQL查询,给我一些像： M

我试图找出我的用户的滑动窗口统计信息.我有一个用户的表和列,如created_at和verified_at.每个月,我想知道有多少用户注册(一个简单的group by date_trunc的created_at),然后是那些人,在我的滑动窗口内验证了多少(称为60天).

我想做一个SQL查询,给我一些像：

Month    | Registered | Verified in 60 days
Jan 2009 | 1543       | 107
Feb 2009 | 2000       | 250

我正在使用postgresql.我开始查看sum(case …),但是我不知道我是否可以让我的case被依赖于date_trunc.

这当然不行,但这里的想法是：

SELECT DATE_TRUNC('month',created_at) as month,COUNT(*) as registered,SUM(CASE WHEN verified_at < month+60 THEN 1 ELSE 0 END) as verified
FROM users
GROUP BY DATE_TRUNC('month',created_at)

解决方法

SELECT  COUNT(created_at) AS registered,SUM(CASE WHEN verified_at <= created_at + '60 day'::INTERVAL THEN 1 ELSE 0 END) AS verified
FROM    generate_series(1,20) s
LEFT JOIN
        users
ON      created_at >= '2009-01-01'::datetime + (s || ' month')::interval
        AND created_at < '2009-01-01'::datetime + (s + 1 || ' month')::interval
GROUP BY
        s

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!