ITC シュウちゃん のすべての投稿

バイト数分の表示と数値3桁ごとカンマ区切り表示(SQL Server)

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

IMG_8371

写真は、島根の実家の庭で咲かせているボタンの花です。以前、大根島(島根県松江市)の由志園に行ったときに購入したボタンを、父親が庭に植えていたのですが、今年もきれいに咲いたということで、父も喜んでいました。
また、先祖代々大事に育ててきた樹齢200年ほどの松の木があるのですが、毎年初夏に、新芽を摘む手入れをしているということで、父親が頑張って剪定作業を行っていました。今まであまり意識していなかったのですが、松の木の樹形を整えるのもなかなか大変だということを、改めて感じました。

<本日の題材>
バイト数分の表示と数値3桁ごとカンマ区切り表示(SQL Server)

データは日本語や英語、半角数値などが入り混じっている項目について、指定のバイト数のみを表示したいという場合や、数値項目を3桁ごとにカンマ区切りで表示させたい場合などが結構あるかと思いますが、本日は、それについて取り上げてみたいと思います。

例)
項目は、日本語や英語、半角数字などが混在していてバイト数と文字数が一致しない状態だけれども、帳票出力などで、どうしても限られたバイト数分しか表示したくないというケースがあるかと思います。そのときに、指定したバイト数分まで表示するSQLを確認してみます。

まず、今回確認するテーブルを作成し、データを登録します。
CREATE TABLE test_tbl(
ID INT,
NAME NVARCHAR(40),
QTY NUMERIC(12,2)
CONSTRAINT PK_test_tbl PRIMARY KEY (ID));

INSERT INTO test_tbl VALUES(1, 'テスト名称abcd_あいう', 1234567.12);
INSERT INTO test_tbl VALUES(2, 'テスト名称2_10xy', 20046);
INSERT INTO test_tbl VALUES(3, 'テスト名称3_データzyx', 33592.67);
INSERT INTO test_tbl VALUES(4, 'テスト名称4_全角半角混在', 4192.8);

Create.sql

それでは、作成したテーブルの「NAME」列について、15バイト分だけ抽出してみたいのですが、単純に最初の15文字だけをLEFT関数で抽出してみると、以下のようになります。

SELECT
ID,
left(NAME, 15) AS 名称,
QTY AS 数量
FROM test_tbl
ORDER BY ID;

select_1

これは、2バイト文字が混じっているので、明らかに15バイトよりも多いバイト数が抽出されます。
次に、convert関数を利用して抽出してみます。

SELECT
ID,
convert(varchar, substring(convert(text, NAME), 1, 15)) AS 名称,
QTY AS数量
FROM test_tbl
ORDER BY ID;

select_2

CONVERT関数にて、TEXT型に変換したものをSUBSTRING関数にて指定のバイト数を取り出し、さらに VARCHAR型に変換することで、指定バイト数分の表示ができるということです。
ちなみに、4行目の名称については、15バイト目がマルチバイト文字になるため、SQL Serverのほうでその前の文字までが表示されるかたちにしてくれます。

次に、上記の数量について、3桁ごとにカンマ区切りで表示させてみます。
このとき、数値を money型に変換した後、CONVERT関数の第3引数を1にすることで、3桁区切りの数値になります。

SELECT
ID,
convert(varchar, substring(convert(text, NAME), 1, 15)) AS 名称,
convert(nvarchar, convert(money, QTY), 1) AS 数量
FROM test_tbl
ORDER BY ID;

select_3

ただし、CONVERT関数でMONEY型に変換すると、小数点以下は2桁に四捨五入されるとともに、小数点以下2桁は必ず表示されるので、元の項目が整数であったとしても、’.00’が付いて表示されますので、注意が必要です。整数で表示したい場合は、’.00’を空白にREPLACEする必要があります。

今日は以上まで

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

OUTPUT句(SQL Server)

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

土浦花火

