カテゴリー別アーカイブ: 関数

TRANSLATE、REPLACE関数(SQL Server)

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

blog108_skywalk

上の写真は、10月に会社のイベントで三島スカイウォークに行った時の写真です。地名である「三島」と、「まるで空を歩いているような感覚を味わえる」という意味が込められた「スカイウォーク」、この2つのキーワードを入れて「三島スカイウォーク」という名前になったそうです。私は初めて行ったのですが、全長400mの日本一長い歩行者専用吊り橋ということで、富士山や駿河湾が一望できるとても素晴らしい景色が楽しめる場所でした。
また、ロングジップスライドという、往復560mのワイヤーを滑車で滑り降りていき、絶景を楽しみながらスリルを味わえるアクティビティもあり、若い社員が挑戦していましたが、この年になると、なかなか勇気が出ないので、若いということは素晴らしいと改めて感じました。また、森の中につくられ、樹から樹へと渡り歩くようなアドベンチャーコースなどもあり、家族で行っても1日楽しめるのではないかと思います。


<本日の題材>
TRANSLATE、REPLACE関数(SQL Server)

今回も、今までブログで取り上げていなかった関数について紹介しようと思います。指定した文字を別の文字に置換するTRANSLATE関数とREPLACE関数を取り上げます。

例)
まず、TRANSLATE関数ですが、構文は、
TRANSLATE ( inputString, characters, translations ) 
で、inputString という対象文字列に対して、データの中の文字に、characters の文字がどれか含まれていれば、translations の文字でそれぞれの文字を置換するというもので、characters と translations には同じ文字数の設定が必要となります。
例えば、複数の種類のかっこ(「」【】『』)があり、それらを全て [] に置き換えたいというような場合は、下記のように記載します。

SELECT TRANSLATE(
'「こんにちは」【さようなら】『こんばんは』',
'「」【】『』',
'[][][]'
);

blog108_1_

抽出結果は、
[こんにちは][さようなら][こんばんは]
となり、
「 → [ 、 」→ ] 、【 → [ 、 】→ ] 、『 → [ 、 』→ ] というように、それぞれが置換されたことが確認できます。
これを、置換でよく知られている REPLACE 関数を使って同じように処理してみます。REPLACE関数の構文は、REPLACE ( string_expression , string_pattern , string_replacement ) であり、string_expression という対象文字列に対して、string_pattern という検索文字列があれば、string_replacementという文字に置換するというもので、以下のようになります。

SELECT
REPLACE
(
    REPLACE
    (
        REPLACE
        (
            REPLACE
            (
                REPLACE
                (
                    REPLACE
                    (
                        '「こんにちは」【さようなら】『こんばんは』',
                        '「',
                        '['
                     ),
                     '」',
                     ']'
                ),
                '【',
                '['
            ),
            '】'
            ,']'
        ),
        '『',
        '['
    ),
    '』',
    ']'
);

実行してみると

blog108_3

となり、結果は同様ですが、カッコの種類ごとにREPLACE関数で設定しないといけないので、上記のようにSQL文が長くなってしまいます。このような場合には、TRANSLATE関数のほうが簡単に記載できることがわかります。

 
今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

IIF、CHOOSE(SQL Server)

ITコーディネータのシュウです。
精進湖からの富士山

かなり時間が経ってしまいましたが、上の写真は、今年の春に、普段から交流のある地域の壮年たちと一緒に富士山の近くにいったときの写真です。当日は朝から曇りで、なかなかきれいな富士山が撮れなかったのですが、雲の合間に頂上が見れている写真です。富士5湖の一つ、精進湖(しょうじこ)、精進料理の精進に湖と書いて、しょうじこと言うみたいですが、その湖の傍から撮った写真です。富士5湖というのは、山梨県側の富士山麓に位置する5つの湖の総称で、本栖湖、精進湖、西湖、河口湖、山中湖とあって、2013年には世界文化遺産に登録されています。
富士山が綺麗に見えるパノラマ台という場所に1時間半かけて登って、ガスコンロと水、やかんを用意していたので、そこで湯を沸かして、皆でカップヌードルを食べました。とてもおいしかったです。その後、山を下りて、「富士眺望の湯ゆらり」という近くの温泉に行ったときには晴れて、露天風呂から富士山の頂上が真ん前にきれいに見えました。温泉につかりながら、いつまでも富士山を見ていたいという感じでした。やはりときには自然の中にどっぷりと浸かる時間が必要ですね。

<本日の題材>
IIF、CHOOSE(SQL Server)

以前、CASE文を取り上げたことがありましたが、CASE式の簡略版と言えるIIF関数と、後に続くリストに対するインデックスを設定できるCHOOSE関数について紹介したいと思います。

例)
生徒の試験結果について、点数によって成績を振り分ける例を考えてみます。まず、生徒用のテーブルと、試験結果用のテーブルをDBに作成してみます。

CREATE TABLE dbo.Student(
学籍番号  NVARCHAR(8),
氏名      NVARCHAR(20),
性別      NVARCHAR(1),
生年月日  DATE,
CONSTRAINT PK_Student PRIMARY KEY(学籍番号));

INSERT INTO dbo.Student VALUES('20220001', '生徒A', '男', '2006-05-12');
INSERT INTO dbo.Student VALUES('20220002', '生徒B', '女', '2007-02-21');
INSERT INTO dbo.Student VALUES('20220003', '生徒C', '女', '2006-11-07');
INSERT INTO dbo.Student VALUES('20220004', '生徒D', '男', '2006-07-18');
INSERT INTO dbo.Student VALUES('20220005', '生徒E', '男', '2006-12-25');

CREATE TABLE dbo.Test_result(
学籍番号  NVARCHAR(10),
試験日    DATE,
点数      INT,
CONSTRAINT PK_Test_result PRIMARY KEY(学籍番号,試験日));

