SQL -平均x每y，每z

Question

我有一个两列的数据集，一个ID和一个日期时间

我试图得到一个平均值count每个ID每月小时数。

到目前为止，我已经成功地使用这段代码来获取每天的小时数:

SELECT
id,
DATE(created),
DATE_PART(hour, created)::int AS "hour",
--Counts 15 minute windows of activity
FLOOR(DATE_PART(minute, created) / 15) AS "15 mins",
(COUNT(*) OVER (PARTITION BY id,
DATE(created)) / 4.0) AS "Online Hours"
FROM
--The temporary table I pulled the data into
#tempres
GROUP BY
id,
DATE(created),
DATE_PART(hour, created),
FLOOR(DATE_PART(minute, created) / 15)
ORDER BY
id DESC,
DATE(created),
"hour" ASC,
"15 mins" ASC;

但是我还没有弄清楚如何在窗口函数上应用AVG来获得每月的结果。

这个mockoo链接应该产生与我使用的相同类型的数据。

我知道这可能不是最精简的起点，所以我非常愿意接受任何其他想法!

Answer 1

好的，所以我已经提出了一个基本功能的查询。

通过将旧查询放入CTE中，我可以在每月的时间段内对其运行平均聚合函数。

仍然对更干净的选择持开放态度，但我想我应该把我拥有的放在这里。

WITH cte AS (
SELECT
id,
MAX(created) AS "created",
DATE_PART(month,
created) AS "month",
DATE(created) AS "day",
DATE_PART(hour,
created)::int AS "hour",
FLOOR(DATE_PART(minute,
created) / 15) AS "15_mins",
COUNT(id),
(COUNT(*) OVER (PARTITION BY id,
DATE(created)) / 4.0) AS "online_hours"
FROM
#tempres
GROUP BY
id,
DATE_PART(month,
created),
DATE(created),
DATE_PART(hour,
created),
FLOOR(DATE_PART(minute,
created) / 15)
ORDER BY
id DESC,
DATE(created),
"hour" ASC,
"15_mins" ASC
) SELECT
id,
DATE_PART(year, cte.created) AS "Year",
cte.month,
ROUND(AVG(online_hours), 2) AS "Average Hours"
FROM
cte
GROUP BY
id,
DATE_PART(year, cte.created),
cte.month
ORDER BY
id,
DATE_PART(year, cte.created),
cte.month;