На главную Наши проекты:
Журнал   ·   Discuz!ML   ·   Wiki   ·   DRKB   ·   Помощь проекту
ПРАВИЛА 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'))
      )


    Запрос работает правильно. Но мне кажется, что он получился уж слишком громоздким.
    Можно ли его переписать/оптимизировать?
      В общем, на 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 элементов.
      Получается и красивше, и пожалуй, шустрее.
        Где ж ты раньше был.
        Сообщение отредактировано: Gonarh -
          Цитата JoeUser @
          А потом просто пробежаться по записям и собрать в своей проге вектор из 12 элементов.

          Ну формально это можно и в запросе сделать - если затолкать твой запрос в CTE. Особенно если средство отображения в программе не умеет само пивотить и требует дополнительного кода.
            Цитата Akina @
            Ну формально это можно и в запросе сделать - если затолкать твой запрос в CTE. Особенно если средство отображения в программе не умеет само пивотить и требует дополнительного кода.

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

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

            В смысле, я что-то сделал нетак? :)
              Цитата 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]);
                // ...

              Я считаю, что оно того стоило!
                Цитата JoeUser @
                Возможности конечно гораздо беднее того же PostgreSQL.

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

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

                Код копеечный - тогда нормально.
                0 пользователей читают эту тему (0 гостей и 0 скрытых пользователей)
                0 пользователей:


                Рейтинг@Mail.ru
                [ Script execution time: 0,0467 ]   [ 15 queries used ]   [ Generated: 28.03.24, 18:41 GMT ]