Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / MySQL Новый топик    Ответить
 Запрос с суммированием по дням месяца  [new]
LiYing
Member

Откуда:
Сообщений: 480
В таблице data имеются поля:
date_put datetime - дата-время путевки
mhrs double - кол-во моточасов
number varchar(10) - номер автотранспорта

Нужно просуммировать кол-во моточасов по каждому порядковому дню месяца выбранного периода времени по каждому автотранспорту. Причем период может быть не только с начала по конец одного месяца, но и захватывать другой месяц(ы); в один день может быть только одна путевка. Должно получиться что-то типа этого:
Картинка с другого сайта.

Начал писать запрос и понял, что тупо завис на суммировании по дням месяца...
SELECT DAYOFMONTH(date_put) dp, mhrs, number 
FROM data 
WHERE date_put BETWEEN :ds AND :de
GROUP BY dp
ORDER BY number

Прошу помощи в составлении такого запроса, если он возможен.
21 авг 18, 11:56    [21649230]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием по дням месяца  [new]
982183
Member

Откуда: VL
Сообщений: 2428
1. sum(mhrs)
2. GROUP BY dp, number

И останется только развернуть.
21 авг 18, 12:05    [21649251]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием по дням месяца  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1123
LiYing,

"разворот" подручными средствами
21 авг 18, 12:36    [21649300]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием по дням месяца  [new]
LiYing
Member

Откуда:
Сообщений: 480
Если правильно понял про "разворот", то должно быть так:
SELECT number,
       SUM(case when DAYOFMONTH(date_put)=1 then mhrs else null end) d1,
       SUM(case when DAYOFMONTH(date_put)=2 then mhrs else null end) d2,
       SUM(case when DAYOFMONTH(date_put)=3 then mhrs else null end) d3,
       SUM(case when DAYOFMONTH(date_put)=4 then mhrs else null end) d4,
       SUM(case when DAYOFMONTH(date_put)=5 then mhrs else null end) d5,
       SUM(case when DAYOFMONTH(date_put)=6 then mhrs else null end) d6,
       SUM(case when DAYOFMONTH(date_put)=7 then mhrs else null end) d7,
       SUM(case when DAYOFMONTH(date_put)=8 then mhrs else null end) d8,
       SUM(case when DAYOFMONTH(date_put)=9 then mhrs else null end) d9,
       SUM(case when DAYOFMONTH(date_put)=10 then mhrs else null end) d10,
       SUM(case when DAYOFMONTH(date_put)=11 then mhrs else null end) d11,
       SUM(case when DAYOFMONTH(date_put)=12 then mhrs else null end) d12,
       SUM(case when DAYOFMONTH(date_put)=13 then mhrs else null end) d13,
       SUM(case when DAYOFMONTH(date_put)=14 then mhrs else null end) d14,
       SUM(case when DAYOFMONTH(date_put)=15 then mhrs else null end) d15,
       SUM(case when DAYOFMONTH(date_put)=16 then mhrs else null end) d16,
       SUM(case when DAYOFMONTH(date_put)=17 then mhrs else null end) d17,
       SUM(case when DAYOFMONTH(date_put)=18 then mhrs else null end) d18,
       SUM(case when DAYOFMONTH(date_put)=19 then mhrs else null end) d19,
       SUM(case when DAYOFMONTH(date_put)=20 then mhrs else null end) d20,
       SUM(case when DAYOFMONTH(date_put)=21 then mhrs else null end) d21,
       SUM(case when DAYOFMONTH(date_put)=22 then mhrs else null end) d22,
       SUM(case when DAYOFMONTH(date_put)=23 then mhrs else null end) d23,
       SUM(case when DAYOFMONTH(date_put)=24 then mhrs else null end) d24,
       SUM(case when DAYOFMONTH(date_put)=25 then mhrs else null end) d25,
       SUM(case when DAYOFMONTH(date_put)=26 then mhrs else null end) d26,
       SUM(case when DAYOFMONTH(date_put)=27 then mhrs else null end) d27,
       SUM(case when DAYOFMONTH(date_put)=28 then mhrs else null end) d28,
       SUM(case when DAYOFMONTH(date_put)=29 then mhrs else null end) d29,
       SUM(case when DAYOFMONTH(date_put)=30 then mhrs else null end) d30,
       SUM(case when DAYOFMONTH(date_put)=31 then mhrs else null end) d31
