Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Запрос на формирование суммы по полям с группировкой по дате из нескольких таблиц / 9 сообщений из 9, страница 1 из 1
16.10.2018, 11:21
    #39718005
ForMan
ForMan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на формирование суммы по полям с группировкой по дате из нескольких таблиц
Доброго времени суток всем форумчанам.

Я не очень силен в практике составления запросов.
Кроме как select* ftom test по сути ...

Помогите разобраться с такой задачей:
1) База данных MySQL (MariaDB)
1) Есть ряд таблиц (их около 50 шт) по учету статистических данных у которых есть ряд одинаковых полей
пример
Код: 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.
create table `1_1`(
  `id` VARCHAR(10),
  `date` DATETIME,
  `time` VARCHAR(8),
  `result` decimal(15,5));

create table `2_1`(
  `id` VARCHAR(10),
  `date` DATETIME,
  `time` VARCHAR(8),
  `result` decimal(15,5));

create table `2_2`(
  `id` VARCHAR(10),
  `date` DATETIME,
  `time` VARCHAR(8),
  `result` decimal(15,5));

-- inserting test data

insert `1_1` values('2018090108', '2018-09-01 00:00:00', '08:00:00', 100.5);
insert `1_1` values('2018090120', '2018-09-01 00:00:00', '20:00:00', 200.5);
insert `1_1` values('2018090208', '2018-09-02 00:00:00', '08:00:00', 300.5);
insert `1_1` values('2018090220', '2018-09-02 00:00:00', '20:00:00', 400.5);
insert `2_1` values('2018090108', '2018-09-01 00:00:00', '08:00:00', 0.5);
insert `2_1` values('2018090120', '2018-09-01 00:00:00', '20:00:00', 0.6);
insert `2_1` values('2018090208', '2018-09-02 00:00:00', '08:00:00', 0.7);
insert `2_1` values('2018090220', '2018-09-02 00:00:00', '20:00:00', 0.8);
insert `2_2` values('2018090108', '2018-09-01 00:00:00', '08:00:00', 1.5);
insert `2_2` values('2018090120', '2018-09-01 00:00:00', '20:00:00', 2.6);
insert `2_2` values('2018090208', '2018-09-02 00:00:00', '08:00:00', 3.7);
insert `2_2` values('2018090220', '2018-09-02 00:00:00', '20:00:00', 4.8);

select `date`, sum(result) from 1_1 where `date`>='2018-09-01' group by `date`;



Таким запросом я выберу сумму с группировкой по дате из таблицы 1_1
Код: sql
1.
2.
3.
4.
5.
6.
7.
-----------------------
|date       | a      |
-----------------------
|2018-09-01 | 301.00 |
-----------------------
|2018-09-02 | 701.00 |
-----------------------


а мне надо из n таблиц

как лучше поступить???

или городить такой огород:
Код: sql
1.
2.
3.
4.
5.
select `date`, sum(result) as `a` from 1_1 where `date`>='2018-09-01' group by `date`
union
select `date`, sum(result) as `b` from 2_1 where `date`>='2018-09-01' group by `date`
union
select `date`, sum(result) as `c` from 2_2 where `date`>='2018-09-01' group by `date`



Но так выдает результат странно:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
-----------------------
|date       | a      |
-----------------------
|2018-09-01 | 301.00 |
-----------------------
|2018-09-02 | 701.00 |
-----------------------
|2018-09-01 |   1.10 |
-----------------------
|2018-09-02 |   1.50 |
-----------------------
|2018-09-01 |   4.10 |
-----------------------
|2018-09-02 |   8.50 |
-----------------------



А хотелось бы в таком виде:
Код: sql
1.
2.
3.
4.
5.
6.
7.
---------------------------------------
|date       | a      | b      | c     |
---------------------------------------
|2018-09-01 | 301.00 |   1.10 |  4.10 |
---------------------------------------
|2018-09-02 | 701.00 |   1.50 |  8.50 |
---------------------------------------



Заранее благодарен.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.10.2018, 11:34
    #39718015
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на формирование суммы по полям с группировкой по дате из нескольких таблиц
В общем случае запрос будет таким:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
SELECT dates.date, COALESCE(t1.result1, 0) result1, COALESCE(t2.result2, 0) result2, ...
FROM ( SELECT date FROM table1
       UNION
       SELECT date FROM table2
       UNION
      ...
     ) dates
LEFT JOIN ( SELECT date, SUM(result) result1 
            FROM table1 
            GROUP BY date) t1 
    ON dates.date = t1.date
LEFT JOIN ( SELECT date, SUM(result) result2 
            FROM table2 
            GROUP BY date) t2
    ON dates.date = t2.date
LEFT JOIN ...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.10.2018, 11:47
    #39718026
ForMan
ForMan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на формирование суммы по полям с группировкой по дате из нескольких таблиц
Akina, СПАСИБО!

для моей задачи такой метод подходит

для приведенного примера работает
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
SELECT dates.date, COALESCE(t1.a, 0) a, COALESCE(t2.b, 0) b, COALESCE(t3.c, 0) c
FROM ( SELECT date FROM 1_1
       UNION
       SELECT date FROM 2_1
       UNION
       SELECT date FROM 2_2
     ) dates
LEFT JOIN ( SELECT date, SUM(result) a 
            FROM 1_1 
            GROUP BY date) t1 
    ON dates.date = t1.date
LEFT JOIN ( SELECT date, SUM(result) b 
            FROM 2_1 
            GROUP BY date) t2
    ON dates.date = t2.date
LEFT JOIN ( SELECT date, SUM(result) c 
            FROM 2_2 
            GROUP BY date) t3
    ON dates.date = t3.date



