powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Задачка SQL: сохранить всю левую таблицу при уникальности связи
9 сообщений из 9, страница 1 из 1
Задачка SQL: сохранить всю левую таблицу при уникальности связи
    #40005098
Larr`
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
У меня есть некие таблицы, из которых надо сделать выборку, соединив их нечёткой связью, но при этом сохранить уникальность.
То есть при множестве подходящих под критерий строк из левой и правой таблицы выбирать только по одной.
При этом левая таблица должна быть выбрана вся полностью.
Всё это на оракле 11g.
Для простоты представим, что Маша, Вася и Петя зовут людей в магазин, но когда человек приходит, бонус за это мы даём только Маше, потому что она первая позвала, но знать, что Вася и Петя работали, тоже надо.
Не слишком долго думая, я запилил такое
Код: plsql
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.
with a as 
(select 'A1' as name, 1 as dt from dual
 union all
 select 'A2' as name, 3 as dt from dual
  union all
 select 'A3' as name, 3 as dt from dual
   union all
 select 'A4' as name, 4 as dt from dual
   ),
 b as 
 (select 'B1' as name, 1 as dt from dual
 union all
 select 'B2' as name, 1 as dt from dual
 union all 
 select 'B3' as name, 3 as dt from dual
  union all
 select 'B4' as name, 3 as dt from dual)
select a.name, a.dt, y.name2
from a left join
( 
     select x.name1, x.name2, x.dt
     from
     (     
       select a.name as name1, b.name as name2, a.dt,
              row_number() over (partition by a.dt order by a.name, b.name ) rn     
       from a left join b on a.dt=b.dt
      )x
      where x.rn=1
)y on a.name=y.name1 and a.dt=y.dt
order by a.name, a.dt

что вполне удовлетворяет критериям, но тормозит на реальных данных.
Вопрос: Нельзя ли обойтись без верхнего левого джойна или может есть другое интересное решение?
...
Рейтинг: 0 / 0
Задачка SQL: сохранить всю левую таблицу при уникальности связи
    #40005101
dmdmdm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
тормозит на реальных данных

Оптимизацию начинают с плана.
...
Рейтинг: 0 / 0
Задачка SQL: сохранить всю левую таблицу при уникальности связи
    #40005105
graycode
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Larr`,

Если я правильно понял условие:
Код: plsql
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.
with a as 
(select 'A1' as name, 1 as dt from dual
 union all
 select 'A2' as name, 3 as dt from dual
  union all
 select 'A3' as name, 3 as dt from dual
   union all
 select 'A4' as name, 4 as dt from dual
   ),
 b as 
 (select 'B1' as name, 1 as dt from dual
 union all
 select 'B2' as name, 1 as dt from dual
 union all 
 select 'B3' as name, 3 as dt from dual
  union all
 select 'B4' as name, 3 as dt from dual)
, y as
(select min(name) name2, dt from b group by dt)
select a.name, a.dt,
 case when
   coalesce(lag(a.dt) over (order by a.dt, a.name), -1) != a.dt
 then y.name2 end
from a left join y on a.dt=y.dt
order by a.name, a.dt
...
Рейтинг: 0 / 0
Задачка SQL: сохранить всю левую таблицу при уникальности связи
    #40005106
graycode
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
dmdmdm
Оптимизацию начинают с плана.

Начинать с логики получения результата все же лучше, можно долго оптимизировать процесс чесания левого уха правой пяткой, но левой рукой по любому сподручнее.
...
Рейтинг: 0 / 0
Задачка SQL: сохранить всю левую таблицу при уникальности связи
    #40005147
Larr`
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
graycode,
спасибо, интересное решение, но суть в том, что я, похоже, чрезмерно упростил пример
Там нельзя взять минимальную дату и получить уникальный набор по b, потому что одна дата зависит от другой.
То есть на самом деле условие соединения не a.dt=b.dt , а b.dt between a.dt and a.dt+3 .
Мой вариант даёт правильно, а ваш - дублит
В 12-ом оракле я бы пропихнул условие и соединил outer apply, а что делать в 11, ума не приложу
...
Рейтинг: 0 / 0
Задачка SQL: сохранить всю левую таблицу при уникальности связи
    #40005180
graycode
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Larr`,

