Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Задачка SQL: сохранить всю левую таблицу при уникальности связи / 9 сообщений из 9, страница 1 из 1
03.10.2020, 11:23
    #40005098
Larr`
Larr`
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка SQL: сохранить всю левую таблицу при уникальности связи
У меня есть некие таблицы, из которых надо сделать выборку, соединив их нечёткой связью, но при этом сохранить уникальность.
То есть при множестве подходящих под критерий строк из левой и правой таблицы выбирать только по одной.
При этом левая таблица должна быть выбрана вся полностью.
Всё это на оракле 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
| Ответить | Цитировать | Написать  
03.10.2020, 12:16
    #40005101
dmdmdm
dmdmdm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка SQL: сохранить всю левую таблицу при уникальности связи
тормозит на реальных данных

Оптимизацию начинают с плана.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2020, 12:32
    #40005105
graycode
graycode
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка SQL: сохранить всю левую таблицу при уникальности связи
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
| Ответить | Цитировать | Написать  
03.10.2020, 12:35
    #40005106
graycode
graycode
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка SQL: сохранить всю левую таблицу при уникальности связи
dmdmdm
Оптимизацию начинают с плана.

Начинать с логики получения результата все же лучше, можно долго оптимизировать процесс чесания левого уха правой пяткой, но левой рукой по любому сподручнее.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2020, 18:11
    #40005147
Larr`
Larr`
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка SQL: сохранить всю левую таблицу при уникальности связи
graycode,
спасибо, интересное решение, но суть в том, что я, похоже, чрезмерно упростил пример
Там нельзя взять минимальную дату и получить уникальный набор по b, потому что одна дата зависит от другой.
То есть на самом деле условие соединения не a.dt=b.dt , а b.dt between a.dt and a.dt+3 .
Мой вариант даёт правильно, а ваш - дублит
В 12-ом оракле я бы пропихнул условие и соединил outer apply, а что делать в 11, ума не приложу
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2020, 20:41
    #40005180
graycode
graycode
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка SQL: сохранить всю левую таблицу при уникальности связи
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
| Ответить | Цитировать | Написать  
04.10.2020, 00:00
    #40005216
graycode
graycode
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка SQL: сохранить всю левую таблицу при уникальности связи
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
| Ответить | Цитировать | Написать  
04.10.2020, 15:12
    #40005272
Бельфя
Бельфя
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка SQL: сохранить всю левую таблицу при уникальности связи
На мой взгляд в задаче не хватает связи "позванных" с "позвавшими".
Это приводит к декартову произведению между наборами. От того и тормозит на реальных данных.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.10.2020, 23:30
    #40005333
Larr`
Larr`
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Задачка SQL: сохранить всю левую таблицу при уникальности связи
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
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Задачка SQL: сохранить всю левую таблицу при уникальности связи / 9 сообщений из 9, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 146ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1880833]:
 0ms
get settings:
 7ms
get forum list:
 10ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 60ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 40ms
get tp. blocked users:
 1ms
others: 11ms
total:  146ms
x
x
Закрыть


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