上の写真は、11/4に日本三大花火大会の一つといわれる土浦の花火(正式名は土浦全国花火競技大会)に家族で見に行った時の写真になります。家族で車で行ったのは初めてで、ある程度余裕をもって家を出発したつもりでしたが、土浦市内に入ると、めちゃくちゃ車が混んでいて、止められるかなと思っていたモールのほうも、当日は駐車禁止ということになっていたり、通行止めがあったりして、全く車が進まない状況に。それでも何とか駐車場を見つけようと、市内をうろうろしていたところ、あるおじさんが声をかけてくれて、自宅の駐車場の空いているスペースを貸してくれるという奇跡のような出来事がありました。妻と娘も車に乗っていたこともあり、心配して声をかけてくださったのだと思いますが、本当に感謝でした。

<本日の題材>
OUTPUT句(SQL Server

今回は、SQL Serverの機能で、OUTPUT句について取り上げてみたいと思います。これは、INSERT/UPDATE/DELETE/MERGEで追加/更新/削除 された行の情報を取得・出力できるというもので、とても便利な機能になります。最近の開発の案件の中で、この処理を使うことで、開発の要件を満たすことができました。

例)
テーブルにデータを登録/更新/削除するときに、通常は登録/更新/削除したデータをその時点では確認できず、結果のデータと処理前のデータを比較することで確認できるというかたちになるかと思いますが、OUTPUT句を使うことで、その時点でそれらのデータを出力することができるので、それを確認したいと思います。

まず、今回確認するテーブルを作成し、初期のデータを登録します。

CREATE TABLE トランA(
NO    INT NOT NULL,
名称  NVARCHAR(20),
数量  INT,
CONSTRAINT PK_トランA PRIMARY KEY(NO));

INSERT INTO トランA VALUES(1, '名称1', 100);
INSERT INTO トランA VALUES(2, '名称2', 200);

画像1

それでは、INSERT/UPDATE/DELETE の処理を実行するとともに、OUTPUT句によって出力してみます。

まず、INSERT から
INSERT INTO トランA OUTPUT inserted.*
VALUES(3, '名称3', 300);

画像2

通常、INSERT文を実行すると、
(1 行処理されました)
というようなメッセージのみが出力されますが、OUTPUT句を使用することで、INSERTしたデータを出力することができます。

次に、UPDATEを試します。
UPDATE トランA SET
数量 = 400
OUTPUT deleted.*, inserted.*
WHERE NO = 3;

画像3

上記結果のように、deleted.* で更新前の(削除される)データ、inserted.* で更新後の(追加される)データを出力しています。

次に、DELETEを試してみます。

DELETE FROM トランA
OUTPUT deleted.*
WHERE NO = 3;

画像4

上記のように、DELETEされるデータを出力しているのが確認できます。

次に、MERGE処理でOUTPUT句を使用するために、トランAと同様の定義を持つワークテーブルを作成し、そこにもデータを登録します。

CREATE TABLE ワークA(
NO   INT NOT NULL,
名称 NVARCHAR(20),
数量 INT,
CONSTRAINT PK_ワークA PRIMARY KEY(NO));

INSERT INTO ワークA VALUES(1 '修正名称1', 1000);
INSERT INTO ワークA VALUES(3 '名称3', 3000);
INSERT INTO ワークA VALUES(4 '名称4', 4000);

画像5

それでは、MERGEを実行する前に、再度トランAテーブルのデータを確認します。

SELECT * FROM トランA;

画像6

トランAのテーブルに対して、ワークAのデータを参照して、キーがマッチするものは更新し、マッチしないもので、トランにはなくワークに存在するものは登録し、トランにはあるがワークには存在しないものは削除するMERGE処理を実行してみますが、このときに、OUTPUT句によって、更新・登録・削除のデータを出力してみたいと思います。

MERGE INTO トランA
USING ワークA
ON (トランA.NO = ワークA.NO)
WHEN MATCHED THEN
UPDATE SET
  名称 = ワークA.名称
 ,数量 = ワークA.数量
WHEN NOT MATCHED THEN
INSERT (NO, 名称, 数量)
VALUES(ワークA.NO, ワークA.名称, ワークA.数量)
WHEN NOT MATCHED BY SOURCE
DELETE
OUTPUT inserted.*,  deleted.*, $action;

画像7

$action という項目に、UPDATE/INSERT/DELETE のどの処理が行われたのかが出力されます。
したがって、このOUTPUTの結果を一時テーブルなどに登録すれば、MERGE文などの処理の後に、登録・削除・更新されたデータをその後の処理で活用することが可能になります。
最終的なトランAの結果を確認してみます。

