Коллеги, добрый день! Подскажите, пожалуйста, можно ли такое сделать в

кликхаусе.

Есть таблица:

CREATE TABLE db.entity_agg_count
(
`entity_id` UUID,
`loc_campus_id` UUID,
`loc_building_id` UUID,
`loc_ward_id` UUID,
`loc_floor_id` UUID,
`loc_room_id` UUID,
`start_of_hour` DateTime,
`dirty` AggregateFunction(argMax, UInt64, DateTime),
`clean` AggregateFunction(argMax, UInt64, DateTime),
`occupied` AggregateFunction(argMax, UInt64, DateTime),
`occupied_infected` AggregateFunction(argMax, UInt64, DateTime),
`dirty_infected` AggregateFunction(argMax, UInt64, DateTime),
`working` AggregateFunction(argMax, UInt64, DateTime),
`broken` AggregateFunction(argMax, UInt64, DateTime)
) ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/tables/{shard}/db/entity_agg_count',
'{replica}') PARTITION BY toYYYYMM(start_of_hour) PRIMARY KEY (loc_campus_id, start_of_hour, entity_id)
ORDER BY (loc_campus_id, start_of_hour, entity_id, loc_ward_id, loc_building_id, loc_room_id, loc_floor_id) SETTINGS index_granularity = 8192;

С помощью запроса

SELECT sum(clean) as clean,
sum(occupied) as occupied,
sum(dirty) as dirty,
sum(occupied_infected) as occupied_infected,
sum(dirty_infected) as dirty_infected,
sum(working) as working,
sum(broken) as broken
FROM (SELECT site_id,
argMaxMerge(clean) as clean,
argMaxMerge(occupied) as occupied,
argMaxMerge(dirty) as dirty,
argMaxMerge(occupied_infected) as occupied_infected,
argMaxMerge(dirty_infected) as dirty_infected,
argMaxMerge(working) as working,
argMaxMerge(broken) as broken
FROM db.dis_site_agg_count
WHERE start_of_hour <= toDateTime(1572515014)
AND loc_campus_id = '3a5c43bd-1f3b-43de-b303-95d519535c83'
GROUP BY site_id
ORDER BY site_id)

получаю последие состояния сущностей на какой- то час и суммирую эти состояния.

Хочу получить тоже самое, только от начала дня с группировкой по часу. То есть, на каждый час будет видно, сколько было каких сущностей.

SELECT start_of_hour,
sum(clean) as clean,
sum(occupied) as occupied,
sum(dirty) as dirty,
sum(occupied_infected) as occupied_infected,
sum(dirty_infected) as dirty_infected,
sum(working) as working,
sum(broken) as broken
FROM (SELECT start_of_hour,
site_id,
argMaxMerge(clean) as clean,
argMaxMerge(occupied) as occupied,
argMaxMerge(dirty) as dirty,
argMaxMerge(occupied_infected) as occupied_infected,
argMaxMerge(dirty_infected) as dirty_infected,
argMaxMerge(working) as working,
argMaxMerge(broken) as broken
FROM db.dis_site_agg_count
WHERE start_of_hour <= toDateTime(1572515027)
AND loc_campus_id = '3a5c43bd-1f3b-43de-b303-95d519535c83'
AND start_of_hour >= toDate(18199)
GROUP BY start_of_hour, site_id
ORDER BY site_id)
GROUP BY start_of_hour;

Пробовал данный запрос, но он выводит не последнее состояние на каждый час, а только те состояния, которые были записаны в этом часу. Это не то,что требуется.

Есть ли какой- то способ сделать то, что мне нужно одним запросом, либо надо делать в худшем случае 24 запроса?

argMaxMerge(clean) у вас max от от времени? т.е. типа если в этом часу не было состояния то его надо взять из предыдущего