Вариант "в лоб":
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
select a.name, a.dt,
 case when
    coalesce(lag(a.dt) over (order by a.dt, a.name), -1) != a.dt
 then 
    (select min(name) from b where b.dt between a.dt and a.dt + 3)
 end as b_name
from a
order by a.name, a.dt



Вариант втащить a.dt в b:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
, d as
(
 select dt from a group by dt
)
, y (name2, dt) as
(
 select min(b.name) name2, d.dt
 from b join d on b.dt between d.dt and d.dt + 3
 group by d.dt
)
select a.name, a.dt,
 case when
   coalesce(lag(a.dt) over (order by a.dt, a.name), -1) != a.dt
 then y.name2 end as b_name
from a left join y on a.dt=y.dt
order by a.name, a.dt
...
Рейтинг: 0 / 0
Задачка SQL: сохранить всю левую таблицу при уникальности связи
    #40005216
graycode
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Larr`
Вопрос: Нельзя ли обойтись без верхнего левого джойна или может есть другое интересное решение?

Можно обойтись
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
select name1 as name, dt, min(case when rn = 1 then name2 end) b_name
from
(
       select a.name as name1, b.name as name2, a.dt,
              row_number() over (partition by a.dt order by a.name, b.name ) rn     
       from a left join b on b.dt between a.dt and a.dt + 3
)
group by name1, dt


Только внутренний left join дает полупроизведение и основная проблема думаю кроется именно в этом, посмотрите сколько строк в таблице a и сколько строк дает a left join b on b.dt between a.dt and a.dt + 3 .
...
Рейтинг: 0 / 0
Задачка SQL: сохранить всю левую таблицу при уникальности связи
    #40005272
Бельфя
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
На мой взгляд в задаче не хватает связи "позванных" с "позвавшими".
Это приводит к декартову произведению между наборами. От того и тормозит на реальных данных.
...
Рейтинг: 0 / 0
Задачка SQL: сохранить всю левую таблицу при уникальности связи
    #40005333
Larr`
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
graycode

Можно обойтись
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
select name1 as name, dt, min(case when rn = 1 then name2 end) b_name
from
(
       select a.name as name1, b.name as name2, a.dt,
              row_number() over (partition by a.dt order by a.name, b.name ) rn     
       from a left join b on b.dt between a.dt and a.dt + 3
)
group by name1, dt


Только внутренний left join дает полупроизведение и основная проблема думаю кроется именно в этом, посмотрите сколько строк в таблице a и сколько строк дает a left join b on b.dt between a.dt and a.dt + 3 .

Вот, это то, что нужно!
Нет, на самом деле, конечно, проблема кроется в том, что оракл пробегает по всем секциям таблицы b, секционированной по dt вместо того, чтобы ограничиться dt из a. С этим разобраться легко.
Но поскольку я небольшой начальник и ревьюер кода и всем джуниорам раздаю "сокровенную мудрость" вроде "используй таблицы в джойнах так редко как сможешь" или "самоджойн в эру оконных функций от лукавого",
то вот хотелось джуниору, который принёс мне эту функцию сделанную тремя запросами, показать эталон, но оказалось, что SQL потихоньку я стал забывать :)
Бельфя
На мой взгляд в задаче не хватает связи "позванных" с "позвавшими".
Это приводит к декартову произведению между наборами. От того и тормозит на реальных данных.

Насколько я помню, раньше каждая маша-зазывала давала клиенту свой промокод на скидку, который он вводил/говорил, но ушлые продавцы на местах слишком часто забивают её скидку своей.
Я бизнесу попредлагал вариантов, пусть думают, но премии-то давать уже надо сейчас
...
Рейтинг: 0 / 0
9 сообщений из 9, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Задачка SQL: сохранить всю левую таблицу при уникальности связи
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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