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

Откуда:
Сообщений: 579
Имеется такой запрос (упрощенный для примера):
SELECT
  c.id,
  d.h_sum_debt+d.hw_sum_debt dhhw,
  n.date_from ndf,n.debt_total,
  cl.date_from cldf,cl.debt_sum_h+cl.debt_sum_hw cldebt,
  t.date_from tdf,t.debt_sum_h+t.debt_sum_hw tdebt
FROM data_sgrc d 
INNER JOIN contragents c ON d.id_contragent=c.id 
INNER JOIN mkd m ON c.id_mkd=m.id 
LEFT JOIN notifications n ON c.id=n.id_contragent AND n.period<=d.period
LEFT JOIN claims cl ON c.id=cl.id_contragent AND cl.period<=d.period
LEFT JOIN test t ON c.id=t.id_contragent AND t.period<=d.period
WHERE m.id=152 AND d.period='2019-12-01'
ORDER BY c.id

Задача: выбрать по каждому контрагенту (c.id) в доме (mkd) некоторую информацию из data_sgrc, присовокупив к выборке данные еще из нескольких таблиц по контрагенту. Все работает хорошо до тех пор, пока в таблице test не появилось несколько записей на одного контрагента и их все надо выбрать. Получается вот так:
Картинка с другого сайта.

Т.е. дублирование данных на каждую добавленную запись из test. Как можно этого избежать? Т.е. чтобы вместо данных в красной рамке было Null.
Дополнительно: в таблице notifications всегда только одна запись на контрагента, но в claims также может быть несколько записей на одного контрагента (в примере - одна).
15 янв 20, 11:43    [22059568]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
crutchmaster
Member

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

Сгруппировать данные в test у тебя будет одна запись на 1 c.id

А вообще всё плохо, я только запрос увидел и уже захотелось выпить. Присовокупляй данные как-нибудь отдельно, а не всё в одной куче. Раньше я делал такое с помошью inmemory таблицы и update, а сейчас делаю на промежуточном слое. Волосы стали мягкими и шелковистыми, бросил пить, вернулась жена.
15 янв 20, 11:57    [22059585]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
LiYing
Member

Откуда:
Сообщений: 579
crutchmaster
Сгруппировать данные в test у тебя будет одна запись на 1 c.id

Нужно как раз 4 записи из test (на c.id=9884) и столько, сколько есть из notifications, claims.
15 янв 20, 13:08    [22059678]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
LiYing
Member

Откуда:
Сообщений: 579
Если дублирующиеся данные из первых двух столбцов нельзя за-null-ить, то может это можно сделать хотя бы для дубликатов из notifications, claims?
Чтобы стало так:
Картинка с другого сайта.
15 янв 20, 15:31    [22059867]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19999
Версию MySQL принципиально не указываем? а зря - на ЭТОЙ задаче версия не просто важна, а критична.
А так - да, решаемо.
15 янв 20, 15:34    [22059871]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
LiYing
Member

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

Извиняюсь, упустил сей момент. Версия 8.0.13
15 янв 20, 15:41    [22059877]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19999
Если точно так же упрощённо - то

SELECT c.id,
       CASE WHEN @id != c.id THEN d.fields END,
       CASE WHEN @id != c.id THEN c.fields END,
       CASE WHEN @id != c.id THEN m.fields END,
       CASE WHEN @id != c.id THEN n.fields END,
       CASE WHEN @id != c.id THEN cl.fields END,
       CASE WHEN @id := c.id THEN t.fields END
FROM data_sgrc d 
INNER JOIN contragents c ON d.id_contragent=c.id 
INNER JOIN mkd m ON c.id_mkd=m.id 
LEFT JOIN notifications n ON c.id=n.id_contragent AND n.period<=d.period
LEFT JOIN claims cl ON c.id=cl.id_contragent AND cl.period<=d.period
LEFT JOIN test t ON c.id=t.id_contragent AND t.period<=d.period
WHERE m.id=152 
  AND d.period='2019-12-01'
  AND (@id := -1)
ORDER BY c.id

c.id я всё же оставил. И думаю, что его не надо удалять хотя бы для контроля.

