Применение оконных функций

Как мне не хватало нестрогого выполнения group by. Однако на просторах интернета удалось найти обход. Обход заключается в использовании оконных функций.

Оконная функция - функция агрегирования (sum, min, max …), которая выполняется для части множества.

Задача

Требуется вывести id свойства, определить действует оно или нет и iddoc документа, в котором устанавливалось свойство. Для этого будем использовать оконную функцию over().

Задаем таблицу и заполняем ее данными.

CREATE TABLE test.test_group_by
(
  iddoc DOUBLE PRECISION
, id_svo_vo DOUBLE PRECISION
, "date" TIMESTAMP
, is_active DOUBLE PRECISION
);
INSERT INTO test.test_group_by(iddoc, id_svo_vo, "date", is_active) VALUES ( 0, 1,'2012/01/01 00:00:00.000', 1);
INSERT INTO test.test_group_by(iddoc, id_svo_vo, "date", is_active) VALUES ( 0, 2,'2012/01/01 00:00:00.000', 1);
INSERT INTO test.test_group_by(iddoc, id_svo_vo, "date", is_active) VALUES ( 0, 3,'2012/01/01 00:00:00.000', 1);
INSERT INTO test.test_group_by(iddoc, id_svo_vo, "date", is_active) VALUES ( 1, 4,'2012/01/01 00:00:00.000', 1);
INSERT INTO test.test_group_by(iddoc, id_svo_vo, "date", is_active) VALUES ( 1, 5,'2012/01/01 00:00:00.000', 1);
INSERT INTO test.test_group_by(iddoc, id_svo_vo, "date", is_active) VALUES ( 2, 1,'2012/01/02 00:00:00.000', 0);
INSERT INTO test.test_group_by(iddoc, id_svo_vo, "date", is_active) VALUES ( 2, 4,'2012/01/02 00:00:00.000', 0);
INSERT INTO test.test_group_by(iddoc, id_svo_vo, "date", is_active) VALUES ( 2, 5,'2012/01/02 00:00:00.000', 0);
INSERT INTO test.test_group_by(iddoc, id_svo_vo, "date", is_active) VALUES ( 3, 1,'2013/01/01 00:00:00.000', 1);
INSERT INTO test.test_group_by(iddoc, id_svo_vo, "date", is_active) VALUES ( 4, 2,'2014/01/02 00:00:00.000', 0);
INSERT INTO test.test_group_by(iddoc, id_svo_vo, "date", is_active) VALUES ( 4, 3,'2014/01/02 00:00:00.000', 0);

 Далее выполняем скрипт, который выводит требуемые данные

SELECT *
FROM (
       SELECT
          iddoc,
          id_svo_vo,
          max("date") over(partition by id_svo_vo) as max_date, 
          date, 
          is_active
        FROM test.test_group_by
       ) as t1
WHERE max_date = date;

Функция агрегирования работает на множестве ключей id_svo_vo. В результате добавляется к каждой строке столбец с максимальной датой на множестве ключей id_svo_vo. В where max_date = date идет отбор необходимых данных, у которых максимальная дата на множестве совпадает с датой самого документа.

Красота да и только!

Ссылки на материалы:

1. Оконные функции http://postgresql.ru.net/manual/tutorial-window.html
2. Выражения, возвращающие одиночное значение http://postgresql.ru.net/manual/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS

• Вконтакте ()
• Google+
• Facebook ( )

• < Назад
• Вперёд >