SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

分析関数の衝撃

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

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

  • X ポスト
  • このエントリーをはてなブックマークに追加

ダウンロード SourceCode (2.2 KB)

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

  • X ポスト
  • このエントリーをはてなブックマークに追加

はじめに

 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のイメージ

会員登録無料すると、続きをお読みいただけます

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

次のページ
2. 前年と年商が同じ年度を求める

この記事は参考になりましたか?

  • X ポスト
  • このエントリーをはてなブックマークに追加
分析関数の衝撃連載記事一覧

もっと読む

この記事の著者

山岸 賢治(ヤマギシ ケンジ)

趣味が競技プログラミングなWebエンジニアで、OracleSQLパズルの運営者。AtCoderの最高レーティングは1204(水色)。

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/2693 2009/08/11 14:00

おすすめ

アクセスランキング

アクセスランキング

イベント

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング