sql - 针对一年中的季度或周运行查询

Question

我有一个适用于 1 个季度的查询。但是，如果我想在日历年的第二、第三和第四季度甚至每周执行相同的查询怎么办？

如何在不手动更改定义一年中的季度或周的值的情况下执行相同的查询？

SELECT count(1), AVG(resolved_at::TIMESTAMP - created_at::TIMESTAMP) 
FROM supp_cases 
WHERE created_at::TIMESTAMP >= '2017-01-01 00:00:00'::TIMESTAMP 
AND resolved_at::TIMESTAMP <= '2017-03-31 23:59:59'::TIMESTAMP;

Q1 = 2017-01-01 00:00:00 TO 2017-03-31 23:59:59
Q2 = 2017-04-01 00:00:00 TO 2017-06-30 23:59:59
Q3 = 2017-07-01 00:00:00 TO 2017-09-30 23:59:59
Q4 = 2017-10-01 00:00:00 TO 2017-12-31 23:59:59

Answer 1

这个怎么样？

SELECT TO_CHAR(created_at, 'YYYY-Q') as created_at_yyyyq,
       TO_CHAR(resolved_at, 'YYYY-Q') as resolved_at_yyyyq,
       count(*)
FROM supp_cases 
GROUP BY created_at_yyyyq, resolved_at_yyyyq
ORDER BY created_at_yyyyq, resolved_at_yyyyq;

如果您确实希望在同一季度内创建和解决行，您可以添加:

WHERE TO_CHAR(created_at, 'YYYY-Q') = TO_CHAR(resolved_at, 'YYYY-Q')