Версия для печати
Нажмите сюда для просмотра этой темы в оригинальном формате
Форум на Исходниках.RU > Базы данных: SQL > Оптимизировать запрос по месяцам


Автор: JoeUser 29.06.20, 05:03
Всем привет!

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

<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    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'))
    )


Запрос работает правильно. Но мне кажется, что он получился уж слишком громоздким.
Можно ли его переписать/оптимизировать?

Автор: JoeUser 29.06.20, 07:50
В общем, на SO, на примере убедили, что лучше вот такой запрос:

<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    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 29.06.20, 08:26
Где ж ты раньше был.

Автор: Akina 29.06.20, 09:32
Цитата JoeUser @
А потом просто пробежаться по записям и собрать в своей проге вектор из 12 элементов.

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

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

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

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

В смысле, я что-то сделал нетак? :)

Автор: JoeUser 29.06.20, 10:57
Цитата Akina @
Особенно если средство отображения в программе не умеет само пивотить и требует дополнительного кода.

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

<{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}>
    // ...
    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]);
    // ...

Я считаю, что оно того стоило!

Автор: Akina 29.06.20, 18:41
Цитата JoeUser @
Возможности конечно гораздо беднее того же PostgreSQL.

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

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

Код копеечный - тогда нормально.

Powered by Invision Power Board (https://www.invisionboard.com)
© Invision Power Services (https://www.invisionpower.com)