基于移动日期窗口有条件地求和的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2045463/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query to conditionally sum based on moving date window
提问by teich
I'm trying to figure out some sliding window stats on my users. I have a table with a user, and columns such as created_at and verified_at. For each month, I'd like to find out how many users registered (a simple group by date_trunc of the created_at), and then of those people, how many verified within my sliding window (call it 60 days).
我试图找出我的用户的一些滑动窗口统计信息。我有一个包含用户的表，以及诸如 created_at 和 verify_at 之类的列。对于每个月，我想知道有多少用户注册（一个简单的组，由 created_at 的 date_trunc 组成），然后在这些人中，有多少在我的滑动窗口（称之为 60 天）内进行了验证。
I'd like to do a SQL query that gives me something like:
我想做一个 SQL 查询，它给了我类似的东西：
Month | Registered | Verified in 60 days
Jan 2009 | 1543 | 107
Feb 2009 | 2000 | 250
I'm using postgresql. I starting looking at sum(case...), but I don't know if I can get my case to be dependent on the date_trunc somehow.
我正在使用 postgresql。我开始查看 sum(case...)，但我不知道是否可以让我的案例以某种方式依赖于 date_trunc。
This doesn't work, of course, but here's the idea:
这当然行不通，但这里的想法是：
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)
回答by Quassnoi
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
回答by SquidScareMe
perhaps you could union together the different months.
也许你可以将不同的月份结合在一起。
select sum(whatever), 'january' from user where month = 'january'
union all
select sum(whatever), 'february' from user where month = 'february'
...
回答by Paul Creasey
SELECT
MONTH,
COUNT(*) AS Registered,
SUM (CASE WHEN datediff(day,reg_date,ver_date) < 60 THEN 1 ELSE 0) as 'Verified in 60 //days datediff is an MSSQL function amend for postgresql'
FROM
TABLE
GROUP BY
MONTH
