PostgreSQL按小时统计工作日频率

Question

我希望生成一个类似于下面这个（来自Google Analytics App for Android的截图）的报告：

我有每天发生10-15次的事件，并且我想看到每个工作日的频率按小时分组，从开始算起。

我只需要使用名为“created_at”的DateTime（时间戳）字段（是一个Rails项目）

如何在PostgreSQL中实现？

谢谢。

- zener79

---

输入数据是什么样子的？它存储在哪些表格中，结构如何？ - harmic

我只需要使用一个名为“created_at”的DateTime（时间戳）字段。 - zener79

Answer 1

已经提供的答案都是正确的，但为了丰富一下，这里介绍一种使用'tablefunc'扩展来对结果进行交叉表格化的方法，使其与您的示例完全相同。

在使用之前，您必须创建tablefunc扩展（它在postgresql的contrib包中可用）:

CREATE EXTENSION IF NOT EXISTS tablefunc;

以下是查询语句，假设输入数据位于表t的created_at列中：

SELECT * FROM CROSSTAB($$SELECT h.hour AS hour_of_day,
    dow.day AS day, 
    COUNT(t.created_at)::INT
FROM (values('Mon'),('Tue'),('Wed'),('Thu'),('Fri'),('Sat'),('Sun')) AS dow(day)
CROSS JOIN generate_series(0,23) as h(hour)
LEFT JOIN t ON to_char(t.created_at, 'Dy')=dow.day AND extract(hour from t.created_at)=h.hour
GROUP BY dow.day,h.hour
ORDER BY h.hour,dow.day$$) AS d(Hour int, "Mon" int,"Tue" int,"Wed" int,"Thu" int,"Fri" int,"Sat" int,"Sun" int);

需要注意的要点：

• 查询将天数集合与00-23小时集合进行交叉连接，以便所有输出中的单元格都存在，即使对于给定单元格在输入表中没有数据也是如此。
• crosstab函数接受SQL查询作为输入并交叉制表结果，生成记录集。
• 由于某些原因我不完全清楚，我必须将count的返回值转换为类型（我猜它返回BIGINT？）
• 您（不幸的是）必须通过AS子句在结果中拼写列名，如所示。

以下是结果:

 hour | Mon | Tue | Wed | Thu | Fri | Sat | Sun
------+-----+-----+-----+-----+-----+-----+-----
    0 |   0 |   0 |   0 |   0 |   0 |   0 |   0
    1 |   0 |   0 |   0 |   0 |   0 |   0 |   0
    2 |   0 |   0 |   0 |   0 |   0 |   0 |   0
    3 |   0 |   0 |   0 |   0 |   0 |   0 |   0
    4 |   0 |   0 |   0 |   0 |   0 |   0 |   0
    5 |   0 |   0 |   0 |   0 |   0 |   0 |   0
    6 |   0 |   0 |   0 |   0 |   0 |   0 |   0
    7 |   0 |   0 |   0 |   0 |   0 |   0 |   0
    8 |   0 |   0 |   0 |   0 |   0 |   0 |   0
    9 |   0 |   0 |   0 |   0 |   0 |   0 |   0
   10 |   0 |   0 |   0 |   0 |   0 |   0 |   1
   11 |   0 |   0 |   0 |   0 |   0 |   0 |   0
   12 |   0 |   0 |   0 |   0 |   0 |   0 |   0
   13 |   0 |   0 |   0 |   1 |   0 |   0 |   0
   14 |   0 |   0 |   0 |   0 |   0 |   0 |   0
   15 |   0 |   0 |   0 |   0 |   0 |   0 |   0
   16 |   0 |   0 |   0 |   0 |   0 |   0 |   0
   17 |   0 |   0 |   1 |   0 |   0 |   0 |   0
   18 |   0 |   0 |   0 |   0 |   0 |   0 |   0
   19 |   0 |   0 |   0 |   0 |   0 |   0 |   0
   20 |   0 |   0 |   0 |   0 |   0 |   0 |   0
   21 |   0 |   0 |   0 |   0 |   0 |   0 |   0
   22 |   0 |   0 |   0 |   0 |   0 |   0 |   0
   23 |   0 |   0 |   0 |   0 |   1 |   0 |   0
(24 rows)

这是从样本数据生成的：

         created_at
----------------------------
 2014-06-12 23:06:03.746884
 2014-01-15 10:00:00
 2014-05-25 13:00:00
 2014-03-01 17:00:00
(4 rows)

- harmic

---

这正是我所需要的。搭配 Highchart->Heatmap，我完成了工作。你的查询中有一个错别字：“FROM”子句中的“Tues”应该改为“Tue”。 - zener79

Answer 2

这很简单。您只需要按天的小时和日期分组，然后计算有多少个元素。结果的前两列（天的小时和日期）是您图表中对应单元格的2D坐标。第三列（计数）给出了该单元格的颜色。

例如：

SELECT 
   extract('hour' FROM starttime) as hour, 
   date_trunc('day', starttime) as day,
   count(*) as nbmr
FROM actions
GROUP BY hour, day;

(实时演示)

在这个例子中，"hour" 和 "day" 列对应于您图表中单元格的 y 和 x 轴。"nmbr" 列告诉您该单元格的颜色。

您可以轻松修改此查询，例如按百分比和星期几分组显示（0 代表星期日）：

SELECT 
   extract('hour' FROM starttime) as hour, 
   extract('dow' FROM starttime) as day,
   count(*) * 100.0 / (select count(*) from actions) as nbmr
FROM actions
GROUP BY hour, day;

(

实时的小提琴

)

- stefan.schwetschke