我有以下PostgreSQL表:
create table companies (
id bigserial primary key,
created_at timestamp(6),
name varchar(200),
status varchar(200)
);
我想把结果按月份划分，并通过参数传递月份. 如何使用SQL查询得到以下结果:
| created_at | Active | Blocked |
|---|---|---|
| 2023-8-07 18:06:56.000000 | 54 | 32 |
| 2023-5-07 18:06:56.000000 | 51 | 13 |
| 2023-12-07 18:06:56.000000 | 35 | 35 |
您可以将date_trunc()与aggregate filter子句组合使用:demo
select date_trunc('month',created_at-'6d 18:06:56'::interval)
+'6d 18:06:56'::interval as created_at,
count(*)filter(where status='Active') as "Active",
count(*)filter(where status='Blocked') as "Blocked"
from companies
group by 1 order by 1;
它减go 自定义月份开始，将结果与月份开始对齐，恢复起始点.如果created_at处于或晚于该自定义起始点，则它将停留在其月份.否则，它将被分配给前一个.
基于此:
| id | created_at | name | status |
|---|---|---|---|
| 1 | 2023-10-07 18:06:55.9 | Right before custom October start | Active |
| 2 | 2023-10-07 18:06:56.01 | Right after custom October start | Blocked |
| 3 | 2022-11-03 17:53:38.894278 | a5b4172 | Blocked |
| 4 | 2022-10-03 14:52:14.827078 | 2bfc05d9-25f4- | Active |
它返回以下内容:
| created_at | Active | Blocked |
|---|---|---|
| 2022-09-07 18:06:56 | 1 | 0 |
| 2022-10-07 18:06:56 | 0 | 1 |
| 2023-09-07 18:06:56 | 1 | 0 |
| 2023-10-07 18:06:56 | 0 | 1 |