SHOEISHA iD

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

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

分析関数の衝撃

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

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

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

ダウンロード SourceCode (2.2 KB)

4. 累計を求める

 次に累計を求めるSQLです。『相関サブクエリで行と行を比較する』では、以下のSQLが提示されています。

累計を求める:ノイマン型再帰集合の利用
SELECT prc_date, A1.prc_amt,
      (SELECT SUM(prc_amt)
         FROM Accounts A2
        WHERE A1.prc_date >= A2.prc_date ) AS onhand_amt
  FROM Accounts A1
 ORDER BY prc_date;

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

Accounts
prc_date prc_amt
2006/10/26 12000
2006/10/28 2500
2006/10/31 -15000
2006/11/03 34000
2006/11/04 -5000
2006/11/06 7200
2006/11/11 11000
出力結果
prc_date prc_amt onhand_amt
2006/10/26 12,000 12,000
2006/10/28 2,500 14,500
2006/10/31 -15,000 -500
2006/11/03 34,000 33,500
2006/11/04 -5,000 28,500
2006/11/06 7,200 35,700
2006/11/11 11,000 46,700

 window関数のsum関数を使って、答えは下記となります。

window関数で書き換えたSQLその1
select prc_date,prc_amt,
sum(prc_amt) over(order by prc_date
                   Rows between unbounded preceding
                    and current row) as onhand_amt
  from Accounts
order by prc_date;

 window関数のsum関数で、frame_clauseという句を使用してます。frame_clauseは、下記のように解釈すると分かりやすいと思います。

frame_clauseの解釈
order by prc_date    -- prc_dateの昇順で、
 Rows between        -- 行の範囲は、
unbounded preceding  -- 小さいほうは、際限なし
  and current row    -- 大きいほうは、現在の行まで

 SQLのイメージは、下記となります。

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

 なお、frame_clauseを完全に省略した場合、デフォルトのRANGE UNBOUNDED PRECEDINGになります。これは、RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWと同じ意味で、ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWとも同じ意味なので下記のSQLでもいいです。

window関数で書き換えたSQLその2
select prc_date,prc_amt,
sum(prc_amt) over(order by prc_date) as onhand_amt
  from Accounts
order by prc_date;

5. 移動累計を求める

 次に移動累計を求めるSQLです。『相関サブクエリで行と行を比較する』では、以下のSQLが提示されています。

移動累計を求める その2:3行に満たない期間は無効扱い
SELECT prc_date, A1.prc_amt,
 (SELECT SUM(prc_amt)
    FROM Accounts A2
   WHERE A1.prc_date >= A2.prc_date
     AND (SELECT COUNT(*)
            FROM Accounts A3
           WHERE A3.prc_date 
             BETWEEN A2.prc_date AND A1.prc_date  ) <= 3
   HAVING  COUNT(*) =3) AS mvg_sum  --3行未満は非表示
  FROM  Accounts A1
 ORDER BY prc_date;

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

Accounts
prc_date prc_amt
2006/10/26 12000
2006/10/28 2500
2006/10/31 -15000
2006/11/03 34000
2006/11/04 -5000
2006/11/06 7200
2006/11/11 11000
出力結果
prc_date prc_amt onhand_amt
2006/10/26 12,000 null
2006/10/28 2,500 null
2006/10/31 -15,000 -500
2006/11/03 34,000 21,500
2006/11/04 -5,000 14,000
2006/11/06 7,200 36,200
2006/11/11 11,000 13,200

 OracleやDB2の分析関数では、Rows 2 Precedingといった指定ができますので、 下記のSQLが使えましたが、PostgreSQL 8.4では文法エラーになります。

PostgreSQL8.4では文法エラー
select prc_date,prc_amt,
case when count(*) over(order by prc_date) >= 3
     then sum(prc_amt) over(order by prc_date rows 2 Preceding)
     end as mvg_sum
  from Accounts;

 Lag関数を使って、答えは下記となります。

