помогите с запросом ...

[korotkov]

Имеется таблица истории движения оборудования (FireBird 1.52).

create table tblPoints
(
ID integer not null,
DateTime timestamp not null,
IDObject integer default 0,
IDServer integer default 0,
IDClient integer default 0,
IDGame integer default 0,
IDPosition integer default 0,
State varchar(1) default 0,
IDUser integer,
constraint PK_ID_Point primary key (ID, DateTime)
);

Требуется выбрать все IDGame,
- где State = 2 у (select first 1 * from tblPoints where DateTime <= TStart)
- где DateTime в диапазоне дат между DTStart и DTEnd and State > 0

Иными словами:
выбрать все активные IDGame ...,
т.е. которые были активированы перед началом указанного диапазона дат
и состояние их не менялось (последняя запись <= TStart and State = 2) ,
а также с которыми происходили события в указанном диапазоне дат (имеются записи State > 0),
т.е. активные в заданном временном промежутке (и не все время тоже), если имеется таблица истории включения и выключения точек.

Вызывает затруднение выборка:
выбрать для всех IDGame посление перед DTStart записи, у которых State = 2

а также как эти два условия (см. выше) соединить в один запрос.

Заранее спасибо.

Сергей

[kdv]

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

для начала контр-вопрос - почему у этой таблицы первичный ключ состоит из идентификатора и даты? Идентификатор (ID) что, ПОВТОРЯЕТСЯ?

[Merlin]

для начала контр-вопрос - почему у этой таблицы первичный ключ состоит из идентификатора и даты? Идентификатор (ID) что, ПОВТОРЯЕТСЯ?

Дим, это архив. Или периодический справочник. Правда PK по любому на таймштапе базировать не след - рано или поздно наступишь на изменения, регистрируемые в одну и ту же миллисекунду. Я лет 8 назад, когда сделал такую ошибку, наступил на 4-ый день. ID у архива должен быть свой сплошной, ID архивируемой сущности - неуникальный индекс, возможно, FK на неё, таймштамп - отдельный атрибут, если часто фигурирует в запросах - индексированный. Да, эт я не тебе объясняю А то, что вопрос сумбурный, с упоминанием поля не то не упомянутого, не то из другой таблицы, да с какой-то запуткой в голове насчёт того, что такое поле Status - действительно лишает возможности что-то вразумительное сказать без повышения напряжения на входном каскаде ТЛ

[sag]

Вызывает затруднение выборка:
выбрать для всех IDGame посление перед DTStart записи, у которых State = 2

а также как эти два условия (см. выше) соединить в один запрос.

Задачу можно решить и запросом с доп.подзапросом(exists) в where-разделе, можно решить и через хп. Идея обоих вариантов примерно такая:

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

create procedure p(DTStart .., DTEnd ...)
returns (P_IDGame ...) as
begin
  for select distinct IDGame 
      from tblPoints
      where DateTime>=:DTStart and DateTime<=:DTEnd
      and State > 0
      into :P_IDGame
  do
  begin
    if (exists(select 1 IDGame 
      from tblPoints
      where DateTime<:DTStart
      and State = 2
      and IDGame=:P_IDGame)) 
    then suspend;
  end
end

Какой из запросов помещать в "for select", а какой в "exists" (или в основной запрос и в подзапрос соответственно в случае решения задачи не через хп, а через запрос), какие индесы тебе нужны - это все определять тебе.

Можно эту заджачу решить и одиночным запросом с дистинктом и обычным джойном (без всячекских подзапросов), синтаксически (с точки зрения sql) это будет выглядеть стройно и правильно, но по производительности на приличных объемах будет уступать предыдущим вариантам, если ты вообще дождешься выполнения этого запроса.

[korotkov]

Спасибо sag за направление, вот что требовалось:

create procedure p(DTStart timestamp, DTEnd timestamp)
returns (P_IDGame integer) as
begin
for
select distinct IDGame from tblPoints
where DateTime <= TEnd and State = 2
into _IDGame
do
begin
if(not exists(select first 1 IDGame from tblPoints
where DateTime<=:DTStart
and State = 3
and IDGame=:P_IDGame
order by DateTime desc))
then suspend;
end
end

Как бы оптимизировать эту конструкцию так , чтобы в первой части IDGame выбирались только по первым ближайшим DateTime для каждого IDGame, а не все <=:DTStart

Т.е. выбрать ПОСЛЕДНИЕ записи для каждого IDGame у которых State=2 and DateTime<=:DTEnd и IDGame у которых в диапазоне DTStart DTEnd имеется запись с State=3, т.е. последняя запись State=3

[korotkov]

Так читабельнее

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

create procedure p(DTStart timestamp, DTEnd timestamp)
returns (P_IDGame integer) as
begin 
  for
    select distinct IDGame from tblPoints
    where DateTime <= :DTEnd and State = 2
    into :P_IDGame
  do
    begin
      if(not exists(select first 1 IDGame from tblPoints
                    where DateTime<=:DTStart
                    and State = 3
                    and IDGame=:P_IDGame
                    order by DateTime desc))
      then suspend;
    end
end

[sag]

Как бы оптимизировать эту конструкцию так , чтобы в первой части IDGame выбирались только по первым ближайшим DateTime для каждого IDGame, а не все <=:DTStart

А диапазон-то куда делся? В твоей хп только "<=:DTStart" вижу. Тяжело советовать не понимая самой задачи.
Я тебе пример написал, надеясь на следущее:
в основном запросе (фор_селекте) обрабатываем с дистинктом сам диапазон "DateTime>=:DTStart and DateTime<=:DTEnd ", конечно если в твоем случае количество записей в рассматриваемом диапазоне гораздо меньше числа записей по условию "<=:DTStart"; тут может быть будет наиболее эффективно пустить этот запрос по индексу на поле "DateTime". А затем уже полученные идентификаторы проверить на экзист-прдзапрос с "<=:DTStart".