SELECT * FROM トランA;

画像8

今日は以上まで

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

GENERATE_SERIES(SQL Server 2022)

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

IMG_6862

上の写真は、ゴールデンウィークに、家族で埼玉県滑川町にある、国営武蔵丘陵森林公園に行ったときに撮った写真です。結構敷地が広くて、東京ドームの約65倍の広さがあるそうで、一回りするだけでもかなりの時間を要します。中央口、西口、北口、南口のエリアがあり、それぞれに楽しめるような場所が用意されていて、家族でゆっくりと自然に触れるのにはとてもよいところだと思います。今回は、西口広場に、約10万本のネモフィラという北アメリカ原産の植物で、きれいな青色に染まる花畑があるということで写真を撮ってきました。ただ、少し時期が遅かったので、だいぶ色は薄くなってしまったようです。ネモフィラは、茨城県のひたち海浜公園のネモフィラもとても有名ですね。

<本日の題材>
GENERATE_SERIES(SQL Server 2022

今回は、SQL Server2022で新しく機能として追加された関数で、GENERATE_SERIESという関数について取り上げてみたいと思います。この関数は、引数で指定した範囲に指定した間隔で数値を生成する関数になります。

例)
以前、WITH句を使って、1から20までの連続した整数を出力するSQLを取り上げたことがありましたが、それを、今回のGENERATE_SERIES関数で簡単に作成できることを確認したいと思います。

まず、前回行った WITH句を使ったやり方は、下記のようなものでした。

WITH RECUR_SEISU(val) AS (
SELECT 1
UNION ALL
SELECT val+1
FROM RECUR_SEISU
WHERE val+1 <= 20)
SELECT val FROM RECUR_SEISU;

blog111_1

再帰WITH句で、最初にSELECT 1として最初の値を取得し、その結果を使用してUNION ALLの下のSELECT文で、条件を満たすあいだそれを繰り返して処理をしているかたちですが、慣れないと少々わかりにくい方法です。

これに対して、GENERATE_SERIES関数は、構文が
GENERATE_SERIES ( start, stop [, step ] )
ということで、start(開始の値)、stop(終了の値)、step(間隔の値)を入れるだけで、同様のことができます。stepは、マイナス値や小数点なども設定することができ、省略すると既定値の1と判断されます。
下記のように設定できます。

SELECT value
FROM GENERATE_SERIES(1, 20);

blog111_2

WITH句で行ったときと同様の結果が出力されました。

同じようにして、1 から 0 まで -0.1 ずつ減らしていくかたちにしてやってみます。

SELECT value
FROM GENERATE_SERIES(1, 0, -0.1);

blog111_3

エラーが起きてしまいました。
エラーメッセージを見ると、引数はすべて同じ型でないといけないと言っています。
start と stop の値が 1、0 で int に対して、step の値が -0.1 で decimalの値になっているのが問題のようです。
なので、すべて decimal のかたちにしてみます。

SELECT value
FROM GENERATE_SERIES(1.0, 0.0, -0.1);

blog111_4

今度はエラーなく実行できたことが確認できました。

今日は以上まで。

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

OPENJSON(SQL Server)

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

blog110_1

上の写真は、少し前になりましたが、昨年、会社のイベントで山梨県の忍野八海に行ったときの写真です。富士山の伏流水に水源を発する湧水池である「忍野八海」は、天然記念物であり、世界遺産富士山の構成資産の一部として認定されているとのことで、前々から一度は行ってみたいと思っていたところでしたので、百聞は一見に如かずということで、行くことができてよかったです。それぞれの湧水は、思ったほど大きくはなく、一つだけ離れた「出口池」以外の7つの池を巡りました。その中で、とても深くて透明度が高い「湧池」というのが印象に残りました。また、途中、お昼ご飯に寄ったお食事処で食べた山梨名物のほうとうがとても美味しかったです。富士山が曇っていてよく見えなかったのが少し残念でした。

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

