sql - 对列值的更改 date_trunc 进行不同搜索

Question

简短而简单，我想获得一个唯一时间列表及其计数代表。

SELECT DISTINCT(date_trunc('hour', occurred)) as time, COUNT(*)
  FROM log
  WHERE occurred BETWEEN '2014-01-01 00:00' AND '2014-01-01 23:59'
GROUP BY occurred;

这行不通，因为我得到的结果比 24 多得多。
所以我继续尝试:

SELECT DISTINCT(occurred), COUNT(*)
    FROM log
    WHERE occurred BETWEEN .. AND ..
GROUP BY date_trunc('hour', occurred);

这是一个无效的语法，显然我只是即兴发挥，因此我需要帮助。如何解决这个问题？

Answer 1

这里根本不需要DISTINCT。

如果您只对发生任何事情的“时间”列表感兴趣(适用于任何时间范围):

SELECT date_trunc('hour', occurred) AS hour, COUNT(*) AS ct
FROM   log
WHERE  occurred >= '2014-01-01 0:0'
AND    occurred <  '2014-01-02 0:0'  -- do not use between for this
GROUP  BY 1
ORDER  BY 1;

BETWEEN 包括上限和下限，这通常不适用于时间戳范围:

• Optimize BETWEEN date statement

如果超过一个小时没有任何反应怎么办？如果您仍然希望结果中有几小时没有事件的行(忽略此变体中的日期!):

SELECT hour, COUNT(l.occurred) AS ct
FROM   generate_series (0,23) h(hour)
LEFT   JOIN log l ON occurred >= '2014-01-01 0:0'
                 AND occurred <  '2014-01-02 0:0'  -- do not use between for this
                 AND EXTRACT(hour FROM occurred)::int = h.hour
GROUP  BY hour
ORDER  BY hour;

generate_series() 提供每天的完整小时数。
LEFT JOIN 保留所有这些。
只计算实际的日志条目。