INSERT INTO dbo.Test_result VALUES('20220001', '2022-05-18', 92);
INSERT INTO dbo.Test_result VALUES('20220002', '2022-05-18', 48);
INSERT INTO dbo.Test_result VALUES('20220003', '2022-05-18', 69);
INSERT INTO dbo.Test_result VALUES('20220004', '2022-05-18', 28);
INSERT INTO dbo.Test_result VALUES('20220005', '2022-05-18', 82);

ここで、試験の点数によって、成績をS,A,B,C,D に分ける抽出を行ってみます。CASE文を使って抽出すると、下記のようにSQL文を書けます。

SELECT
学籍番号,
試験日,
点数,
CASE WHEN 点数 > 90 THEN 'S'
     WHEN 点数 > 80 THEN 'A'
     WHEN 点数 > 60 THEN 'B'
     WHEN 点数 > 30 THEN 'C'
     ELSE 'D' END AS 成績
FROM dbo.Test_result
ORDER BY 学籍番号;

blog107_1

これを、IIF関数を使うと、以下のようにも書けます。

SELECT
学籍番号,
試験日,
点数,
IIF(点数 > 90, 'S', IIF(点数 > 80, 'A', IIF(点数 > 60, 'B', IIF(点数 > 30, 'C', 'D')))) AS 成績
FROM dbo.Test_result
ORDER BY 学籍番号;

blog107_2

IIF関数の構文は、IIF( boolean_expression, true_value, false_value ) ということで、boolean_expression が true に評価された場合に、true value の値を返し、false に評価された場合に、flase value の値を返します。IIF関数は、最大10の入れ子ができるということなので、上記の例では入れ子の設定にしてみましたが、CASE文と同様の抽出ができました。

次に、CHOOSE関数ですが、構文は、CHOOSE ( index, val_1, val_2 [, val_n ] ) であり、後続の val_1 以降のリストから、index 引数の順番の値を返すものになります。
例えば、CHOOSE(3, 'A', 'B', 'C', 'D') であれば、’C’ が返ってきます。

blog107_3

先ほどの Studentテーブルの生徒について、生年月日によって、’春’,’夏’,’秋’,’冬’ のどの季節に生まれたのかを抽出してみたいと思います。

SELECT 学籍番号, 氏名, 生年月日,
CHOOSE (DATEPART(MM, 生年月日), '冬','冬','春','春','春','夏','夏','夏','秋','秋','秋','冬') AS 誕生季節
FROM dbo.Student
ORDER BY 学籍番号;

blog107_4

誕生月によって、季節をCHOOSE関数のリストで設定したので、それに合わせて各生徒の誕生日の季節が抽出されることを確認できました。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

STRING_SPLIT(SQL Server)

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

royalwing

少し前に、娘と一緒に、横浜港の大桟橋国際客船ターミナルから乗船するレストラン船「ロイヤルウイング」に乗って、食事をしながらクルージングを楽しむ機会がありました。大桟橋国際客船ターミナルを出航し、ベイブリッジをくぐり東京湾へ出て、その後再びベイブリッジを通過し横浜港をゆっくり周遊するというもので、90分ほどの内容でしたが、初めてのこともあり、楽しいひと時を過ごすことができました。少し天候がよくなかったというのが残念でしたが、ピアノの生演奏を聴けたり、食事後は外のデッキに出て、海を眺めて過ごすことができます。上の写真は、乗船する前に撮った写真です。
時間も余裕もなかなかないですが、何日間かかけてのクルージングの旅行とか、できたら楽しいでしょうね!

<本日の題材>
STRING_SPLIT(SQL Server

SQL Serverで便利な関数を見つけたので、今回ブログで取り上げてみたいと思います。これは、何らかの区切り文字を含んだ状態でデータが存在しているときに、そのデータを区切り文字で分割して、複数行として値を返すことができます。

例)
衣料品に関する「製品情報」テーブルに「サイズ」「色」という項目があり、それらに、区切り文字を含んで該当するサイズや色が登録されているとします。そのときに、区切り文字を含む項目について、区切り文字で分割して、データを複数行で表示したいときに使用できます。

最初に「製品情報」テーブルを作成して、データを登録します。

CREATE TABLE dbo.製品情報(
製品ID INT,
製品名 NVARCHAR(30),
サイズ NVARCHAR(30),
色     NVARCHAR(30),
CONSTRAINT PK_製品情報 PRIMARY KEY (製品ID));

INSERT INTO dbo.製品情報 VALUES(1, 'Tシャツ_001', 'S,M,L,XL,XXL', '赤/青/黄/橙/緑/黒');
INSERT INTO dbo.製品情報 VALUES(2, 'Tシャツ_002', 'S,M,L', '青/桃/黄');
INSERT INTO dbo.製品情報 VALUES(3, 'Yシャツ_001', 'S,M,L,XL', '白/灰');
INSERT INTO dbo.製品情報 VALUES(4, 'Yシャツ_002', 'S,M,L,XL,XXL', '白');
INSERT INTO dbo.製品情報 VALUES(5, 'ポロシャツ_001', 'S,M,L', '赤/青/黒/白');

上記のようにデータを登録した製品情報テーブルについて、データを抽出すると以下になります。

SELECT * FROM [dbo].[製品情報]
ORDER BY 製品ID;

blog106_1

これについて、製品毎、サイズ毎に行を分けて抽出したい場合に、以下のようにすると簡単に抽出が可能となります。
サイズの場合の区切り文字「,」を CROSS APPLY STRING_SPLITの後に、項目名とともに設定します。

SELECT 製品ID, 製品名, value AS サイズ
FROM dbo.製品情報
CROSS APPLY STRING_SPLIT(サイズ, ',');

blog106_2

同様に、製品毎、色毎に行を分けて抽出したい場合には、区切り文字「/」を CROSS APPLY STRING_SPLITの後に、項目名とともに設定します。

SELECT 製品ID, 製品名, value AS
FROM dbo.
製品情報
CROSS APPLY STRING_SPLIT(
, '/');

blog106_3

