Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

PostgreSQLの分析関数の衝撃6
(window関数の応用例)

複数列のdistinctなcountなど

  • ブックマーク
  • LINEで送る
  • このエントリーをはてなブックマークに追加
2010/01/20 14:00

ダウンロード SourceCode (1.6 KB)

 2009年7月に正式リリースされたPostgreSQL 8.4で、分析関数(window関数)がサポートされました。本稿では、『分析関数の衝撃6(応用編)』を、PostgreSQL8.4用にリニューアルした内容を扱います。

目次

はじめに

 2009年7月に正式リリースされたPostgreSQL 8.4で、分析関数(window関数)がサポートされました。本連載では、分析関数の衝撃シリーズをPostgreSQL用にアレンジした内容と、OracleやDB2の分析関数をPostgreSQL 8.4で代用する方法を扱います。

 本稿では、『分析関数の衝撃6(応用編)』を、PostgreSQL 8.4用にリニューアルした内容を扱います。

対象読者

  • PostgreSQLでwindow関数を使ってみたい方
  • 分析関数の理解を深めたい方

必要な環境

 本稿で扱うSQLは、PostgreSQL 8.4 beta 2で動作確認しました。その他、次の環境でも応用が可能です。

  • Oracle
  • DB2
  • SQL Server

1. 複数列のdistinctなcount

 最初は、複数列のdistinctcountを求めるSQLです。まずは、テーブルのデータと、出力結果を考えます。

IDTable
ID ColA ColB
1 ab c
2 a bc
3 a b
4 a b
5 a b

 ColA,ColBの組み合わせのdistinctcountを求めます。(ColA,ColB) = (ab,c),(a,bc),(a,b)で3通りの組み合わせがあるので、これをdisCntとして下記の形で出力します。

出力結果
ID ColA ColB disCnt
1 ab c 3
2 a bc 3
3 a b 3
4 a b 3
5 a b 3

 集約関数で、複数列のdistinctcountを取得する方法として、行コンストラクタを使った下記の方法があります。

集約関数で、複数列のdistinctなcount
with tmp(ID,Val1,Val2) as(
values('AA',1,1),
      ('AA',1,1),
      ('AA',2,2),
      ('BB',1,2),
      ('BB',2,1),
      ('BB',2,1),
      ('BB',3,1))
select ID,count(distinct Row(Val1,Val2)) as disCnt
  from tmp
group by ID;
出力結果
ID disCnt
AA 2
BB 3

 上記をふまえて、window関数のcount関数で同じようにdistinctオプションを使用すればいいように思えますが、 PostgreSQL 8.4では、window関数でのdistinctオプションが実装されていないので、文法エラーになります。

PostgreSQL 8.4では文法エラー
select ID,ColA,ColB,
count(distinct Row(ColA,ColB)) over() as disCnt
  from IDTable;

ERROR:  DISTINCT is not implemented for window functions
LINE 2: count(distinct Row(ColA,ColB)) over() as disCnt

 答えは、下記の逆ソートを使う方法となります。

逆ソートを使う方法
select ID,ColA,ColB,
-1+dense_rank() over(order by ColA asc ,ColB asc )
  +dense_rank() over(order by ColA desc,ColB desc) as disCnt
  from IDTable
order by ID;

 order by ColA asc,ColB ascでのascは省略可能なのですが、descと対比させたほうが分かりやすいと考え、省略せずに記述しています。

 下記の数式をふまえてdense_rank関数を使っています。-1を加算しているのは、自分を2回数えているからです。

ふまえた数式
-1 + 重複を除いた自分以上の組み合わせの数
   + 重複を除いた自分以下の組み合わせの数
= 重複を除いた組み合わせの数

  • ブックマーク
  • LINEで送る
  • このエントリーをはてなブックマークに追加

著者プロフィール

バックナンバー

連載:分析関数の衝撃

もっと読む

All contents copyright © 2005-2019 Shoeisha Co., Ltd. All rights reserved. ver.1.5