clickhouse select request with shifting filter

Question

need some help) I have simple request:

    SELECT uniq(DeviceId)
    from events
    where EventDateTime between '2020-12-28 00:00:00' and '2020-12-28 23:59:59'

Now, i need to find the same result with one hour shift:

    SELECT uniq(DeviceId)
    from events
    where EventDateTime between '2020-12-27 00:23:00' and '2020-12-28 22:59:59'

And one hour more:

    SELECT uniq(DeviceId)
    from events
    where EventDateTime between '2020-12-27 00:22:00' and '2020-12-28 21:59:59'

So, for 24h I need 24 requests - is there is way to do this in one request?

Expected result:

'2020-12-28 00:00:00' - '2020-12-28 23:59:59', 'uniqValue1'
'2020-12-27 00:23:00' - '2020-12-28 22:59:59', 'uniqValue2' 
'2020-12-27 00:22:00' - '2020-12-28 21:59:59', 'uniqValue3'
... 
'2020-12-27 00:00:00' - '2020-12-27 23:59:59, 'uniqValue24'

Answer 1

Updated:

WITH 5 AS steps
SELECT result.2 EventDateTime, result.1 UniqDeviceId
FROM
(
    SELECT
        groupArray(hour) AS hour_arr,    
        groupArray(uniqDeviceIdState) AS state_arr,
        arrayReduceInRanges('uniqMerge', arrayMap(x -> (x, length(state_arr)), range(1, 1 + steps)), state_arr) AS calculated_uniq_metrics,
        arrayMap((x, i) -> (x, hour_arr[i]), calculated_uniq_metrics, arrayEnumerate(calculated_uniq_metrics)) metric_hour_arr,
        arrayJoin(metric_hour_arr) result
    FROM
    (
        SELECT
            toStartOfHour(EventDateTime) AS hour,
            uniqState(DeviceId) AS uniqDeviceIdState
        FROM
        (
            /* Emulate the test dataset. */
            SELECT
                addHours(toDate('2020-05-05'), hour) AS EventDateTime ,
                arrayJoin(DeviceIds) AS DeviceId
            FROM
            (
                SELECT
                    number % 48 AS hour,
                    groupArray(number) AS DeviceIds
                FROM numbers(96)
                GROUP BY hour
            )
        )
        WHERE EventDateTime between '2020-05-05 00:00:00' and '2020-05-07 23:59:59'
        GROUP BY hour
        ORDER BY hour ASC
    )
)

/*
┌───────EventDateTime─┬─UniqDeviceId─┐
│ 2020-05-05 00:00:00 │           96 │
│ 2020-05-05 01:00:00 │           94 │
│ 2020-05-05 02:00:00 │           92 │
│ 2020-05-05 03:00:00 │           90 │
│ 2020-05-05 04:00:00 │           88 │
└─────────────────────┴──────────────┘
*/

---

Consider using grouping and toStartOfHour-function:

SELECT toStartOfHour(EventDateTime) startOfHour, uniq(DeviceId)
from events
where EventDateTime between '2020-12-28 00:00:00' and '2020-12-28 23:59:59'
group by startOfHour
order by startOfHour