Запросы к ClickHouse

Запрос на получение метрик по объектам:

SELECT 
	sum(count) as count,
	multiIf(portal_id in &ПереходыВыходы AND metric_type == 'out', 'in', portal_id in &ПереходыВыходы AND metric_type == 'in', 'out', toString(metric_type)) as metric_type,
	toStartOfDay(toDateTime(timestamp, &ЧасовойПояс)) as timestampDay
FROM
	metrics_by_portals FINAL 
WHERE
	portal_id in  &ПереходыВсе
	AND toDateTime(timestamp, &ЧасовойПояс) BETWEEN toDateTime(&ДатаНачалаОтбора, &ЧасовойПояс) AND toDateTime(&ДатаОкончанияОтбора, &ЧасовойПояс)
	AND HOUR(toDateTime(timestamp, &ЧасовойПояс)) IN &МассивЧасовРаботы
	AND multiIf(HOUR(toDateTime(timestamp, &ЧасовойПояс)) = &ЧасНачалаРаботы, MINUTE(toDateTime(timestamp, &ЧасовойПояс)) >= &МинутаНачалаРаботы, HOUR(toDateTime(timestamp, &ЧасовойПояс)) = &ЧасОкончанияРаботы, MINUTE(toDateTime(timestamp, &ЧасовойПояс)) < &МинутаОкончанияРаботы, MINUTE(toDateTime(timestamp, &ЧасовойПояс)) >= 0)
GROUP BY
(
	metric_type,
	timestampDay
)
HAVING metric_type = 'in' 
SETTINGS optimize_move_to_prewhere_if_final = 1

где, 

• &ПереходыВыходы - массив идентификаторов переходов, которые являются выходами для объекта (backward в direction).
• &ПереходыВсе - массив идентификаторов  всех переходов объекта .
• &ЧасовойПояс - Часовой пояс объекта .
• &ДатаНачалаОтбора и &ДатаОкончанияОтбора -  период отбора данных. Рекомендуется получать данные за полные дни.
• &МассивЧасовРаботы - массив часов работы объекта (см. пример запроса). 
• &ЧасНачалаРаботы и &МинутаНачалаРаботы - час и минута времени начала работы объекта.
• &ЧасОкончанияРаботыи &МинутаОкончанияРаботы- час и минута времени окончания работы объекта.

Отбор по времени работы объекта использовать только в случае если объект не круглосуточный. 

 

Пример заполненного запроса:

SELECT 
	sum(count) as count,
	multiIf(portal_id in ('074d4fdd-6b5d-417b-a9b3-ee84b52b2252') AND metric_type == 'out', 'in', portal_id in ('4ad9d597-f21d-45dc-880a-72926630edea') AND metric_type == 'in', 'out', toString(metric_type)) as metric_type,
	toStartOfDay(toDateTime(timestamp, 'Asia/Yekaterinburg')) as timestampDay
FROM
	metrics_by_portals FINAL 
WHERE
	portal_id in ('05b6bef9-5c34-41b4-ae8f-8e09869e3f12', '074d4fdd-6b5d-417b-a9b3-ee84b52b2252')
	AND toDateTime(timestamp, 'Asia/Yekaterinburg') BETWEEN toDateTime('2023-03-26 00:00:00', 'Asia/Yekaterinburg') AND toDateTime('2023-03-26 23:59:59', 'Asia/Yekaterinburg')
	AND HOUR(toDateTime(timestamp, 'Asia/Yekaterinburg')) IN (5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21)
	AND multiIf(HOUR(toDateTime(timestamp, 'Asia/Yekaterinburg')) = 5, MINUTE(toDateTime(timestamp, 'Asia/Yekaterinburg')) >= 0, HOUR(toDateTime(timestamp, 'Asia/Yekaterinburg')) = 21, MINUTE(toDateTime(timestamp, 'Asia/Yekaterinburg')) < 25, MINUTE(toDateTime(timestamp, 'Asia/Yekaterinburg')) >= 0)
GROUP BY
(
	metric_type,
	timestampDay
)
HAVING metric_type = 'in' 
SETTINGS optimize_move_to_prewhere_if_final = 1

 

Получение данных по объекту:

SELECT 
	object_id as object_id,
	name as name, 
	portal_id as portal_id, 
	direction as direction,
	working_hours.timezone,
	working_hours.start_time,
	working_hours.end_time,
	working_hours.around_the_clock 
FROM 
	objects
	LEFT ANY JOIN working_hours AS wh
	  ON working_hours.object_id = objects.object_id
 ORDER BY object_id

Пример результата запроса: