prestoクエリサンプル：window関数内で集約関数を利用する

TreasureData by IDC Frontier presto version 0.152.3の場合。 ＊基本的に標準sqlに従っているので他のクエリにも応用可能なはず。

クエリ

あるサイトの日ごとの各ページのpv数データから、全期間でのpv数をページごとに出し、pv数が多い順にランキング付けして並べる。

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, count)
)

SELECT
  path,
  SUM(count) AS count,
  RANK() OVER(ORDER BY SUM(count) DESC) AS rank
FROM data
GROUP BY path
ORDER BY rank
;

結果

path	count	rank
/	300	1
/faq	200	2
/category	200	2
/category/technology	100	4

説明

• まず VALUES を使用してサンプルデータを用意した。この方法はTreasureDataでも推奨されている。

  Presto Query FAQs | Treasure Data

  ある日付 date にあるページ path のpv count がいくつだったか？というデータである。

• ページごとのpv数をだすのには GROUP BY path で SUM(count) とすればよい。

• ランキング付けするにはwindow関数の RANK を SUM(count) に対して使いたいが、それはそのまま RANK() OVER(ORDER BY SUM(count) DESC) とすればよい。

  6.15. Window Functions — Presto 0.158 Documentation

• このときポイントなのは、 GROUP BY に使うのは path だけでよいということである。 RANK()... は集約関数でないが、GROUP BY に含めなくてよい。

• そして以下のように RANK() OVER(ORDER BY path) とした場合でも、やはり RANK()... はGROUP BY に含めなくてよい。

WITH data AS (
...
),

SELECT
  path,
  SUM(count) AS count,
  RANK() OVER(ORDER BY path) AS rank
FROM data
GROUP BY path
ORDER BY rank
;

path	count	rank
/	300	1
/category	200	2
/category/technology	100	2
/faq	200	4

• window関数は集約も含めた他のコラムの処理のあとに行われるということなのだろう。