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
関数で書き換えてみます。まずは、テーブルのデータと、出力結果を考えます。
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
関数を使って、答えは下記となります。
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
は、下記のように解釈すると分かりやすいと思います。
order by prc_date -- prc_dateの昇順で、 Rows between -- 行の範囲は、 unbounded preceding -- 小さいほうは、際限なし and current row -- 大きいほうは、現在の行まで
SQLのイメージは、下記となります。
なお、frame_clause
を完全に省略した場合、デフォルトのRANGE UNBOUNDED PRECEDING
になります。これは、RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
と同じ意味で、ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
とも同じ意味なので下記のSQLでもいいです。
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が提示されています。
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
関数で書き換えてみます。まずは、テーブルのデータと、出力結果を考えます。
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では文法エラーになります。
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
関数を使って、答えは下記となります。
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
句をまとめて記述することもできます。
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では、例えば25日移動累計などを求める場合に、24回もLag
関数を記述する必要がありますので、別解として、下記の3つのSQLを紹介します。
自分の行までの累計から、3行前までの累計を引けばいいと考えたのが下記のSQLです。window
関数は、入れ子にできないので、インラインビューが必要になります。sum
関数は、bigInt
型の値を返しますので、Lag関数の第3引数で0::bigInt
として、0をbigInt
型にキャストしてます。PostgreSQL 8.4では、このようなキャストをしないと文法エラーになります。
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です。
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では文法エラーになりません。
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文書)
- 『8.14. 配列』(PostgreSQL 8.4.0文書)
- 『4.2.8. ウィンドウ関数呼び出し』(PostgreSQL 8.4.0文書)
PostgreSQLのマニュアルです。ウィンドウ関数に関する説明です。
PostgreSQLのマニュアルです。配列型に関する説明です。
PostgreSQLのマニュアルです。ウィンドウ関数のframe_clause
に関する説明です。