时间戳

首页 > 解决方案 > 针对一年中的季度或周运行查询

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

问题描述

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

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

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

标签： sqlpostgresql

解决方案

这个怎么样？

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')

推荐阅读