提问人：Simonas Petkevičius 提问时间：7/11/2022 最后编辑：Simonas Petkevičius 更新时间：7/12/2022 访问量：87
如何按月聚合数据 重叠的 PostgreSQL
how to aggregate data by month overlapping postgresql
问：
我有 SCD 表类型 2，我将其与其他各种表合并，并且我希望汇总单个月份内处于活动状态的任何实体（我所说的活动是指尚未end_date的实体）的总和。
目前，我有一个类似于这样的查询（假设汇总 2022 年 5 月和 2022 年 4 月的数据）：
select
count(1) as enitities_agg,
DATE_TRUNC('Month', h.start) as date,
sum(h.price) filter (where c.name='HIGH') as sum_total,
----....----
from
project as p
join class as c on p.class_id = c.id
join stage as s on s.project_id = p.id
join stage_info as si on si.stage_id = s.id
join history as h on h.stage_info_id = si.id
where
h.start <= '2022-06-01' and
h.end_date >= '2022-04-01' and
COALESCE(p.end_date, '2099-01-01') >= '2022-04-01' and
COALESCE(p.start_date, '2099-01-01') <= '2022-06-01' and
COALESCE(stage.end, '2099-01-01') >= '2022-04-01' and
h.price is not null and
h.price != 0
group by DATE_TRUNC('Month', h.start)
它只汇总那些历史始于 5 月或 4 月的那些，而不是那些与这些月份重叠并且仍然活跃的那些。
我遇到的问题是，一些历史实体从 4 月、3 月等开始，到 5 月仍然没有结束。因为我按历史开始日期分组，所以我没有得到早于 4 月或 5 月开始并在 5 月之后继续活跃的实体，我只得到它们开始的那些月份的聚合。group by DATE_TRUNC('Month', h.start)
我试图通过按生成的月份生成系列和分组来做到这一点，但是，我没有找到一种可以正确分组它们的方法。例如，我尝试过的一个实验。
from
generate_series('2022-03-01', '2022-07-01', INTERVAL '1 month') as mt
join project as p on COALESCE(p.end_date, '2099-01-01') >= mt and
COALESCE(p.start_date, '2099-01-01') <= mt + INTERVAL '1 month'
join class as c on p.class_id = c.id
join stage as stage on stage.project_id = p.id and
COALESCE(stage.end, '2099-01-01') >= mt
join stage_info as si on si.stage_id = stage.id
join history as h on h.stage_info_id = si.id
where
h.start <= mt and
h.end_date >= mt + INTERVAL '1 month' and
h.price is not null and
h.price != 0
group by mt
如何遍历聚合一个月内任何活动实体的历史记录表，并按同一月份对它们进行分组并得到类似的东西？
"enitities_agg" | "date" | "sum_total"
832 | "2022-04-01 00:00:00" | 15432234
1020 | "2022-05-01 00:00:00" | 19979458
答：
1赞
jian
7/11/2022
#1
您希望所有历史实体都发生在 2022 年 5 月期间吗？如果是这样，以下内容可能会有所帮助。
daterange(h.start,
h.end_date,
'[]') && daterange('2022-05-01', '2022-06-01', '[]');
演示：
CREATE temp TABLE test (
begin_ date,
_end date
);
INSERT INTO test
VALUES ('2022-01-01', '2022-05-01');
INSERT INTO test
VALUES ('2022-01-01', '2022-05-11');
INSERT INTO test
VALUES ('2022-05-01', '2022-07-11');
INSERT INTO test
VALUES ('2022-06-11', '2022-07-11');
SELECT
*,
daterange(begin_, _end, '[]')
FROM
test t
WHERE
daterange(t.begin_, t._end, '[]') && daterange('2022-05-01', '2022-05-31', '[]');
&&范围运算符参考：https://www.postgresql.org/docs/current/functions-range.html#RANGE-OPERATORS-TABLE
评论
0赞
Simonas Petkevičius
7/11/2022
'[]' 部分作为 daterange（） 函数中的第三个参数代表什么？它是否和数学一样，[]是包容性的，（）是排他性的？
0赞
Simonas Petkevičius
7/11/2022
感谢您的回答，daterange 函数真的很有帮助，因为我以前从未遇到过它，我现在需要对日期范围进行大量处理。但是，我一开始可能会非常误导地格式化这个问题。这个答案非常适合获取一个月的聚合，但是，我正在努力扩展它以一次获得多个月的正确聚合值。请参阅更新的问题。
1赞
jian
7/11/2022
@SimonasPetkevičius你是对的。[ 是非独占的，（ 是独占的。
0赞
jian
7/11/2022
@SimonasPetkevičius我有点迷茫。您能否在 dbfiddle.uk/?rdbms=postgres_14 中共享示例数据并共享预期结果。
1赞
jian
7/11/2022
dbfiddle.uk/......我来了最好的。自 7 月 8 月以来，两者都为空。我不知道该如何处理...... @SimonasPetkevičius
1赞
vasja
7/12/2022
#2
似乎你的逻辑是：如果 begin_ - _end 间隔的任何一天落入月，请将其计算在内。这是从预期结果中猜测的最难的部分。
所以我想你需要这个：
with dim as (
select
m::date as month_start
,(date_trunc('month', m) + interval '1 month - 1 day')::date as month_end
,to_char(date_trunc('month', m), 'Mon') as month
from generate_series('2022-01-01', '2022-08-01', INTERVAL '1 month') as m
)
SELECT
dim.month
, sum(coalesce(t.price, 0)) as sum_price
FROM dim
left join test as t
on t.begin_ <= dim.month_end
and t._end >= dim.month_start
group by dim.month_start, dim.month
order by dim.month_start, dim.month
;
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=614030d4db5e03876f693a9a8a5ff122
评论
sum(entity.price)