Использование предложения Partition by с аналитическими функциями

В оракловых аналитических функциях можно использовать предложение partition by для группировки данных возвращаемых SQL-select запросом. Именно для данных в этих группах будет применена аналитическая функция. Создадим тестовую таблицу для демонстрации примера:

CREATE TABLE test_month (
  val       number,
  dt        date
);
Теперь заполним таблицу некоторым количеством данных — парами из цифр и дат:
ALTER session SET nls_date_format = 'DD.MM.YYYY';
 
INSERT INTO test_month (val,dt) VALUES (18,'28.08.2000');
INSERT INTO test_month (val,dt) VALUES (19,'02.08.2000');
INSERT INTO test_month (val,dt) VALUES (22,'27.09.2000');
INSERT INTO test_month (val,dt) VALUES (23,'04.09.2000');
INSERT INTO test_month (val,dt) VALUES (20,'12.08.2000');
INSERT INTO test_month (val,dt) VALUES (24,'15.09.2000');
INSERT INTO test_month (val,dt) VALUES (19,'27.07.2000');
INSERT INTO test_month (val,dt) VALUES (18,'01.07.2000');
INSERT INTO test_month (val,dt) VALUES (21,'26.07.2000');
INSERT INTO test_month (val,dt) VALUES (24,'03.06.2000');
INSERT INTO test_month (val,dt) VALUES (22,'11.07.2000');
INSERT INTO test_month (val,dt) VALUES (21,'14.06.2000');
Теперь создадим вьюху/представление которое будет иметь флаг указывающий на наибольшую/последнюю дату в месяце. Под последней датой имеем ввиду не 30.06.2000 для примера, а 14.06.2000 — последняя дата в июне, присутствующая в нашей таблице. Для реализации задуманного используем аналитическую функцию max вместе с предложением partition by. C помощью partition by сгруппируем данные возвращаемые запросом по месяцам, а функцию max используем для нахождения максимальной даты в каждой группе:
CREATE VIEW test_last_of_month AS
     SELECT val, dt, (case when dt=max_dt then 'Y' else 'N' end) last_dt
       FROM (SELECT val, dt, max(dt) over (partition BY to_char(dt,'YYYY.MM')) max_dt
	       FROM test_month);
 
SELECT * FROM test_last_of_month;
Запрос вернет следующее:
        24 03.06.2000 N
        21 14.06.2000 Y
         9 27.07.2000 Y
        18 01.07.2000 N
        22 11.07.2000 N
        21 26.07.2000 N
         8 28.08.2000 Y
        19 02.08.2000 N
        20 12.08.2000 N
         2 27.09.2000 Y
        23 04.09.2000 N
        24 15.09.2000 N

Источник: http://adp-gmbh.ch/ora/sql/analytical/partition_by.html

Также про использование предложения Partition by можно почитать в записи о сортировке по одному полю в таблице из нескольких столбцов.

Оставить комментарий