Redshift SQL中按时间区间分组统计网站访问时段的技术问题

看起来你已经找对了核心方向——用PostgreSQL的类型转换提取时间部分来做区间统计，之前的字符串拆分截取方法确实不够严谨，而且可读性差，难怪会被指出问题。我来帮你把正确的实现理清楚：

核心思路：利用::time类型转换直接操作时间字段 #

如果你的timestamp字段是原生timestamp类型（不是字符串），直接用字段名::time就能提取时间部分，完全可以作用在列上，之前你觉得无法作用于列名可能是语法小失误？直接用你期望的BETWEEN逻辑就能实现精准的区间统计：

SELECT
  -- 统计00:00:00 - 04:59:59的访问量
  SUM(CASE WHEN timestamp::time BETWEEN '00:00:00'::time AND '04:59:59'::time THEN 1 ELSE 0 END) AS time_00_04,
  -- 05:00:00 - 07:59:59
  SUM(CASE WHEN timestamp::time BETWEEN '05:00:00'::time AND '07:59:59'::time THEN 1 ELSE 0 END) AS time_05_07,
  -- 08:00:00 - 12:59:59
  SUM(CASE WHEN timestamp::time BETWEEN '08:00:00'::time AND '12:59:59'::time THEN 1 ELSE 0 END) AS time_08_12,
  -- 13:00:00 - 17:59:59
  SUM(CASE WHEN timestamp::time BETWEEN '13:00:00'::time AND '17:59:59'::time THEN 1 ELSE 0 END) AS time_13_17,
  -- 18:00:00 - 22:59:59
  SUM(CASE WHEN timestamp::time BETWEEN '18:00:00'::time AND '22:59:59'::time THEN 1 ELSE 0 END) AS time_18_22,
  -- 23:00:00 - 23:59:59
  SUM(CASE WHEN timestamp::time BETWEEN '23:00:00'::time AND '23:59:59'::time THEN 1 ELSE 0 END) AS time_23_23
FROM your_table_name; -- 替换成你的表名

如果字段是字符串类型怎么办？ #

如果你的timestamp字段实际是文本类型（比如存的是'2018-05-25 12:00:00'这样的字符串），只需要先把它转成timestamp类型再提取时间：

SELECT
  SUM(CASE WHEN CAST(timestamp AS TIMESTAMP)::time BETWEEN '08:00:00'::time AND '12:59:59'::time THEN 1 ELSE 0 END) AS time_08_12
  -- 其他区间按照上面的逻辑复制即可
FROM your_table_name;

为什么这个方法比你的初始方案好？ #

• 类型安全：直接对时间类型操作，不会因为字符串格式异常（比如小时位不是两位、字段有脏数据）导致统计错误
• 精度更高：可以精确到秒，比如04:59:59会被正确归到00-04区间，而你的初始方案只取小时位，会漏掉这最后一秒的情况
• 可读性更强：逻辑一目了然，后续维护的人能快速看懂统计规则

额外小技巧：用DATE_PART简化小时区间统计 #

如果你的统计只需要精确到小时，不需要分秒，也可以用DATE_PART提取小时数来简化代码：

SELECT
  SUM(CASE WHEN DATE_PART('hour', timestamp) IN (0,1,2,3,4) THEN 1 ELSE 0 END) AS time_00_04,
  SUM(CASE WHEN DATE_PART('hour', timestamp) BETWEEN 5 AND 7 THEN 1 ELSE 0 END) AS time_05_07,
  SUM(CASE WHEN DATE_PART('hour', timestamp) BETWEEN 8 AND 12 THEN 1 ELSE 0 END) AS time_08_12
  -- 其他区间同理
FROM your_table_name;

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