Сообщение было отредактировано: 15 янв 20, 15:49
15 янв 20, 15:45    [22059883]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19999
LiYing
Версия 8.0.13


Тогда можно не использовать переменные:

SELECT c.id,
       CASE WHEN c.id = LAG(c.id) OVER () THEN NULL ELSE d.fields END,
       CASE WHEN c.id = LAG(c.id) OVER () THEN NULL ELSE c.fields END,
       CASE WHEN c.id = LAG(c.id) OVER () THEN NULL ELSE m.fields END,
       CASE WHEN c.id = LAG(c.id) OVER () THEN NULL ELSE n.fields END,
       CASE WHEN c.id = LAG(c.id) OVER () THEN NULL ELSE cl.fields END,
       t.fields
FROM data_sgrc d 
INNER JOIN contragents c ON d.id_contragent=c.id 
INNER JOIN mkd m ON c.id_mkd=m.id 
LEFT JOIN notifications n ON c.id=n.id_contragent AND n.period<=d.period
LEFT JOIN claims cl ON c.id=cl.id_contragent AND cl.period<=d.period
LEFT JOIN test t ON c.id=t.id_contragent AND t.period<=d.period
WHERE m.id=152 
  AND d.period='2019-12-01'
ORDER BY c.id
15 янв 20, 15:58    [22059901]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
LiYing
Member

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

Оба варианта работают как хотелось, спасибо!
Записал так пример:
SELECT c.id,
       CASE WHEN c.id = LAG(c.id) OVER () THEN NULL ELSE d.h_sum_debt+d.hw_sum_debt END dhhw,
       CASE WHEN c.id = LAG(c.id) OVER () THEN NULL ELSE n.date_from END ndf,
       CASE WHEN c.id = LAG(c.id) OVER () THEN NULL ELSE n.debt_total END debt_total,
       CASE WHEN c.id = LAG(c.id) OVER () THEN NULL ELSE cl.date_from END cldf,
       CASE WHEN c.id = LAG(c.id) OVER () THEN NULL ELSE cl.debt_sum_h+cl.debt_sum_hw END cldebt,
       t.date_from tdf,t.debt_sum_h+t.debt_sum_hw tdebt
FROM data_sgrc d 
INNER JOIN contragents c ON d.id_contragent=c.id 
INNER JOIN mkd m ON c.id_mkd=m.id 
LEFT JOIN notifications n ON c.id=n.id_contragent AND n.period<=d.period
LEFT JOIN claims cl ON c.id=cl.id_contragent AND cl.period<=d.period
LEFT JOIN test t ON c.id=t.id_contragent AND t.period<=d.period
WHERE m.id=152 
  AND d.period='2019-12-01'
ORDER BY c.id


Я правильно понимаю, что если в claims будет несколько записей на одного контрагента, а в test одна или ни одной, то и
t.date_from tdf,t.debt_sum_h+t.debt_sum_hw tdebt
в селекте нужно обернуть в CASE?
15 янв 20, 16:13    [22059926]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19999
В CASE НЕ оборачиваются все выражения, в которых присутствует хотя бы одно поле из таблицы, в которой есть дубликаты. Все остальные - в выражениях которых есть только поля из таблиц с гарантированно одной записью,- оборачиваются в CASE, за исключением одного идентифицирующего поля.
16 янв 20, 07:45    [22060354]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
LiYing
Member

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

Понял, спасибо!
16 янв 20, 08:29    [22060364]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
LiYing
Member

Откуда:
Сообщений: 579
Что-то не так получается... Добавил в запрос тестовую таблицу testx, теперь на одного контрагента c.id=9884 приходится 1 запись из notifications, 3 записи из testx и 4 записи из test:
SELECT c.id,
       CASE WHEN c.id = LAG(c.id) OVER () THEN NULL ELSE d.h_sum_debt+d.hw_sum_debt END dhhw,
       CASE WHEN c.id = LAG(c.id) OVER () THEN NULL ELSE n.date_from END n_df,
       CASE WHEN c.id = LAG(c.id) OVER () THEN NULL ELSE n.debt_total END n_debt,
       x.date_from x_df,x.debt_sum_h+x.debt_sum_hw x_debt,
       t.date_from t_df,t.debt_sum_h+t.debt_sum_hw t_debt
