PostgreSQL：按月分组计算所有数据，包括间隔

h4cxqtbf 于 2023-06-05 发布在 PostgreSQL

关注(0)|答案(1)|浏览(317)

我有一个非常简单的查询要做-一个表有一个日期字段，我需要选择所有行的计数，按月份分组，包括间隙。
我有下面的查询，工程天，但它不工作的月份

SELECT f.date::date as period, count(e.id) as count
FROM generate_series((now() - interval '1 year')::date, now()::date, '1 month'::interval) AS f(date)
LEFT JOIN my_table AS e ON e.created_at::date = f.date::date
GROUP BY f.date
ORDER BY f.date DESC;

由于某种原因，该查询对所有行返回零。
此查询可以工作，但不能填补空白

SELECT date_trunc('month', created_at) AS txn_month, count(*) as count
FROM my_table
GROUP BY txn_month

怎样做才是正确的呢？

postgresql

来源：https://stackoverflow.com/questions/76373142/postgresql-count-all-grouped-by-month-including-gaps

1条答案

按热度按时间

ubby3x7f1#

以下查询返回my_table中每个期间的行数，包括my_table中没有相应数据的期间。

WITH agg_periods AS (
  SELECT s.period_start, s.period_start + p.agg_interval AS period_end
    FROM (SELECT 'P1M'::interval AS agg_interval) p
   CROSS JOIN generate_series(date_trunc('month', now()) - 'P1Y'::interval, now(), p.agg_interval) s(period_start)
)
SELECT f.period_start AS period, count(e.id) AS COUNT
  FROM agg_periods f
  LEFT JOIN my_table e
    ON f.period_start <= e.created_at
       AND e.created_at < f.period_end
 GROUP BY f.period_start
 ORDER BY f.period_start;

该查询的结构是这样的，通过更改子查询agg_periods中的期间生成参数，可以适应不同的期间。因为created_at在比较中没有使用类型转换，所以PostgreSQL不会被阻止在该列上使用索引。

赞(0）回复(0）举报 2023-06-05

我来回答

PostgreSQL：按月分组计算所有数据，包括间隔

1条答案

相关问题

热门标签

最新问答