また、色に「青」「白」をともにデータとして持つレコードを抽出したい場合に、下記のようなSQLで抽出が可能です。

SELECT 製品ID, 製品名, サイズ,  
FROM dbo.
製品情報
WHERE '
' IN (SELECT value FROM STRING_SPLIT(, '/'))
  AND '
' IN (SELECT value FROM STRING_SPLIT(, '/'));  

blog106_4

同様に、サイズで「XL」「XXL」をともにデータとして持つレコードを抽出したい場合、以下のようにできます。

SELECT 製品ID, 製品名, サイズ,  
FROM dbo.
製品情報
WHERE 'XL'  IN (SELECT value FROM STRING_SPLIT(
サイズ, ','))
  AND 'XXL' IN (SELECT value FROM STRING_SPLIT(
サイズ, ',')); 

blog106_5

さらに、色毎の製品の件数を、多い順に抽出してみます。

SELECT value AS , COUNT(*) AS 色毎の製品件数
FROM dbo.
製品情報 
CROSS APPLY STRING_SPLIT(
, '/') 
GROUP BY value
ORDER BY COUNT(*) DESC;

blog106_6

色の種類ごとの、製品の件数が多い順に表示されることが確認されました。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

FIRST_VALUE / LAST_VALUE(SQL Server)

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

blog104_1

写真は、群馬県の榛名山の写真です。群馬県の高崎にいる知人が写真を送ってくれました。
群馬県には、赤城山、榛名山、妙義山の上毛三山と言われる有名な山があり、榛名山は中央に位置する火山であり、複数の山で構成される山体の名称です。山頂には、カルデラ湖である榛名湖と榛名富士があり、それらを複数の山が囲む形になっています。火山としては、5世紀から6世紀の頃に、大規模な噴火が度々発生していたとの記録があります。
以前は関越自動車道に乗ることがよくあって、榛名山、赤城山を始めたくさんの山々の自然を見ながらの走行はとても気分がいいものですね。榛名山の側には伊香保温泉もあるということなので、いつか行けたらいいと思います。

<本日の題材>
FIRST_VALUE / LAST_VALUE(SQL Server)

かなり以前のブログで、ORACLEでの FIRST / LAST関数について取り上げましたが、SQL Serverにおいても同様の機能があるのを見つけましたので、試してみたいと思います。なお、SQL Serverについては、OVER句についても取り上げたことがありますが、今回の FIRST_VALUE / LAST_VALUE関数は、OVER句と一緒に使用するかたちになります。

例)
以前ブログで取り上げたときと同様に、商品マスタに登録されたデータについて、分類ごとの値段が最も高いものと低いものを、商品名などの情報とともに出力するということをしてみたいと思います。

CREATE TABLE syomst(
 syo_cd   NVARCHAR(10)
, syo_name NVARCHAR(20)
, bnrui    NVARCHAR(20)
, price    NUMERIC(10)
, CONSTRAINT PK_syomst PRIMARY KEY (syo_cd));

データを登録します。