前回は、SQL ServerでJSONデータを利用できる件についての第1弾として、「RDBMSの表形式のデータをJSONフォーマットに変換して出力できる」ことについて、「FOR JSON」句を利用することで可能なことを確認してみました。
本日は、第2弾として、「JSONフォーマットで入力したデータを、RDBMSの表形式に変換して格納できる」という部分について、試してみたいと思います。
データの型について補足ですが、XML を SQL Server テーブルに取り込む際は「XML データ型」が使用できますが、JSON では JSONそのままの形で SQL Server に格納する「JSONデータ型」は用意されておらず、OPENJSON句を使うことで、表形式のデータに変換することができます。

例)
前回使用した、StudentテーブルのデータをJSON形式で出力した結果のテキストを使用してみたいと思います。そのデータの1行分のデータを、@json という変数に設定し、OPENJSON句を使ってSELECTすると、

DECLARE @json NVARCHAR(MAX)

SET @json='{"学籍番号":"20220001","氏名":"生徒A","性別":"男","生年月日":"2006-05-12","身長":175}';

SELECT * FOM OPENJSON(@json);

blog110_2

上記のように、key、value、type ということで、各項目の名前と値が、それぞれkey、value として抽出され、type には、valueの値がどのような型かを表わす数字が下記のように表示されます。
 文字列:1
 整数:2
 真偽値:3
 配列:4
 オブジェクト:5
 NULL:0

それでは、前回使用した、StudentテーブルのデータをJSON形式で出力した結果の全ての行を変数として読み込んで、行列形式で表示したい場合には、下記のように WITH句を使用します。

DECLARE @json NVARCHAR(MAX)
SET @json =  
  N'[
       {
         "学籍番号":"20220001",
         "氏名":"生徒A",
         "性別":"男",
         "生年月日":"2006-05-12",
         "身長":175
       },
       {
         "学籍番号":"20220002",
         "氏名":"生徒B",
         "性別":"女",
         "生年月日":"2007-02-21",
         "身長":158
       },
       {
         "学籍番号":"20220003",
         "氏名":"生徒C",
         "性別":"女",
         "生年月日":"2006-11-07",
         "身長":162
       },
       {
         "学籍番号":"20220004",
         "氏名":"生徒D",
         "性別":"男",
         "生年月日":"2006-07-18",
         "身長":182
       },
       {
         "学籍番号":"20220005",
         "氏名":"生徒E",
         "性別":"男",
         "生年月日":"2006-12-25"
       }
    ]'

SELECT * FROM 
 OPENJSON ( @json ) 
WITH (
         学籍番号   NVARCHAR(8),
         氏名       NVARCHAR(20),
         性別       NVARCHAR(1),
         生年月日   DATE,
         身長       int
 );

blog110_3

上記のようにRDBのテーブルのように抽出されることから、一旦 dbo.Studentテーブルの値を削除した後、先ほどのJSONフォーマットの出力データから、Studentテーブルにデータを登録したいと思います。

TRUNCATE TABLE dbo.Student;

blog110_4

JSONフォーマットの出力データから、データを登録します。

DECLARE @json NVARCHAR(MAX)
SET @json =  
  N'[
              {"学籍番号":"20220001","氏名":"生徒A","性別":"男","生年月日":"2006-05-12","身長":175},
              {"学籍番号":"20220002","氏名":"生徒B","性別":"女","生年月日":"2007-02-21","身長":158},
              {"学籍番号":"20220003","氏名":"生徒C","性別":"女","生年月日":"2006-11-07","身長":162},
              {"学籍番号":"20220004","氏名":"生徒D","性別":"男","生年月日":"2006-07-18","身長":182},
              {"学籍番号":"20220005","氏名":"生徒E","性別":"男","生年月日":"2006-12-25"}
    ]';

INSERT INTO dbo.Student
SELECT * FROM 
 OPENJSON ( @json ) 
WITH (
         学籍番号   NVARCHAR(8),
         氏名       NVARCHAR(20),
         性別       NVARCHAR(1),
         生年月日   DATE,
         身長       int
 );

blog110_5

登録されたデータを確認してみます。

SELECT * FROM dbo.Student
ORDER BY 学籍番号;

blog110_6

JSONフォーマットのテキストデータから、OPENJSON句を使ったINSERT文で、SQLServerのテーブルにデータを登録することができることが確認されました。