window関数で書き換えたSQLその1
select prc_date,prc_amt,
case when count(*) over(order by prc_date) >= 3
     then Lag(prc_amt,2) over(order by prc_date)
         +Lag(prc_amt,1) over(order by prc_date)
         +prc_amt end as mvg_sum
  from Accounts
order by prc_date;

 解説しますと、count(*) over(order by prc_date)でprc_dateの昇順にソートして何番目かを求めてます。それが3番目以降だったら、sum(prc_amt) over(order by prc_date rows 2 Preceding)の代用案として、Lag関数で前の行の値を取得して足し算を行い、3行の累計を求めてます。

 下記のSQLのようにover句をまとめて記述することもできます。

window関数で書き換えたSQLその2
select prc_date,prc_amt,
case when count(*) over W1 >= 3
     then Lag(prc_amt,2) over W1
         +Lag(prc_amt,1) over W1
         +prc_amt end as mvg_sum
  from Accounts
window W1 as (order by prc_date)
order by prc_date;

 SQLのイメージは、下記となります。

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

 上記のSQLでは、例えば25日移動累計などを求める場合に、24回もLag関数を記述する必要がありますので、別解として、下記の3つのSQLを紹介します。

 自分の行までの累計から、3行前までの累計を引けばいいと考えたのが下記のSQLです。window関数は、入れ子にできないので、インラインビューが必要になります。sum関数は、bigInt型の値を返しますので、Lag関数の第3引数で0::bigIntとして、0をbigInt型にキャストしてます。PostgreSQL 8.4では、このようなキャストをしないと文法エラーになります。

window関数で書き換えたSQLその3
select prc_date,prc_amt,
case when count(*) over(order by prc_date) >= 3
     then runSum - Lag(runSum,3,0::bigInt) over(order by prc_date)
     end as mvg_sum
from (select prc_date,prc_amt,
      sum(prc_amt) over(order by prc_date) as runSum
        from Accounts) a
order by prc_date;

 『MySQLで分析関数を模倣4 (完結編)』の「2. Rows指定のSum関数(preceding指定)」を応用したのが下記のSQLです。

相関サブクエリを使うSQL1
select prc_date,prc_amt,
(select sum(b.prc_amt)
   from Accounts b
  where (select count(*) from Accounts c
          where c.prc_date between b.prc_date
                               and a.prc_date)
        between 1 and 2+1
 having count(*) = 3) as mvg_sum
  from Accounts a
order by prc_date;

 上記のSQLをLimit句を使って変形したのが、下記のSQLです。select句での相関サブクエリにおいて、from句でのインラインビューのwhere句で大本の列を使うと、MySQL 5.1.28では文法エラーですが、PostgreSQL 8.4では文法エラーになりません。

相関サブクエリを使うSQL2
select prc_date,prc_amt,
(select sum(c.prc_amt)
   from (select prc_amt
           from Accounts b
          where b.prc_date <= a.prc_date
         order by b.prc_date desc Limit 3) c
 having count(*) = 3) as mvg_sum
  from Accounts a
order by prc_date;

最後に

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

参考資料

  • 9.19. ウィンドウ関数』(PostgreSQL 8.4.0文書)
  • PostgreSQLのマニュアルです。ウィンドウ関数に関する説明です。

  • 8.14. 配列』(PostgreSQL 8.4.0文書)
  • PostgreSQLのマニュアルです。配列型に関する説明です。

  • 4.2.8. ウィンドウ関数呼び出し』(PostgreSQL 8.4.0文書)
  • PostgreSQLのマニュアルです。ウィンドウ関数のframe_clauseに関する説明です。

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

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

もっと読む

この記事の著者

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

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

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

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

この記事をシェア

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

おすすめ

アクセスランキング

アクセスランキング

イベント

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

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

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

メールバックナンバー

アクセスランキング

アクセスランキング