Советы и подсказки MariaDB
Это часть серии быстрых советов и рекомендаций, которые я накопил за год и которые, как мне кажется, могут быть полезны другим.
Если у вас есть похожие короткие советы и рекомендации, пожалуйста, оставьте комментарий.
Создание последовательности даты и времени
При работе с данными, которые каким-то образом связаны с датами и временем, вам может понадобиться создать последовательность дат для группировки или выбора из них.
Хитрость здесь заключается в использовании рекурсивного CTE (Common Table Expression). Оно может быть немного запутанным для чтения, но суть его в том, что CTE создает объединение с одной дополнительной датой для каждой рекурсии. Больше примеров можно найти в документации MariaDb. А синтаксис очень похож на синтаксис SQL Server, как вы можете видеть здесь.
Код ниже создаст последовательность временных данных с интервалом в 10 минут между каждым временным значением.
Как и рекурсия в целом, этот метод может занимать много ресурсов и быть медленным, поэтому я бы не рекомендовал использовать его в производственном коде с большим количеством выполнений. Но для анализа данных и углубления в данные этот трюк может быть очень полезен.
SET @stepSizeInMinutes = 10; -- Change this line to change the time interval
SET @from = '2017-01-01 00:00:00.00000';
SET @to = '2017-01-04 23:50:00.00000';
-- Create Recursive Discrete Table
WITH RECURSIVE Recursive_CTE AS
(
SELECT @from AS TimestampUtc
UNION ALL
SELECT TimestampUtc + INTERVAL @stepSizeInMinutes MINUTE
FROM Recursive_CTE
WHERE TimestampUtc < @to
)
SELECT *
FROM Recursive_CTE
ORDER BY TimestampUtc
Создание последовательности с помощью механизма последовательности MariaDB
Как отметил в комментариях @darkain, спасибо за совет, последовательность дат может быть создана с помощью механизма MariaDB Sequence, и это решение намного эффективнее, чем использование рекурсивного CTE. Вот SQL для этого:
SET @stepSizeInMinutes = 10; -- Change this line to change the time interval
SET @from = '2017-01-01 00:00:00.00000';
SET @to = '2017-01-04 23:50:00.00000';
SELECT @from + INTERVAL seq * @stepSizeInMinutes MINUTE AS `Datetime sequence`
FROM seq_0_to_99999999
WHERE seq BETWEEN 0 and (SELECT TIMESTAMPDIFF(MINUTE, @from, @to) / @stepSizeInMinutes);