今日は以上まで

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

JSON出力(SQL Server)

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

blog109_1

上の写真は、昨年12月に仙台に行く機会があり、帰りが夜になったのですが、そのときに行われていた「2022SENDAI光のページェント」のイルミネーションの様子を撮ったものです。冬の仙台を明るくしたい」「杜の都を光の都へ」という想いで、1986年に始まったものらしいのですが、とても綺麗だということを聞いていたので、是非見てみたいと思い、車でその中を通って帰りました。定禅寺通の欅並木をメインステージとして、本当に大きな光のトンネルのようなイルミネーションで、とても感動しました。世界的に様々、暗いニュースが多い中で、ここを通る人たちの心に希望の明かりを灯そうという試みは、素晴らしいと感じました。
新しい1年が始まりましたが、2023年が、皆様にとって素晴らしい1年になりますようにと願いながら、今年もよろしくお願いいたします。

<本日の題材>
JSON出力(SQL Server)

今回は、SQL ServerでJSONデータを利用できる件について、確認してみようと思います。
JSONは「JavaScript Object Notation」の略称で、開発当初はJavaScriptでの利用を前提に作られた、テキストをベースにした、軽量なデータ交換をおこなうためのフォーマットとして作られました。データの構造が単純なため、人間が見たときに一目でわかるようになっていますし、コンピュータから見た場合も、読み込んだり加工したりしやすい構造で、その使いやすさからCやJava、Pythonなど、多くのプログラミング言語で広く活用されています。例えば、JSON形式のデータは以下のようなものになります。
[
   { "Id": 1, "Name" : "山田", "Age" : 20 } ,
   { "Id": 2, "Name" : "高橋", "Age" : 22 } ,
   { "Id": 3, "Name" : "鈴木", "Age" : 19 }
]
CSVファイルよりは見た目がわかりやすく、XML形式よりは加工などがしやすいと言えます。
そのJSONフォーマットをSQLServerでも利用できるようにということで、SQL Server 2016からJSON対応機能が追加されています。その機能とは、「RDBMSの表形式のデータをJSONフォーマットに変換して出力できる」ことと、逆に「JSONフォーマットで入力したデータを、RDBMSの表形式に変換して格納できる」という機能になります。

例)
以前、ブログで扱ったことのある「Student」テーブルの定義を少し変更して、データを登録し、JSONフォーマットに変換して出力してみます。

「Student」テーブルを下記のようなSQLで作成します。
CREATE TABLE dbo.Student(
学籍番号  NVARCHAR(8),
氏名      NVARCHAR(20),
性別      NVARCHAR(1),
生年月日  DATE,
身長    INT,
CONSTRAINT PK_Student PRIMARY KEY(学籍番号));

データを登録します。
INSERT INTO dbo.Student VALUES('20220001', '生徒A', '男', '2006-05-12', 175);
INSERT INTO dbo.Student VALUES('20220002', '生徒B', '女', '2007-02-21', 158);
INSERT INTO dbo.Student VALUES('20220003', '生徒C', '女', '2006-11-07', 162);
INSERT INTO dbo.Student VALUES('20220004', '生徒D', '男', '2006-07-18', 182);
INSERT INTO dbo.Student VALUES('20220005', '生徒E', '男', '2006-12-25', NULL);

データを確認すると、
SELECT * FROM dbo.Student
ORDER BY 学籍番号;

blog109_2

このSQLの最後に「FOR JSON AUTO」を追加します。
SELECT * FROM dbo.Student
ORDER BY 学籍番号 FOR JSON AUTO;

blog109_3

結果セットとして返ってくる列は1つのみで、列名は常に"JSON_F52E2B61-18A1-11d1-B105-00805F49916B"という値になり、指定はできません。そして、結果セットのデータがJSONフォーマットの値になっています。結果セットの値に改行を入れて整形すると以下のようになります。

