Конструкция KEEP FIRST/LAST в SQL за 5 минут, ясно и понятно

добавлено: 02 окт 17
понравилось:0
просмотров: 508
комментов: 0

теги:

Автор: Myp3_u_K

Конструкция KEEP FIRST/LAST используется в SQL Oracle для вычисления значения, первой или последней записи в заданной подгруппе, отcортированной по некоторому признаку
она так же позволяет найти результат агрегатной функции по сгруппированным данным, если таких значений несколько

Проще понять это на примере
создадим таблицу курсов валют

CREATE TABLE prices
( 
 ticker VARCHAR2(3), -- валюта сокращ
 pdate DATE, -- дата 
 price FLOAT -- курс валюты
);

заполним таблицу тестовыми данными
за каждый день в таблице может быть несколько курсов заданной валюты


INSERT INTO prices( ticker, pdate, price) VALUES ('usd', DATE'2016-10-22', 55.11);
INSERT INTO prices( ticker, pdate, price) VALUES ('usd', DATE'2016-10-22', 56.11);
INSERT INTO prices( ticker, pdate, price) VALUES ('usd', DATE'2016-10-25', 57.11);
INSERT INTO prices( ticker, pdate, price) VALUES ('usd', DATE'2016-10-25', 57.22);
INSERT INTO prices( ticker, pdate, price) VALUES ('usd', DATE'2016-10-27', 58.11);
INSERT INTO prices( ticker, pdate, price) VALUES ('usd', DATE'2016-11-29', 57.11);

INSERT INTO prices( ticker, pdate, price) VALUES ('eur', DATE'2016-11-22', 65.11);
INSERT INTO prices( ticker, pdate, price) VALUES ('eur', DATE'2016-11-22', 65.12);
INSERT INTO prices( ticker, pdate, price) VALUES ('eur', DATE'2016-11-25', 67.11);
INSERT INTO prices( ticker, pdate, price) VALUES ('eur', DATE'2016-11-27', 68.11);

INSERT INTO prices( ticker, pdate, price) VALUES ('chf', DATE'2016-11-22', 88.11);
INSERT INTO prices( ticker, pdate, price) VALUES ('chf', DATE'2016-11-25', 88.33);
INSERT INTO prices( ticker, pdate, price) VALUES ('chf', DATE'2016-11-25', 89.33);
COMMIT;

-- обратите внимание, за каждую дату может быть несколько разных курсов одной и той же валюты
напишем запрос, который бы выбирал минимальное и максимальное значение курса каждой валюты за наибольшую дату
и за наименьшую , наиболее отдаленную во времени, дату

SELECT DISTINCT ticker
, (SELECT MIN(price) FROM prices p1 WHERE p1.pdate  = 
    (SELECT MIN(p3.pdate) FROM prices p3 WHERE p3.ticker = P.ticker) AND p1.ticker = P.ticker) minfirstprice
, (SELECT MAX(price) FROM prices p2 WHERE p2.pdate = 
    (SELECT MAX(p3.pdate) FROM prices p3 WHERE p3.ticker = P.ticker) AND p2.ticker = P.ticker) maxlastprice
FROM prices P ; 
-- минимальное и максимальное значение курса за наибольшую дату
-- получилось довольно сложно и громоздко, но результат правильный

chf 88,11 88,11 88,33 89,33 88,11 177,66
eur 65,11 65,12 68,11 68,11 130,23 68,11
usd 55,11 56,11 57,11 57,11 111,22 57,11


-- а теперь используем KEEP FIRST, KEEP LAST, посмотрите, насколько улучшился наш запрос
SELECT 
  ticker,
  min(price) KEEP (DENSE_RANK FIRST ORDER BY pdate) as minfirstprice,
  max(price) KEEP (DENSE_RANK FIRST ORDER BY pdate) as maxfirstprice,
  min(price) KEEP (DENSE_RANK LAST ORDER BY pdate) as minlastprice,
  max(price) KEEP (DENSE_RANK LAST ORDER BY pdate) as maxlastprice,
  sum(price) KEEP (DENSE_RANK FIRST ORDER BY pdate) as sumfirstprice,
  sum(price) KEEP (DENSE_RANK LAST ORDER BY pdate) as sumlastprice
FROM prices
GROUP BY ticker
ORDER BY ticker;


chf 88,11 88,11 88,33 89,33 88,11 177,66
eur 65,11 65,12 68,11 68,11 130,23 68,11
usd 55,11 56,11 57,11 57,11 111,22 57,11

немного подробнее о том , что мы здесь написали
-- функция sum, max, min находит сумму максимальное и минимальное значение из курсов валют за поcледнюю и первую дата в группе ticker
-- в конструкции KEEP FIRST , KEEP LAST мы можем использовать следующие агрегатные функции
MIN, MAX, SUM, AVG, COUNT, VARIANCE, STDDEV

-- конструкция KEEP (DENSE_RANK LAST ORDER BY pdate) означает , что мы осуществляем работу с последними LAST значениями, с сортировкой по полю PDATE, в свою очередь, DENSE_RANK FIRST ORDER BY pdate вернет агрегатной функции max(price) первые значения price отсортированные по PDATE

-- и еще плюс , использование KEEP FIRST LAST вместе с аналитической функцией
SELECT
  ticker,
  price,
  min(price) KEEP (DENSE_RANK FIRST ORDER BY pdate) OVER (PARTITION BY ticker) minfirstprice,
  mAX(price) KEEP (DENSE_RANK FIRST ORDER BY pdate) OVER (PARTITION BY ticker) mAXfirstprice,    
  max(price) KEEP (DENSE_RANK LAST ORDER BY pdate) OVER (PARTITION BY ticker) minlasttprice,
  mIN(price) KEEP (DENSE_RANK LAST ORDER BY pdate) OVER (PARTITION BY ticker) maxlastprice
FROM prices
ORDER BY PRICE;


-- разумеется, здесь так же можно использовать и другие агрегатные функции
MIN, MAX, SUM, AVG, COUNT, VARIANCE, STDDEV

usd 55,11 55,11 56,11 57,11 57,11
usd 56,11 55,11 56,11 57,11 57,11
usd 57,11 55,11 56,11 57,11 57,11
usd 57,11 55,11 56,11 57,11 57,11
usd 57,11 55,11 56,11 57,11 57,11
usd 57,22 55,11 56,11 57,11 57,11
usd 58,11 55,11 56,11 57,11 57,11
eur 65,11 65,11 65,12 68,11 68,11
eur 65,12 65,11 65,12 68,11 68,11
eur 67,11 65,11 65,12 68,11 68,11
eur 68,11 65,11 65,12 68,11 68,11
chf 88,11 88,11 88,11 89,33 88,33
chf 88,33 88,11 88,11 89,33 88,33
chf 89,33 88,11 88,11 89,33 88,33

Комментарии




Необходимо войти на сайт, чтобы оставлять комментарии