sql - Postgres-快速汇总月份中最后一天的行

我想查询一个表，并为一个列求一个月最后一天的所有行的和。
以下表为例：

CREATE TABLE example(dt date, value int)

（真正的表有更多的列，并且比较大，真正的查询更复杂）
我有以下疑问：

SELECT dt, SUM(value)
FROM example
WHERE dt IN  (SELECT DISTINCT
     date_trunc('MONTH', generate_series('2012-01-01'::date,
                                         '2016-12-01'::date,
                                         interval '1 day') + INTERVAL '1 MONTH - 1 day')::date)
GROUP BY dt

它在我真正的桌子上运行大约2秒。
但是，如果我在我的范围内生成月底天数的完整列表并对查询进行参数化，如下所示：

SELECT dt, SUM(value)
FROM example
WHERE dt IN ('2012-01-31', ...)
GROUP BY dt

更快，~750ms。
我不希望生成日期并将其传递给这样的查询，是否有一种方法可以完全在SQL中完成此操作并使其与后一个版本一样快？

最佳答案

子选择是不必要的复杂。它可以简化为：

SELECT dt, SUM(value)
FROM example
WHERE dt IN (SELECT d::date
             from generate_series('2012-01-01'::date, '2016-12-01'::date, interval '1 month') dates (d)
GROUP BY dt;  --<< the group by is necessary

也许这会加快查询速度。
您还可以尝试将日期生成放入CTE中：

with dates (d) as (
  SELECT t::date
  from generate_series('2012-01-01'::date, '2016-12-01'::date, interval '1 month') t
)
SELECT dt, SUM(value)
FROM example
WHERE dt IN ( select d from dates)
GROUP BY dt;

有时加入也更有效：

with dates (d) as (
  SELECT t::date
  from generate_series('2012-01-01'::date, '2016-12-01'::date, interval '1 month') t
)
SELECT dt, SUM(value)
FROM example
  JOIN dates on example.dt = dates.d
GROUP BY dt;

关于sql - Postgres-快速汇总月份中最后一天的行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40513791/