ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Выбрать из 1й таблицы, но за разный период
25 сообщений из 32, страница 1 из 2
  1  все
Выбрать из 1й таблицы, но за разный период
    #39879385
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiYing
Гость
В таблице
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
CREATE TABLE data (
    id_contragent int(11) NOT NULL,
    period date,
    h_sum_bill double,
    h_sum_change double,
    h_sum_get double,
    KEY `fk_contr` (`id_contragent`),
    KEY `i_per` (`period`)
);


хранятся данные на 1-е число каждого месяца по каждому контрагенту. Нужно выбрать значения трех полей (h_sum_bill, h_sum_change, h_sum_get) за период в несколько месяцев таким образом:
h_sum_bill, h_sum_change взять из текущего месяца периода, а h_sum_get из следующего. Если след.месяца периода в БД нет, вернуть в h_sum_get NULL.
Подскажите, пожалуйста, какой запрос написать?
Пробовал JOIN и двойной подзапрос, но что-то не то выходит...
Создал фидл: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=04df9cb36642125d36979e6ed4c66a17
...
Рейтинг: 0 / 0
21.10.2019, 15:24
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39879436
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
А эталонный ответ-то где?
...
Рейтинг: 0 / 0
21.10.2019, 16:12
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39879441
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Посмотри
Код: sql
1.
2.
3.
4.
5.
SELECT d.h_sum_bill,d.h_sum_change,dn.h_sum_get
FROM data d
LEFT JOIN data dn 
    ON d.id_contragent=dn.id_contragent
    AND 12*(YEAR(dn.period) - YEAR(d.period)) + MONTH(dn.period) - MONTH(d.period) = 1
...
Рейтинг: 0 / 0
21.10.2019, 16:15
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39879452
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiYing
Гость
AkinaА эталонный ответ-то где?
Для тех данных, что в фидле и периода = period BETWEEN '2018-10-01' AND '2018-12-01' для id_contragent=1 должно быть:
h_sum_billh_sum_changeh_sum_get500033350192225020NULL
...
Рейтинг: 0 / 0
21.10.2019, 16:28
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39879454
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiYing
Гость
AkinaПосмотри

Не подходит. Может не ясно выразился, но надо по одному контрагенту и за заданный период, а тут по все и за все время.
...
Рейтинг: 0 / 0
21.10.2019, 16:33
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39879469
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Ну добавь WHERE по первой копии таблицы, какие сложности-то?
...
Рейтинг: 0 / 0
21.10.2019, 16:44
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39879481
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiYing
Гость
AkinaНу добавь WHERE по первой копии таблицы, какие сложности-то?
Никаких, это я ступил :)
Как обычно, спасибо за решение!

Такой explain получился, вроде норм?
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEdreffk_contr;i_perfk_contr5const1223.59Using where1SIMPLEdnreffk_contrfk_contr5const12100.00Using where
...
Рейтинг: 0 / 0
21.10.2019, 16:50
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39879493
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
LiYingТакой explain получился, вроде норм?Не очень. Лучше, конечно, избавиться от скана по правой таблице.
...
Рейтинг: 0 / 0
21.10.2019, 16:58
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39879495
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiYing
Гость
AkinaLiYingТакой explain получился, вроде норм?Не очень. Лучше, конечно, избавиться от скана по правой таблице.
Ммм... как?
...
Рейтинг: 0 / 0
21.10.2019, 17:00
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39879551
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Преобразовать
Код: sql
1.
AND 12*(YEAR(dn.period) - YEAR(d.period)) + MONTH(dn.period) - MONTH(d.period) = 1


в формат
Код: sql
1.
AND dn.period >= (выражение 1 от d.period) AND dn.period <= (выражение 2 от d.period)
...
Рейтинг: 0 / 0
21.10.2019, 19:07
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39879652
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiYing
Гость
Akina,

См. 3 запроса на примере - https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=7597c80a4980b895b31317e83cef6478
После такого преобразования (запрос №3) получается мой 1-й запрос только без BETWEEN, 2-й - правильный.
Или что подразумевалось под (выражение 1 от d.period) и (выражение 2 от d.period) ?
...
Рейтинг: 0 / 0
22.10.2019, 08:22
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39879721
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT d.period,d.h_sum_bill,d.h_sum_change,dn.h_sum_get
FROM data d
LEFT JOIN data dn 
    ON d.id_contragent=dn.id_contragent
    AND dn.period >= d.period + INTERVAL 1 MONTH
    AND dn.period < d.period + INTERVAL 2 MONTH
WHERE (d.id_contragent=1) AND (d.period BETWEEN '2018-10-01' AND '2018-12-01')


