sql - 独特的季度记录

Question

我有一张这样的 table -

patient_id   date            org
I5434        2020-06-05      Methodist Hospital
ertw4        2020-08-03      New York Hospital
ouiku35      2020-08-30      Tristar 
ouiku35      2020-01-05      Tristar
ouiku35      2020-04-22      Tristar 

我需要的输出是

quarter   count
2020Q1    1
2020Q2    1 
2020Q3    1 

上表中的第3条记录未被计算在内，因为它是同一医院前几个季度的同一患者

Answer 1

我们可以在这里尝试使用日历表方法:

WITH quarters AS (
    SELECT 2020 AS year, 1 AS quarter, '2020Q1' AS label UNION ALL
    SELECT 2020, 2, '2020Q2' UNION ALL
    SELECT 2020, 3, '2020Q3' UNION ALL
    SELECT 2020, 4, '2020Q4'
)

SELECT q.label, COUNT(t.date) AS count
FROM quarters q
LEFT JOIN yourTable t
    ON EXTRACT(year FROM t.date) = q.year AND
       EXTRACT(quarter FROM t.date) = q.quarter
GROUP BY q.label
ORDER BY q.label;

Demo