[
              {"学籍番号":"20220001","氏名":"生徒A","性別":"男","生年月日":"2006-05-12","身長":175},
              {"学籍番号":"20220002","氏名":"生徒B","性別":"女","生年月日":"2007-02-21","身長":158},
              {"学籍番号":"20220003","氏名":"生徒C","性別":"女","生年月日":"2006-11-07","身長":162},
              {"学籍番号":"20220004","氏名":"生徒D","性別":"男","生年月日":"2006-07-18","身長":182},
              {"学籍番号":"20220005","氏名":"生徒E","性別":"男","生年月日":"2006-12-25"}
]

出力されているJSONフォーマットを見ると、学籍番号、氏名、性別はNVARCHAR型なので、文字として、生年月日はDATE型なので、こちらもJSONフォーマットでは文字としてダブルクォーテーションで囲んで出力され、身長はINT型なので、数値として、ダブルクォーテーションで囲まれずに出力されているのがわかります。また、学籍番号:20220005のデータは、身長がNULLですが、NULLの場合JSONフォーマットでは、身長というキー名も出力されないというかたちになります。
この、データがNULLの場合はキー名も出力されないというのは、SQLのオプション指定で変更できます。INCLUDE_NULL_VALUES というオプションを最後に付けるかたちになります。

SELECT * FROM dbo.Student
ORDER BY 学籍番号 FOR JSON AUTO, INCLUDE_NULL_VALUES;

blog109_4

結果セットの値に改行を入れて整形すると以下のようになります。

[
              {"学籍番号":"20220001","氏名":"生徒A","性別":"男","生年月日":"2006-05-12","身長":175},
              {"学籍番号":"20220002","氏名":"生徒B","性別":"女","生年月日":"2007-02-21","身長":158},
              {"学籍番号":"20220003","氏名":"生徒C","性別":"女","生年月日":"2006-11-07","身長":162},
              {"学籍番号":"20220004","氏名":"生徒D","性別":"男","生年月日":"2006-07-18","身長":182},
              {"学籍番号":"20220005","氏名":"生徒E","性別":"男","生年月日":"2006-12-25","身長":null}
]

学籍番号:20220005のデータに、「"身長":null」が出力されていることがわかります。

上記の FOR JSON句の「AUTO」オプションは、AUTOモードという意味で、自動的にJSON出力を書式設定してくれるものですが、「PATH」オプションを使用すると、下記のように「性別」項目以降を入れ子にするかたちのクエリを使用することで、入れ子になった出力をするようなことができます。

SELECT
              学籍番号,
              氏名,
              性別 AS [情報.性別],
              生年月日 AS [情報.生年月日],
              身長 AS [情報.身長]
FROM dbo.Student
ORDER BY 学籍番号 FOR JSON PATH, INCLUDE_NULL_VALUES;

blog109_5

結果セットの値に改行を入れて整理すると、

[
{"学籍番号":"20220001","氏名":"生徒A","情報":{"性別":"男","生年月日":"2006-05-12","身長":175}},
{"学籍番号":"20220002","氏名":"生徒B","情報":{"性別":"女","生年月日":"2007-02-21","身長":158}},
{"学籍番号":"20220003","氏名":"生徒C","情報":{"性別":"女","生年月日":"2006-11-07","身長":162}},
{"学籍番号":"20220004","氏名":"生徒D","情報":{"性別":"男","生年月日":"2006-07-18","身長":182}},
{"学籍番号":"20220005","氏名":"生徒E","情報":{"性別":"男","生年月日":"2006-12-25","身長":null}}
]

「性別」「生年月日」「身長」が、「情報」という項目の入れ子として出力されているのが確認できました。

今日は以上まで

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

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技術ブログへ
にほんブログ村

一時テーブル使用時の照合順序(SQL Server)

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

IMG_5417

これは、昨年11月に島根の実家に帰省した際に、牡丹の花で有名な大根島の「由志園」でお土産に購入した牡丹の花を、父親が家の庭に植えていたものが、今年になって咲いたのを撮った写真です。綺麗に咲いたので、とても嬉しいですね。
現在、大根島は量、質ともに全国一の牡丹苗の生産地ということです。大根島は水田や農地が少なく養蚕と漁業以外に産業とよべるものはなかったそうですが、昭和30年代から島の女性達は家族を養うために全国へ牡丹などの苗木を売り歩く行商に出かけたそうです。島の女性達によって美しい牡丹の花は全国にたくさんのファンを作り、大根島の名前は牡丹の花とともに知られるようになりました。機会があれば、また「由志園」に行ってみたいと思います。

