【SQL Server(連載)】UNION -41-

www.pon-x.jp

今回はUNIONを紹介します。

UNION

簡単に言うと、行結合です。
2つのテーブルを行方向に結合してあげるためにUNIONを使います。

ためしに、Person.PersonテーブルのLastNameを結合してみます。
ただし、WHERE句でPersonTypeを抽出条件として指定しています。

SELECT LastName FROM Person.Person WHERE PersonType = 'EM'

UNION

SELECT LastName FROM Person.Person WHERE PersonType = 'IN'

570件のデータが確認されます。
実は、UNION とだけ指定すると、重複する行は削除されてユニークな値のみが出力されるのです。

UNION ALL

では、重複削除を行わず単純にテーブルを行結合するにはどうすればよいかというと、UNIONのあとにALLを付けます。
こんなかんじです。

SELECT LastName FROM Person.Person WHERE PersonType = 'EM'

UNION ALL

SELECT LastName FROM Person.Person WHERE PersonType = 'IN'

18757件と結果が全然違います。

注意

テーブル同士を行結合するのですから、カラム数は一致していなくてはなりません。 例えばこれはエラーになります。

SELECT LastName FROM Person.Person WHERE PersonType = 'EM'

UNION ALL

SELECT LastName,FirstName FROM Person.Person WHERE PersonType = 'IN'

こんなかんじです。




データ型の違いもダメです。

SELECT LastName FROM Person.Person WHERE PersonType = 'EM'

UNION ALL

SELECT BusinessEntityID FROM Person.Person WHERE PersonType = 'IN'

エラーになります。

以上、今回はここまで。

【SQL Server(連載)】WITH -40-

www.pon-x.jp

あけましておめでとうございます。今年もプログラ生活をよろしくお願いいたします。

さて、今年一発目はWITHの使い方です。

WITH

結構前に、サブクエリの話をしました。

www.pon-x.jp

WITHはこのようなサブクエリを可読性良く書けるのが特徴です。
早速書いてみよう。

上記の過去記事のSQLはこんなかんじでした。

SELECT * 
FROM 
    (
        SELECT 
            PersonType,
            COUNT(BusinessEntityID) AS CNT
        FROM Person.Person
        WHERE MiddleName IS NOT NULL
        GROUP BY PersonType
    ) TBL1
WHERE CNT > 100

これの出力はこんなかんじでした。

これをWITHを使って書いてみます。

WITH TBL AS (SELECT 
            PersonType,
            COUNT(BusinessEntityID) AS CNT
        FROM Person.Person
        WHERE MiddleName IS NOT NULL
        GROUP BY PersonType )


SELECT * FROM TBL WHERE CNT > 100

こんなかんじです。

サブクエリとしていた部分を分離しているようにかけているので、見やすくなりましたね。

複数定義することも可能です。
適当に、WITHで定義したテーブルを内部結合してみます。

WITH TBL1 AS (
        SELECT 
            *
        FROM Person.Person
        )
,TBL2 AS (
        SELECT * 
        FROM Person.Password
    )


SELECT * 
FROM TBL1 T1
    INNER JOIN TBL2 T2
        ON T1.BusinessEntityID = T2.BusinessEntityID

こんなかんじに、可読性を考えてあげるとメンテナンスもしやすくなるので積極的に使っていきたいですね。

【SQL Server(連載)】トランザクションの実装 その2 -39-

www.pon-x.jp

前回の続き

前回から、トランザクションの実装をしてますが、今回はエラーを検知してロールバックしてみたいと思います。

エラー検知

エラー検知はこのような構文です。

BEGIN TRY

    -- ###############################
    -- ここでエラーが発生したらCATCHに飛ばす
    -- ###############################

END TRY

BEGIN CATCH

    -- ###############################
    -- エラーがあったらここの内容を処理する
    -- ###############################

END CATCH

こんな感じです。

前回の内容に組み込んでみる。

では、早速やってみましょう。

IF OBJECT_ID(N'tempdb..#TEMP', N'U') IS NOT NULL
    DROP TABLE #TEMP

CREATE TABLE #TEMP
(
    NUM BIGINT,
    TXT NVARCHAR(10)
)