Т.е. выбрать ПОСЛЕДНИЕ записи для каждого IDGame у которых State=2 and DateTime<=:DTEnd и IDGame у которых в диапазоне DTStart DTEnd имеется запись с State=3, т.е. последняя запись State=3

Тут вспомнились собтвенные творения похожего направления. Специфика была в том, что данные в таблицу вставлялись не со скоростью стрельбы корабельной пушечки ОКА-300, но данных было прилично - сотни тысяч записей. В таблице было два поля с временем: "дата_события" и "дата_следующего_события". При вставке записи "дата_следующего_события" устанавливалась в null, и апдейтилась "дата_следующего_события" записи с предыдущим состоянием объекта. В этом случае похожий поиск нужных записей (типа "последнее событие до диапазона-параметра") отсекалось условием так

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

...
where "дата_события"<:DTEnd
and "дата_следующего_события">=:DTStart 

или

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

...
where "дата_события"<:DTEnd
and "дата_следующего_события" is null

в зависимости от нужного результата

[korotkov]

А диапазон-то куда делся? В твоей хп только "<=:DTStart" вижу.

Диапазон на месте:

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

...
    where DateTime <= :DTEnd and State = 2 
    into :P_IDGame 

...
                    where DateTime<=:DTStart 
                    and State = 3 

Т.е. я вывернул запрос:
выбрал ВСЕ астивные IDGame, а затем оставил только, те у которых не было последней записи с удалением до начала диапазона.

Тяжело советовать не понимая самой задачи.

Таким образом получил IDGame активные в диапазоне дат.

Но тут вот какая загвоздка:
мне также нужно знать ID этих записей из tblPoints, а с distinct мне это узнать не удастся! Как вместе с уже полученными IDGame получить ID записей tblPoints?

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

create table tblPoints
 (
    ID integer not null,
    DateTime timestamp,
    IDObject integer default 0,
    IDServer integer default 0,
    IDClient integer default 0,
    IDGame integer default 0,
    IDPosition integer default 0,
    State varchar(1) default 0,
    IDUser integer,
    constraint PK_ID_Point primary key (ID)
  );

[Merlin]

Чё-т сильно вникать влом, но имхо там основной запрос должен быть с мах-ом по дате и группировкой по ID, а не distinct.

[sag]

А диапазон-то куда делся? В твоей хп только "<=:DTStart" вижу.

Диапазон на месте:

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

...
    where DateTime <= :DTEnd and State = 2 
    into :P_IDGame 

...
                    where DateTime<=:DTStart 
                    and State = 3 

Он у тебя "размазался" по осн.запросу и подзапросу, я имел в виду, что возможно эффективнее основной (верхний) запрос проверять на принадлежность диапазону, чтобы сузить поиск.

Таким образом получил IDGame активные в диапазоне дат.
Но тут вот какая загвоздка:
мне также нужно знать ID этих записей из tblPoints, а с distinct мне это узнать не удастся! Как вместе с уже полученными IDGame получить ID записей tblPoints?

Тогда не distinct, а group:

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

select IDGame, max(ID) from tblPoints 
    where DateTime <= :DTEnd and State = 2 
    group by 1
    into :P_IDGame, :P_ID
...

если ID в твоей системе выдаются последовательно.

Идея с твумя датами тебе не подошла?

[korotkov]

если ID в твоей системе выдаются последовательно.

не последовательно, но имеется дата события, потому max(DateTime)

Идея с твумя датами тебе не подошла?

Она чуток не про то ...

Ситуация такая:
Имеется таблица точек с установленным оборудованием. Точка с оборудованием может находиться в нескольких состояниях, для простоты: вкл. и выкл. При изменении состояния данные заносятся в tblPoints. Требуется выбрать точки с оборудованием, которые находились в состоянии Вкл. в диапазоне дат DTStart DTEnd, а затем и данные для них из других таблиц.

Как включенные до диапазона, но не выключенные, так и включенные в диапазоне дат. Фух, кажется сформулировал!

[sag]

Идея с твумя датами тебе не подошла?

Она чуток не про то ...

Скорее всего я непонятно изъяснился. Вроде как должно подойти. Тут идея нагрузить инсерт при вставке записи с событием, но при этом катастрофически разгрузить селект. Если есть интерес, но понятно - спрашивай.

[korotkov]

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

Не хочется запутывать логику приложения, она и сама запутается

И так работает тоже:

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

create procedure SP_GetPoints(IDObject integer, DTStart timestamp, DTEnd timestamp, SActive integer, SDeleted integer)
returns (DateTime timestamp, IDGame integer) as
begin
  for
    select IDGame, max(DateTime) from tblPoints
      where IDObject = :IDObject and
            DateTime <= :DTEnd and
            State = :SActive
      group by 1
      into :IDGame, :DateTime
  do
    begin
      if(not exists(select first 1 IDGame from tblPoints
                    where IDObject = :IDObject and
                          DateTime <= :DTStart and
                          State = :SDeleted and
                          IDGame = :IDGame
                    order by DateTime desc)) then suspend;
    end
end

но немогу выбрать остальные ID записи В принципе по имеющимся данным (IDGame, DateTime, State) можно сделать поиск, но это как-то через одно место.

[sag]

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

Не хочется запутывать логику приложения, она и сама запутается

Хозяинбарин. Уговаривать не буду.
Совет: данных в таблицу накидай побольше и позапускай свой окончательный вариант.