ITコーディネータのシュウです。

4月も終盤を迎え、もうすぐゴールデンウイークですね。
先日、埼玉県の埼玉県比企郡滑川町にある、国営武蔵丘陵森林公園に行ってきました。長年埼玉にいながら、実は行ったことがなかったのですが、東京ドーム65個分の広さがあり、雑木林にはたくさんの木があって、ストレス解消、森林浴にはもってこいの場所だと思いました。上の写真はハーブガーデンというハーブがいろいろと植えられているところの写真です。
下の写真は、ネモフィラという花が咲いている花畑です。

日本には17個くらいの国営の公園があるのですが、ここが一番最初に整備されたところだそうです。
季節によっていろいろと見れる花もあり、サイクリングコースや子供が遊べるキッズコーナー、日本一大きなエアートランポリンなど、いろいろと楽しめそうです。ゆっくり歩いたので、一部しか回れませんでしたが、今度また家族で行こうかと思いました。
埼玉にも、探せばいろんないいところがありますね。今まで忙しさにかまけて行かなかったのが本当に残念！
子供が小さいときに、もっと自然と触れ合う機会を持つようにすればよかったね、と妻と話をすることが増えた今日この頃です。

＜本日の題材＞
OVER句（Oracle）

前回、SQL Serverに関して、OVER句のROWSというものを題材に取り上げてみましたが、Oracleでも同様のことはできるのか？ということを調べてみたところ、以前からできるようなので、今回はOracleで確認してみたいと思います。

OracleでもOVER句と一緒に以下のような句が使用できます。

前後の行の値を取得する
LAG
--- 前の行
LEAD
--- 後の行

前回の例でも見た移動平均や移動累計の取得
ROWS n PRECEDING
--- 現在の行からn行前を含めて対象とする

最初の行、最後の行、n番目の行
FIRST_VALUE
--- パーティションの最初の行
LAST_VALUE
--- パーティションの最後の行
NTH_VALUE
--- パーティションのN番目の行

※上記で、IGNORE NULLS という句を付けると、NULL以外で最初、最後などの指定ができる

OVER句やPARTITION BY句を絡めた書き方としては、

LAG(項目名)
OVER (PARTITION BY 項目名1
     ORDER BY 項目名2
     )

項目名1ごとに、項目名2の並び順で、項目名の前の行の値(LAG)、項目名の後の行の値(LEAD)を抽出します。

LAG(項目名)のところは、以下のような設定が可能
LEAD(項目名)
FIRST_VALUE(項目名)
LAST_VALUE(項目名)

※LAG、LEADの代わりにSUM(項目名)、MAX(項目名)なども使えます。

例として、前回SQL Serverで行ったオリンピックのメダル数の抽出の例をOracleでも試してみます。

テーブルは以下の定義：
CREATE TABLE olympic_medal(
 season VARCHAR2(4)
,year   DECIMAL(4)
,color  VARCHAR(2)
,counts     INT
,CONSTRAINT pk_olympic_medal PRIMARY KEY (season, year, count));

このテーブルに、夏季、冬季の日本が取ったメダル数を登録してみます。（前回と同様にデータをInsertします。途中省略）

INSERT INTO olympic_medal VALUES('夏季',1912,'金',0);
INSERT INTO olympic_medal VALUES('夏季',1912,'銀',0);
INSERT INTO olympic_medal VALUES('夏季',1912,'銅',0);
…
INSERT INTO olympic_medal VALUES('冬季',2018,'金',4);
INSERT INTO olympic_medal VALUES('冬季',2018,'銀',5);
INSERT INTO olympic_medal VALUES('冬季',2018,'銅',4);

夏季、冬季それぞれの、各年毎、メダル色毎のメダル数（前回、次回も含めて）と、直近3回のメダル色毎の合計数、平均数を抽出し、年の新しい順に抽出してみます。

--まず出力結果のフォーマットを設定します。
col 季節 format a4
col 年 format 9999
col 色 format a4
col 今回 format 9999
col 前回 format 9999
col 次回 format 9999
col 直近3回合計 format 9999
col 直近3回平均 format 9999

--以下がSQL
SELECT
 season 季節
,year 年
,color 色
,counts 今回
,LAG(counts)
   OVER (PARTITION BY season, color
     ORDER BY year
     ) AS 前回
,LEAD(counts)
   OVER (PARTITION BY season, color
     ORDER BY year
     ) AS 次回
,SUM(counts)
   OVER (PARTITION BY season, color
     ORDER BY year
     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
   　　-- 現在の行から2行前から現在行までの3行分
     ) AS 直近3回合計
,AVG(counts)
   OVER (PARTITION BY season, color
     ORDER BY year
     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
　　 -- 現在の行から2行前から現在行までの3行分
     ) AS 直近3回平均
FROM olympic_medal
ORDER BY season, year DESC, color;

次に、金、銀、銅で色を分けずに、季節、年毎のメダル数の合計について、最初からの累計の合計個数と平均個数を抽出し、冬季、夏季の年の新しい順に表示してみます。

col 季節 format a4
col 年 format 9999
col 色 format a4
col 今回 format 9999
col 累計の合計 format 9999
col 累計の平均 format 9999

WITH C_YEAR_MEDAL(season, year, counts) AS
(SELECT season, year, SUM(counts)
   FROM olympic_medal
GROUP BY season, year
)
SELECT
season
,year
,counts
,SUM(counts)
   OVER (PARTITION BY season
     ORDER BY year
     ROWS BETWEEN UNBOUNDED PRECEDING
         AND CURRENT ROW　　   -- 最初の行から現在行まで
     ) AS 累計の合計counts
,AVG(counts)
   OVER (PARTITION BY season
     ORDER BY year
     ROWS BETWEEN UNBOUNDED PRECEDING
         AND CURRENT ROW   　　-- 最初の行から現在行まで
     ) AS 累計の平均counts
FROM C_YEAR_MEDAL
ORDER BY season DESC, year DESC;

上記の結果から、SQL Serverと同様に、OVER句を使用することで、各行に対しての直近の数回や累計の合計などを抽出することができることが確認できました。

今日は以上まで

にほんブログ村