FROM data 
WHERE date_put BETWEEN '2018-08-01 00:00:00' AND '2018-08-15 23:59:59' 
GROUP BY number

По-крайней мере, это считает и вроде даже то, что надо :)
И группировка по dp оказалась не нужна. Проверьте, плиз, правильный это запрос получился и нельзя ли упростить сию простыню?
21 авг 18, 13:35    [21649446]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием по дням месяца  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 18115
LiYing
нельзя ли упростить сию простыню?
Ну разве что ELSE NULL поубирать - оно и так при невыполнении NULL будет. Опять же можно попробовать уменьшить количество обращений к функции, "закэшировав" значение в переменной:
SELECT number,
       SUM(case when (@day_num := DAYOFMONTH(date_put))=1 then mhrs end) d1,
       SUM(case when @day_num=2 then mhrs end) d2,
       SUM(case when @day_num=3 then mhrs end) d3,
-- ...
       SUM(case when @day_num=31 then mhrs end) d31
FROM data, (SELECT @day_num := 0) xxx 
WHERE date_put BETWEEN '2018-08-01 00:00:00' AND '2018-08-15 23:59:59' 
GROUP BY number

Теоретически - в документации не описан порядок вычисления полей выходного набора, но на практике мне не приходилось сталкиваться с тем, что он не соответствует тексту запроса.
21 авг 18, 13:48    [21649487]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием по дням месяца  [new]
982183
Member

Откуда: VL
Сообщений: 2428
Но правильно работать будет, если в один день на машину не больше одной путевки.
21 авг 18, 13:58    [21649512]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием по дням месяца  [new]
LiYing
Member

Откуда:
Сообщений: 480
Akina,
Спасибо, с переменной работает также. Вопрос, зачем нужно
(SELECT @day_num := 0) xxx
? Работает и без этой строки.
21 авг 18, 14:10    [21649533]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием по дням месяца  [new]
LiYing
Member

Откуда:
Сообщений: 480
982183
Но правильно работать будет, если в один день на машину не больше одной путевки.

Упс... уточнил в гараже, есть нюансы при которых может быть НЕСКОЛЬКО путевок в один день! Как быть в этом случае?
21 авг 18, 14:11    [21649535]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием по дням месяца  [new]
LiYing
Member

Откуда:
Сообщений: 480
Проверил на тестовой БД с несколькими путевками в один день на один авто - ложная тревога, мой запрос-простыня работает, моточасы суммируются!
21 авг 18, 14:18    [21649546]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием по дням месяца  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 18115
LiYing
Работает и без этой строки.
Запусти запрос подряд дважды в одном сеансе и сравни первые строки в выводе...
21 авг 18, 15:16    [21649646]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием по дням месяца  [new]
LiYing
Member

Откуда:
Сообщений: 480
Akina
LiYing
Работает и без этой строки.
Запусти запрос подряд дважды в одном сеансе и сравни первые строки в выводе...

Извиняюсь, за возвращение к поднятому вопросу - не дает покоя :))
Запускал дважды и даже несколько раз, возвращаемые наборы данных - идентичны.
Так все-таки, в чем смысл использования
(SELECT @day_num := 0) xxx

??? Поясните, пожалуйста, если работает и без этой строки.
8 ноя 18, 14:16    [21728153]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием по дням месяца  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 18115
LiYing
Поясните, пожалуйста, если работает и без этой строки.

Изучай.
mysql> create table test(id int);
Query OK, 0 rows affected (1.63 sec)