fiddle
Впрочем, чтобы увидеть разницу (если её вообще можно увидеть), нужно обрабатывать ну очень большой объём данных...
...
Рейтинг: 0 / 0
22.10.2019, 10:21
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39879750
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiYing
Гость
Проверил на реальной БД, explain получился одинаковый с предыдущим:
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEdreffk_contr;i_perfk_contr5const1223.59Using where1SIMPLEdnreffk_contr;i_perfk_contr5const12100.00Using where
Записей в таблице data = 167805 и каждый месяц величивается на 20+ тыс.
...
Рейтинг: 0 / 0
22.10.2019, 10:53
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39879897
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
LiYingexplain получился одинаковый с предыдущимУгу. Только расчёт упростится. Но это крохи по сравнению с доступом к исходным данным.
...
Рейтинг: 0 / 0
22.10.2019, 13:41
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39879904
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiYing
Гость
Akina,

...
Рейтинг: 0 / 0
22.10.2019, 13:52
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39944745
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiYing
Гость
Задача усложнилась и новый вопрос в продолжение темы...

Теперь идентичные данные из одного запроса
Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT d.period,d.h_sum_bill,d.h_sum_change,dn.h_sum_get
FROM data d
LEFT JOIN data dn 
    ON d.id_contragent=dn.id_contragent
    AND dn.period >= d.period + INTERVAL 1 MONTH
    AND dn.period < d.period + INTERVAL 2 MONTH
WHERE (d.id_contragent=1) AND (d.period BETWEEN '2018-10-01' AND '2018-12-01')


нужно просуммировать с данными из другого аналогичного (используется другая таблица)
Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT d2.period,d2.h_sum_bill,d2.h_sum_change,dn2.h_sum_get
FROM data_nu d2
LEFT JOIN data_nu dn2 
    ON d2.id_contragent=dn2.id_contragent
    AND dn2.period >= d2.period + INTERVAL 1 MONTH
    AND dn2.period < d2.period + INTERVAL 2 MONTH
WHERE (d2.id_contragent=1) AND (d2.period BETWEEN '2018-10-01' AND '2018-12-01')


Т.е. на выходе получить (d.h_sum_bill + d2.h_sum_bill), (d.h_sum_change + d2.h_sum_change), (dn.h_sum_get + dn2.h_sum_get) для одного period .
Параметры в WHERE запросов одинаковы. Но количество записей в этих двух выборках может отличаться, к примеру во 2-м запросе может отсутствовать d2.period соответствующий d.period. Возможные варианты:
1) есть данные в data и есть данные в data_nu
2) есть данные в data и нет данных в data_nu
3) нет данных в data и есть данные в data_nu

Собственно вопрос - можно ли объединить эти два запроса в один, чтобы получить желаемое? Или проще получать данные двумя запросами и уже программно суммировать соответствующие поля? Буду благодарен за помощь!
...
Рейтинг: 0 / 0
08.04.2020, 10:33
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39944833
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.
WITH 
t1 AS
(SELECT d.period,d.h_sum_bill,d.h_sum_change,dn.h_sum_get
FROM data d
LEFT JOIN data dn ON d.id_contragent=dn.id_contragent
    AND dn.period >= d.period + INTERVAL 1 MONTH
    AND dn.period < d.period + INTERVAL 2 MONTH 
WHERE (d.id_contragent=1) AND (d.period BETWEEN '2018-10-01' AND '2018-12-01')),

t2 AS
(SELECT d.period,d.h_sum_bill,d.h_sum_change,dn.h_sum_get
FROM data_nu d
LEFT JOIN data_nu dn ON d.id_contragent=dn.id_contragent
    AND dn.period >= d.period + INTERVAL 1 MONTH
    AND dn.period < d.period + INTERVAL 2 MONTH 
WHERE (d.id_contragent=1) AND (d.period BETWEEN '2018-10-01' AND '2018-12-01'))

SELECT t1.period,t2.period,
(COALESCE(t1.h_sum_bill, 0)+COALESCE(t2.h_sum_bill, 0)),
(COALESCE(t1.h_sum_change, 0)+COALESCE(t2.h_sum_change, 0)),
(COALESCE(t1.h_sum_get, 0)+COALESCE(t2.h_sum_get, 0))
FROM t1
LEFT JOIN t2 ON t1.period=t2.period


но проблема в том, что если нет данных в таблице t1 , то в результате имеем пустую выборку. Или пропуск строки из t2 , если соответствущий ему период t1.period = NULL.
Как это победить?
И попутно, что правильней использовать: COALESCE или IFNULL при суммировании?
...
Рейтинг: 0 / 0
08.04.2020, 13:13
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39944842
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
LiYing
Возможные варианты:
1) есть данные в data и есть данные в data_nu
2) есть данные в data и нет данных в data_nu
3) нет данных в data и есть данные в data_nu

Собственно вопрос - можно ли объединить эти два запроса в один, чтобы получить желаемое?

Можно. Но придётся получать полный список периодов отдельным подзапросом - MySQL не поддерживает FULL JOIN.
LiYing
проблема в том, что

