逐小时统计

社区 数据与报告
6

下面的查询将显示按一天中的每小时创建的帖子数量。

-- [params]
-- int :months_ago = 1

WITH query_period AS
(SELECT date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' AS period_start,
                                                    date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' + INTERVAL '1 month' - INTERVAL '1 second' AS period_end)
SELECT EXTRACT (HOUR FROM p.created_at) AS hour,
  COUNT(p.id) AS amount
FROM posts p
JOIN topics t ON t.id = p.topic_id
JOIN query_period qp ON p.created_at >= qp.period_start
AND p.created_at <= qp.period_end
WHERE t.archetype = 'regular'
AND p.user_id > 0
GROUP BY hour
ORDER BY hour

数据探索器插件是我们默认安装的插件之一。您只需在“管理 - 插件”中启用它即可。如果您有任何关于我们托管服务的具体问题，请发送至 support@discoursehosting.com。