我有一個包含以下欄位的表：
tickets: id, createddate, resolutiondate
一組樣本資料具有：
jira=# select * from tickets;
id | createddate | resolutiondate
--------- ------------- ----------------
ticket1 | 2020-09-21 | 2020-10-01
ticket2 | 2020-09-22 | 2020-09-23
ticket3 | 2020-10-01 |
ticket4 | 2020-10-01 | 2020-10-04
ticket5 | 2020-10-01 |
ticket6 | 2020-10-01 | 2020-10-07
(6 rows)
jira=#
我想創建一個報告的查詢：
Week: Issues Created: Issues Resolved
我可以做兩個單獨的查詢：
# select date_trunc('week', createddate) week, count(id) created
from tickets
group by week
order by week desc
;
week | created
------------------------ ---------
2020-09-28 00:00:00 00 | 4
2020-09-21 00:00:00 00 | 2
(2 rows)
# select date_trunc('week', resolutiondate) week, count(id) resolved
from tickets
where resolutiondate is not NULL
group by week
order by week desc
;
week | resolved
------------------------ ----------
2020-10-05 00:00:00 00 | 1
2020-09-28 00:00:00 00 | 2
2020-09-21 00:00:00 00 | 1
(3 rows)
但是 - 我無法弄清楚如何（使用連接、聯合、子查詢，...？）將這些查詢組合成具有適當聚合的組合查詢。
我正在做的是 Postgres - 任何指標將不勝感激。
uj5u.com熱心網友回復：
在聚合值之前執行聯合可能在這里作業，例如
select week,
count(id_created) as created,
count(id_resolved) as resolved
from (
select date_trunc('week', resolutiondate) week, NULL as id_created, id as id_resolved from tickets UNION ALL
select date_trunc('week', createddate) week, id as id_created, NULL as id_resolved from tickets
) t
group by week
order by week desc
讓我知道這是否適合您。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/321608.html
標籤：sql PostgreSQL 加入 通过...分组 聚合函数