И выдает правильный результат.
Более того такой запрос легко масштабируется.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.10.2018, 12:45
    #39718068
ForMan
ForMan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на формирование суммы по полям с группировкой по дате из нескольких таблиц
Уважаемый Akina, и другие форумчане

Правильно ли будет получение общего результата в данном примере через такой запрос
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
SELECT Count(dates.date) count, Sum(COALESCE(t1.result1, 0)) a, Sum(COALESCE(t2.result2, 0)) b, Sum(COALESCE(t3.result3, 0)) c
FROM ( SELECT date FROM 1_1
       UNION
       SELECT date FROM 2_1
       UNION
				SELECT date FROM 2_2
     ) dates
LEFT JOIN ( SELECT date, SUM(result) result1 
            FROM 1_1 
            GROUP BY date) t1 
    ON dates.date = t1.date
LEFT JOIN ( SELECT date, SUM(result) result2 
            FROM 2_1 
            GROUP BY date) t2
    ON dates.date = t2.date
LEFT JOIN ( SELECT date, SUM(result) result3 
            FROM 2_2 
            GROUP BY date) t3
    ON dates.date = t3.date


Приведенный код рабочий. Но...
Может есть более простая (рациональная) конструкция.

Спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.10.2018, 13:00
    #39718087
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на формирование суммы по полям с группировкой по дате из нескольких таблиц
Поскольку нужна чисто суммарная информация, без группировок - вполне нормальный код. Ну если не считать только момента, что Sum(COALESCE(t1.result1, 0)) эквивалентен COALESCE(Sum(t1.result1, 0)) , что немножко уменьшит количество расчётов (Sum игнорирует NULL-ы).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.10.2018, 14:44
    #39718196
ForMan
ForMan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на формирование суммы по полям с группировкой по дате из нескольких таблиц
Спасибо,Akina.

Как я понял что можно оптимизировать таким образом:
Код: sql
1.
2.
3.
4.
SELECT COALESCE(count(t1.id), 0) count, COALESCE(Sum(t1.result1), 0) a, COALESCE(Sum(t2.result2), 0) b, COALESCE(Sum(t3.result3), 0) c
FROM ( SELECT id, result as result1 FROM 1_1 ) t1 
LEFT JOIN ( SELECT id, result as result2 FROM 2_1 ) t2 ON t1.id = t2.id
LEFT JOIN ( SELECT id, result as result3 FROM 2_2 ) t3 ON t1.id = t3.id
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.10.2018, 14:57
    #39718205
ForMan
ForMan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на формирование суммы по полям с группировкой по дате из нескольких таблиц
Ну и собственно вытекающая из этого всего следующая задача

Каким образом составить запрос на получение данных сумм с нарастающим итогом.
т.е. что бы было так
Код: sql
1.
2.
3.
4.
5.
6.
7.
---------------------------------------
|date       | a      | b      | c     |
---------------------------------------
|2018-09-01 | 301.00 |   1.10 |  4.10 |
---------------------------------------
|2018-09-02 | 1002.00|   2.60 |  12.60|
---------------------------------------



Спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.10.2018, 15:29
    #39718235
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на формирование суммы по полям с группировкой по дате из нескольких таблиц
ForManзапрос на получение данных сумм с нарастающим итогом.Проще всего это выполнить с помощью переменных.

Когда есть исходная таблица tab типа
id val1 val21111222122.........сумма с накоплением получается так:
Код: sql
1.
2.
3.
SELECT id, (@sum1:=@sum1+val1) sum_val1, (@sum2:=@sum2+val2) sum_val2
FROM tab, (SELECT @sum1:=0, @sum2:=0) dummy
ORDER BY id ASC
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.10.2018, 16:18
    #39718261
ForMan
ForMan
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос на формирование суммы по полям с группировкой по дате из нескольких таблиц
Akina, Полный Респект.

Это шикардос!

а если с убывающим итогом то это еще в 1 селект обернуть и ву-а-ля:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
select * from(
SELECT date, (@sum1:=@sum1+a) a, (@sum2:=@sum2+b) b, (@sum3:=@sum3+c) c
FROM (SELECT dates.date as date, COALESCE(t1.a, 0) a, COALESCE(t2.b, 0) b, COALESCE(t3.c, 0) c
FROM ( SELECT date FROM 1_1
       UNION
       SELECT date FROM 2_1
       UNION
       SELECT date FROM 2_2
     ) dates
LEFT JOIN ( SELECT date, SUM(result) a 
            FROM 1_1 
            GROUP BY date) t1 
    ON dates.date = t1.date
LEFT JOIN ( SELECT date, SUM(result) b 
            FROM 2_1 
            GROUP BY date) t2
    ON dates.date = t2.date
LEFT JOIN ( SELECT date, SUM(result) c 
            FROM 2_2 
            GROUP BY date) t3
    ON dates.date = t3.date) tab, (SELECT @sum1:=0, @sum2:=0, @sum3:=0) dummy
ORDER BY date asc) tal order by date desc



Результат:
Код: sql
1.
2.
3.
4.
5.
6.
7.
---------------------------------------
|date       | a      | b      | c     |
---------------------------------------
|2018-09-02 | 1002.00|   2.60 |  12.60|
---------------------------------------
|2018-09-01 | 301.00 |   1.10 |  4.10 |
---------------------------------------



Огромное человеческое спасибо!
P.S.: Классный форум. Супер скорость ответов.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Запрос на формирование суммы по полям с группировкой по дате из нескольких таблиц / 9 сообщений из 9, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 352ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1829540]:
 0ms
get settings:
 11ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 34ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 42ms
get tp. blocked users:
 1ms
others: 233ms
total:  352ms
x
x
Закрыть


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