なるテク

なるほど！なテクニックや技術をまとめるブログ

sqlのwindow関数のframe

window関数のframeについてまとめたい。 なおwindow関数はpostgresql, prestoでは利用可能で、MySQLでは利用できない。

今回使ってみるのはpresto 0.152である。

WITH data AS (
SELECT *
FROM (VALUES
('2016-12-01', '/',                    100),
('2016-12-01', '/category',            100),
('2016-12-01', '/faq',                 100),
('2016-12-02', '/',                    100),
('2016-12-02', '/faq',                 100),
('2016-12-03', '/',                    100),
('2016-12-03', '/category',            100),
('2016-12-04', '/category/technology', 100)
) AS temp (date, path, pv)
)

SELECT
  date,
  path,
  pv,
  SUM(pv) OVER(PARTITION BY path) AS "ページごとのpv合計",
  SUM(pv) OVER(PARTITION BY path ORDER BY date) AS "ページごとのその日までのpv合計",
  SUM(pv) OVER(PARTITION BY path ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS "ページごとの前日までのpv合計"
FROM data
ORDER BY date, path
;
date path pv ページごとのpv合計 ページごとのその日までのpv合計 ページごとの前日までのpv合計
2016-12-01 / 100 300 100
2016-12-01 /category 100 200 100
2016-12-01 /faq 100 200 100
2016-12-02 / 100 300 200 100
2016-12-02 /faq 100 200 200 100
2016-12-03 / 100 300 300 200
2016-12-03 /category 100 200 200 100
2016-12-04 /category/technology 100 100 100
  • frameの設定には ROWS RANGE の二種類ある。

参考リンク