Перейти из форума на сайт.

Новости • Файловые архивы
Поиск • Активные темы • Топ лист
Правила • Кто в on-line?
Вход • Забыли пароль? • Первый раз на этом сайте? • Регистрация
Компьютерный форум Ru.Board » Компьютеры » Прикладное программирование » SQL запрос

Модерирует : ShIvADeSt

 Версия для печати • Подписаться • Добавить в закладки
Страницы: 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 35 36 37 38 39 40 41 42 43

Открыть новую тему     Написать ответ в эту тему

Unnicked

Advanced Member		Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Спасибо!
Есть еще одна просьба.
 
Есть табличка ARCH_DATA со столбцами DT (DATE), ID (NUMBER) - первичный ключ, VAL (NUMBER) и табличка ARCH_KEY со столбцом ID (NUMBER).
 
 
Нужно для всех ID из ARCH_KEY получить VAL с максимальным DT из ARCH_DATA. При этом если в ARCH_DATA нет данных по какому-то ID, нужно вернуть строку с этим ID и null null.
 
Пример:
ARCH_DATA
ID DT VAL
1 1 22
1 2 33
2 3 44
 
ARCH_KEY
ID
1
2
3
4
 
Требуемый результат:
ID DT VAL
1 2 33
2 3 44
3 null null
4 null null
Всего записей: 1134 | Зарегистр. 19-08-2005 | Отправлено: 01:24 01-03-2015
Mic777

Junior Member		Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Как найти MAX значение Вы уже вроде разобрались
Осталось в этой задаче объединить с ARCH_DATA и ARCH_KEY.
Причем ARCH_KEY основная таблица и к ней по LEFT JOIN присоединяем ARCH_DATA.
Всего записей: 90 | Зарегистр. 15-12-2005 | Отправлено: 08:57 01-03-2015
landy



Full Member		Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Mic777, нужно использовать LEFT OUTER JOIN, либо коррелированный подзапрос вместо группировки
Всего записей: 576 | Зарегистр. 17-01-2003 | Отправлено: 11:29 01-03-2015 | Исправлено: landy, 11:30 01-03-2015
Mic777

Junior Member		Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
landy

Цитата:
нужно использовать LEFT OUTER JOIN

 
Вы часто пишете OUTER ?
 
Любое внешнее объединение LEFT, RIGHTили FULL уже подразумевает то, что оно OUTER.
Потому практически всегда стоит опционально в синтаксисе [OUTER] для совместимости с ANSI SQL стандартом.
 
В любом случае человека задающего вопрос главное направить в нужное русло, остальное детали.
Всего записей: 90 | Зарегистр. 15-12-2005 | Отправлено: 12:52 01-03-2015
Unnicked

Advanced Member		Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору

Цитата:
 Причем ARCH_KEY основная таблица и к ней по LEFT JOIN присоединяем ARCH_DATA.  

Вот с этим и проблема, т.к. в первой таблице нет всех столбцов, которые нужные в результирующем запросе, там только ID. И не могу разобраться, как к ней приJOINить столбцы из второй таблицы или NULL...
Всего записей: 1134 | Зарегистр. 19-08-2005 | Отправлено: 15:43 01-03-2015
vikkiv



Advanced Member		Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Unnicked: SQL Server 2014:
Код:
select k.id,m.dt,d.val from arch_key k left join  
(select distinct id,first_value(val)over(partition by id order by dt desc)val from arch_data)d on k.id=d.id
left join(select id,max(dt)dt from arch_data group by id)m on k.id=m.id order by 1
Всего записей: 748 | Зарегистр. 10-11-2005 | Отправлено: 15:55 01-03-2015 | Исправлено: vikkiv, 16:11 01-03-2015
Unnicked

Advanced Member		Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
vikkiv
Спасибо, Oracle тоже запрос отработал. В его результаты я добавил максимальное dt следующим образом:

Код:
select distinct k.id,d.dt,d.val from arch_key k left join  
(select distinct id,first_value(dt)over(partition by id order by dt desc)dt, first_value(val)over(partition by id order by dt desc)val from arch_data)d on k.id=d.id  