BEGIN TRY

    BEGIN TRANSACTION


        DECLARE @CNT INT

        SET @CNT = 0

        WHILE @CNT <= 30
            BEGIN
                INSERT INTO #TEMP
                VALUES(
                    POWER(2, @CNT),
                    '2^' + CONVERT(VARCHAR, @CNT)
                )

                IF @CNT = 20
                    SET @CNT = @CNT / 0

                SET @CNT += 1
            END


    COMMIT TRANSACTION
END TRY

BEGIN CATCH

    ROLLBACK TRANSACTION

END CATCH

SELECT * FROM #TEMP

上記の内容は、WHILEのループ内で@CNTが20の時に、わざとエラーを発生させています。
エラーをキャッチしてロールバックしているので、＃TEMPはからになって、最後に出力されます。
こんな感じです。

ロールバックしないで、エラーが出るまで処理をする

通常、エラーがでたら処理がとまってしまうのですが、TRY～CATCHはうまくエラーをハンドリングすることができます。
では、エラーがでるまで処理するというよみましょう。

IF OBJECT_ID(N'tempdb..#TEMP', N'U') IS NOT NULL
    DROP TABLE #TEMP

CREATE TABLE #TEMP
(
    NUM BIGINT,
    TXT NVARCHAR(10)
)


BEGIN TRY

    BEGIN TRANSACTION


        DECLARE @CNT INT

        SET @CNT = 0

        WHILE @CNT <= 30
            BEGIN
                INSERT INTO #TEMP
                VALUES(
                    POWER(2, @CNT),
                    '2^' + CONVERT(VARCHAR, @CNT)
                )

                IF @CNT = 20
                    SET @CNT = @CNT / 0

                SET @CNT += 1
            END


    COMMIT TRANSACTION
END TRY

BEGIN CATCH

    PRINT('##### ERROR #####')
    PRINT('@CNT -> ' + CONVERT(VARCHAR, @CNT))
    PRINT('##### ERROR #####')
    RETURN

END CATCH

SELECT * FROM #TEMP

上記の内容は、WHILEのループ内で@CNTが20の時に、わざとエラーを発生させています。
@CNTを0で割ろうとしていますが、分母が0になる数は存在しないので、エラーになります。

こんなかんじの出力です。
@CNTが20でおわっているのが確認できますね。

BEGIN CATCH以下の内容を確認しておこう。

BEGIN CATCH

    PRINT('##### ERROR #####')
    PRINT('@CNT -> ' + CONVERT(VARCHAR, @CNT))
    PRINT('##### ERROR #####')
    RETURN

END CATCH

SELECT * FROM #TEMP

こんなかんじですが、RETURNで処理を終了しています。
つまり、それ以下のSELECT * FROM #TEMPは処理されないということになります。
SELECT * FROM #TEMPは、もしエラーが発生されなかったら処理されるといことになります。

こんなかんじで、エラーがあったときにそこで終わるのかどうかということも明示しておく必要があります。

以上、今回はここまで。S

【SQL Server(連載)】トランザクションの実装 その1 -38-

www.pon-x.jp

前回はトランザクションの意義について説明しました。
今回は実装してみたいと思います。

コミット

では、トランザクションで処理を行いコミットしてみたいと思います。
以下例文です。

IF OBJECT_ID(N'tempdb..#TEMP', N'U') IS NOT NULL
    DROP TABLE #TEMP

CREATE TABLE #TEMP
(
    NUM BIGINT,
    TXT NVARCHAR(10)
)


BEGIN TRANSACTION


    DECLARE @CNT INT

    SET @CNT = 0

    WHILE @CNT <= 30
        BEGIN
            INSERT INTO #TEMP
            VALUES(
                POWER(2, @CNT),
                '2^' + CONVERT(VARCHAR, @CNT)
            )

            SET @CNT += 1
        END


COMMIT TRANSACTION

SELECT * FROM #TEMP

こんな感じに出力されます。

BEGIN TRANSACTIONでトランザクションを開始して、COMMIT TRANSACTIONでコミットされるということです。

ロールバック

ではもし、COMMIT TRANSACTIONではなく、ROLLBACK TRANSACTIONだとどうなるでしょうか。

IF OBJECT_ID(N'tempdb..#TEMP', N'U') IS NOT NULL
    DROP TABLE #TEMP

CREATE TABLE #TEMP
(
    NUM BIGINT,
    TXT NVARCHAR(10)
)


