Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Запрос с суммированием по дням месяца / 24 сообщений из 24, страница 1 из 1
21.08.2018, 11:56
    #39690915
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
В таблице data имеются поля:
date_put datetime - дата-время путевки
mhrs double - кол-во моточасов
number varchar(10) - номер автотранспорта

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


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


Прошу помощи в составлении такого запроса, если он возможен.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.08.2018, 12:05
    #39690923
982183
982183
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
1. sum(mhrs)
2. GROUP BY dp, number

И останется только развернуть.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.08.2018, 12:36
    #39690950
Щукина Анна
Щукина Анна
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
LiYing,

"разворот" подручными средствами
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.08.2018, 13:35
    #39691027
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
Если правильно понял про "разворот", то должно быть так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
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 оказалась не нужна. Проверьте, плиз, правильный это запрос получился и нельзя ли упростить сию простыню?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.08.2018, 13:48
    #39691045
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
LiYingнельзя ли упростить сию простыню?Ну разве что ELSE NULL поубирать - оно и так при невыполнении NULL будет. Опять же можно попробовать уменьшить количество обращений к функции, "закэшировав" значение в переменной:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
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


Теоретически - в документации не описан порядок вычисления полей выходного набора, но на практике мне не приходилось сталкиваться с тем, что он не соответствует тексту запроса.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.08.2018, 13:58
    #39691061
982183
982183
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
Но правильно работать будет, если в один день на машину не больше одной путевки.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.08.2018, 14:10
    #39691076
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
Akina ,
Спасибо, с переменной работает также. Вопрос, зачем нужно
Код: sql
1.
(SELECT @day_num := 0) xxx

? Работает и без этой строки.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.08.2018, 14:11
    #39691078
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
982183Но правильно работать будет, если в один день на машину не больше одной путевки.
Упс... уточнил в гараже, есть нюансы при которых может быть НЕСКОЛЬКО путевок в один день! Как быть в этом случае?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.08.2018, 14:18
    #39691082
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
Проверил на тестовой БД с несколькими путевками в один день на один авто - ложная тревога, мой запрос-простыня работает, моточасы суммируются!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.08.2018, 15:16
    #39691139
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
LiYingРаботает и без этой строки.Запусти запрос подряд дважды в одном сеансе и сравни первые строки в выводе...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2018, 14:16
    #39729927
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
AkinaLiYingРаботает и без этой строки.Запусти запрос подряд дважды в одном сеансе и сравни первые строки в выводе...
Извиняюсь, за возвращение к поднятому вопросу - не дает покоя :))
Запускал дважды и даже несколько раз, возвращаемые наборы данных - идентичны.
Так все-таки, в чем смысл использования
Код: sql
1.
(SELECT @day_num := 0) xxx


??? Поясните, пожалуйста, если работает и без этой строки.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2018, 14:25
    #39729934
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
LiYingПоясните, пожалуйста, если работает и без этой строки.
Изучай.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
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>
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2018, 14:43
    #39729949
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
Akina, хм, у меня другой результат повтора ваших шагов:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
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>
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2018, 14:53
    #39729959
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
Но тем не менее, результаты двойного запроса в одной сессии моего рабочего запроса без строки
Код: sql
1.
(SELECT @day_num := 0) xxx

все равно одинаковы. Может что в самом запросе на это влияет?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2018, 15:35
    #39730002
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
LiYingдругой результатА версия MySQL - какая? И - выполняете через консольного клиента (mysql.exe) или через что-то ещё?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2018, 15:58
    #39730012
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
Akina,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
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...", который вызывает консоль.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2018, 16:19
    #39730024
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
LiYingИспользую Navicat Premium, в нем есть тул "Console...", который вызывает консоль.Такие тулзы как минимум шлют серверу дополнительные запросы, а порой ещё и творчески перерабатывают существующие...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2018, 16:37
    #39730037
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
Akina,

тоже так подумалось, потому пошел на сервер, запустил mysql.exe и ваш пример. Итог тот же:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
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>
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2018, 16:48
    #39730040
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
Ладно, хрен с ею, с разницей... в любом случае второй вариант запроса, с инициализацией в секции WHERE, всегда даст один и тот же результат. А вот без инициализации, как видим, результат не детерминирован.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.11.2018, 08:26
    #39730285
Близнец1980
Близнец1980
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
Akina,

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

при первом запуске:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
select id, @rn3:=coalesce(@rn3+1, 1) rownumber
     from test1
     order by id;

id	rownumber
1	1
10	1
100	1


все верно, ведь в этом случае
Код: sql
1.
coalesce(@rn3+1, 1) = coalesce(null+1, 1)

а потом уже @rn3=1 и повторные запуски его увеличивают, интересно почему у вас по другому.
Ну и в касаемо самого запроса:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
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


тут действительно
Код: sql
1.
(SELECT @day_num := 0) xxx

думаю не нужен, т.к. сразу же первой строкой идет присвоение
Код: sql
1.
@day_num := DAYOFMONTH(date_put)


и переменная сразу имеет значение и при каждом следующем запросе переприсваиваеся.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.11.2018, 08:59
    #39730294
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
Близнец1980все верно, ведь в этом случаеА вот теперь объясни, почему у меня то же самое на Server version: 8.0.12 MySQL Community Server - GPL даёт другой результат...
То есть я понимаю, что у меня сервер выполняет итерационную обработку, а у вас пакетную, но чем это определяется и как настраивается?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.11.2018, 09:03
    #39730296
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
Близнец1980касаемо самого запросаДа, согласен. Но...
Практика показывает, что вычисление выходного набора всегда выполняется строго по тексту... и пока отклонений от этого принципа не зарегистрировано (а если вдруг - то взвоет куча народу). Но увы, такое поведение не документировано...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.11.2018, 11:24
    #39730404
Близнец1980
Близнец1980
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
Поторопился что-то, ведь 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 даёт другой результат...

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

Может тогда лучше так запрос переписать:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.11.2018, 12:55
    #39730513
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с суммированием по дням месяца
Близнец1980Покопался в документации, нашел вот что:Во-о-т... я тоже всегда на это кивал... а то, что наблюдается у вас, этому противоречит.
Близнец1980coalesce(@rn3+1, 1) = coalesce(null+1, 1) только для первой строки, а потом переменная уже должна иметь значениеОптимизатор тоже считает это самое выражение, получает единицу, а потом, сцуко, считает это выражение константным!!!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Запрос с суммированием по дням месяца / 24 сообщений из 24, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 362ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1829497]:
 0ms
get settings:
 9ms
get forum list:
 12ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 34ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 54ms
get tp. blocked users:
 1ms
others: 229ms
total:  362ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]