FROM data_sgrc d 
INNER JOIN contragents c ON d.id_contragent=c.id 
INNER JOIN mkd m ON c.id_mkd=m.id 
LEFT JOIN notifications n ON c.id=n.id_contragent AND n.period<=d.period
LEFT JOIN testx x ON c.id=x.id_contragent AND x.period<=d.period
LEFT JOIN test t ON c.id=t.id_contragent AND t.period<=d.period
WHERE m.id=152 
  AND d.period='2019-12-01'
ORDER BY c.id

Получается такой результат:
Картинка с другого сайта.

Появился мультипликативный эффект: 3*4=12 строк на c.id=9884 вместо требуемых четырех. Что делать? :)
16 янв 20, 10:17    [22060419]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19999
LiYing
Что делать?
Для каждого поля дополнительно сравнивать значение текущей и предыдущей записи, и заменять на NULL, если ни значение, ни идентификатор не изменились. Типа

CASE WHEN     c.id = LAG(c.id) OVER () 
          AND d.h_sum_debt = LAG(d.h_sum_debt) OVER () 
          AND d.hw_sum_debt = LAG(d.hw_sum_debt) OVER () 
     THEN NULL 
     ELSE d.h_sum_debt+d.hw_sum_debt 
     END dhhw,
CASE WHEN     c.id = LAG(c.id) OVER () 
          AND n.date_from = LAG(n.date_from) OVER () 
     THEN NULL ELSE n.date_from END ndf,
16 янв 20, 11:45    [22060511]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
LiYing
Member

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

Нет, не выходит, как только не мучил...
Если так
SELECT c.id,
       CASE WHEN c.id = LAG(c.id) OVER () AND d.h_sum_debt = LAG(d.h_sum_debt) OVER () AND d.hw_sum_debt = LAG(d.hw_sum_debt) OVER () THEN NULL ELSE d.h_sum_debt+d.hw_sum_debt END dhhw,
       CASE WHEN c.id = LAG(c.id) OVER () AND n.date_from = LAG(n.date_from) OVER () THEN NULL ELSE n.date_from END n_df,
       CASE WHEN c.id = LAG(c.id) OVER () AND n.debt_total = LAG(n.debt_total) OVER () THEN NULL ELSE n.debt_total END n_debt,
       x.date_from x_df,x.debt_sum_h+x.debt_sum_hw x_debt,
       t.date_from t_df,t.debt_sum_h+t.debt_sum_hw t_debt
FROM data_sgrc d 
INNER JOIN contragents c ON d.id_contragent=c.id 
INNER JOIN mkd m ON c.id_mkd=m.id 
LEFT JOIN notifications n ON c.id=n.id_contragent AND n.period<=d.period
LEFT JOIN testx x ON c.id=x.id_contragent AND x.period<=d.period
LEFT JOIN test t ON c.id=t.id_contragent AND t.period<=d.period
WHERE m.id=152 
  AND d.period='2019-12-01'
ORDER BY c.id

то результат такой же как и выше:
Картинка с другого сайта.

А если же все завернуть в CASE
SELECT c.id,
       CASE WHEN c.id = LAG(c.id) OVER () AND d.h_sum_debt = LAG(d.h_sum_debt) OVER () AND d.hw_sum_debt = LAG(d.hw_sum_debt) OVER () THEN NULL ELSE d.h_sum_debt+d.hw_sum_debt END dhhw,
       CASE WHEN c.id = LAG(c.id) OVER () AND n.date_from = LAG(n.date_from) OVER () THEN NULL ELSE n.date_from END n_df,
       CASE WHEN c.id = LAG(c.id) OVER () AND n.debt_total = LAG(n.debt_total) OVER () THEN NULL ELSE n.debt_total END n_debt,
       CASE WHEN c.id = LAG(c.id) OVER () AND x.date_from = LAG(x.date_from) OVER () THEN NULL ELSE x.date_from END x_df,	
       CASE WHEN c.id = LAG(c.id) OVER () AND x.debt_sum_h = LAG(x.debt_sum_h) OVER () AND x.debt_sum_hw = LAG(x.debt_sum_hw) OVER () THEN NULL ELSE x.debt_sum_h+x.debt_sum_hw END x_debt,
       CASE WHEN c.id = LAG(c.id) OVER () AND t.date_from = LAG(t.date_from) OVER () THEN NULL ELSE t.date_from END t_df,
       CASE WHEN c.id = LAG(c.id) OVER () AND t.debt_sum_h = LAG(t.debt_sum_h) OVER () AND t.debt_sum_hw = LAG(t.debt_sum_hw) OVER () THEN NULL ELSE t.debt_sum_h+t.debt_sum_hw END t_debt
