Сложный SQL запрос PostgreSQL

[SilverFish]

Добрый день.
Устройство присылает сигналы в три колонки T, H, P. При первом запуске приходят все три значения. Пример значений:
24.18      99.93    21.22
в четвертой колонке время timestamp without time zone NOT NULL
Если показания в устройстве не изменились например по показателю H и P, то устройство эти данные не пришлет и в базе появиться запись, к примеру такая
30.15   NULL  NULL
30.15 появилось т.к. это отличное значение от предыдущего.
Реально в устройстве показания теперь такие:
30.15   99.93    21.22
Но поскольку показания H и P не изменились они не были присланы.

Итак, в базе могут быть такие строки:
25.18   99.93    24.22
NULL    22.16   NULL
24.17   99.99  22.1
NULL   NULL   14.16
NULL   NULL   NULL
Значит реальные свежие данные такие:
24.17    99.99   14.16
Мне нужно сделать запрос, чтобы выбрать показание с самым свежим временем и если в одной из колонок NULL то ”спустить сверху” самое последние свежее показание.
Как такое можно сделать?

[SilverFish]

Пришла к выводу, что наверное лучше всего придумать тригер который вместо NULL вставлял бы предудущее не пустое значение.

[gen]

В случае "сложного запроса" пробуем разбить его на несколько "простых". Итак нам надо: 1 - найти последнее время, тут все просто select max(time_field) from table1; 2 - найти последнее не пустое значение t - select t from table1 t1 where time_field=(select max(time_field) from table1 where t is not null); 3,4 - повторить так же для h и p. Затем объединяем все это в один запрос:

Код

SELECT MAX(time_field)
,(SELECT t FROM table1 t1 WHERE time_field=(SELECT MAX(time_field) FROM table1 WHERE t IS NOT NULL)) AS t
,(SELECT h FROM table1 t2 WHERE time_field=(SELECT MAX(time_field) FROM table1 WHERE h IS NOT NULL)) AS h
,(SELECT p FROM table1 t3 WHERE time_field=(SELECT MAX(time_field) FROM table1 WHERE p IS NOT NULL)) AS p
 FROM table1
 

[SilverFish]

Огромное спасибо!

P.S.
По заданию мне надо два запроса.
1. Первый благодаря вам готов.  
2. Второй запрос попросили вчера такой же но за 7 полных дней без NULL. Существует ли возможность к запросу в этой теме добавить Ваш запрос из этой:
ссылка на тему

Если нет, то есть ли возможность объединить эти сложные запросы в како-той триггер, который бы в NULL значения ставил бы реальные? Тогда в базе вообще не будет пустых ячеек... Что посоветует как быть?

[gen]

Добавить выборку по периоду наверное можно, но там запрос будет далеко не тривиальный, т.к. надо учесть что в пределы периода могут попасть только "пустые" значения, а вытянуть значение за пределами периода будет не просто. Может что подскажут гуру по SQL.  По поводу вставки не нулевых значений в триггере - это где-то так

Код

CREATE TRIGGER table1_tr
  BEFORE INSERT 
  ON public.table1 FOR EACH ROW 
  EXECUTE PROCEDURE public.f_insert();

Код

CREATE OR REPLACE FUNCTION public.f_insert (
)
RETURNS TRIGGER AS
$body$
BEGIN
 
IF NEW.t IS NULL THEN 
BEGIN 
 SELECT t INTO NEW.t FROM table1 WHERE time_field=(SELECT MAX(time_field) FROM table1 WHERE time_fieldNEW.time_field AND t IS NOT NULL);
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
  --не найдено значения t, что-то предпринять или будет null;
 END; 
END IF;
IF NEW.h IS NULL THEN
 BEGIN 
 SELECT h INTO NEW.h FROM table1 WHERE time_field=(SELECT MAX(time_field) FROM table1 WHERE time_fieldNEW.time_field AND h IS NOT NULL);
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
  --не найдено значения h, что-то предпринять или будет null;
 END;  
END IF;
IF NEW.p IS NULL THEN 
BEGIN
 SELECT p INTO NEW.p FROM table1 WHERE time_field=(SELECT MAX(time_field) FROM table1 WHERE time_fieldNEW.time_field AND p IS NOT NULL);
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
  --не найдено значения h, что-то предпринять или будет null;
 END;   
END IF;
 
RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

тут триггер только на Insert, если надо учесть и возможность правки, то добавить событие Update.

[SilverFish]

Огромное спасибо!

Надо что-то подправить, не соображу что. Тест показал следующее:
смотрю колонку температура к примеру...
Пришло два значения для двух устройств:
23.75 и 23.81 в базе все ОК
Затем оба устройства прислали NULL и в базу почему-то записалось 23.75 для двух записей.
Должно быть снова 23.75 и 23.81
Суть происходящего на рисунке. Подскажите, пожалуйста, что мне подправить?

[OVE]

Думаю, что в запрос нужно добавить id устройства

Код:

...
 SELECT t INTO NEW.t FROM table1 WHERE time_field=(SELECT MAX(time_field) FROM table1 WHERE time_field AND t IS NOT NULL AND id=NEW.id) AND id=NEW.id;
...

И так для трех полей сделать.

[SilverFish]

Большое спасибо, все работает как швейцарские часы!

P.S.
Два вопроса

SELECT t INTO NEW.t FROM table1 WHERE time_field=(SELECT MAX(time_field) FROM table1 WHERE time_field AND t IS NOT NULL AND id=NEW.id) AND id=NEW.id;

Не совсем понимаю, зачем дублировать AND id=NEW.id если оно есть во внутреннем SELECT?

Что делает модификатор public /public.f_insert/?
Я когда создаю триггер и функцию в pgAdmin то этот модификатор исчезает из их объявления после создания триггера и функции.
PostgrSQL 8.3

[OVE]

Внутренний Select должен вернуть максимальное время записи с ненулевым параметром именно для конкретного прибора.
Если убрать AND id=NEW.id из внешнего Select, то при совпадении максимального времени для двух приборов может быть выбрана не та запись (т.к. их будет возвращено 2 штуки).
Насчет модификатора - ничего подсказать не могу.

[SilverFish]

Большое спасибо!!!