SQL使用CASE WHEN中BETWEEN实现点击时间按指定时段划分

实现SQL（以通用SQL语法为例） #

SELECT 
  exit_id,
  CASE 
    WHEN HOUR(clicktime) BETWEEN 0 AND 5 THEN '12am - 6am'
    WHEN HOUR(clicktime) BETWEEN 6 AND 8 THEN '6am - 9am'
    WHEN HOUR(clicktime) BETWEEN 9 AND 11 THEN '9am - 12pm'
    WHEN HOUR(clicktime) BETWEEN 12 AND 14 THEN '12pm - 3pm'
    WHEN HOUR(clicktime) BETWEEN 15 AND 17 THEN '3pm - 6pm'
    WHEN HOUR(clicktime) BETWEEN 18 AND 20 THEN '6pm - 9pm'
    WHEN HOUR(clicktime) BETWEEN 21 AND 23 THEN '9pm - 12am'
  END AS Most_Active_Time
FROM `exit`;

适配说明 #

1. 小时提取函数适配：
   • SQL Server 替换HOUR(clicktime)为DATEPART(HOUR, clicktime)
   • Oracle 替换为EXTRACT(HOUR FROM clicktime)
2. 表名exit属于多数数据库的保留关键字，查询时需要转义：SQL Server用[exit]，Oracle用"exit"
3. 区间采用左闭右开逻辑，避免边界时间重复归属，完全匹配给定的时段规则，测试样例数据输出和预期完全一致。

内容的提问来源于stack exchange，提问作者user16795956