sql - GROUP BY 外键和日期范围

Question

我正在使用一个名为 appointments 的表，其中的列是:

scheduled_at: 日期时间doctor_id:整数描述:文字finished_at:日期时间

我想获得所有已完成的约会的计数，这些约会按 doctor_id 分组，并按一组日期间隔分组，这些日期间隔因给定的输入集而异。我得到的输入是:from 是一个日期，to 也是一个日期。

我要解决的第一个用例是从给定的 from 和 to 中按月拆分结果。因此，如果我得到 from = '2018-02-03' 和 to = '2018-10-03' 我想获得所有约会的 COUNT从 2018-02 到 2018-10，每个医生和每个月都已完成。

我知道如何按 doctor_id 进行分组，但我不知道如何按这些动态日期间隔进行分组。

这是我目前所拥有的:


选择计数(*)
从约会
WHERE finished_at 不为空
按 doctor_id 分组


我知道如果我事先有时间间隔，我可以做尽可能多的查询，所以我最终会每个月做一个查询，例如:


选择计数(*)
从约会
WHERE finished_at 不为空
AND scheduled_at BETWEEN '2018-02-01' AND '2018-03-01'
按 doctor_id 分组



选择计数(*)
从约会
WHERE finished_at 不为空
AND scheduled_at BETWEEN '2018-03-01' AND '2018-04-01'
按 doctor_id 分组


我只是想知道是否有一种方法可以在 SQL 中执行此操作，所以我只需要执行一个查询。

Answer 1

使用case 表达式进行条件聚合:

SELECT doctor_id,
       COUNT(*),
       COUNT(case when scheduled_at BETWEEN '2018-02-01' AND '2018-03-01' then 1 end),
       COUNT(case when scheduled_at BETWEEN '2018-03-01' AND '2018-04-01' then 1 end)
FROM appointments
WHERE finished_at IS NOT NULL
GROUP BY doctor_id

或者有一个group by年和月的查询:

SELECT doctor_id, year(scheduled_at), month(scheduled_at), COUNT(*)
FROM appointments
WHERE finished_at IS NOT NULL
GROUP BY doctor_id, year(scheduled_at), month(scheduled_at)

或者，也许应该使用 ANSI SQL 函数 EXTRACT:

SELECT doctor_id,
       extract(year from scheduled_at), extract(month from scheduled_at), COUNT(*)
FROM appointments
WHERE finished_at IS NOT NULL
GROUP BY doctor_id, extract(year from scheduled_at), extract(month from scheduled_at)