Сбор статистики за последние 12 месяцев: группировка записей по месяцам в SQLite

Delphi , Базы данных , SQL

Для выполнения задачи по сбору статистики за последние 12 месяцев, включая текущий, с использованием базы данных SQLite и языка запросов SQL, можно использовать следующий подход. В примере используется таблица EVENTS с полем WODATE, которое содержит даты событий.

Оригинальный запрос

Изначально пользователь пытался получить количество записей, сгруппированных по месяцам за 2020 год, но хотел получить данные за последние 12 месяцев, включая текущий, даже если в некоторых месяцах записей не было.

Решение проблемы

С версии SQLite 3.8.3 появилась возможность использования общих табличных выражений (CTE) и конструкции VALUES, что позволяет создавать временные таблицы с фиксированным набором данных. В данном случае можно создать временную таблицу с датами начала и конца каждого из последних 12 месяцев.

Вот пример SQL-запроса, который решает поставленную задачу:

WITH DateRanges(StartDate, EndDate) AS (
  VALUES
    (date('now', 'start of month', '-11 month'), date('now', 'start of month', '-10 month', '-1 day')),
    -- ... другие месяцы ...
    (date('now', 'start of month'),              date('now', 'start of month', '+1 month', '-1 day'))
)
SELECT
  strftime('%m/%Y', DateRanges.StartDate) as Month,
  COUNT(Events.ROWID) AS Count
FROM DateRanges
  LEFT OUTER JOIN Events ON (DateRanges.StartDate <= Events.WODate) AND (Events.WODate <= DateRanges.EndDate)
GROUP BY
  DateRanges.StartDate, DateRanges.EndDate
ORDER BY
  DateRanges.StartDate;

Пример кода на Object Pascal (Delphi)

Для выполнения этого запроса в Delphi с использованием компонента TFDQuery, код может выглядеть следующим образом:

with aFDQuery.SQL do
begin
  BeginUpdate;
  try
    Add('WITH DateRanges(StartDate, EndDate) AS (VALUES');
    // ... добавление дат начала и конца для каждого из последних 12 месяцев ...
    Add('SELECT strftime(\'%m/%Y\', DateRanges.StartDate) as Month, COUNT(Events.ROWID) AS Count');
    Add('FROM DateRanges LEFT OUTER JOIN Events ON (DateRanges.StartDate <= Events.WODate) AND (Events.WODate <= DateRanges.EndDate)');
    Add('GROUP BY DateRanges.StartDate, DateRanges.EndDate');
    Add('ORDER BY DateRanges.StartDate');
  finally
    EndUpdate;
  end;
end;

Замечания

• В примере кода на Object Pascal (Delphi) необходимо убедиться, что каждая строка SQL-запроса заключена в соответствующие двойные кавычки, учитывая особенности экранирования символов в строках Delphi.
• Важно правильно указать даты начала и конца для каждого месяца в конструкции VALUES.
• При использовании внешних инструментов, таких как SQLFiddle, можно проверить работоспособность запроса в изолированной среде.

Используя данный подход, можно получить статистику по количеству записей за последние 12 месяцев, включая текущий месяц, даже если в некоторых месяцах записей не было.

Создано по материалам из источника по ссылке.

Задание состоит в сборе статистических данных за последние 12 месяцев, включая текущий, с группировкой по месяцам в базе данных SQLite.

Комментарии и вопросы

Получайте свежие новости и обновления по Object Pascal, Delphi и Lazarus прямо в свой смартфон. Подпишитесь на наш Telegram-канал delphi_kansoftware и будьте в курсе последних тенденций в разработке под Linux, Windows, Android и iOS

:: Главная :: SQL ::