» SQL запрос

Как найти MAX значение Вы уже вроде разобрались
Осталось в этой задаче объединить с ARCH_DATA и ARCH_KEY.
Причем ARCH_KEY основная таблица и к ней по LEFT JOIN присоединяем ARCH_DATA.

Mic777, нужно использовать LEFT OUTER JOIN, либо коррелированный подзапрос вместо группировки

landy

Цитата:

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

Вы часто пишете OUTER ?

Любое внешнее объединение LEFT, RIGHTили FULL уже подразумевает то, что оно OUTER.
Потому практически всегда стоит опционально в синтаксисе [OUTER] для совместимости с ANSI SQL стандартом.

В любом случае человека задающего вопрос главное направить в нужное русло, остальное детали.

Цитата:

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

Вот с этим и проблема, т.к. в первой таблице нет всех столбцов, которые нужные в результирующем запросе, там только ID. И не могу разобраться, как к ней приJOINить столбцы из второй таблицы или NULL...

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

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

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

Самый простой вариант запроса (без подзапросов):


Код: 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;

landy
По поводу кода - такой вариант не прокатит из-за max(val) - точнее результат будет неправильный.

по поводу Oracle - как может "по-умолчанию подразумевается inner join" быть для LEFT к примеру?
LEFT INNER JOIN что ли?
Если ты указал LEFT (не важно в ANSI синтаксисе или оракловым (+)) - он всегда OUTER.

по теме, согласен с vikkiv - надо смотреть конкретную задачу и реальный план выполнения на реальных данных. Если разобраться с аналитическими функциями запросы строить проще - не надо самообъединение и работают они достаточно шустро.

И еще, если реальных данных не 500 строк абы поиграться, а реально больше - то надо индекс по Dt, соответственно null в индексы не попадут - уже не есть хорошо, не всегда в индекс попадем, значит надо какую-то дату использовать как null и тогда соответствующий индекс с NVL-ом мутить.

Цитата:

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

inner - он не left, и не right, это соединение всех имеющихся в обоих таблицах значений, именно оно подразумевается, когда пишешь a.ID=b.ID и называется просто join.

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

если его вообще можно опустить.

Более полемику предлагаю здесь не разводить, если есть какие то замечания пожалуйста в личку.

Цитата:

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

Реально больше, где-то 1.5 млрд Придется как-то оптимизировать запрос, т.к. выполняется ооооочень долго...

Unnicked

Цитата:

Реально больше, где-то 1.5 млрд

- уже интересней, есть за что бороться

план запроса можно в студию?
скорей всего полный перебор таблицы из-за null

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

а что с индексами по таблицам?

Mic777

Наверно, вот это имелось в виду?

Unnicked
какие индексы на таблицах?
какая структура данных, в том плане что сколько к примеру записей с dt is null
сколько уникальных записей по dt на каждый id?

Результаты этих запросов?: [more]
select count(*)
from ARCH_KEY;
select count(*)
from ARCH_DATA;
select count(*)
from ARCH_DATA a where a.dt is null;
select count(*)
from (
select id, count(*)
from ARCH_DATA a
where a.dt is not null
group by id
);
[/more]

согласен с индексами (как долго строится будут другой вопрос, особенно бардак с оптимизацией/статистикой если обновления частые).. лучше (опять-же для MSSQL а не Oracle) в arch_data : clustered на id и обычный на dt ... но навряд-ли человеку задающему такие вопросы дадут доступ на индексацию таблиц в полтора миллиарда записей .. так что это скорее о том как под индексы запрос подобрать а не как таблицу оптимизировать чтобы запросы ускорить.
Судя по структуре бд/таблиц и соблюдая основные правила вангования не думаю что в arch_data есть null, разве что после очистки update, скорее всего в обоих таблицах null отсутствуют, другое дело что у зарегистрированного в arch_key id может и не быть операций в arch_data, отсюда и nullы в результатах

а есть ли способ посчитать на SQL нарастающий итог по какойлибо колонке?

deyatel1974, да, например, для oracle через аналитические функции это будет выглядеть примерно так.

Либо подзапросом.

Да, можно и подзапросом, но будет работать существенно медленнее. Аналитика сейчас входит в стандарт SQL и поддерживается во многих БД, в частности, в Oracle, MS SQL, PostgreSQL.

deyatel1974, у тебя какая БД?

у меня именно оракл сейчас читаю про аналитику, спасибо. пока непотяно что такое rank, dense_rank?

deyatel1974, вот краткое описание этих функций. Смысл в том, что они нумеруют элементы внутри групп, при этом rank одинаковым значениям элементов сопоставляет одинаковые номера, увеличивая счетчик только на отличающихся элементах, а dense_rank нумерует по-порядку.

Здравствуйте.
Нужна ваша помощь.
есть некая таблица на сервере с oracle.

вот код по которому информацию выдало
select *
from таблица where имя_поля between '01.04.2015' and '02.04.2015'

а вот код по которому информации якобы ни какой
select *
from таблица where имя_поля between '25.03.2015' and '02.04.2015'


начинаю разбираться и понимаю что "имя_поля" в таблице идёт как текстовое

переписываю так
select to_date(имя_поля)
from таблица where to_date(имя_поля) between 'начальная дата' and 'конечная дата'

запускаю и выдает ошибку что поле должно быть численным в пределах...
тогда ограничиваю диапазон и результат есть...

подскажите как написать запрос что бы была проверка на ошибки и если в строке встретилась ошибка to_date(имя_поля)=ОШИБКА ТО NULL и продолжить выборку

пример записей в таблице
ФАМИЛИЯ ИМЯ ОТЧЕСТВО дата(текстовое поле)

VadKomarov, добавьте подзапрос к Вашему запросу, Который будет отсеивать null-овые поля даты
select to_date(имя_поля)
from
(select * from таблица where имя_поля is not null)
where to_date(имя_поля) between 'начальная дата' and 'конечная дата'

nyinick
Спасибо учту, но вот ещё есть вопрос а если поле не пустое а с текстом? как быть ?
Я пишу на Delphi и по аналогии бы функцию типа TRY ... EXCEPT есть ли такая возможность ?

VadKomarov, вариант сделать много есть, но как один из:
Создаёшь в оракле свою функцию

FUNCTION convert_date (input_string_date IN VARCHAR2) RETURN DATE
IS
return_value DATE := NULL;
BEGIN
return_value := TO_DATE (input_string_date);
EXCEPTION
WHEN OTHERS THEN return_value := NULL;
END;

а потом селектишь данные из таблицы вызывая эту функцию и передавая ей имя_поля

select *, converted_date as имя_поля from
(select *, convert_date(имя_поля) as converted_date from таблица where converted_date is not null)
where to_date(имя_поля) between 'начальная дата' and 'конечная дата'

Возможны оошибки в синтаксисе, т.к. Оракла под рукой нет что бы проверить, но решение рабочее

VadKomarov,

Цитата:

Спасибо учту, но вот ещё есть вопрос а если поле не пустое а с текстом? как быть ?
Я пишу на Delphi и по аналогии бы функцию типа TRY ... EXCEPT есть ли такая возможность ?

В PL-SQL есть обработка Exceptions
https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/07_errs.htm

Всем огромное спасибо ваши подсказки помогли !!!