Так нормально, или можно как-то проще/ эффективнее?
Увидел Вашу откорректированную версию. Подскажите, какой вариант более оптимален?
Всего записей: 1134 | Зарегистр. 19-08-2005 | Отправлено: 16:14 01-03-2015 | Исправлено: Unnicked, 16:33 01-03-2015
vikkiv



Advanced Member		Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Unnicked функции агрегирования {max()/groupby} работают быстрее чем оконные/cte {..over(partition by..) или with cte} поэтому по моему если там записей больше миллиона то исходный вариант может быть полегче, хотя как в оракл-среде незнаю, статистику скоростей выполнений некэшированных запросов надо сравнивать
если записей десятки-сотни миллионов или больше миллиарда то можно сначала сократить массив до промежуточного минимума и с ним уже дальше работать типа варианта для исходных данных (который уже к общей массе id с null дальше по условиям прикручивать, напр если при максимальной дате может быть несколько значений val):
Код:
select x.id,x.dt,a.val from
(select id,max(dt)dt from arch_data group by id)x
left join arch_data a on x.id=a.id and x.dt=a.dt
Всего записей: 748 | Зарегистр. 10-11-2005 | Отправлено: 16:34 01-03-2015 | Исправлено: vikkiv, 16:53 01-03-2015
landy



Full Member		Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Самый простой вариант запроса (без подзапросов):
 

Код:
select k.id, max(dt) dt, max(val) val
  from arch_key k, arch_data d
 where d.id (+)= k.id
 group by k.id
 order by k.id;
 

 
Mic777, я пишу на oracle, а там свой синтаксис соединений, в котором по-умолчанию подразумевается inner join. Но ты прав, в ANSI он по-умолчанию outer.
Всего записей: 576 | Зарегистр. 17-01-2003 | Отправлено: 16:01 02-03-2015
Mic777

Junior Member		Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
landy
По поводу кода - такой вариант не прокатит из-за max(val) - точнее результат будет неправильный.
 
по поводу Oracle - как может "по-умолчанию подразумевается inner join" быть для LEFT к примеру?
LEFT INNER JOIN что ли?  
Если ты указал LEFT (не важно в ANSI синтаксисе или оракловым (+)) - он всегда OUTER.
 
по теме, согласен с vikkiv - надо смотреть конкретную задачу и реальный план выполнения на реальных данных. Если разобраться с аналитическими функциями запросы строить проще - не надо самообъединение и работают они достаточно шустро.
 
И еще, если реальных данных не 500 строк абы поиграться, а реально больше - то надо индекс по Dt, соответственно null в индексы не попадут - уже не есть хорошо, не всегда в индекс попадем, значит надо какую-то дату использовать как null и тогда соответствующий индекс с NVL-ом мутить.
Всего записей: 90 | Зарегистр. 15-12-2005 | Отправлено: 16:28 02-03-2015 | Исправлено: Mic777, 16:40 02-03-2015
landy



Full Member		Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору

Цитата:
по поводу Oracle - как может "по-умолчанию подразумевается inner join" быть для LEFT к примеру?  

inner - он не left, и не right, это соединение всех имеющихся в обоих таблицах значений, именно оно подразумевается, когда пишешь a.ID=b.ID и называется просто join.
Всего записей: 576 | Зарегистр. 17-01-2003 | Отправлено: 16:52 02-03-2015
Mic777

Junior Member		Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
landy

Цитата:
когда пишешь a.ID=b.ID и называется просто join.

как раз таки подразумевается INNER JOIN, а если бы был (+) с какой либо стороны, то это был бы OUTER JOIN (и не важно левым или правым), т.к. объединение всегда является либо внутренним (INNER) либо внешним (OUTER).
 
Другими словами если объединение НЕ внешнее, значит оно внутреннее, поэтому в синтаксисе запроса INNER и OUTER стоят в [] - т.е. являются опциональными, потому что и без них всегда известно по наличию или отсутствию LEFT, RIGHT какое это объединение, а не потому что где-то, что-то по умолчанию.

Цитата:
на oracle, а там свой синтаксис соединений, в котором по-умолчанию подразумевается inner join. Но ты прав, в ANSI он по-умолчанию outer.

 
Поэтому мне не понятно для чего заострять внимание на OUTER

Цитата:
нужно использовать LEFT OUTER JOIN
 
 
если его вообще можно опустить.
 