BEGIN TRANSACTION


    DECLARE @CNT INT

    SET @CNT = 0

    WHILE @CNT <= 30
        BEGIN
            INSERT INTO #TEMP
            VALUES(
                POWER(2, @CNT),
                '2^' + CONVERT(VARCHAR, @CNT)
            )

            SET @CNT += 1
        END


ROLLBACK TRANSACTION

SELECT * FROM #TEMP

こんなかんじに、トランザクションを開始してそれ以降の処理を行いますが、ロールバックを行うので、最後はトランザクション処理の前に戻ってしまうので、SELECTで出力しても何も中身はありません。

以上、次回はエラーを検知してロールバック処理を行います。

【SQL Server(連載)】トランザクション -37-

www.pon-x.jp

前回の続き

今回はトランザクションのはなしです。
ちなみに今回は100%読み物です。

トランザクション

例えば、銀行の預金を管理するデータベースがあったとします。
Aさんは10万円の預金があります。
また、キャッシュカードはAさんと、Aさんの奥さんのBさんも家族カードとして同一口座のものをもっています。
例えば、ほぼ同時に2人が6万円ずつ払い戻しをしたとします。
ほぼ同時だったので、データベースを参照したら10万円の預金があったので、結果6万円ずつ払い戻しができました・・・。となったら大変なことですよね。

こんな時に必要な処理がトランザクションです。

トランザクションは複数の処理をひとまとめにして、処理の最中は他の処理によってテーブルが更新されないようにロックしてしまうものです。
今回の預金の話は、

1. 預金額を参照する
2. 払い戻し金額が預金額を超えていたら、処理を終える。
3. 払い戻し金額が預金額を超えていなければ払い戻し金額を書き込み、現金を出力する。

という処理ですね。(実際はもっとあると思いますが、ひとまず単純に考えます)

トランザクションによるテーブルロックがなければ、預金額を参照してAさん、Bさんともに10万円というデータが得られてしまえば、2人とも上記の3の処理を行ってしまうということになってしまうのです。

ですから、上記1，2，3の処理を一連のひとつの処理と考え、先にアクセスした人がテーブルロックを行い、次にアクセスした人は前の人のロックが解除するのをまっているというのがトランザクションの考え方です。

コミットとロールバック

トランザクションは複数の処理をひとつにまとめるわけですから、処理途中にひとつひとつが確定したのでは、まとめるとはいえません。
そこで、仮にデータの書き換えなどの処理を行い、最終的にそれで確定するか、もしくは処理をなかったことにするかを選択できます。
もし、処理を確定するときはコミット、処理をなかったことにするときはロールバックを行います。

また、ロールバック処理を行う際は、例外がおきたとき、つまりエラー検知を行うことがセットにされることが多いので、これも紹介をしておきます。

エラー検知

処理を大量に行うとき、必ずしもすべてうまくいくとはかぎりません。
ですので予期せぬ例外がおきたときの対応をしておくことも必要です。
エラーが起きたときにはどのように処理を行うのかを決めておく必要があります。
エラーが予期できるようなことであれば、ルールベースで回避しておくべきでしょうが、そうでないときは多くは、トランザクション内でおきていることであればロールバックさせておくことが無難です。

以上、今回はここまで。
次回は実践してみたいと思います。

【SQL Server(連載)】正規化(第3正規化) -36-

www.pon-x.jp

前回の続き

今回は、第3正規化についてのはなしです。

第3正規化

前回まで第2正規化まで行い、以下のテーブルが得られました。

TABLE: 売上　Primary Key：注文番号、商品ID

注文番号	注文年月日	商品ID	数量	顧客ID	顧客名	顧客TEL	顧客住所
1001	2020/12/20	K101	3	P501	岡氏　好男	0000-00-0000	東京都
1001	2020/12/20	K112	2	P501	岡氏　好男	0000-00-0000	東京都
1001	2020/12/20	K126	1	P501	岡氏　好男	0000-00-0000	東京都
1002	2020/12/21	Y516	2	P905	野菜　好子	1111-11-1111	埼玉県
1002	2020/12/21	Y842	3	P905	野菜　好子	1111-11-1111	埼玉県

TABLE: 商品　Primary Key：商品ID

商品ID	商品名	単価
K101	うまい棒	10
K112	がりがりくん	60
K126	チップスター	150
Y516	とまと	90
Y842	きゅうり	30

