分析関数の衝撃

PostgreSQLの分析関数の衝撃2
（Lag関数と累計と移動累計）

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

山岸賢治[著]

2009/08/11 14:00

ポスト

ダウンロード 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のイメージ

　なお、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では、例えば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;