Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

PostgreSQLの分析関数の衝撃2
(Lag関数と累計と移動累計)

PostgreSQLの基本的なwindow関数の使用例2

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

ダウンロード SourceCode (2.2 KB)

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

目次

はじめに

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

 本稿では、『分析関数の衝撃2 (中編)』を、PostgreSQL8.4用にリニューアルした内容を扱います。

対象読者

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

 『自己結合の使い方』と 『相関サブクエリで行と行を比較する』に記載されているSQLをwindow関数を使って記述していきますので、『自己結合の使い方』と『相関サブクエリで行と行を比較する』を読まれてからのほうが理解しやすいと思います。

必要な環境

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

  • Oracle
  • DB2
  • SQL Server

1. 部分的に不一致なキーの検索

 まずは、部分的に不一致なキーを検索するSQLについてです。 『自己結合の使い方』では、以下の自己非等値結合を使うSQLが提示されています。

同じ家族だけど、住所が違うレコードを検索するSQL
SELECT DISTINCT A1.name, A1.address
  FROM Addresses A1,
       Addresses A2
 WHERE A1.family_id = A2.family_id
   AND A1.address <> A2.address ;

 これをwindow関数で書き換えてみます。まずは、少しデータを追加した「Addresses」テーブルのデータと出力結果を考えます。

Addresses
name family_id address
前田 義明 100 港区虎ノ門3-2-29
前田 由美 100 港区虎ノ門3-2-92
加藤 茶 200 新宿区西新宿2-8-1
加藤 勝 200 新宿区西新宿2-8-1
ホームズ 300 ベーカー街221B
ワトソン 400 ベーカー街221B
織田 信長 500 京都
織田 信忠 500 京都
織田 長益 500 京都
徳川 家康 600 関ヶ原
松平 忠吉 600 関ヶ原
徳川 秀忠 600 上田城
出力結果
name family_id address
前田 義明 100 港区虎ノ門3-2-29
前田 由美 100 港区虎ノ門3-2-92
徳川 家康 600 関ヶ原
松平 忠吉 600 関ヶ原
徳川 秀忠 600 上田城

 Oracleでは分析関数のcount関数で、distinctオプションが使用できますので、下記のSQLが使えましたが、PostgreSQL 8.4では文法エラーになります。

PostgreSQL8.4では文法エラー
select name,address
from (select name,address,
      count(distinct address) over(partition by address)
      as distinctAddressCount
        from Addresses) a
where distinctAddressCount > 1;

 答えとして、下記の4つのSQLを紹介します。

 『分析関数の衝撃6 (応用編)』の「1. 複数列のdistinctなcount」を応用し、逆ソートを使ってcount(distinct address) over(partition by family_id)を求める方法。

window関数で書き換えたSQL1
select name,address
from (select name,address,
      -1+dense_rank() over(partition by family_id order by address asc)
        +dense_rank() over(partition by family_id order by address desc) as cnt
        from Addresses) a
 where cnt > 1;

 同じfamily_idでaddressが2通り以上あったら、すなわち、最小値と最大値が異なれば出力対象になると考える方法。

window関数で書き換えたSQL2
select name,address
from (select name,address,
      max(address) over(partition by family_id) as MaxAddress,
      min(address) over(partition by family_id) as MinAddress
        from Addresses) a
where MaxAddress != MinAddress;

 PostgreSQL 8.4ではarray_agg関数という集合関数が追加されました。array_agg関数はwindow関数としても使えますので、array_agg関数で配列型にまとめる方法も使えます。

window関数で書き換えたSQL3
select name,address
from (select name,address,
      array_agg(address) over(partition by family_id) as aggAddress
        from Addresses) a
 where address != any(aggAddress);
window関数で書き換えたSQL4
select name,address
from (select name,address,
      array_agg(address) over(partition by family_id) as aggAddress
        from Addresses) a
where aggAddress[1] != any(aggAddress);

 SQLのイメージは下記となります。partition by family_idなので、family_idを赤線で区切ってます。

SQLのイメージ
SQLのイメージ

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

著者プロフィール

バックナンバー

連載:分析関数の衝撃

もっと読む

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