前回、関数従属性の説明をしましたね。
そのことから、商品テーブルにカラムを分離させてあげたということです。

第2正規化は主キーに対いて従属しているカラムを分離させましたが、主キー以外の項目についても従属関係があるカラムがあるとき、これを推移的関数従属といい、別テーブルに切り分ける必要があります。
これを第3正規化といい、第3正規化した結果を第3正規形といいます。

推移的関数従属

上述しましたが、推移的関数従属は主キー以外のカラムで従属関係にあるカラムをテーブル分離することですが、今回のケースは売上テーブルの、顧客IDに対する、顧客名と顧客TELと顧客住所がこれにあたります。
なぜなら、顧客IDはユニークなはずなので、顧客名と顧客TELと顧客住所は一意にきまるだめです。

では、第3正規化をしてみましょう。

TABLE: 売上　Primary Key：注文番号、商品ID

注文番号	注文年月日	商品ID	数量	顧客ID
1001	2020/12/20	K101	3	P501
1001	2020/12/20	K112	2	P501
1001	2020/12/20	K126	1	P501
1002	2020/12/21	Y516	2	P905
1002	2020/12/21	Y842	3	P905

TABLE: 商品　Primary Key：商品ID

商品ID	商品名	単価
K101	うまい棒	10
K112	がりがりくん	60
K126	チップスター	150
Y516	とまと	90
Y842	きゅうり	30

TABLE: 顧客　Primary Key：顧客ID

顧客ID	顧客名	顧客TEL	顧客住所
P501	岡氏　好男	0000-00-0000	東京都
P501	岡氏　好男	0000-00-0000	東京都
P501	岡氏　好男	0000-00-0000	東京都
P905	野菜　好子	1111-11-1111	埼玉県
P905	野菜　好子	1111-11-1111	埼玉県

これで第3正規形まで完成です。







以上、3回にわたり正規形の説明でした。

【SQL Server(連載)】正規化(第2正規化) -35-

www.pon-x.jp

前回の続き

今回は、第2正規化についてのはなしです。

第2正規化

前回、第1正規化を行い以下のようにテーブルを設計してきました。

注文番号	注文年月日	商品ID	商品名	単価	数量	顧客ID	顧客名	顧客TEL	顧客住所
1001	2020/12/20	K101	うまい棒	10	3	P501	岡氏　好男	0000-00-0000	東京都
1001	2020/12/20	K112	がりがりくん	60	2	P501	岡氏　好男	0000-00-0000	東京都
1001	2020/12/20	K126	チップスター	150	1	P501	岡氏　好男	0000-00-0000	東京都
1002	2020/12/21	Y516	とまと	90	2	P905	野菜　好子	1111-11-1111	埼玉県
1002	2020/12/21	Y842	きゅうり	30	3	P905	野菜　好子	1111-11-1111	埼玉県

上記の形式で、リレーショナルデータベースとしては扱えるのですが、まだこれではメンテナンス効率の悪い構造ですので、引き続き正規化を進めていきます。

第2正規化では、主キーに従属するカラムを別のテーブルに分離してあげることを目的とします。

関数従属性

一方の値が決まるともう一方の値も決まる関係を関数従属性といいます。
その関係のなかで主キー全てのカラムを用いて値が決まる関係を完全従属、主キーの一部のカラムによって値が決まる関係を部分従属といいます。
では、関数従属性の考え方を用いて、カラムを分離してみます。

TABLE: 売上　Primary Key：注文番号、商品ID

注文番号	注文年月日	商品ID	数量	顧客ID	顧客名	顧客TEL	顧客住所
1001	2020/12/20	K101	3	P501	岡氏　好男	0000-00-0000	東京都
1001	2020/12/20	K112	2	P501	岡氏　好男	0000-00-0000	東京都
1001	2020/12/20	K126	1	P501	岡氏　好男	0000-00-0000	東京都
1002	2020/12/21	Y516	2	P905	野菜　好子	1111-11-1111	埼玉県
1002	2020/12/21	Y842	3	P905	野菜　好子	1111-11-1111	埼玉県

TABLE: 商品　Primary Key：商品ID

商品ID	商品名	単価
K101	うまい棒	10
K112	がりがりくん	60
K126	チップスター	150
Y516	とまと	90
Y842	きゅうり	30

以上、今回はここまで。
次回は第3正規化をやります。