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 学籍番号;

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

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

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’ が返ってきます。

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

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

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

今日は以上まで

にほんブログ村

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

少し前に、娘と一緒に、横浜港の大桟橋国際客船ターミナルから乗船するレストラン船「ロイヤルウイング」に乗って、食事をしながらクルージングを楽しむ機会がありました。大桟橋国際客船ターミナルを出航し、ベイブリッジをくぐり東京湾へ出て、その後再びベイブリッジを通過し横浜港をゆっくり周遊するというもので、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, 'Ｔシャツ_001', 'S,M,L,XL,XXL', '赤/青/黄/橙/緑/黒');
INSERT INTO dbo.製品情報 VALUES(2, 'Ｔシャツ_002', 'S,M,L', '青/桃/黄');
INSERT INTO dbo.製品情報 VALUES(3, 'Ｙシャツ_001', 'S,M,L,XL', '白/灰');
INSERT INTO dbo.製品情報 VALUES(4, 'Ｙシャツ_002', 'S,M,L,XL,XXL', '白');
INSERT INTO dbo.製品情報 VALUES(5, 'ポロシャツ_001', 'S,M,L', '赤/青/黒/白');

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

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

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

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

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

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

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

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

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

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

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

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

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

今日は以上まで

にほんブログ村