52 lines
1.9 KiB
SQL
52 lines
1.9 KiB
SQL
-- Переход на дневные счётчики: переносим старые построчные сообщения
|
||
-- в analytics.messages_daily и удаляем журнал bot.messages.
|
||
--
|
||
-- Для старых строк chat_id неизвестен (в bot.messages его не было), поэтому
|
||
-- пишем 0. Чтение группирует по (day, user_id) и суммирует count, так что
|
||
-- старые и новые записи одного дня не дублируются в интерфейсе.
|
||
DO $$
|
||
DECLARE
|
||
min_day date;
|
||
max_day date;
|
||
d date;
|
||
BEGIN
|
||
SELECT min((dt AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Moscow')::date),
|
||
max((dt AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Moscow')::date)
|
||
INTO min_day, max_day
|
||
FROM bot.messages
|
||
WHERE dt IS NOT NULL;
|
||
|
||
IF min_day IS NULL THEN
|
||
RETURN;
|
||
END IF;
|
||
|
||
-- Партиции на все исторические дни, иначе вставка ниже не найдёт партицию.
|
||
FOR d IN
|
||
SELECT min_day + i
|
||
FROM generate_series(0, max_day - min_day) AS i
|
||
LOOP
|
||
EXECUTE format(
|
||
'CREATE TABLE IF NOT EXISTS analytics.messages_daily_%s '
|
||
'PARTITION OF analytics.messages_daily '
|
||
'FOR VALUES FROM (%L) TO (%L)',
|
||
to_char(d, 'YYYYMMDD'), d, d + 1
|
||
);
|
||
END LOOP;
|
||
END $$;
|
||
|
||
INSERT INTO analytics.messages_daily (day, user_id, username, chat_id, count)
|
||
SELECT (m.dt AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Moscow')::date AS day,
|
||
m.id_user,
|
||
coalesce(tg.username, '') AS username,
|
||
0 AS chat_id,
|
||
count(*) AS count
|
||
FROM bot.messages AS m
|
||
LEFT JOIN bot.tg_users AS tg ON tg.id_user = m.id_user
|
||
WHERE m.dt IS NOT NULL
|
||
GROUP BY 1, 2, 3
|
||
ON CONFLICT (day, user_id, chat_id) DO UPDATE
|
||
SET count = analytics.messages_daily.count + EXCLUDED.count,
|
||
username = EXCLUDED.username;
|
||
|
||
DROP TABLE IF EXISTS bot.messages;
|