SQL:如何选择员工的时间范围并按时间间隔30分钟分组

Question

我有三列，time-in(timestamp)、time-out(timestamp) 和 employee。我需要获取在特定时间范围内(30 分钟间隔)工作的员工数量。例如:

    employee_id              timein              timeout
    101                      10:10               12:59
    102                       9:07               12:16
    103                      11:16               12:08

我需要一个能给我这个结果的查询

    timeframe         count(employee_id)
    09:00                    1
    09:30                    1
    10:00                    2
    10:30                    2
    11:00                    3
    11:30                    3
    12:00                    3
    12:30                    1

我真的希望我说清楚了。谢谢

Answer 1

查看此演示:http://sqlfiddle.com/#!17/2477f/1

SELECT x.timeframe, count(employee_id)
FROM (
   select time '8:00' + x * interval '30 minute' as timeframe,
          time '8:00' + (x+1) * interval '30 minute' as timeframe_end
   from generate_series(0,10) x
) x
LEFT JOIN employee t
/* (StartA <= EndB) and (EndA >= StartB) */
ON x.timeframe <= t.timeout
AND x.timeframe_end >= t.timein
GROUP BY x.timeframe
ORDER BY 1

SELECT x.timeframe, count(employee_id)
FROM (
   select time '8:00' + x * interval '30 minute' as timeframe,
          time '8:00' + (x+1) * interval '30 minute' as timeframe_end
   from generate_series(0,12) x
) x
LEFT JOIN employee t
/* (StartA < EndB) and (EndA > StartB) */
ON x.timeframe < t.timeout
AND x.timeframe_end > t.timein
GROUP BY x.timeframe
ORDER BY 1
| timeframe | count |
|-----------|-------|
|  08:00:00 |     0 |
|  08:30:00 |     0 |
|  09:00:00 |     1 |
|  09:30:00 |     1 |
|  10:00:00 |     2 |
|  10:30:00 |     2 |
|  11:00:00 |     3 |
|  11:30:00 |     3 |
|  12:00:00 |     3 |
|  12:30:00 |     1 |
|  13:00:00 |     1 |
|  13:30:00 |     1 |
|  14:00:00 |     0 |

---

连接条件使用来自 this answer 的公式用于检查两个范围是否重叠:

(StartA < EndB) and (EndA > StartB)

该演示还展示了查询在边缘情况下的行为方式:

(113, '13:00', '13:01'),
(115, '13:30', '14:00')

后一个员工从 13:30 开始，到 14:00 结束，所以它包含在 13:30 时间范围内，但不包含在 14:00 时间范围。

|  13:00:00 |     1 |
|  13:30:00 |     1 |
|  14:00:00 |     0 |

---

问题可能出在同一时间段内多次开始和完成一项工作的员工(经常喝咖啡休息的员工)，例如:

(113, '13:00', '13:01'),
(113, '13:12', '13:15'),
(113, '13:22', '13:26')

对于这种情况，您需要计算不同的员工，使用:count(DISTINCT employee_id)