FROM data_sgrc d 
INNER JOIN contragents c ON d.id_contragent=c.id 
INNER JOIN mkd m ON c.id_mkd=m.id 
LEFT JOIN notifications n ON c.id=n.id_contragent AND n.period<=d.period
LEFT JOIN testx x ON c.id=x.id_contragent AND x.period<=d.period
LEFT JOIN test t ON c.id=t.id_contragent AND t.period<=d.period
WHERE m.id=152 
  AND d.period='2019-12-01'
ORDER BY c.id

то опять не то:
Картинка с другого сайта.

Еще варианты?

Сообщение было отредактировано: 16 янв 20, 13:44
16 янв 20, 13:43    [22060619]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19999
LiYing
Еще варианты?
Создайте адекватную модельную fiddle - и будем на ней играться.
16 янв 20, 15:19    [22060710]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
LiYing
Member

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

Создал fiddle, немного упростив.
Хочется видеть для c.id=9884 четыре строки (а не 12 как получается), как нафотошопил тут:
Картинка с другого сайта.
16 янв 20, 16:43    [22060828]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19999
Эээ... это будет совсем другая технология! это ни разу не джойн в лоб, как у тебя.

Получается вот такой монстр:

WITH RECURSIVE
cte_d AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY id_contragent
                                         ORDER BY period ) rn 
           FROM data
           WHERE period = '2019-12-01'),
cte_n AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY id_contragent
                                         ORDER BY period ) rn
           FROM notifications 
           WHERE period <= '2019-12-01'),
cte_x AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY id_contragent
                                         ORDER BY date_from ) rn
           FROM testx 
           WHERE period <= '2019-12-01'),
cte_t AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY id_contragent
                                         ORDER BY date_from ) rn 
           FROM test 
           WHERE period <= '2019-12-01'),
cte_1 AS ( SELECT id_contragent, rn FROM cte_d
           UNION ALL
           SELECT id_contragent, rn FROM cte_n
           UNION ALL
           SELECT id_contragent, rn FROM cte_x
           UNION ALL
           SELECT id_contragent, rn FROM cte_t ),
cte_2 AS ( SELECT id_contragent, MAX(rn) rn
           FROM cte_1
           GROUP BY id_contragent ),
cte_c AS ( SELECT id, 1 rn
           FROM contragents 
           UNION ALL
           SELECT id, cte_c.rn+1
           FROM cte_c, cte_2
           WHERE cte_c.id = cte_2.id_contragent
             AND cte_c.rn < cte_2.rn)
SELECT c.rn, c.id,
       d.h+d.hw dhhw,
       n.date_from n_df,
       n.debt n_debt,
       x.date_from x_df,
       x.debt_sum_h+x.debt_sum_hw x_debt,
       t.date_from t_df,
       t.debt_sum_h+t.debt_sum_hw t_debt
FROM cte_c c 
LEFT JOIN cte_d d ON c.rn=d.rn AND d.id_contragent=c.id 
LEFT JOIN cte_n n ON c.rn=n.rn AND c.id=n.id_contragent
LEFT JOIN cte_x x ON c.rn=x.rn AND c.id=x.id_contragent
LEFT JOIN cte_t t ON c.rn=t.rn AND c.id=t.id_contragent
ORDER BY c.id, c.rn;

fiddle
Впрочем, если разобраться, что делает каждая отдельная CTE, то всё довольно просто.

Сообщение было отредактировано: 16 янв 20, 20:11
16 янв 20, 20:10    [22061059]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
LiYing
Member

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

