Частичный distinct, неполный group by в агрегатах?

[Andy117]

В упрощенном виде таблица выглядит так:
CREATE TABLE LINES (
ID INTEGER NOT NULL,
EVENT_ID INTEGER NOT NULL,
VTIME TIMESTAMP,
VALUE DECIMAL(8,2) ,
);

Т.е. имется некоторые события (EVENT_ID), для которых фиксирутся показания (VALUE) и время снятия показаний (VTIME).
Задача - выбрать самые первые (последние, третьи и т.д) показания для каждого события.
Запрос типа:

Код: Выделить всё

 select l.event_id, l.vtime,l.value from lines l
 where l.vtime = (
    select min(l2.vtime) from lines l2
    where l.event_id = l2.event_id
 )

маслал минут 40 (в талице полмиллиона записей), я не дождался результатов и его прибил.

Вопрос - кто тормозит, я или FB?

Вроде задачка-то простая. Если отбросить всякие SQL, то выглядит элементарно: сортируем таблицу по event_id, vtime, затем выбираем самые первые записи для каждого event_id. Т.е. так, как буд-то можно было указать distinct только для одного поля (EVENT_ID):

Код: Выделить всё

select distinct (l.event_id), l.value from lines l
order by l.vtime

Либо group by не для всех полей:

Код: Выделить всё

 select l.event_id, min(l.vtime), l.value from lines l
 group by l.event_id

Либо был агрегат типа FIRST:

Код: Выделить всё

 select l.event_id,  FIRST(l.value) from lines l
 order by l.vtime
 group by l.event_id

Так как же выбрать нужные значения одним (желательно эффективным ) запросом, не прибегая к к процедурам, промежуточным результатам, вьюхам?

[Andy117]

Хотя вроде удалось приспособить FIRST:

Код: Выделить всё

select L.event_id,
  (select first 1 L2.value
   from lines L2
   where L.event_id = L2.event_id
   order by L2.vtime) as value
from lines L
Group by L.event_id

Работает значительно быстрее, чем с min.

P.S. Млин, это первые записи показываются значительно быстрей.
А полный фетч 14000 записей - 14 минут

А запрос

Код: Выделить всё

select distinct L.event_id,
  (select first 1 L2.value
   from lines L2
   where L.event_id = L2.event_id
   order by L2.vtime) as value
from lines L

Вооще тормозной.

[CyberMax]

1. Приведи план первого запроса.
2. Напиши, какие созданы индексы для таблицы.

[Andy117]

1. Приведи план первого запроса.

PLAN (L2 ORDER LINES_IDX1 INDEX (FK_LINES_EVENTS))
PLAN (L ORDER FK_LINES_EVENTS)

LINES_IDX1 - это на поле VTIME

2. Напиши, какие созданы индексы для таблицы.

ALTER TABLE LINES ADD CONSTRAINT PK_LINES PRIMARY KEY (ID);
ALTER TABLE LINES ADD CONSTRAINT FK_LINES_EVENTS FOREIGN KEY (EVENT_ID) REFERENCES EVENTS (ID);
CREATE INDEX LINES_IDX1 ON LINES (VTIME);

Кстати, первый запрос показывает первые данные за пару секунд (в IBExpert), правда при переходе даже на вторую запись эксперт замирает на минуту. Далее медленный фетч.
А у второго данные и за 30 минут не показались, не утерпел и прибил заразу.
План второго:
PLAN (L2 ORDER LINES_IDX1 INDEX (FK_LINES_EVENTS))
PLAN (L2 ORDER LINES_IDX1 INDEX (FK_LINES_EVENTS))
PLAN SORT ((L NATURAL))

P.S. Попробовал еще поизголяться с min:

Код: Выделить всё

select l.event_id, min(l.vtime), (
    select first 1 l2."VALUE" from lines l2
    where l2.event_id = l.event_id
    and l2.vtime = min(l.vtime)
 )
from lines l
group by l.event_id

Полный фетч за 6 секунд! Это уже что-то!
План:
PLAN (L2 INDEX (LINES_IDX1, FK_LINES_EVENTS))
PLAN (L ORDER FK_LINES_EVENTS)

P.P.S. А вообще такой синтаксис, когда в сравнениях используюся агрегаты или вычисляемые поля, меня все время пугает. Возникает чувство, что агрегат будет вычисляться всегда, когда его упоминают. ИМХО логичнее было б использовать имя:

select l.event_id, min(l.vtime) as MINVTIME, (
select first 1 l2."VALUE" from lines l2
where l2.event_id = l.event_id
and l2.vtime = MINVTIME
)
from lines l
group by l.event_id