INSERT INTO syomst VALUES('A0001', 'チョコレート', 'お菓子', 120);
INSERT INTO syomst VALUES('B0001', '
りんご', '果物', 100);
INSERT INTO syomst VALUES('C0001', '
キャベツ', '野菜', 160);
INSERT INTO syomst VALUES('A0002', '
ビスケット', 'お菓子', 200);
INSERT INTO syomst VALUES('B0002', '
', '果物', 160);
INSERT INTO syomst VALUES('C0002', '
にんじん', '野菜', 150);
INSERT INTO syomst VALUES('A0003', '
ガム', 'お菓子', 100);
INSERT INTO syomst VALUES('B0003', '
みかん', '果物', 80);
INSERT INTO syomst VALUES('C0003', '
じゃがいも', '野菜', 100);
INSERT INTO syomst VALUES('A0004', '
スナック', 'お菓子', 140);
INSERT INTO syomst VALUES('B0004', '
', '果物', 120);
INSERT INTO syomst VALUES('C0004', '
玉ねぎ', '野菜', 150);

それでは、商品マスタのデータを抽出するとともに、FIRST_VALUE、LAST_VALUEを使用して、同じ商品の分類の値段が最も低いものと高いものの商品名も右側に並べて抽出してみます。

SELECT bnrui, syo_cd, syo_name, price,
FIRST_VALUE(syo_name) OVER
(PARTITION BY bnrui ORDER BY price ASC 
 ROWS UNBOUNDED PRECEDING
 ) AS
分類で最も安い商品名,
LAST_VALUE(syo_name) OVER
(PARTITION BY bnrui ORDER BY price ASC
 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 ) AS
分類で最も高い商品名
FROM dbo.syomst
ORDER BY bnrui, price, syo_cd;

blog104_3

商品マスタの各項目の値が左側に、その右に、分類で最も安い価格の商品の名前と、分類で最も高い価格の商品の名前を正しく抽出することができました。

さて、FIRST_VALUELAST_VALUE を使う場合の構文は、以下になります。

FIRST_VALUE / LAST_VALUE ( [scalar_expression ] ) 
[ IGNORE NULLS | RESPECT NULLS ]
OVER ( [partition_by_clause] order_by_clause [rows_range_clause] )

上記のパラメータについては、
scalar_expression

返される値:今回は、商品名称を表示したいので、syo_name

IGNORE NULLS - パーティションの最初の値の計算時に、データセット内の null 値を無視します。
RESPECT NULLS -
パーティションの最初の値の計算時に、データセット内の null 値を使用します。
(今回は商品名称はNULLデータがなく関係がないので指定してはいません)

partition_by_clause は、指定した項目でパーティションに分割して、パーティション毎の結果を出すときに使用しますが、今回は、分類(bunrui)毎に抽出するため、設定しています。
order_by_clause
は、今回は価格(price)の最も安い価格と最も高い価格を出すために使用します。

rows_range_clause は始点と終点を指定することによって、パーティション内の行をさらに条件付けることが可能になります。
今回の例について説明すると、FIRST_VALUE句のほうで設定している「ROWS UNBOUNDED PRECEDING」は、分類毎の最初の行から開始することを指定しています。
LAST_VALUE
句のほうの「RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING」は、現在の行の値から最後の行の値までということを表わしています。

注意点として、LAST_VALUEのときに、上記の範囲の設定を省略すると、結果が異なってきます。
試してみます。

SELECT bnrui, syo_cd, syo_name, price,
FIRST_VALUE(syo_name) OVER
(PARTITION BY bnrui ORDER BY price ASC 
 ) AS
分類で最も安い商品名,
LAST_VALUE(syo_name) OVER
(PARTITION BY bnrui ORDER BY price ASC
 ) AS
分類で最も高い商品名
FROM dbo.syomst
ORDER BY bnrui, price, syo_cd;

blog104_4

分類で最も安い商品名のほうは問題ないですが、分類で最も高い商品名の結果が、前回の結果と異なり、分類毎の最も高い価格の商品名ではなく、抽出した行の商品名が抽出されていることがわかります。これは、rows_range_clause のデフォルトの設定が、「RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW」ということで、分類毎の最初の行の値から現在の行の値までということになり、分類毎、price順にソートしているため、LAST_VALUEとしては、結局、現在の行の値になってしまっているということです。

そのため、LAST_VALUEを使用する場合には、ROWSやRANGEで、UNBOUNDED FOLLOWINGまでというように、範囲を適切に指定するようにしたほうがよいようですね。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

メモリ最適化テーブル変数(SQL Server)

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

IMG_4486

この写真は、前回のブログで写真を載せた、大根島にある「由志園」に行ったときに、ついでに訪れた、島根半島の先のほうにある「美保神社」で撮ったものです。私も高校生の時に一度兄に連れて行ってもらったことがあったというかすかな記憶がありましたが、それ以来なので、ほとんど初めてという感じでした。この神社の祭神は、えびす様(事代主神)と大国主神の后の三穂津姫で、商売繁盛、海上安全、五穀豊穣、夫婦和合などの守護神として祀られています。実家が、毎年、正月には美保神社のお札を送ってもらうようにしているということを、道すがら初めて聞きました。父親も数十年ぶりに参ることができたと喜んでいたので、少し遠回りにはなりましたが、行って良かったと思います。

<本日の題材>
メモリ最適化テーブル変数(SQL Server

SQL Server2014から実装されたIn-Memory OLTP 機能に、メモリ最適化テーブルというものがあります。今まであまり試したことがなかったのですが、今回は、そのメモリ最適化テーブル変数というものを試してみたいと思います。

例)
テスト用テーブルを作成し、テストデータを50万件作成します。そこから検索したいデータを抽出し、テスト用テーブルの列定義と同じ設定のメモリ最適化テーブル変数に登録して、結果を抽出してみます。

まず、メモリ最適化機能を使用するために、データベースに MEMORY_OPTIMIZED_DATA で宣言された FILEGROUPを作る必要があります。今回は、既存のデータベース「BLOG」に追加します。

ALTER DATABASE BLOG ADD FILEGROUP BLOG_mod CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE BLOG ADD FILE (name='BLOG_mod1', filename='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\iBLOG_mod1') TO FILEGROUP BLOG_mod;

blog102_2

次に、データを登録する「 mem_test」テーブルを作成します。

CREATE TABLE mem_test(
id numeric(8)
,名前 nvarchar(20)
,区分 nvarchar(2)
,ポイント int
,Constraint PK_mem_teste Primary key(id));

blog102_3

このテーブルにテストデータを作成します。今回は、50万件を以下のように作成します。「id」項目には、1~500000までシーケンシャルに値を設定し、名前は「顧客名_」の後にidを頭0埋めで設定、区分は2桁で、10,20,30, ~90までをランダムに、ポイントは、1~100の整数をランダムに登録してみます。ランダムに値を設定するのは、RAND関数と、NewID関数を使用してみます。

--データの作成
DECLARE @i int = 0
WHILE @i < 500000
BEGIN
   SET @i = @i + 1
   INSERT INTO mem_test(id, 名前, 区分, ポイント)
   VALUES(@i, '顧客名_'+ RIGHT('000000'+CAST(@i as nvarchar),6), CAST(FLOOR(1 + RAND()*9)*10 AS NVARCHAR), 1+ABS(CHECKSUM(NewId())) % 100)
END

blog102_4

ランダム値についてですが、RAND関数は、0~1の間の数が生成されるので、指定範囲内の整数値乱数が必要な場合は、
SELECT FLOOR([FROM値] + (RAND() * ([TO値] - [FROM値] + 1)))
のようにすれば取得できます。今回は、10,20,..,90 という文字型の値にしたかったので、CAST(FLOOR(1 + RAND()*9)*10 AS NVARCHAR) としました。
また、NEWID関数は、uniqueidentifier データ型の値を返してきますが、データを数値にしてチェックするCHECKSUM関数と、負の数が返ってくる場合もあるため絶対値を取得するABS関数を使い、さらに求める値の範囲を考慮して、今回は1~100までの値のため、100で割った余りを使うかたちで、1+ABS(CHECKSUM(NewId())) % 100 としました。

データを確認してみます。
SELECT * FROM mem_test
ORDER BY id;

blog102_5

区分とポイントはランダムに値が登録されているのが確認できました。
それでは、メモリ最適化テーブル変数を使用するために、そのためのユーザー定義テーブル型を宣言します。通常と違うのは、「MEMORY_OPTIMIZED = ON」を付けることと、ハッシュインデックスを付与する点が異なります。

CREATE TYPE mem_test_type AS TABLE
(id numeric(8)
,名前 nvarchar(20)
,区分 nvarchar(2)
,ポイント int
,INDEX idx1 NONCLUSTERED HASH (id) WITH ( BUCKET_COUNT = 500000 )
) WITH ( MEMORY_OPTIMIZED = ON );

blog102_6

※HASHインデックスでは、BUCKET_COUNT(バケット数)を適切な値へ設定していないと、性能低下の原因に繋がるようです。(特に小さすぎると性能が大きくさがるとのこと)

ちなみに、最初に行った、データベースに MEMORY_OPTIMIZED_DATA で宣言された FILEGROUPを作成していなかった場合には、上記のタイプを作成しようとしたときに、「メモリ最適化テーブル を作成できません。メモリ最適化テーブル を作成するには、オンライン状態かつ 1 つ以上のコンテナーがある MEMORY_OPTIMIZED_FILEGROUP がデータベースに含まれている必要があります。」というエラーが出ます。

blog102_8

それでは、上記を使ってメモリ最適化テーブル変数を宣言し、指定した5つの区分についてのデータ件数を確認してみたいと思います。

-- メモリ最適化テーブル変数の宣言
DECLARE @retValue dbo.mem_test_type

-- メモリ最適化テーブルへデータの INSERT
DECLARE
       @区分1 nvarchar(2) = '20',
       @区分2 nvarchar(2) = '40',
       @区分3 nvarchar(2) = '50',
       @区分4 nvarchar(2) = '70',
       @区分5 nvarchar(2) = '90';
 
   INSERT INTO @retValue
   SELECT id, 名前, 区分, ポイント
   FROM dbo.mem_test
   WHERE 区分 = @区分1
              OR 区分 = @区分2
              OR 区分 = @区分3
              OR 区分 = @区分4
              OR 区分 = @区分5
 
-- メモリ最適化テーブル変数に格納した結果を集計
SELECT 区分, COUNT(*) 件数
FROM @retValue
GROUP BY 区分
ORDER BY 区分;

blog102_7

ほとんど時間はかからずに結果が抽出されてきました。
メモリ最適化テーブル変数を、一時テーブルのようなかたちで使用できることが確認できましたが、実際には、もっと適した使用法があるのではないかと思います。今回は、取りあえず使えることを確認してみました。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

APPLY(SQL Server)

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

IMG_1412

自宅から会社に行く途中に道路のそばに咲いているポピーの花です。自転車で通るので、きれいに咲いているときには少し止まって眺めたりするこの頃ですが、若いときは、頭の中は仕事のことか、差し迫ったやるべきことなどでいっぱい(もしくはぼーっとしている?)で、見ていても記憶に残らないという感じだったと思います。妻にあそこにきれいな花が咲いているでしょう?と聞かれても、そうだっけ?という返事をするので、そういう感覚が欠落しているかわいそうな人だとあきれられていたものですが、少しずつ花などの自然も意識するようになってきました。

ちなみに、ポピーは色とりどりの花を咲かせるケシ科の植物の総称で、その実から採れる乳液には、入眠や麻痺の作用があることから、紀元前400年頃のギリシアでは麻酔薬や睡眠導入薬として用いられていたそうです。花言葉も「なぐさめ」「心の平静」「いたわり」「思いやり」などその効能にちなんだものが多くあり、相手をいたわる気持ちや、助けたい気持ちを表現するときによく贈られる花だそうです。

<本日の題材>
APPLY(SQL Server)

今回は、APPLYという演算子について取り上げてみます。
以前、テーブル値関数について記事として載せたことがありますが、あるテーブルのデータの値と、その値によって取得されるテーブル値関数の結果を組み合わせて抽出したいという場合に、APPLY という演算子を使用することができるようです。(SQL Server 2005から)
SQLのFROM句の後に、まずテーブルを記載し、「CROSS APLLY」、もしくは「OUTER APPLY」の後側にテーブル値関数を指定するかたちになり、SELECT のほうではテーブルの列とテーブル値関数の結果の列を含めるかたちになります。
私も今まであまり使ったことがなかったのですが、今回、試してみようと思います。

例)
使用するテーブルは、以前ブログで使ったことのある、日本の都道府県、山、川、湖などの大きさや高さや長さなどをデータにしたランキングのデータを使ってみたいと思います。
今回使用するテーブル(「ランキング」「ランキング区分」)の定義は、下記のような設定。

テーブル定義

データを抽出してみると、
「ランキング区分」テーブルは、

ランキング区分データ

「ランキング」テーブルは、

ランキングデータ

今回使用するテーブル値関数は、「区分NO」の値からランキングテーブルのデータを抽出する簡単なものです。定義は以下:

CREATE FUNCTION [dbo].[ufn_ランク上位取得]
(
         @p区分NO DECIMAL(4,0)
) RETURNS @ランク上位 TABLE (
         区分名 VARCHAR(40),
         ランキング DECIMAL(3,0),
         名称 VARCHAR(40),
         数値 DECIMAL(8,1)
)
AS
BEGIN
         INSERT @ランク上位
         SELECT
                   K.区分名
                  ,RANK() OVER (ORDER BY R.数値 DESC)
                  ,R.名称
                  ,R.数値
         FROM dbo.ランキング R
         JOIN dbo.ランキング区分 K ON R.区分NO = K.区分NO
         WHERE K.区分NO = @p区分NO;
 
        RETURN;
END;

このテーブル値関数にパラメータを直接指定して抽出すると、

SELECT * FROM dbo.ufn_ランク上位取得(1)
ORDER BY ランキング;

テーブル値関数抽出

これを、「ランキング区分」テーブルの「区分NO」をテーブル値関数にセットして、「区分NO」毎にランキング情報を抽出するように、APPLY句を使用すると、以下のように抽出できます。

SELECT K.区分NO, K.区分名, K.単位, R.ランキング, R.名称, R.数値
  FROM dbo.ランキング区分 K
  CROSS APPLY dbo.ufn_ランク上位取得(K.区分NO) AS R
ORDER BY K.区分NO, R.ランキング;

CROSSAPPLY

ここで、「ランキング区分」テーブルにあっても、テーブル値関数で結果のないものは抽出されないのですが、外部結合のように、上記のようなデータも左側のランキング区分の情報は表示し、テーブル値関数側の結果は null で表示したい場合には、「OUTER APPLY」演算子を使用します。

SELECT K.区分NO, K.区分名, K.単位, R.ランキング, R.名称, R.数値
  FROM dbo.ランキング区分 K
  OUTER APPLY dbo.ufn_ランク上位取得(K.区分NO) AS R
ORDER BY K.区分NO, R.ランキング;

OUTERAPPLY

 上記のように、「区分NO」=2, 3 のテーブル値関数では抽出されないデータも表示されることが確認できます。

ちなみに、APPLY演算子の右側は必ずテーブル値関数である必要はなく、上記の内容は、以下のようなSQLでも抽出できます。(CROSS APPLYの例)

SELECT K.区分NO, K.区分名, K.単位, RK.ランキング, RK.名称, RK.数値
  FROM dbo.ランキング区分 K
  CROSS APPLY
   (SELECT
                   K.区分名
                  ,RANK() OVER (ORDER BY R.数値 DESC) AS ランキング
                  ,R.名称
                  ,R.数値
         FROM dbo.ランキング R
         WHERE R.区分NO = K.区分NO) AS RK
ORDER BY K.区分NO, RK.ランキング;

CROSSAPPLY2

今まではあまり使ったことはなかったのですが、今後は、使えるときもあるのではないかと思います。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

OVER句(Oracle)

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

IMG_1325

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

IMG_1352

日本には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;

86_1

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

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;

86_2

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

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

OVER句のROWS(SQL Server)

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

IMG_1284

2018年も、あっという間に3月の最後の週を迎えました。
ここしばらく、開発の案件が忙しく、ブログの原稿を作る時間が取れませんでした。
多少落ち着いてきたので、久しぶりに書いています。上の写真は、休みの日に息子と神奈川のほうに行く機会があり、桜が満開になっていたので写真を撮ってきました。やはり埼玉に比べると、少し神奈川のほうが暖かい気がしますし、桜も開花が早いですね!
久しぶりに長男と二人きりで電車に乗ったり、歩いたりして過ごしました。今度高校に入学しますが、いつの間にか成長している息子を見て、無事に育ってくれてとても感謝の気持ちが湧いてきました。これからいろいろとぶつかる壁や課題も出てくると思うけれど、頑張って乗り越えてほしいです。

それから、dbSheetClientの呉信用金庫様の事例がアップされています。
Excel I/Fの各種業務をdbSheetClientを使って、
圧倒的な処理スピードの向上を実現!(10分以上が瞬時に)
トップマネジメントを含めて全社員(約650名)が利用!
興味のある方は以下をご参照ください。
https://www.newcom07.jp/dbsheetclient/usrvoice/kure_shinkin.html

 <本日の題材>
OVER句のROWS(SQL Server)

以前、このブログでも「RANK, DENSE_RANK」という題目で、ランキング関数の中でOVER句を使った内容を題材として取り上げましたが、OVER句と一緒に使用する引数に、ROWS句というものがあり、行の計算対象を指定することができるという機能がありましたので、今回、それを取り上げてみます。これは、SQL Server 2012からの機能で、以下のような記述で範囲を指定します。

OVER (PARTITION BY 項目名1
     ORDER BY 項目名2
     ROWS BETWEEN A AND B
     )

上記の書き方で、項目名1毎にグループ化しながら、項目名2の並び順で、AからBの範囲内での抽出結果を表示することができます。(PARTITION BY句、ORDER BY句、ROWS句は必要なときのみ指定します)

このときの、A、Bの記述の仕方には、以下のようなものがあります。

CURRENT ROW
  --- 現在の行
n PRECEDING
  --- 現在の行からn行前
n FOLLOWING
  --- 現在の行から n 行後
UNBOUNDED PRECEDING
  --- パーティションの最初の行
UNBOUNDED FOLLOWING
  --- パーティションの最後の行

例)今回は、平昌オリンピックで日本中が盛り上がったことから、オリンピックで日本が取ったメダル数をデータにしたものを使用します。

テーブルは以下の定義:
CREATE TABLE DBO.メダル数(
季節  VARCHAR(4)
,年    DECIMAL(4)
,メダル色 VARCHAR(2)
,個数     INT
,CONSTRAINT pk_メダル数 PRIMARY KEY (季節,年,メダル色));

このテーブルに、夏季、冬季の日本が取ったメダル数を登録してみます。

INSERT INTO dbo.メダル数 VALUES('夏季',1912,'金',0);
INSERT INTO dbo.メダル数 VALUES('夏季',1912,'銀',0);
INSERT INTO dbo.メダル数 VALUES('夏季',1912,'銅',0);
 
INSERT INTO dbo.メダル数 VALUES('夏季',1920,'金',0);
INSERT INTO dbo.メダル数 VALUES('夏季',1920,'銀',2);
INSERT INTO dbo.メダル数 VALUES('夏季',1920,'銅',0);

--途中省略
INSERT INTO dbo.メダル数 VALUES('冬季',2014,'金',1);
INSERT INTO dbo.メダル数 VALUES('冬季',2014,'銀',4);
INSERT INTO dbo.メダル数 VALUES('冬季',2014,'銅',3);

INSERT INTO dbo.メダル数 VALUES('冬季',2018,'金',4);
INSERT INTO dbo.メダル数 VALUES('冬季',2018,'銀',5);
INSERT INTO dbo.メダル数 VALUES('冬季',2018,'銅',4);

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

SELECT
  季節
,年
,メダル色
,個数
,SUM(個数)
    OVER (PARTITION BY 季節,メダル色
      ORDER BY 年
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  
   -- 現在の行から2行前から現在行までの3行分
      ) AS 直近3回の合計個数
,AVG(個数)
    OVER (PARTITION BY 季節,メダル色
      ORDER BY 年
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    -- 現在の行から2行前から現在行までの3行分
      ) AS 直近3回の平均個数
FROM dbo.メダル数
ORDER BY 季節,年 DESC,メダル色;

sql1

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

WITH C_YEAR_MEDAL(季節, 年, 個数) AS
(SELECT 季節, 年, SUM(個数)
    FROM dbo.メダル数
  GROUP BY 季節, 年
)
SELECT
  季節
 ,年
 ,個数
,SUM(個数)
      OVER (PARTITION BY 季節
           ORDER BY 年
           ROWS BETWEEN UNBOUNDED PRECEDING 
                           AND  CURRENT ROW
                           -- 最初の行から現在行まで
           ) AS 累計の合計個数
,AVG(個数)
       OVER (PARTITION BY 季節
           ORDER BY 年
           ROWS BETWEEN UNBOUNDED PRECEDING
                            AND CURRENT ROW
                           -- 最初の行から現在行まで
           ) AS 累計の平均個数
FROM C_YEAR_MEDAL
ORDER BY 季節 DESC, 年 DESC;

sql2_1
sql2_2

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

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

PIVOT、UNPIVOT(SQL Server)

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

夏みかん

知人の方から、家に夏みかんの木があって、たくさん実がなるということで頂きました。その写真です。

さて、米国では、トランプ大統領が就任しましたが、就任直後から大統領令を連発し、大きな話題になるとともに、中にはきわめて大きな反発も起こっていますね。オバマケア見直し、TPPの離脱をはじめ、選挙のときから掲げてきた内容を具体的に実施しているわけですが、大きな波紋を呼んだものとしては、メキシコ国境の壁の建設、そして、中東・アフリカ7カ国からの渡航者の入国禁止(シリアからの難民受け入れの拒否も含む)です。
特に入国禁止については、米国政府内でも批判的な声が上がり、反対するデモも各地で起きていて、かなり混乱した状況ですね。この大統領令は憲法に違反しているということで、差し止めを命じるワシントン州の連邦地裁も現れました。強力な力を持つ大統領令ですが、司法のほうで歯止めをすることが可能だということが今回の内容で確認できました。さすが、民主主義の国!

しかし、おっかない世の中になってきました。これからどうなるのやら? できれば平和裏に進んでほしい。僕もやっぱり平和が好きですから。でもよく考えてみると、うちのかみさんもおっかなかったっけなあ?

 <本日の題材>
PIVOT、UNPIVOT (SQL Server)

以前、複数行のデータを集計して横展開という記事をアップしたことがありましたが、今回はこれと同様のことを、SQL ServerのPIVOTという関係演算子を使って試してみたいと思います。

前回の記事では、部品別の日別の仕入数量のデータが、部品コード、年月日、数量というようなレイアウトでDBに登録されている場合に、部品別に、月を横に並べて仕入数量の月別合計値を表示させるようなケースについて、case文を使いながら各月を横に並べて表示させるようにしていました。
このテーブルのデータを単純に抽出すると以下のようになります。

SELECT * FROM dbo.部品発注表;

blog75_部品発注表

部品別に、月を横に並べて仕入数量の月別合計値を表示させることを、pivot演算子を使って試してみます。

SELECT *
FROM (select 部品コード, substring(年月日,5,2)+'月' as '月', 数量
       from [dbo].[部品発注表]) as B_tab
PIVOT (
   SUM(数量) FOR 月 IN
  ([01月], [02月], [03月], [04月], [05月], [06月], [07月], [08月], [09月], [10月], [11月], [12月])
) as PVTab
ORDER BY PVTab.部品コード;

 blog75_pivot

 上記のSQLでは、FROM の後の集計対象テーブルの後に、PIVOT句を設定し、FOR .. IN で設定した値を列として出力して、それ毎にSUM関数で設定した項目[数量]を集計しています。なお、SUM関数とFOR .. IN で指定されていない「部品コード」でGROUP BYがされていて、それぞれの行が出力されています。最後のORDER BY句はオプションですが、並び順を設定できます。
Excelのピボットと同様のことが、SQLでできるのが確認できました。

また、以前別の記事「横に並んだ項目を縦の行データに変換」で、最初から列として登録されているデータを、複数行の縦のデータに変換して抽出するという件を取り上げましたが、UNPIVOT という関数がこれに該当します。

前回のデータをそのまま利用する場合に、まず、「商品売上」テーブルのデータをそのまま抽出すると、

SELECT * FROM dbo. 商品売上;

blog75_商品売上

 上記データについて、各月ごとのデータを行として表示する場合に、UNPIVOT演算子を使ったSQLは以下のようになります。

SELECT 商品CD, 月, 月売上
FROM
  (SELECT * FROM dbo.商品売上) p
UNPIVOT
  (月売上 FOR 月 IN
     (売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月, 売上10月, 売上11月, 売上12月, 売上1月, 売上2月, 売上3月)
)AS unpvt
ORDER BY unpvt.商品CD desc;

blog75_unpivot

 上記のSQLでは、FROM の後の対象テーブル(最初から横に並んだ項目を持つ)の後に、UNPIVOT句を設定し、FOR .. IN で設定した値を行として出力して、それ毎に月の売上を抽出しています。
また、FOR .. IN で指定されていない「商品CD」でGROUP BYがされていて、商品CD毎の、各月毎の売上が出力されています。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

FIRST / LAST 関数

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

SONY DSC

この写真も、先回に続き、鳥の写真を撮るのに凝り始めたという知人の方から頂いたものです。たぶん白鷺だと思いますが、電線の上に止まっているところをきれいに撮っています。よく田んぼとかで白鷺を見かけることはありますが、電線に乗ることもあるんですね。

 そういえば、先日これはおもしろいと知人に紹介されたドラマで「夢をかなえるゾウ」のスペシャル男の成功編というのをDVDを借りて見ました。その話では人間の体にゾウの鼻、4本の腕を持ったインドのガネーシャという神様が、主人公の男性(小栗旬)に与える様々な課題を実践していく中で、人生をよい方向へと切り開いていく様子が描かれていますが、笑いあり、感動ありの内容でとてもよかったです。そこで出される課題は、以下のようなものだったと思います。(覚えている範囲内で)
・靴を磨く
・コンビニで(お釣りを)募金する
・食事は腹八分目にする
・人の欲しがる物を先取りしてあげる
・会った人を笑わせる
・トイレ掃除をする
・まっすぐ帰宅する
・その日がんばった自分を褒める
・一日何かをやめてみる
・毎朝、全身鏡を見て身なりを整える
・夢を楽しく想像する
・運が良いと口に出して言う
・明日の準備をする
・身近にいる大切な人を喜ばせる
・人のいい所を見つけ褒める
・人の長所を盗む
・サプライズをして喜ばせる

全ての課題に取り組んで、最後には、神様がそばにいなくてもやって行けるまで、どんどん主人公が成長していくという内容でしたが、それぞれの課題に対して、主人公が素直に取り組むのがとてもえらいと思いながら見ていました。
そのドラマの中で、仕事の会議中、最悪のように思える状況で、「運がいい」と口に出して言い、実際にそう思うことで発想を転換できて、厳しいと思っていた状況がよい方向に変わっていくシーンがありました。フィクションだからな!という思いが湧きつつも、確かにやってみる価値はあるなと考えさせられるところもいろいろとありました。

家族で一緒にこのビデオを見たので、妻や子供も、この中で出された課題を紙に書きとめて、自分も実践しようと意気込んでいましたが、さて、やっているのやら。

本題に移りましょうか。今回は、FIRST/LAST関数について取り上げてみたいと思います。

<本日の題材>
FIRST/LAST関数 (ORACLE)

以前、順位付の関数として、RANK関数やDENSE_RANK関数を取り上げたことがありましたが、本日は、DENSE_RANK関数と一緒に使うかたちで使用するFIRST関数、LAST関数を取り上げてみたいと思います。(ORACLEの環境)

構文は、以下のようになります。

グループ関数 KEEP
 ( DENSE_RANK FIRST/LAST ORDER BY ソート列1,[ソート列2,・・・] )
      OVER( [ PARTITION BY 項目1,[項目2,・・・]] )

以前にDENSE_RANK関数を取り上げたときに使ったのが商品マスタでしたので、今回もそれを使ってみます。今回はORACLEで試します。

CREATE TABLE syomst(
  syo_cd   VARCHAR2(10)
, syo_name VARCHAR2(20)
, bnrui    VARCHAR2(20)
, price    NUMBER(10)
, CONSTRAINT PK_syomst PRIMARY KEY (syo_cd));

データを登録します。

INSERT INTO syomst VALUES('A0001', 'チョコレート', 'お菓子', 120);
INSERT INTO syomst VALUES('B0001', 'りんご', '果物', 100);
INSERT INTO syomst VALUES('C0001', 'キャベツ', '野菜', 160);
INSERT INTO syomst VALUES('A0002', 'ビスケット', 'お菓子', 200);
INSERT INTO syomst VALUES('B0002', '桃', '果物', 160);
INSERT INTO syomst VALUES('C0002', 'にんじん', '野菜', 150);
INSERT INTO syomst VALUES('A0003', 'ガム', 'お菓子', 100);
INSERT INTO syomst VALUES('B0003', 'みかん', '果物', 80);
INSERT INTO syomst VALUES('C0003', 'じゃがいも', '野菜', 100);
INSERT INTO syomst VALUES('A0004', 'スナック', 'お菓子', 140);
INSERT INTO syomst VALUES('B0004', '梨', '果物', 120);
INSERT INTO syomst VALUES('C0004', '玉ねぎ', '野菜', 150);
COMMIT;

ここで、商品の分類毎の値段が最も高いものと低いものを出す場合、金額だけ抽出すればよいのであれば、普通は以下のようにします。

SELECT bnrui, MIN(price), MAX(price)
  FROM syomst
 GROUP BY bnrui
 ORDER BY bnrui;

syomst_min_max

ここで、商品の分類毎の値段が最も高いものと低いものの金額とともに商品も抽出したいという場合には、例えば以下のようにすることができます。

SELECT
  bnrui AS 分類
, MIN(syo_name) KEEP (DENSE_RANK FIRST ORDER BY price) AS 商品名_最安
, MIN(price) KEEP (DENSE_RANK FIRST ORDER BY price) AS 最安価格
, MIN(syo_name) KEEP (DENSE_RANK LAST ORDER BY price) AS 商品名_最高
, MIN(price) KEEP (DENSE_RANK LAST ORDER BY price) AS 最高価格
  FROM syomst
 GROUP BY bnrui
 ORDER BY bnrui;

syomst_first_last

実際のデータを確認してみると、

SELECT
  bnrui AS 分類
, syo_name AS 商品名
, price AS 価格
  FROM syomst
 ORDER BY bnrui, price;

syomst_order

確かに、各分類の最も安い価格のものと高い価格のものが抽出されていたことが確認できます。

次に、各商品の金額を表示するとともに、各分類の最も安い金額と高い金額を同じ行で表示するということを行ってみます。先ほどの、FIRST/LAST関数に、OVER(PARTITION BY )句を使用することで可能になります。

SELECT
  bnrui AS 分類
, syo_cd AS 商品CD
, syo_name AS 商品名
, price AS 価格
, MIN(price) KEEP (DENSE_RANK FIRST ORDER BY price)
   OVER(PARTITION BY bnrui) AS 分類の最安価格
, MIN(price) KEEP (DENSE_RANK LAST ORDER BY price)
   OVER(PARTITION BY bnrui) AS 分類の最高価格
  FROM syomst
 ORDER BY bnrui, syo_cd;

syomst_price_first_last

それぞれの商品の価格を、同じ分類の最も安い金額と高い金額と比較して見ることができるようになりました。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村