我有一个以 1 分钟为间隔提取记录数的查询。我想比较平均值与最后一个值。
我只得到他总数,即计数(1)及其有效。平均值和最大值总是返回 1,这是不准确的。
我在这个 SQL 中遗漏了什么吗?
SELECT
FORMAT(timestamp, 'hh:mm') AS tm,
AVG(1) AS avgOccurances,
MAX(1) AS maxocc,
COUNT(1) AS total
FROM
[history]
WHERE
timestamp BETWEEN '2018-04-16 14:00:00.707' AND '2018-04-18 15:00:00.707'
AND result = 'F'
GROUP BY
FORMAT(timestamp, 'hh:mm')
ORDER BY
tm ASC
结果
01:00 1 1 13
01:01 1 1 10
01:02 1 1 11
01:03 1 1 7
01:04 1 1 13
01:05 1 1 7
01:06 1 1 14
01:07 1 1 11
01:08 1 1 12
01:09 1 1 10
01:10 1 1 5
01:11 1 1 6
01:12 1 1 8
01:13 1 1 13
01:14 1 1 9
01:15 1 1 8
01:16 1 1 2
01:17 1 1 10
01:18 1 1 9
01:19 1 1 13
01:20 1 1 9
01:21 1 1 8
01:22 1 1 14
01:23 1 1 10
下面的查询假设每一行都应具有以下内容:
如果那不正确,请告诉我。这是查询:
WITH countbyminute AS (
SELECT
FORMAT(timestamp, 'hh:mm') AS tm,
COUNT(*) AS occurences
FROM history
GROUP BY FORMAT(timestamp, 'hh:mm')
)
SELECT
tm,
occurrences,
LAG(occurrences) OVER (ORDER BY TIMESTAMP) AS priorocc,
MAX(occurrences) OVER () AS maxocc
FROM countbyminute
ORDER BY tm;
我建议使用HH:mm
作为格式字符串,它将使用 24 小时制(下午 1:00 为 13:00)重新显示小时数。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句