Как динамически делать выборку по дням, неделям или месяцам?

Question

[uRoot]

У меня есть сложный запрос для статистики:

Запрос

select 
	country_id,
    MAX(CASE WHEN ts = '2024-04-09' THEN hangup_rate END) AS "2024-04-09_rate",
    MAX(CASE WHEN ts = '2024-04-09' THEN all_answered END) AS "2024-04-09_all_anwered",
    MAX(CASE WHEN ts = '2024-04-10' THEN hangup_rate END) AS "2024-04-10_rate",
    MAX(CASE WHEN ts = '2024-04-10' THEN all_answered END) AS "2024-04-10_all_anwered"
from 
	(select
	ts,
	country_id,
	round((100 * QUEUE_HANGUP/ all_answered), 2) as hangup_rate,
	all_answered
	from 
	(
	SELECT
		toDate(ts) AS ts,
		co.country_id,
		CASE
			WHEN count() > 0 THEN sum(CASE WHEN v.client_call_status = 'QUEUE_HANGUP' THEN 1 ELSE 0 END)
			ELSE NULL
		END as QUEUE_HANGUP,
		CASE
				WHEN count() > 0 THEN sum(CASE WHEN v.client_call_status = 'ANSWERED' THEN 1 ELSE 0 END)
				ELSE NULL
		END as ANSWERED,
		CASE
				WHEN count() > 0 THEN sum(CASE WHEN v.client_call_status in ('ANSWERED','QUEUE_HANGUP') THEN 1 ELSE 0 END)
				ELSE NULL
		END as all_answered
	FROM vt_tm_cdrs v
	join crm_orders co on v.order_id = co.id 
	WHERE
		co.created_at >= '2024-04-09 21:00:00' AND created_at <= '2024-04-10 20:59:59'
		and v.ts  >= '2024-04-09 21:00:00' AND v.ts <= '2024-04-10 20:59:59'
	GROUP BY
		ts, country_id
	ORDER BY
		ts, country_id ASC
	) q
) q2
group by country_id

Как вы видите, тут идёт выборка за 2 дня: 2024-04-09 и 2024-04-10. Но проблема в том, что количество дней указывает пользователь и их может быть сколько угодно. Подскажите, как правильно сделать, что если пользователь указал, например, 10 дней, то выборку сделать за 10 дней (начиная от сегодня)? Также, как я и написал в заглавии, у меня могут быть ещё месяца или недели.

Я бы мог на JS сгенерировать 10 записей типа этой:

MAX(CASE WHEN ts = '2024-04-09' THEN hangup_rate END) AS "2024-04-09_rate",
MAX(CASE WHEN ts = '2024-04-09' THEN all_answered END) AS "2024-04-09_all_anwered",

Но, наверное, это не правильно.

Дополнение:

WHERE
  co.created_at >= '2024-04-09 21:00:00' AND created_at <= '2024-04-10 20:59:59'
  and v.ts  >= '2024-04-09 21:00:00' AND v.ts <= '2024-04-10 20:59:59'

Тут так странно даты указаны, чтобы учесть тайм зону (+3 часа)

Comments

[Akina]

Тут так странно даты указаны, чтобы учесть тайм зону (+3 часа)

Ну если бы вы вместо '2024-04-09 21:00:00' написали CONVERT_TZ('2024-04-10', 'GMT', 'MSK'), то можно было и не пояснять...

Answer 1

[Akina]

Как вы видите, тут идёт выборка за 2 дня: 2024-04-09 и 2024-04-10. Но проблема в том, что количество дней указывает пользователь и их может быть сколько угодно. Подскажите, как правильно сделать, что если пользователь указал, например, 10 дней, то выборку сделать за 10 дней (начиная от сегодня)?

Я вижу, что количество выходных полей зависит от внешних условий. То есть структура выходного набора динамическая, а всё вместе это по сути сводная таблица. То есть штука совсем даже не реляционная.
Да, некоторые СУБД имеют встроенные средства для формирования PIVOT TABLE. Yо вот у MariaDB с этим не очень.

Рекомендую - на стороне СУБД получать нормализованный набор записей (country_id, ts, MAX(hangup_rate), MAX(all_answered)), а сводную строить уже на клиенте.