[PostgreSQL] 分析関数を使ったサブクエリでできるだけローコストにCOUNT(DISTINCT field) OVER()をする #PostgreSQL

PostgreSQL9.5.3 以前（確認時点）ではCOUNT(DISTINCT field) OVER(...)は実装されていません。
ですが代替方法は存在するので今回はその共有です。

今回使用するテーブルのイメージ

都道府県名	公園名	訪問者IP
東京都	水元公園	1.1.1.1
東京都	水元公園	1.1.1.1
東京都	水元公園	2.2.2.2
東京都	水元公園	3.3.3.3
東京都	日比谷公園	4.4.4.4
東京都	日比谷公園	4.3.2.1
神奈川	山下公園	10.11.1.1
神奈川	山下公園	12.11.123.1
神奈川	山下公園	10.11.1.1
神奈川	七沢森公園	1.1.1.1
神奈川	七沢森公園	11.23.45.67
神奈川	七沢森公園	11.23.45.67
神奈川	七沢森公園	12.1.1.1

集計結果として取得したいこと
都道府県・公園名・訪問者IPをすべて表示し、右列に集計結果を表示してほしい。
集計条件は

県別訪問者数
都道府県別公園別訪問者数
都道府県別ユニーク訪問者数
公園別ユニーク訪問者数
ユニーク訪問者数

です。
1と2だけならWindow関数のSUM() OVER()で取得可能です。
3からが普通に考えるとCOUNT DISTINCTが必要になります。
が、残念なことにPostgreSQLではWindow関数でのDISTINCTが実装されていません。

その解決策としてLAG関数を使います。
実際のSQLは下記になります。

PostgreSQLCountDistinctInWindowFunction.sql

-- Window関数でCOUNT(DISTINCT field) OVER()を擬似的に実現する
SELECT
  都道府県名
  , 公園名
  , "訪問者IP"
  , 県別訪問者数
  , 都道府県別公園別訪問者数
  , SUM(都道府県別ユニーク訪問者フラグ) OVER(PARTITION BY 都道府県名 ORDER BY 都道府県名) AS 都道府県別ユニーク訪問者数
  , SUM(公園別ユニーク訪問者フラグ) OVER(PARTITION BY 公園名 ORDER BY 公園名) AS 公園別ユニーク訪問者数
  , SUM(ユニーク訪問者フラグ) OVER() AS ユニーク訪問者数
FROM
(
SELECT 
  都道府県名
  , 公園名
  , "訪問者IP"
  , COUNT(*) OVER(PARTITION BY 都道府県名 ORDER BY 都道府県名) AS 県別訪問者数
  , COUNT(*) OVER(PARTITION BY 都道府県名, 公園名 ORDER BY 都道府県名, 公園名) AS 都道府県別公園別訪問者数
  , CASE WHEN "訪問者IP" = LAG("訪問者IP", 1) OVER(PARTITION BY 都道府県名 ORDER BY 都道府県名, "訪問者IP") THEN 0 ELSE 1 END AS 都道府県別ユニーク訪問者フラグ
  , CASE WHEN "訪問者IP" = LAG("訪問者IP", 1) OVER(PARTITION BY 公園名 ORDER BY 公園名, "訪問者IP") THEN 0 ELSE 1 END AS 公園別ユニーク訪問者フラグ
  , CASE WHEN "訪問者IP" = LAG("訪問者IP", 1) OVER(ORDER BY "訪問者IP") THEN 0 ELSE 1 END ユニーク訪問者フラグ
FROM
  test
ORDER BY
  都道府県名
  , 公園名
) AS 集計基本結果セット
ORDER BY
  都道府県名
  , 公園名

着想ポイントは
直前の行が同じ値だったら0違えば1を立てて、そのあとでSUMったらOKじゃね？
です。
LAG関数利用行の思考を分解します。

CASE の要件は "訪問者IP"が直前行と同じであれば 0 そうでなければ 1を返してほしい
データが指定のソート順でない場合、直前の行が正しく判断できないので、OVER()関数内でグループ化すべき列をPARTITION BY で、LAGが誤集計を行わないようにORDER BYを指定しています。
この二つをうまく組み合わせてWindow関数を使った状態でDISTINCTの内部処理風なことをFromサブクエリに実現させます
サブクエリで取得した1と0を親クエリでSUMって合計を算出という流れです。

直感的でないのであんまり美しいとは言い難いですが、私はSQLが好きなので・・・。