Using SQL Server, I want to see the daily totals of rows that have repeated at least 30 of the same row in the last hour. However, when you query with having after grouping by day, it does not show whether the recording at 00:01 at night has been repeated in the last hour or not.
Table has ID, Message, EventTime columns.
Here is my solution but it is extreamly slow:
SELECT
DATE_TRUNC('DAY', EventTime) AS EventTime,
COUNT(*) AS RecordCount
FROM
(SELECT
EventTime, Message,
(SELECT COUNT(*)
FROM MyTable AS CountTable
WHERE EventTime BETWEEN DATE_ADD(T.EventTime, CAST(-1 AS INTERVAL HOUR)) AND T.EventTime
AND Message = CountTable.Message) AS HourCount
FROM
MyTable AS T
WHERE
EventTime BETWEEN '2024-06-01 00:00:00.000' AND '2024-06-10 23:59:59.999'
WHERE
HourCount >= 30
GROUP BY
DATE_TRUNC('DAY', EventTime)
ORDER BY
DATE_TRUNC('DAY', EventTime)