<本日の題材>
一時テーブル使用時の照合順序(SQL Server

照合順序については、以前もブログで取り上げたことがありますが、最近、お客様の開発案件に携わる中で、一時テーブルを使ったSQLで照合順序に関するエラーが発生し、対応した経緯がありますので、ブログで取り上げてみたいと思います。

例)
以前ブログで取り上げた「照合順序(SQL Server)の異なるデータベース間のジョイン」と現象は同じことになりますが、SQL Serverの照合順序と、あるシステム用に作成したデータベースの照合順序が異なるときに、一時テーブルを利用し、かつ、データベース上のテーブルと一時テーブルをジョインするようなときに起きる現象になります。
サーバーのプロパティで確認できる「サーバーの照合順序」は「Japanese_CI_AS」であるSQL Serverを使用するとします。
サーバープロパティok

ここで、今回のシステムで使用する新しいデータベースの照合順序を、「Japanese_CS_AI_KS_WS」とします。(日本語で、大文字と小文字、ひらがなとカタカナ、全角と半角を区別する照合順序)
新規データベースプロパティok2

この環境で一時テーブルを作成した場合、一時テーブルは、システムデータベースの「tempdb」に作成されるため、一時テーブルの照合順序は「tempdb」の照合順序の設定「Japanese_CI_AS」に従って作成されます。
tempdbプロパティok

新しく作成したデータベースで、社員マスタと部門マスタを作成して、以下のようにJOINしてデータを抽出します。

SELECT b.BUMON_CD, b.BUMON_NAME, s.SYAIN_CD, s.SYAIN_NAME
FROM dbo.SYAIN_M s
JOIN dbo.BUMON_M b
ON s.BUMON_CD = b.BUMON_CD
ORDER BY b.BUMON_CD, s.SYAIN_CD;

テーブルジョイン
これを、部門マスタを一時テーブルに変更して実行してみます。

CREATE TABLE #bumon(
BUMON_CD varchar(3) NOT NULL,
BUMON_NAME varchar(20) NULL);

INSERT INTO #bumon
SELECT * FROM dbo.BUMON;

SELECT b.BUMON_CD, b.BUMON_NAME, s.SYAIN_CD, s.SYAIN_NAME
FROM dbo.SYAIN_M s
JOIN #bumon b
ON s.BUMON_CD = b.BUMON_CD
ORDER BY b.BUMON_CD, s.SYAIN_CD;

すると、下記のようなエラーが返ってきます。
「メッセージ 468、レベル 16、状態 9、行 13
equal to
操作の "Japanese_CI_AS" "Japanese_CS_AI_KS_WS" 間での照合順序の競合を解決できません。」

一時テーブルジョインエラー

テーブルの存在するデータベースとtempdbデータベースの照合順序が異なるためにこのエラーが起きることが確認されました。

そのため、前回のブログでも取り上げたように、ジョインする項目にCOLLATE句を使用するか、一時テーブルを作成するときに、COLLATE句を設定することで、エラーを回避できます。

ジョインの際にCOLLATE句を使用する場合:

SELECT b.BUMON_CD, b.BUMON_NAME, s.SYAIN_CD, s.SYAIN_NAME
FROM dbo.SYAIN_M s
JOIN #bumon b
ON s.BUMON_CD = b.BUMON_CD COLLATE Japanese_CS_AI_KS_WS
ORDER BY b.BUMON_CD, s.SYAIN_CD;

一時テーブルジョイン_collate

一時テーブルを作成するときに、COLLATE句を使用する場合:

CREATE TABLE #bumon(
BUMON_CD varchar(3) COLLATE Japanese_CS_AI_KS_WS NOT NULL,
BUMON_NAME varchar(20) COLLATE Japanese_CS_AI_KS_WS NULL);

一時テーブル作成_collate

一時テーブルとのジョインでもエラーにならずに抽出できることが確認されました。使用するデータベースの照合順序を、SQL Serverの照合順序と異なるものを使用するときには、注意が必要ですね。

今日は以上まで

にほんブログ村 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技術ブログへ
にほんブログ村