mysql> insert into test values (1),(100),(10);
Query OK, 3 rows affected (0.22 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select id, @rn:=coalesce(@rn+1, 1) rownumber
    -> from test
    -> order by id;
+------+-----------+
| id   | rownumber |
+------+-----------+
|    1 |         1 |
|   10 |         2 |
|  100 |         3 |
+------+-----------+
3 rows in set (0.00 sec)

mysql> select id, @rn:=coalesce(@rn+1, 1) rownumber
    -> from test
    -> order by id;
+------+-----------+
| id   | rownumber |
+------+-----------+
|    1 |         4 |
|   10 |         5 |
|  100 |         6 |
+------+-----------+
3 rows in set (0.00 sec)

mysql> select id, @rn:=coalesce(@rn+1, 1) rownumber
    -> from test, (select @rn:=0) init
    -> order by id;
+------+-----------+
| id   | rownumber |
+------+-----------+
|    1 |         1 |
|   10 |         2 |
|  100 |         3 |
+------+-----------+
3 rows in set (0.04 sec)

mysql>
8 ноя 18, 14:25    [21728166]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием по дням месяца  [new]
LiYing
Member

Откуда:
Сообщений: 480
Akina, хм, у меня другой результат повтора ваших шагов:

mysql> create table test(id int);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into test values (1),(100),(10);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select id, @rn:=coalesce(@rn+1, 1) rownumber
from test
order by id;
+-----+-----------+
| id  | rownumber |
+-----+-----------+
|   1 |         1 |
|  10 |         1 |
| 100 |         1 |
+-----+-----------+
3 rows in set (0.01 sec)

mysql> select id, @rn:=coalesce(@rn+1, 1) rownumber
from test
order by id;
+-----+-----------+
| id  | rownumber |
+-----+-----------+
|   1 |         2 |
|  10 |         3 |
| 100 |         4 |
+-----+-----------+
3 rows in set (0.01 sec)

mysql> select id, @rn:=coalesce(@rn+1, 1) rownumber
from test, (select @rn:=0) init
order by id;
+-----+-----------+
| id  | rownumber |
+-----+-----------+
|   1 |         1 |
|  10 |         2 |
| 100 |         3 |
+-----+-----------+
3 rows in set (0.01 sec)

mysql> 
8 ноя 18, 14:43    [21728204]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием по дням месяца  [new]
LiYing
Member

Откуда:
Сообщений: 480
Но тем не менее, результаты двойного запроса в одной сессии моего рабочего запроса без строки
(SELECT @day_num := 0) xxx
все равно одинаковы. Может что в самом запросе на это влияет?
8 ноя 18, 14:53    [21728232]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием по дням месяца  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 18115
LiYing
другой результат
А версия MySQL - какая? И - выполняете через консольного клиента (mysql.exe) или через что-то ещё?
8 ноя 18, 15:35    [21728318]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием по дням месяца  [new]
LiYing
Member

Откуда:
Сообщений: 480
Akina,

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.18                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.18-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+


Использую Navicat Premium, в нем есть тул "Console...", который вызывает консоль.
8 ноя 18, 15:58    [21728345]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием по дням месяца  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 18115
LiYing
Использую Navicat Premium, в нем есть тул "Console...", который вызывает консоль.
Такие тулзы как минимум шлют серверу дополнительные запросы, а порой ещё и творчески перерабатывают существующие...
8 ноя 18, 16:19    [21728367]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием по дням месяца  [new]
LiYing
Member

Откуда:
Сообщений: 480
Akina,

тоже так подумалось, потому пошел на сервер, запустил mysql.exe и ваш пример. Итог тот же:
mysql> create table test(id int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test values (1),(100),(10);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select id, @rn:=coalesce(@rn+1, 1) rownumber
    -> from test
    -> order by id;
+------+-----------+
| id   | rownumber |
+------+-----------+
|    1 |         1 |
|   10 |         1 |
|  100 |         1 |
+------+-----------+
3 rows in set (0.00 sec)

mysql> select id, @rn:=coalesce(@rn+1, 1) rownumber
    -> from test
    -> order by id;
+------+-----------+
| id   | rownumber |
+------+-----------+
|    1 |         2 |
|   10 |         3 |
|  100 |         4 |
+------+-----------+
3 rows in set (0.00 sec)

mysql> select id, @rn:=coalesce(@rn+1, 1) rownumber
    -> from test, (select @rn:=0) init
    -> order by id
    -> ;
+------+-----------+
| id   | rownumber |
+------+-----------+
|    1 |         1 |
|   10 |         2 |
|  100 |         3 |
+------+-----------+
3 rows in set (0.00 sec)

mysql>
8 ноя 18, 16:37    [21728392]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием по дням месяца  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 18115
Ладно, хрен с ею, с разницей... в любом случае второй вариант запроса, с инициализацией в секции WHERE, всегда даст один и тот же результат. А вот без инициализации, как видим, результат не детерминирован.
8 ноя 18, 16:48    [21728408]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием по дням месяца  [new]
Близнец1980
Member

Откуда:
Сообщений: 65
Akina,

Для интереса решил у себя проверить, сервер 5.6.13

при первом запуске:
select id, @rn3:=coalesce(@rn3+1, 1) rownumber
     from test1
     order by id;

id	rownumber
1	1
10	1
100	1

все верно, ведь в этом случае
coalesce(@rn3+1, 1) = coalesce(null+1, 1)
а потом уже @rn3=1 и повторные запуски его увеличивают, интересно почему у вас по другому.
Ну и в касаемо самого запроса:
SELECT number,
       SUM(case when (@day_num := DAYOFMONTH(date_put))=1 then mhrs end) d1,
       SUM(case when @day_num=2 then mhrs end) d2,
       SUM(case when @day_num=3 then mhrs end) d3,
-- ...
       SUM(case when @day_num=31 then mhrs end) d31
FROM data, (SELECT @day_num := 0) xxx 
WHERE date_put BETWEEN '2018-08-01 00:00:00' AND '2018-08-15 23:59:59' 
GROUP BY number

тут действительно
(SELECT @day_num := 0) xxx
думаю не нужен, т.к. сразу же первой строкой идет присвоение
@day_num := DAYOFMONTH(date_put)

и переменная сразу имеет значение и при каждом следующем запросе переприсваиваеся.
9 ноя 18, 08:26    [21728987]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием по дням месяца  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 18115
Близнец1980
все верно, ведь в этом случае
А вот теперь объясни, почему у меня то же самое на Server version: 8.0.12 MySQL Community Server - GPL даёт другой результат...
То есть я понимаю, что у меня сервер выполняет итерационную обработку, а у вас пакетную, но чем это определяется и как настраивается?
9 ноя 18, 08:59    [21728997]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием по дням месяца  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 18115
Близнец1980
касаемо самого запроса
Да, согласен. Но...
Практика показывает, что вычисление выходного набора всегда выполняется строго по тексту... и пока отклонений от этого принципа не зарегистрировано (а если вдруг - то взвоет куча народу). Но увы, такое поведение не документировано...
9 ноя 18, 09:03    [21728999]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием по дням месяца  [new]
Близнец1980
Member

Откуда:
Сообщений: 65
Поторопился что-то, ведь coalesce(@rn3+1, 1) = coalesce(null+1, 1) только для первой строки, а потом переменная уже должна иметь значение.

Покопался в документации, нашел вот что:


"The order of evaluation for expressions involving user variables is undefined. For example, there is no guarantee that SELECT @a, @a:=@a+1 evaluates @a first and then performs the assignment.

In addition, the default result type of a variable is based on its type at the beginning of the statement. This may have unintended effects if a variable holds a value of one type at the beginning of a statement in which it is also assigned a new value of a different type."

https://dev.mysql.com/doc/refman/8.0/en/user-variables.html

с локальными переменными вообще надо быть аккуратней.

Akina
А вот теперь объясни, почему у меня то же самое на Server version: 8.0.12 MySQL Community Server - GPL даёт другой результат...

х.з. видать звезды так сошлись :) (видать логику поменяли при обращении к неинициализованной переменной до начала инструкции).

Может тогда лучше так запрос переписать:
SELECT number, 
       SUM(case when dm=1 then mhrs end) d1,
       SUM(case when dm=2 then mhrs end) d2,
       SUM(case when dm=3 then mhrs end) d3,
       SUM(case when dm=4 then mhrs end) d4,
       SUM(case when dm=5 then mhrs end) d5,
       SUM(case when dm=6 then mhrs end) d6,
       SUM(case when dm=7 then mhrs end) d7,
       SUM(case when dm=8 then mhrs end) d8,
       SUM(case when dm=9 then mhrs end) d9,
---
       SUM(case when dm=31 then mhrs end) d31
  FROM(
  SELECT number, DAYOFMONTH(date_put) AS dm, mhrs
FROM data
    WHERE date_put  BETWEEN '2018-08-01 00:00:00' AND '2018-08-15 23:59:59' 
) AS tmp
    GROUP BY number
9 ноя 18, 11:24    [21729160]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с суммированием по дням месяца  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 18115
Близнец1980
Покопался в документации, нашел вот что:
Во-о-т... я тоже всегда на это кивал... а то, что наблюдается у вас, этому противоречит.
Близнец1980
coalesce(@rn3+1, 1) = coalesce(null+1, 1) только для первой строки, а потом переменная уже должна иметь значение
Оптимизатор тоже считает это самое выражение, получает единицу, а потом, сцуко, считает это выражение константным!!!
9 ноя 18, 12:55    [21729331]     Ответить | Цитировать Сообщить модератору
Все форумы / MySQL Ответить