И смотрится логичней, и душа спокойна - агрегат не будет вычислятся во внутреннем селекте. Но, не дано нам менять стандарты.

[dimitr]

душа спокойна - агрегат не будет вычислятся во внутреннем селекте

наивный. Имя тебе ничего гарантировать не может.

[Andy117]

наивный. Имя тебе ничего гарантировать не может.

Это почему же?
Возмем выражение:
select l.event_id, ... (
select ... from lines l2
where l2.event_id = l.event_id
...
)
from lines l

Каждый раз к моменту выполнения внутреннего селекта уже должно быть выбрано и известно текущее значение l.event_id. И во внутреннем селекте сравнение идет не с полем l.event_id вообще, а именно с текущим его значением.

По аналогии могло бы быть в:
select ..., min(l.vtime) as MINVTIME, (
select ... from lines l2
where ... l2.vtime = MINVTIME
)
from lines l

К моменту выполнения внутреннего селекта уже известно текущее значение min(l.vtime) и это значение присвоено текущему выбираемому полю MINVTIME. И во внутреннем селекте сравнение будет идти не с каким-то полем вообще, а именно с текущим значением выбранного поля MINVTIME.

ИМХО это логично. И однозначно. И понятно, что во внутреннем цикле никакого min и впомине нет, а потому о его повторном вычислении и речи быть не может. А если и во внешнем и внутренних селектах у нас (min(l.v1 - l.v2/(SQRT(l.v3)) - 800) /100 || ' руб./кг*час', то не понятно сколько раз это будет вычислятся, да и набирать каждый раз обломно.

[Andy117]

И еще добавление в развитие темы.
Еще до выдачи результатов запроса, но после разбора sql выражения, sql-интерпретатор (компилятор? сервер?) уже знает количество выдаваемых полей, их типы и имена. Кроме того он должен подготовить необходимые переменные/структуры для выдачи/хранения (промежуточных) результатов.
Значит в запросе:

Код: Выделить всё

select ..., min(l.vtime) as MINVTIME, (
select ... from lines l2
where ... l2.vtime = min(l.vtime)
)
from lines l 

он выделит переменную/структуру и под MINVTIME.
Поскольку невозможно построить JOIN по min(..), во внутреннем селекте будет не JOIN, а именно сравнение. И к моменту сравнения значение min(l.vtime) должно быть уже известно. Вопрос когда это значение будет вычислено остается на совести интерпретатора. Если он тупой, то будет вычислять и во внутреннем селекте. А если умный, то поймет ли, что, к примеру, в:

Код: Выделить всё

select ..., min(l.vtime) + 1 as MINVTIME, (
select ... from lines l2
where ... l2.vtime = min(l.vtime) + 5 /*или даже min(l.vtime) + 1 + 4*/
)
from lines l 

min(l.vtime) + 5 = (min(l.vtime) + 1) + 4
и можно не вычислять min(..) по множеству записей, а взять min() из внешнего селекта и прибавить к нему 4? (пример можно придумать посложней и пореалистичней).

Но, вернемся к первому примеру. Итак, min(...) вычислен, его значение (скажем = 5) можно смело записывать в соответствующую внутреннюю переменную как текущее значение выходного поля MINVTIME. Т.к. текущее значение этого поля не изменится ни при каких обстоятельствах. Возможно по каким либо причинам (distinct, having) сервер и не выдаст всей строки, или выдаст строки в другом порядке. Но именно для этой строки MINVTIME будет равно именно 5, а не 10, 15, 128.
А раз известно имя поля, его тип и значение, то почему бы не разрешить использовать значение этого поля в выражениях, сравнениях и т.д.? И обращаться к нему по имени, как и к любому другому полю.
Причем агрегаты - это только частный случай. Можно было бы строить и выражения аля:

select TABLE.FIELD1,
(select ... ну очень крутой запрос ...) as FIELD2,
FIELD2 / 100,
FIELD2 + (select ...),
(select ... еще круче запрос...
where T3.HZ = FIELD2 + 5
)
from ...
group by FIELD2,
order by FIELD2,
having FIELD2 > 100;

Имея возможность строить такие запросы отпала бы надобность в части процедур. Тем более, что процедуру нужно помещать в базу, а запрос можно выполнить и на клиенте!

Мечты, мечты...

[kdv]

А раз известно имя поля, его тип и значение, то почему бы не разрешить использовать значение этого поля в выражениях, сравнениях и т.д.?

посмотри, что говорит на эту тему стандарт.

[Andy117]

посмотри, что говорит на эту тему стандарт.

Вот потому я и говорю об этой фиче в сослагательтом наклонении