Картинка с другого сайта.
Чую, завтра весь день уйдет на понимание, спасибо :)
А учитывая, что это был нормально так упрощенный от реального пример, иэххх!...
16 янв 20, 20:38    [22061078]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19999
LiYing
завтра весь день уйдет на понимание
Да ну, фигня... разве что cte_1 - но рассматривай его в комплексе с cte_2.

Главная во всём тонкость - перенос отборов в CTE, который нужно делать достаточно аккуратно.

Сообщение было отредактировано: 16 янв 20, 20:53
16 янв 20, 20:53    [22061089]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
LiYing
Member

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

Для кого фигня, а для кого и "шарики за ролики" :)
Пытаюсь разобраться в запросе, так вроде по раздельности эти CTE понятны, но есть вопросы :)
1) почему в cte_n используется ORDER BY period, а не ORDER BY date_from ? Опечатка?
2) в текущем виде выборка по всем контрагентам за период, а не соображу как теперь сделать выборку по контрагентам в заданном МКД (m.id=152) за период? Т.е. то, что в моем запросе в первом посте записано как
INNER JOIN contragents c ON d.id_contragent=c.id 
INNER JOIN mkd m ON c.id_mkd=m.id 
...
WHERE m.id=152 AND d.period='2019-12-01'

Куда это условие вставлять?
Добавил в fiddle табличку `mkd`
17 янв 20, 09:53    [22061303]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19999
LiYing
почему в cte_n используется ORDER BY period, а не ORDER BY date_from ?
Потому что это не было определено. Пришлось придумывать на основании исходных данных.

LiYing
как теперь сделать выборку по контрагентам в заданном МКД (m.id=152)
А просто добавить AND c.id_mkd = 152 в финальный запрос - не устраивает?

К тому же у тебя все контрагенты имеют этот МКД - так что на этих данных ты разницу не увидишь.

Сообщение было отредактировано: 17 янв 20, 12:09
17 янв 20, 12:08    [22061468]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
LiYing
Member

Откуда:
Сообщений: 579
Akina
А просто добавить AND c.id_mkd = 152 в финальный запрос - не устраивает?

Оно бы устраивало, если бы работало :)
Я чуть изменил в fiddle запрос cte_c и финальный, но споткнулся на ошибке "The used SELECT statements have a different number of columns", т.к. не могу добавить id_mkd в cte_2 из-за того, что используемые в cte_1 CTE ничего не знают о таблице mkd. Запутался вконец...

Akina
К тому же у тебя все контрагенты имеют этот МКД - так что на этих данных ты разницу не увидишь.

Ну пример просто краткий, не стал сотни записей добавлять, чтобы не загромождать фиддл. Ну предположим, там несколько сотен записей, а не 3. И на каждый id_mkd имеется по сотне контрагентов c.id. Как тогда?
17 янв 20, 13:05    [22061541]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19999
LiYing
не могу добавить id_mkd в cte_2 из-за того, что используемые в cte_1 CTE ничего не знают о таблице mkd
А нафига они там?

Сообщение было отредактировано: 17 янв 20, 14:29
17 янв 20, 14:28    [22061614]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
LiYing
Member

Откуда:
Сообщений: 579
Akina
А нафига они там?

Может и не нафига, но как тогда или устранить ошибку в последнем фиддле или изменить его так, чтобы выбирать данные только по контрагентам из одного заданного МКД? Посмотрите фиддл, плиз.
17 янв 20, 15:07    [22061649]     Ответить | Цитировать Сообщить модератору
 Re: Совет по JOIN  [new]
LiYing
Member

Откуда:
Сообщений: 579
Вроде разобрался как выбрать контрагентов только из нужного МКД, добавил WHERE id_mkd=152, благо больше никакие данные из таблицы mkd не нужны:
cte_c AS ( SELECT id, 1 rn
           FROM contragents WHERE id_mkd=152
           UNION ALL
           SELECT id, cte_c.rn+1
           FROM cte_c, cte_2
           WHERE cte_c.id = cte_2.id_contragent AND cte_c.rn < cte_2.rn)

Норм решение?
Но тут другая бяда выяснилась :) Отбираются все контрагенты по МКД, но нужно только тех, кто присутствует в таблице data в указанный период. С этим-то как быть?
17 янв 20, 16:53    [22061746]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / MySQL Ответить