На главную
ПРАВИЛА FAQ Помощь Участники Календарь Избранное RSS
msm.ru
! информация о разделе
user posted imageДанный раздел предназначается исключительно для обсуждения вопросов использования языка запросов SQL. Обсуждение общих вопросов, связанных с тематикой баз данных - обсуждаем в разделе "Базы данных: общие вопросы". Убедительная просьба - соблюдать "Правила форума" и не пренебрегать "Правильным оформлением своих тем". Прежде, чем создавать тему, имеет смысл заглянуть в раздел "Базы данных: FAQ", возможно там уже есть ответ.

Модераторы: Akina
  
> Оптимизировать запрос по месяцам, SQLite 3
Всем привет!

В таблице есть поле "Activity" в котором по факту записывается штамп времени. Мне нужно за определенный год (допустим 2020) вывести количество записей по месяцам. Результатом должна быть запись с 12-ю полями. Я составил вот такой запрос:

ExpandedWrap disabled
    SELECT
      SUM(F1)  AS 'M1',
      SUM(F2)  AS 'M2',
      SUM(F3)  AS 'M3',
      SUM(F4)  AS 'M4',
      SUM(F5)  AS 'M5',
      SUM(F6)  AS 'M6',
      SUM(F7)  AS 'M7',
      SUM(F8)  AS 'M8',
      SUM(F9)  AS 'M9',
      SUM(F10) AS 'M10',
      SUM(F11) AS 'M11',
      SUM(F12) AS 'M12'
    FROM (
      SELECT
        CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '01'  THEN COUNT(G.Activity) ELSE 0 END AS 'F1',
        CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '02'  THEN COUNT(G.Activity) ELSE 0 END AS 'F2',
        CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '03'  THEN COUNT(G.Activity) ELSE 0 END AS 'F3',
        CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '04'  THEN COUNT(G.Activity) ELSE 0 END AS 'F4',
        CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '05'  THEN COUNT(G.Activity) ELSE 0 END AS 'F5',
        CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '06'  THEN COUNT(G.Activity) ELSE 0 END AS 'F6',
        CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '07'  THEN COUNT(G.Activity) ELSE 0 END AS 'F7',
        CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '08'  THEN COUNT(G.Activity) ELSE 0 END AS 'F8',
        CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '09'  THEN COUNT(G.Activity) ELSE 0 END AS 'F9',
        CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '10'  THEN COUNT(G.Activity) ELSE 0 END AS 'F10',
        CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '11'  THEN COUNT(G.Activity) ELSE 0 END AS 'F11',
        CASE WHEN strftime('%m', datetime(G.Activity, 'unixepoch')) = '12'  THEN COUNT(G.Activity) ELSE 0 END AS 'F12'
      FROM Guests AS G
      WHERE strftime('%Y', datetime(G.Activity, 'unixepoch')) = '2020'
      GROUP BY strftime('%m', datetime(G.Activity, 'unixepoch'))
    )


Запрос работает правильно. Но мне кажется, что он получился уж слишком громоздким.
Можно ли его переписать/оптимизировать?
Мои программные ништякиhttps://majestio.info
В общем, на SO, на примере убедили, что лучше вот такой запрос:

ExpandedWrap disabled
    SELECT
      CAST(strftime('%m', datetime(Activity, 'unixepoch')) AS INTEGER)  AS Month,
      COUNT(Activity) AS Count
    FROM Guests
    WHERE strftime('%Y', datetime(Activity, 'unixepoch')) = '2020'
    GROUP BY strftime('%m', datetime(Activity, 'unixepoch'))

А потом просто пробежаться по записям и собрать в своей проге вектор из 12 элементов.
Получается и красивше, и пожалуй, шустрее.
Мои программные ништякиhttps://majestio.info
Где ж ты раньше был.
Сообщение отредактировано: Gonarh -
Цитата JoeUser @
А потом просто пробежаться по записям и собрать в своей проге вектор из 12 элементов.

Ну формально это можно и в запросе сделать - если затолкать твой запрос в CTE. Особенно если средство отображения в программе не умеет само пивотить и требует дополнительного кода.
Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
Есть претензии ко мне как к участнику? да ради бога.
Не нравятся мои ответы? не читайте их.
В общем, берегите себя. Нервные клетки не восстанавливаются.
Цитата Akina @
Ну формально это можно и в запросе сделать - если затолкать твой запрос в CTE. Особенно если средство отображения в программе не умеет само пивотить и требует дополнительного кода.

С особенностями SQLite 3 - знакомлюсь по ходу разработки. Возможности конечно гораздо беднее того же PostgreSQL.

Добавлено
Цитата Gonarh @
Где ж ты раньше был.

В смысле, я что-то сделал нетак? :)
Мои программные ништякиhttps://majestio.info
Цитата Akina @
Особенно если средство отображения в программе не умеет само пивотить и требует дополнительного кода.

Ну у меня это вылилось в некоторую долю дополнительного кода (Qt5/C++):

ExpandedWrap disabled
    // ...
    int Max = 0;
    // 1) ►►► дополнительный вектор
    QVector<int> Res {0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0};
    // 2) ►►► дополнительный цикл перебора результирующих записей и заполнение вектора
    while (Query.next()) {
        QSqlRecord R = Query.record();
        Res[Query.value(R.indexOf("Month")).toInt() - 1] = Query.value(R.indexOf("Count")).toInt();
        if (Query.value(R.indexOf("Count")).toInt() > Max)
            Max = Query.value(R.indexOf("Count")).toInt();
    }
    // заполняем сет (максимум - выбираем ближайшим большим или равным, но кратным четырем)
    Max = ((Max + 3) / 4) * 4;
    AxisY->setRange(0, Max);
    // ?) ►►► значения берем не из Query.value(i).toInt() , а из Res[i]
    for (int i = 0; i < 12; i++)
        BarSet->replace(i, Res[i]);
    // ...

Я считаю, что оно того стоило!
Мои программные ништякиhttps://majestio.info
Цитата JoeUser @
Возможности конечно гораздо беднее того же PostgreSQL.

Зато SQLite может успешно работать и возвращать результат там, где PostgreSQL вообще не запустится.

Цитата JoeUser @
Ну у меня это вылилось в некоторую долю дополнительного кода

Код копеечный - тогда нормально.
Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
Есть претензии ко мне как к участнику? да ради бога.
Не нравятся мои ответы? не читайте их.
В общем, берегите себя. Нервные клетки не восстанавливаются.
1 пользователей читают эту тему (1 гостей и 0 скрытых пользователей)
0 пользователей:


Рейтинг@Mail.ru
[ Script Execution time: 0,0978 ]   [ 20 queries used ]   [ Generated: 10.07.20, 22:34 GMT ]