Код: sql
1.
2.
3.
cte3 AS ( SELECT cte1.period
          UNION 
          SELECT cte2.period )


LiYing
что правильней использовать: COALESCE или IFNULL

Правильно - забыть про существование IFNULL навсегда.
...
Рейтинг: 0 / 0
08.04.2020, 13:33
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39944865
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.
WITH 
t1 AS
(SELECT d.period,d.h_sum_bill,d.h_sum_change,dn.h_sum_get
FROM data d
LEFT JOIN data dn ON d.id_contragent=dn.id_contragent
    AND dn.period >= d.period + INTERVAL 1 MONTH
    AND dn.period < d.period + INTERVAL 2 MONTH 
WHERE (d.id_contragent=1) AND (d.period BETWEEN '2018-10-01' AND '2018-12-01')),

t2 AS
(SELECT d.period,d.h_sum_bill,d.h_sum_change,dn.h_sum_get
FROM data_nu d
LEFT JOIN data_nu dn ON d.id_contragent=dn.id_contragent
    AND dn.period >= d.period + INTERVAL 1 MONTH
    AND dn.period < d.period + INTERVAL 2 MONTH 
WHERE (d.id_contragent=1) AND (d.period BETWEEN '2018-10-01' AND '2018-12-01')),

t3 AS 
(SELECT t1.period
UNION 
SELECT t2.period)
					
SELECT period
FROM t3


получаю ошибку > 1109 - Unknown table 't1' in field list
> Time: 0,002s
без указания в какой строке. Где косячу?
...
Рейтинг: 0 / 0
08.04.2020, 14:24
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39944941
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
LiYing
без указания в какой строке.
LiYing
Код: sql
1.
SELECT t1.period

t1 в списке полей употребляется в единственной строке
...
Рейтинг: 0 / 0
08.04.2020, 17:03
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39944950
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiYing
Гость
del
...
Рейтинг: 0 / 0
08.04.2020, 17:22
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39944951
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
LiYing
Почему t1 неизвестна в t3?
Потому что SELECT без FROM
...
Рейтинг: 0 / 0
08.04.2020, 17:22
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39944954
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiYing
Гость
miksoft,

как всегда самое очевидное оказалось пропущено из внимания... уже когда нажал кнопку опубликовать пост понял свой косяк :)
...
Рейтинг: 0 / 0
08.04.2020, 17:25
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39945219
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.
WITH 
t1 AS
(SELECT d.period,d.h_sum_bill,d.h_sum_change,dn.h_sum_get
FROM data d
LEFT JOIN data dn ON d.id_contragent=dn.id_contragent
    AND dn.period >= d.period + INTERVAL 1 MONTH
    AND dn.period < d.period + INTERVAL 2 MONTH 
WHERE (d.id_contragent=1) AND (d.period BETWEEN '2018-10-01' AND '2018-12-01')),

t2 AS
(SELECT d.period,d.h_sum_bill,d.h_sum_change,dn.h_sum_get
FROM data_nu d
LEFT JOIN data_nu dn ON d.id_contragent=dn.id_contragent
    AND dn.period >= d.period + INTERVAL 1 MONTH
    AND dn.period < d.period + INTERVAL 2 MONTH 
WHERE (d.id_contragent=1) AND (d.period BETWEEN '2018-10-01' AND '2018-12-01')),

t3 AS 
(SELECT period FROM t1
UNION 
SELECT period FROM t2)

SELECT t3.period,
(COALESCE(t1.h_sum_bill, 0)+COALESCE(t2.h_sum_bill, 0)),
(COALESCE(t1.h_sum_change, 0)+COALESCE(t2.h_sum_change, 0)),
(COALESCE(t1.h_sum_get, 0)+COALESCE(t2.h_sum_get, 0))
FROM t3
LEFT JOIN t1 ON t1.period=t3.period 
LEFT JOIN t2 ON t2.period=t3.period
...
Рейтинг: 0 / 0
09.04.2020, 11:43
| Ответить | Цитировать | Написать  
Выбрать из 1й таблицы, но за разный период
    #39945231
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
LiYing
Код: sql
1.
(COALESCE(t1.h_sum_bill, 0)+COALESCE(t2.h_sum_bill, 0))

Это можно записать еще вот так:
Код: sql
1.
COALESCE(t1.h_sum_bill + t2.h_sum_bill, t1.h_sum_bill, t2.h_sum_bill, 0)


Возможно, будет чуть быстрее.
...
Рейтинг: 0 / 0
09.04.2020, 12:10
| Ответить | Цитировать | Написать  
25 сообщений из 32, страница 1 из 2
  1  все
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Выбрать из 1й таблицы, но за разный период
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=39879454&tid=1828645]:
 0ms
get settings:
 10ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 135ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 53ms
get tp. blocked users:
 1ms
others: 302ms
total:  538ms
созд. / загр.: 538ms
x
x
Закрыть


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