Более полемику предлагаю здесь не разводить, если есть какие то замечания пожалуйста в личку.
Всего записей: 90 | Зарегистр. 15-12-2005 | Отправлено: 17:54 02-03-2015 | Исправлено: Mic777, 18:07 02-03-2015
Unnicked

Advanced Member		Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору

Цитата:
И еще, если реальных данных не 500 строк абы поиграться, а реально больше

Реально больше, где-то 1.5 млрд Придется как-то оптимизировать запрос, т.к. выполняется ооооочень долго...
Всего записей: 1134 | Зарегистр. 19-08-2005 | Отправлено: 17:56 02-03-2015
Mic777

Junior Member		Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Unnicked

Цитата:
Реально больше, где-то 1.5 млрд
- уже интересней, есть за что бороться
 
план запроса можно в студию?
скорей всего полный перебор таблицы из-за null
Всего записей: 90 | Зарегистр. 15-12-2005 | Отправлено: 18:04 02-03-2015 | Исправлено: Mic777, 18:05 02-03-2015
Unnicked

Advanced Member		Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Mic777

Код:
SELECT DISTINCT K.ID, D.DT, D.VAL FROM ARCH_KEY K LEFT JOIN  
(SELECT DISTINCT ID,FIRST_VALUE(VAL)OVER(PARTITION BY ID ORDER BY DT DESC)VAL, FIRST_VALUE(DT)OVER(PARTITION BY ID ORDER BY DT DESC)DT FROM ARCH_DATA)D ON K.ID=D.ID  
ORDER BY K.ID
Всего записей: 1134 | Зарегистр. 19-08-2005 | Отправлено: 18:07 02-03-2015
Mic777

Junior Member		Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
а что с индексами по таблицам?
Всего записей: 90 | Зарегистр. 15-12-2005 | Отправлено: 18:09 02-03-2015 | Исправлено: Mic777, 18:27 02-03-2015
Unnicked

Advanced Member		Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Mic777
 
Наверно, вот это имелось в виду?
 
 
Всего записей: 1134 | Зарегистр. 19-08-2005 | Отправлено: 18:27 02-03-2015
Mic777

Junior Member		Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Unnicked
какие индексы на таблицах?
какая структура данных, в том плане что сколько к примеру записей с dt is null
сколько уникальных записей по dt на каждый id?
 
Результаты этих запросов?: Подробнее...
Всего записей: 90 | Зарегистр. 15-12-2005 | Отправлено: 18:35 02-03-2015 | Исправлено: Mic777, 18:41 02-03-2015
vikkiv



Advanced Member		Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
согласен с индексами (как долго строится будут другой вопрос, особенно бардак с оптимизацией/статистикой если обновления частые).. лучше (опять-же для MSSQL а не Oracle) в arch_data : clustered на id и обычный на dt  ... но навряд-ли человеку задающему такие вопросы дадут доступ на индексацию таблиц в полтора миллиарда записей .. так что это скорее о том как под индексы запрос подобрать а не как таблицу оптимизировать чтобы запросы ускорить.
Судя по структуре бд/таблиц и соблюдая основные правила вангования не думаю что в arch_data есть null, разве что после очистки update, скорее всего в обоих таблицах null отсутствуют, другое дело что у зарегистрированного в arch_key id может и не быть операций в arch_data, отсюда и nullы в результатах
Всего записей: 748 | Зарегистр. 10-11-2005 | Отправлено: 00:26 03-03-2015
deyatel1974

Newbie		Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
а есть ли способ посчитать на SQL нарастающий итог по какойлибо колонке?
Всего записей: 23 | Зарегистр. 10-08-2014 | Отправлено: 10:40 10-03-2015
Открыть новую тему     Написать ответ в эту тему

Страницы: 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 35 36 37 38 39 40 41 42 43

Компьютерный форум Ru.Board » Компьютеры » Прикладное программирование » SQL запрос


Реклама на форуме Ru.Board.

Powered by Ikonboard "v2.1.7b" © 2000 Ikonboard.com
Modified by Ru.B0ard
© Ru.B0ard 2000-2025

BitCoin: 1NGG1chHtUvrtEqjeerQCKDMUi6S6CG4iC

Рейтинг.ru