プログラミング – ページ 2 – サイゼントの技術ブログ

2022年12月26日

ソースコードの重複の排除を関数で学ぶ

「ソースコードから重複を排除して保守性を高める」という考え方は、実務で良いコードを書く上で重要な考え方です。
この考え方が身に付かない内はオブジェクト指向の理解も不十分になるのですが、いきなりオブジェクト指向から入るとこの考え方の重要性がわかりにくくなることがあります。

そこで、ソースコードから重複を排除することの意義を、関数の使い方から学ぶことが有効になることがあります。

今回は、関数を使うことでソースコードから重複を排除し、保守性が高まる例を挙げていきたいと思います。
（サンプルコードはJavaで記述します）

今回のサンプルコードでは、複数の商品の販売価格を計算します。
まずは関数を使わないサンプルコードから書いていこうと思います。

【サンプルコード（関数未使用・修正前）】

・FunctionTestMain.java

public class FunctionTestMain {

    public static void main(String［］ args) {

        // 商品の定価
        int item1OrginalPrice = 100;
        int item2OrginalPrice = 200;

        // 販売額計算
        double taxRate = 1.08;
        double item1SalesPrice = Math.floor(item1OrginalPrice * taxRate);
        double item2SalesPrice = Math.floor(item2OrginalPrice * taxRate);

        // 結果表示
        System.out.println("item1の販売額：" + item1SalesPrice);
        System.out.println("item2の販売額：" + item2SalesPrice);

    }

}

public class FunctionTestMain {

public static void main(String［］ args) {

// 商品の定価

int item1OrginalPrice = 100;

int item2OrginalPrice = 200;

// 販売額計算

double taxRate = 1.08;

double item1SalesPrice = Math.floor(item1OrginalPrice * taxRate);

double item2SalesPrice = Math.floor(item2OrginalPrice * taxRate);

// 結果表示

System.out.println("item1の販売額：" + item1SalesPrice);

System.out.println("item2の販売額：" + item2SalesPrice);

}

【実行結果】

item1の販売額：108.0
item2の販売額：216.0

1 2	item1の販売額：108.0 item2の販売額：216.0

先ほどのソースコードに対して、「店舗独自の割引額を考慮する」という修正を入れていきます。

関数を使用しない場合、割引額（discountRate）を掛けるという修正を複数個所に入れることになります。
これが、ソースコードに重複が発生している状態です。

ソースコードに重複が発生していると、一部だけ修正を漏らすことによるバグに繋がりやすくなります。
このバグを潰すために、テストする範囲も広がってしまいます。

【サンプルコード（関数未使用・修正後）】

・FunctionTestMain.java

public class FunctionTestMain {

    public static void main(String［］ args) {

        // 商品の定価
        int item1OrginalPrice = 100;
        int item2OrginalPrice = 200;

        // 販売額計算
        double taxRate = 1.08;
        double discountRate = 0.95;
        double item1SalesPrice =
                Math.floor(item1OrginalPrice * taxRate * discountRate); // 修正箇所１
        double item2SalesPrice =
                Math.floor(item2OrginalPrice * taxRate * discountRate); // 修正箇所２

        // 結果表示
        System.out.println("item1の販売額：" + item1SalesPrice);
        System.out.println("item2の販売額：" + item2SalesPrice);

    }

}

public class FunctionTestMain {

public static void main(String［］ args) {

// 商品の定価

int item1OrginalPrice = 100;

int item2OrginalPrice = 200;

// 販売額計算

double taxRate = 1.08;

double discountRate = 0.95;

double item1SalesPrice =

Math.floor(item1OrginalPrice * taxRate * discountRate); // 修正箇所１

double item2SalesPrice =

Math.floor(item2OrginalPrice * taxRate * discountRate); // 修正箇所２

// 結果表示

System.out.println("item1の販売額：" + item1SalesPrice);

System.out.println("item2の販売額：" + item2SalesPrice);

}

【実行結果】

item1の販売額：102.0
item2の販売額：205.0

1 2	item1の販売額：102.0 item2の販売額：205.0

次に、ソースコードを一旦修正前の状態に戻して、関数を入れていきます。

販売額を計算する関数（salesPriceCalc）を入れることで、ソースコードから重複を取り除くことができています。

【サンプルコード（関数使用・修正前）】

・FunctionTestMain.java

public class FunctionTestMain {

    public static void main(String［］ args) {

        // 商品の定価
        int item1OrginalPrice = 100;
        int item2OrginalPrice = 200;

        // 販売額計算
        double item1SalesPrice = salesPriceCalc(item1OrginalPrice);
        double item2SalesPrice = salesPriceCalc(item2OrginalPrice);

        // 結果表示
        System.out.println("item1の販売額：" + item1SalesPrice);
        System.out.println("item2の販売額：" + item2SalesPrice);

    }

    public static double salesPriceCalc(int originalPrice) {

        double taxRate = 1.08;
        return Math.floor(originalPrice * taxRate);

    }

}

public class FunctionTestMain {

public static void main(String［］ args) {

// 商品の定価

int item1OrginalPrice = 100;

int item2OrginalPrice = 200;

// 販売額計算

double item1SalesPrice = salesPriceCalc(item1OrginalPrice);

double item2SalesPrice = salesPriceCalc(item2OrginalPrice);

// 結果表示

System.out.println("item1の販売額：" + item1SalesPrice);

System.out.println("item2の販売額：" + item2SalesPrice);

}

public static double salesPriceCalc(int originalPrice) {

double taxRate = 1.08;

return Math.floor(originalPrice * taxRate);

}

【実行結果】

item1の販売額：108.0
item2の販売額：216.0

1 2	item1の販売額：108.0 item2の販売額：216.0

関数を使用したソースコードに対して、先ほどと同じように割引額を考慮する修正を入れます。

重複が関数により排除されているので、割引額を入れる修正は１カ所で済んでいます。
修正箇所が減っているため、修正漏れを心配する必要がなくなり、ソースコードの保守が容易になっています。
言い換えると、時間をかけずにバグが出にくい修正を行うことができるようになります。
修正が繰り返される実務のソースコードでは、これは重要なことです。

【サンプルコード（関数使用・修正後）】

・FunctionTestMain.java

public class FunctionTestMain {

    public static void main(String［］ args) {

        // 商品の定価
        int item1OrginalPrice = 100;
        int item2OrginalPrice = 200;

        // 販売額計算
        double item1SalesPrice = salesPriceCalc(item1OrginalPrice);
        double item2SalesPrice = salesPriceCalc(item2OrginalPrice);

        // 結果表示
        System.out.println("item1の販売額：" + item1SalesPrice);
        System.out.println("item2の販売額：" + item2SalesPrice);

    }

    public static double salesPriceCalc(int originalPrice) {

        double taxRate = 1.08;
        double discountRate = 0.95;
        return Math.floor(originalPrice * taxRate * discountRate); // 修正箇所１

    }

}

public class FunctionTestMain {

public static void main(String［］ args) {

// 商品の定価

int item1OrginalPrice = 100;

int item2OrginalPrice = 200;

// 販売額計算

double item1SalesPrice = salesPriceCalc(item1OrginalPrice);

double item2SalesPrice = salesPriceCalc(item2OrginalPrice);

// 結果表示

System.out.println("item1の販売額：" + item1SalesPrice);

System.out.println("item2の販売額：" + item2SalesPrice);

}

public static double salesPriceCalc(int originalPrice) {

double taxRate = 1.08;

double discountRate = 0.95;

return Math.floor(originalPrice * taxRate * discountRate); // 修正箇所１

}

【実行結果】

item1の販売額：102.0
item2の販売額：205.0

1 2	item1の販売額：102.0 item2の販売額：205.0

今回解説したことは、実際の新人研修でも教えることが多いです。
このような簡単な例を用いることで、ソースコードの重複の排除について、わかりやすく説明することができます。

ところで、突然の発表なのですが、このブログでの私の記事はこれが最後となります。
このブログの記事を読んだことがある、と意外な所から声をかけていただくこともあり、大変嬉しく思っています。
少しでも皆様のお役に立てていたのであれば幸いです。

今まで記事を読んでいただき、ありがとうございました！

2022年12月11日

COBOLのマッチング処理をC#で実装する

COBOLで使われているテクニックは過去のもののように思われがちですが、現在でもちょっとしたツールを作る時に役立ちます。
WindowsOS環境の場合は、ちょっとしたツールはC#で作るのが便利なので、今回はC#でCOBOLのマッチング処理を実装してみました。

今回は、下記の記事を参考に実装しています。
マッチング処理のロジック – サイゼントの技術ブログ

HIGH-VALUEを使う代わりにEOFを示すフラグ変数を使用しているので、その分だけ処理が複雑になっていることには注意してください。
また、この記事に限りませんが、ソースコードをコピペする場合は、「［」を「[」、「］」を「]」、「＞」を「>」、「＜」を「<」に変換するようにお願いします。

【フォルダ構成】

execute.bat
matching.cs
files┬master.csv
     └transaction.csv

execute.bat

matching.cs

files┬master.csv

└transaction.csv

【ソースコード】

・execute.bat

@echo off

C:\Windows\Microsoft.NET\Framework\v4.0.30319\csc.exe matching.cs
matching.exe
del matching.exe

pause

@echo off

C:\Windows\Microsoft.NET\Framework\v4.0.30319\csc.exe matching.cs

matching.exe

del matching.exe

pause

・matching.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;

namespace Program
{
    class Program
    {
        // EOFフラグ
        static bool isSrmEof = false;
        static bool isSrtEof = false;
        
        static void Main(string［］ args)
        {
            // ファイルオープン
            StreamReader srm = new StreamReader
                (@"files\master.csv", Encoding.UTF8);
            StreamReader srt = new StreamReader
                (@"files\transaction.csv", Encoding.UTF8);
            StreamWriter sw = new StreamWriter
                (@"files\matched.csv", false, Encoding.UTF8);
            
            // 先読みRead
            string［］ mRecord;
            string［］ tRecord;
            mRecord = mRead(srm);
            tRecord = tRead(srt);
            
            // マッチング処理のループ
            while (!isSrmEof || !isSrtEof)
            {
                // masterのみの場合
                if ((!isSrmEof &amp;&amp; isSrtEof) ||
                    (string.Compare(mRecord［0］,tRecord［0］) ＜ 0))
                {
                    // 何もしない
                    // master読み込み
                    mRecord = mRead(srm);
                }
                
                // マッチした場合
                else if ((!isSrmEof &amp;&amp; !isSrtEof) &amp;&amp;
                         (string.Compare(mRecord［0］,tRecord［0］) == 0))
                {
                    // transactionが次のキーに進むまでループ
                    while ((!isSrtEof) &amp;&amp;
                           !(string.Compare(mRecord［0］,tRecord［0］) ＜ 0))
                    {
                        // ファイル出力
                        sw.WriteLine(mRecord［1］ + "," + tRecord［1］);
                        
                        // transaction読み込み
                        tRecord = tRead(srt);
                    }
                    
                    // master読み込み
                    mRecord = mRead(srm);
                }
                
                // transactionのみの場合
                else if ((isSrmEof &amp;&amp; !isSrtEof) ||
                         (string.Compare(mRecord［0］,tRecord［0］) ＞ 0))
                {
                    // エラー出力
                    Console.WriteLine("Error:" + tRecord［0］ + " is tran only.");
                    
                    // transaction読み込み
                    tRecord = tRead(srt);
                }
            }
            
            // ファイルクローズ
            srm.Close();
            srt.Close();
            sw.Close();
        }
        
        // MasterファイルRead
        static string［］ mRead(StreamReader srm)
        {
            if (srm.Peek() == -1)
            {
                isSrmEof = true;
                return null;
            }
            else
            {
                string str = srm.ReadLine();
                return str.Split(',');
            }
        }
        
        // TransactionファイルRead
        static string［］ tRead(StreamReader srt)
        {
            if (srt.Peek() == -1)
            {
                isSrtEof = true;
                return null;
            }
            else
            {
                string str = srt.ReadLine();
                return str.Split(',');
            }
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.IO;

namespace Program

{

class Program

{

// EOFフラグ

static bool isSrmEof = false;

static bool isSrtEof = false;

static void Main(string［］ args)

{

// ファイルオープン

StreamReader srm = new StreamReader

(@"files\master.csv", Encoding.UTF8);

StreamReader srt = new StreamReader

(@"files\transaction.csv", Encoding.UTF8);

StreamWriter sw = new StreamWriter

(@"files\matched.csv", false, Encoding.UTF8);

// 先読みRead

string［］ mRecord;

string［］ tRecord;

mRecord = mRead(srm);

tRecord = tRead(srt);

// マッチング処理のループ

while (!isSrmEof || !isSrtEof)

{

// masterのみの場合

if ((!isSrmEof && isSrtEof) ||

(string.Compare(mRecord［0］,tRecord［0］) ＜ 0))

{

// 何もしない

// master読み込み

mRecord = mRead(srm);

}

// マッチした場合

else if ((!isSrmEof && !isSrtEof) &&

(string.Compare(mRecord［0］,tRecord［0］) == 0))

{

// transactionが次のキーに進むまでループ

while ((!isSrtEof) &&

!(string.Compare(mRecord［0］,tRecord［0］) ＜ 0))

{

// ファイル出力

sw.WriteLine(mRecord［1］ + "," + tRecord［1］);

// transaction読み込み

tRecord = tRead(srt);

}

// master読み込み

mRecord = mRead(srm);

}

// transactionのみの場合

else if ((isSrmEof && !isSrtEof) ||

(string.Compare(mRecord［0］,tRecord［0］) ＞ 0))

{

// エラー出力

Console.WriteLine("Error:" + tRecord［0］ + " is tran only.");

// transaction読み込み

tRecord = tRead(srt);

}

// ファイルクローズ

srm.Close();

srt.Close();

sw.Close();

}

// MasterファイルRead

static string［］ mRead(StreamReader srm)

{

if (srm.Peek() == -1)

{

isSrmEof = true;

return null;

}

else

{

string str = srm.ReadLine();

return str.Split(',');

}

// TransactionファイルRead

static string［］ tRead(StreamReader srt)

{

if (srt.Peek() == -1)

{

isSrtEof = true;

return null;

}

else

{

string str = srt.ReadLine();

return str.Split(',');

}

【実行前のファイル】

・files\master.csv

0000001,hoge                
0000002,fuga                
0000004,piyo

0000001,hoge

0000002,fuga

0000004,piyo

・files\transaction.csv

0000001,20180401,00100,00010000
0000001,20180402,00200,00020000
0000003,20180401,00001,00001000
0000004,20180401,00002,00002000

0000001,20180401,00100,00010000

0000001,20180402,00200,00020000

0000003,20180401,00001,00001000

0000004,20180401,00002,00002000

【実行結果】

execute.batをダブルクリックして実行する。

・files\matched.csv

hoge                ,20180401
hoge                ,20180402
piyo                ,20180401

hoge ,20180401

hoge ,20180402

piyo ,20180401

・標準出力

Microsoft (R) Visual C# Compiler version 4.7.3062.0
for C# 5
Copyright (C) Microsoft Corporation. All rights reserved.

This compiler is provided as part of the Microsoft (R) .NET Framework, but only supports language versions up to C# 5, w
hich is no longer the latest version. For compilers that support newer versions of the C# programming language, see http
://go.microsoft.com/fwlink/?LinkID=533240

Error:0000003 is tran only.

続行するには何かキーを押してください . . .

Microsoft (R) Visual C# Compiler version 4.7.3062.0

for C# 5

This compiler is provided as part of the Microsoft (R) .NET Framework, but only supports language versions up to C# 5, w

hich is no longer the latest version. For compilers that support newer versions of the C# programming language, see http

://go.microsoft.com/fwlink/?LinkID=533240

Error:0000003 is tran only.

続行するには何かキーを押してください . . .

Windows OSで作業や運用を行う場合は、C#を使いこなせると何かと便利です。
C#でツールを使う時に便利なソースコードは、これからも公開していきたいと思います！

2022年9月5日

文字コードの入門

この記事では、文字コードに関する初歩的な内容について簡単に書いていきます。

１．文字コードの概念とコード体系

コンピューターで取り扱う文字には、それぞれコードが割り振られています。
コードは1～4バイトの情報として取り扱われ、1バイトの文字は1バイト文字、2～4バイトの文字はマルチバイト文字と呼ばれます。
原則として、半角英数字記号は1バイト文字として扱われ、それ以外の文字はマルチバイト文字として扱われます。
（ここでは詳しく触れませんが、半角カナや人間には読めない制御文字は、1バイト文字のこともあればマルチバイト文字のこともあります）
これらの情報は、16進数で表現されることが多いです。また、プログラム上では、10進数で表現されることも多いです。

どの文字にどのコードが割り振られるのかは、コード体系によって決まります。

1バイト文字に関しては、今日のPCではASCIIというコード体系が使われます。
ASCIIにて、どの文字にどのコードが割り振られているのかは、以下の表から確認することができます。
（例えば、「A」には「0x41」（10進数で「65」）が、「a」には「0x61」（10進数で「97」）が割り振られています）
https://ja.wikipedia.org/wiki/ASCII

また、ホストコンピューターでは、1バイト文字にEBCDICという別のコード体系が使われます。
EBCDICの表は以下の通りであり、ASCIIとは異なる割り振られ方をしていることがわかります。
（例えば、「A」には「0xC1」（10進数で「193」）が割り振られています）
https://ja.wikipedia.org/wiki/EBCDIC

マルチバイト文字については、今日ではUTF-8やShiftJISといったコード体系が使われることが多いです。
（マルチバイト文字に関しては、この記事では詳細を割愛します）

２．ソースコード上で文字コードを取り扱う例

ソースコード上でも、文字コードを取り扱うことができます。
と言うより、文字は内部的には文字コードであり、「A」や「a」といった表示は人間が読みやすいように表現しているものです。

以下は、「A」や「a」といった文字が内部的にどのような文字コードを持っているのかを表示するJavaのプログラムです。
ASCIIコード表通りの文字コードが割り振られていることを確認できます。

■ソースコード

・CharCode1.java

public class CharCode1 {

    public static void main(String［］ args) {

        // 「A」を文字コードとして表現
        String char_A = "A"; // 文字列"A"を定義
        short code_A = (short)char_A.charAt(0); // 文字列"A"を文字コードに変換
        System.out.println("「" + char_A + "」の文字コード：" + code_A); // 表示

        // 「a」を文字コードとして表現
        String char_a = "a"; // 文字列"a"を定義
        short code_a = (short)char_a.charAt(0); // 文字列"a"を文字コードに変換
        System.out.println("「" + char_a + "」の文字コード：" + code_a); // 表示

    }

}

public class CharCode1 {

public static void main(String［］ args) {

// 「A」を文字コードとして表現

String char_A = "A"; // 文字列"A"を定義

short code_A = (short)char_A.charAt(0); // 文字列"A"を文字コードに変換

System.out.println("「" + char_A + "」の文字コード：" + code_A); // 表示

// 「a」を文字コードとして表現

String char_a = "a"; // 文字列"a"を定義

short code_a = (short)char_a.charAt(0); // 文字列"a"を文字コードに変換

System.out.println("「" + char_a + "」の文字コード：" + code_a); // 表示

}

■実行結果

「A」の文字コード：65
「a」の文字コード：97

1 2	「A」の文字コード：65 「a」の文字コード：97

また、文字の比較も、文字コードにより行うことができます。
以下は、「ABC」という文字列を前から1文字ずつ読み込み、「B」の文字を読みこんだ時だけ表示を行うJavaのプログラムです。

■ソースコード

・CharCode2.java

public class CharCode2 {

    public static void main(String［］ args) {

        // 文字列「ABC」を1文字ずつ読込
        String str = "ABC"; // 文字列"ABC"を定義
        for (int i = 0; i ＜ str.length() ; i++) { // 1文字ずつループ
            short code = (short)str.charAt(i); // 読み込んだ文字の文字コード取得
            if (code == 66) { // 文字コードが66（"B"）の場合
            // if (str.substring(i, i+1).equals("B")) { // このように書いても同じ
                System.out.println((i + 1) + "番目の文字：" +
                    str.charAt(i) + " 文字コード：" + code);
            }
        }
    }

}

public class CharCode2 {

public static void main(String［］ args) {

// 文字列「ABC」を1文字ずつ読込

String str = "ABC"; // 文字列"ABC"を定義

for (int i = 0; i ＜ str.length() ; i++) { // 1文字ずつループ

short code = (short)str.charAt(i); // 読み込んだ文字の文字コード取得

if (code == 66) { // 文字コードが66（"B"）の場合

// if (str.substring(i, i+1).equals("B")) { // このように書いても同じ

System.out.println((i + 1) + "番目の文字：" +

str.charAt(i) + " 文字コード：" + code);

}

■実行結果

2番目の文字：B 文字コード：66

1	2番目の文字：B 文字コード：66

３．文字コードを意識する理由

ここで、「なぜプログラミングで文字コードを意識する必要があるのか」と疑問を持った方もいらっしゃるかもしれません。
確かに、人間には読みにくい文字コードを使ったプログラミングをするよりも、文字を直接使ったプログラミングの方が望ましいように思えます。
例えば、この記事で２つ目に取り上げたソースコード（CharCode2.java）では、文字コードを使用しなくても文字コードを使った時のような制御を実現でき、それならば文字コードを使った制御をしない方が望ましいように思えます。

それでも文字コードを意識する一番の理由は、バイナリの制御文字を取り扱うために必要だからです。
文字には、人間が読める文字の他に、コンピューターに命令を送るための制御文字が存在します。
多くの人にとって一番馴染みがある制御文字の一つが「改行文字」です。
コンピューターが改行文字を読みこむと、文章の改行が行われます。
（この記事が正しく改行されて読みやすくなっているのも、この「改行文字」のおかげです）
改行文字はOSによって割り振られている文字コードが異なりますが、Unix/Linuxの場合は「10」が割り振られています。
この文字は、プログラム上で”A”や”a”といった形で記述することができず、プログラミング言語やライブラリでサポートされない場合は、文字コードを参照しないと改行文字であるか否かを判定できません。

例えば、Javaで絵文字を含む文字列を１文字ずつ切り取る場合に、文字コードの参照が必要になります。
解説やコード例は以下の記事に書いています。
java：Unicodeの絵文字をjavaで取り扱う

いかがでしたでしょうか。

文字コードについてプログラミングの入門書に書かれていることは少ないですが、実務では度々目にします。
実務でプログラミングをするのであれば、文字コードについても早めに勉強しておくことをお勧めします。

2022年8月16日

SQL_select文の結果の結合等（集合演算子）

今回は、select文の結果を結合する文法の紹介です。
結果を結合するためには、集合演算子のUNION(重複排除したい場合はUNION ALL)を用います。
運用作業やプログラミングで複数のselect文の結果を１回のSQL文発行で取得したいことがあるので、知っておくと便利です。

ついでに、UNIONやUNION ALL以外の集合演算子についても紹介します。

【文法】

select文
集合演算子
select文
(集合演算子 select文…)
(order by…)

select文

集合演算子

select文

(集合演算子 select文…)

(order by…)

※集合演算子には以下のようなものがある

・UNION

前後のselect文の結果を結合する。
重複する結果は1行にまとめられる。

・UNION ALL

前後のselect文の結果を結合する。
重複する結果はまとめられない。

・MINUS、EXCEPT

前のselect文の結果から後のselect文の結果を取り除く。
OracleはMINUS、それ以外はEXCEPTを用いる。
（MySQLではサポート外）

・INTERSECT

前のselect文の結果と後のselect文の結果で一致するものだけを抽出する。
（MySQLではサポート外）

※「order by」は個別のselect文にはかからず、集合演算子で結合した結果全体にかかる。

【対象テーブル例】

・Ａ支店商品

 +--------+--------+
 | 商品ID | 商品名 |
 +--------+--------+
 | 1      | 商品１ |
 | 2      | 商品２ |
 | 3      | 商品３ |
 +--------+--------+

+--------+--------+

| 商品ID | 商品名 |

+--------+--------+

| 1 | 商品１ |

| 2 | 商品２ |

| 3 | 商品３ |

+--------+--------+

・Ｂ支店商品

 +--------+--------+
 | 商品ID | 商品名 |
 +--------+--------+
 | 3      | 商品３ |
 | 4      | 商品４ |
 | 5      | 商品５ |
 +--------+--------+

+--------+--------+

| 商品ID | 商品名 |

+--------+--------+

| 3 | 商品３ |

| 4 | 商品４ |

| 5 | 商品５ |

+--------+--------+

【使用例】

・UNION

select * from Ａ支店商品
union
select * from Ｂ支店商品
order by 商品ID;

select * from Ａ支店商品

union

select * from Ｂ支店商品

order by 商品ID;

↓

 +--------+--------+
 | 商品ID | 商品名 |
 +--------+--------+
 | 1      | 商品１ |
 | 2      | 商品２ |
 | 3      | 商品３ |
 | 4      | 商品４ |
 | 5      | 商品５ |
 +--------+--------+

+--------+--------+

| 商品ID | 商品名 |

+--------+--------+

| 1 | 商品１ |

| 2 | 商品２ |

| 3 | 商品３ |

| 4 | 商品４ |

| 5 | 商品５ |

+--------+--------+

・UNION ALL

select * from Ａ支店商品
union all
select * from Ｂ支店商品
order by 商品ID;

select * from Ａ支店商品

union all

select * from Ｂ支店商品

order by 商品ID;

↓

 +--------+--------+
 | 商品ID | 商品名 |
 +--------+--------+
 | 1      | 商品１ |
 | 2      | 商品２ |
 | 3      | 商品３ |
 | 3      | 商品３ |
 | 4      | 商品４ |
 | 5      | 商品５ |
 +--------+--------+

+--------+--------+

| 商品ID | 商品名 |

+--------+--------+

| 1 | 商品１ |

| 2 | 商品２ |

| 3 | 商品３ |

| 4 | 商品４ |

| 5 | 商品５ |

+--------+--------+

・MINUS(EXCEPT)

select * from Ａ支店商品
minus
select * from Ｂ支店商品
order by 商品ID;

select * from Ａ支店商品

minus

select * from Ｂ支店商品

order by 商品ID;

↓

 +--------+--------+
 | 商品ID | 商品名 |
 +--------+--------+
 | 1      | 商品１ |
 | 2      | 商品２ |
 +--------+--------+

+--------+--------+

| 商品ID | 商品名 |

+--------+--------+

| 1 | 商品１ |

| 2 | 商品２ |

+--------+--------+

・INTERSECT

select * from Ａ支店商品
intersect
select * from Ｂ支店商品
order by 商品ID;

select * from Ａ支店商品

intersect

select * from Ｂ支店商品

order by 商品ID;

↓

 +--------+--------+
 | 商品ID | 商品名 |
 +--------+--------+
 | 3      | 商品３ |
 +--------+--------+

+--------+--------+

| 商品ID | 商品名 |

+--------+--------+

| 3 | 商品３ |

+--------+--------+

いかがでしたでしょうか。

今回は、UNIONに代表される集合演算子について記事を書きました。
何回かに分けて基本的なSQLの書き方に関する記事を書いてきましたが、これが最後になります。
これまで書いてきた文法は何れも実務では頻出なので、運用作業に携わっている人は特に覚えておいた方が良いものばかりです。知らない・使い方があやふやな文法があれば、これを機に覚えることをお勧めします。

これからも、様々な角度から役に立つ記事を書いていきたいと思います！

2022年8月1日

SQL_副問合せ・相関副問合せ

今回は副問合せと相関副問合せの紹介です。
この文法を用いると、問い合わせの結果を別の問い合わせに使い回すことができるようになり、複雑な問い合わせを１本のSQLで書けるようになります。

副問合せは相関副問合せよりも文法的に簡単で、実務、特に運用作業では頻出です。
また、性能面を考慮すると相関副問合せを使った方が良いケースが多いため、相関副問合せもプログラミングでは良く使われます。

なお、今回は紹介しませんが、副問合せはFROM句にも書くことができます。

【文法】

・副問合せ（単一行を返す場合のみ使用可能）

…where カラム名 比較演算子 (select…)

1	…where カラム名比較演算子 (select…)

・副問合せ（副問合せの何れかの結果と一致することを確認）

…where カラム名 IN (select…)

1	…where カラム名 IN (select…)

・副問合せ（副問合せのどの結果とも一致しないことを確認）

…where カラム名 NOT IN (select…)

1	…where カラム名 NOT IN (select…)

・副問合せ（副問合せの全ての結果と比較し、全ての結果よりも（大きい、小さい等））

…where カラム名 比較演算子 ALL (select…)

1	…where カラム名比較演算子 ALL (select…)

・副問合せ（副問合せの何れかの結果よりも（大きい、小さい等））

…where カラム名 比較演算子 SOME (select…)

1	…where カラム名比較演算子 SOME (select…)

※SOMEの代わりにANYを使用しても良い

・相関問い合わせ（副問合せ部で存在する行のみ抽出）

…from テーブル１
…where EXISTS (select 1 FROM テーブル２ WHERE カラム名 = テーブル１.カラム名 …)

1 2	…from テーブル１ …where EXISTS (select 1 FROM テーブル２ WHERE カラム名 = テーブル１.カラム名 …)

※上記は正式な文法ではないが、通常は上記のように使う
※EXISTSの前のNOTをつけると存在しない行のみ抽出することができる
※EXISTSの中のSELECT文のカラム指定は問わない（1でなくても良い）

【対象テーブル例】

・商品

 +--------+--------+
 | 商品ID | 商品名 |
 +--------+--------+
 | 1      | 商品１ |
 | 2      | 商品２ |
 | 3      | 商品３ |
 | 4      | 商品４ |
 | 5      | 商品５ |
 +--------+--------+

+--------+--------+

| 商品ID | 商品名 |

+--------+--------+

| 1 | 商品１ |

| 2 | 商品２ |

| 3 | 商品３ |

| 4 | 商品４ |

| 5 | 商品５ |

+--------+--------+

・消費者テスト結果

 +--------+------+
 | 商品ID | 得点 |
 +--------+------+
 | 1      | 80   |
 | 2      | 85   |
 | 3      | 90   |
 | 5      | 95   |
 +--------+------+

+--------+------+

| 商品ID | 得点 |

+--------+------+

| 1 | 80 |

| 2 | 85 |

| 3 | 90 |

| 5 | 95 |

+--------+------+

・合格基準点

 +------+
 | 得点 |
 +------+
 | 90   |
 +------+

+------+

| 得点 |

+------+

| 90 |

+------+

【使用例】

asは省略可能である。
実務でも試験でも使用頻度が高い文法のみ例を記載する。

・=演算子

select s.商品名
from   商品 as s
join   消費者テスト結果 as t
on    (s.商品ID = t.商品ID)
where  t.得点 = (select max(t_tmp.得点)
                 from   消費者テスト結果 as t_tmp);

select s.商品名

from 商品 as s

join 消費者テスト結果 as t

on (s.商品ID = t.商品ID)

where t.得点 = (select max(t_tmp.得点)

from 消費者テスト結果 as t_tmp);

↓

select s.商品名
from   商品 as s
join   消費者テスト結果 as t
on    (s.商品ID = t.商品ID)
where  t.得点 = 95;

select s.商品名

from 商品 as s

join 消費者テスト結果 as t

on (s.商品ID = t.商品ID)

where t.得点 = 95;

↓

+--------+
| 商品名 |
+--------+
| 商品５ |
+--------+

+--------+

| 商品名 |

+--------+

| 商品５ |

+--------+

・不等号演算子

select s.商品名
from   商品 as s
join   消費者テスト結果 as t
on    (s.商品ID = t.商品ID)
where  t.得点 ＞ (select k.得点
                 from   合格基準点 as k);

select s.商品名

from 商品 as s

join 消費者テスト結果 as t

on (s.商品ID = t.商品ID)

where t.得点＞ (select k.得点

from 合格基準点 as k);

↓

select s.商品名
from   商品 as s
join   消費者テスト結果 as t
on    (s.商品ID = t.商品ID)
where  t.得点 ＞ 90;

select s.商品名

from 商品 as s

join 消費者テスト結果 as t

on (s.商品ID = t.商品ID)

where t.得点＞ 90;

↓

+--------+
| 商品名 |
+--------+
| 商品３ |
| 商品５ |
+--------+

+--------+

| 商品名 |

+--------+

| 商品３ |

| 商品５ |

+--------+

・IN句

select s.商品名
from   商品 as s
where  s.商品ID IN (select t.商品ID
                    from   消費者テスト結果 as t);

select s.商品名

from 商品 as s

where s.商品ID IN (select t.商品ID

from 消費者テスト結果 as t);

↓

select s.商品名
from   商品 as s
where  s.商品ID IN (1,2,3,5);

select s.商品名

from 商品 as s

where s.商品ID IN (1,2,3,5);

↓

+--------+
| 商品名 |
+--------+
| 商品１ |
| 商品２ |
| 商品３ |
| 商品５ |
+--------+

+--------+

| 商品名 |

+--------+

| 商品１ |

| 商品２ |

| 商品３ |

| 商品５ |

+--------+

・EXISTS句

select s.商品名
from   商品 as s
where  EXISTS (select 1
               from   消費者テスト結果 as t
               where  t.商品ID = s.商品ID);

select s.商品名

from 商品 as s

where EXISTS (select 1

from 消費者テスト結果 as t

where t.商品ID = s.商品ID);

↓

+--------+
| 商品名 |
+--------+
| 商品１ |
| 商品２ |
| 商品３ |
| 商品５ |
+--------+

+--------+

| 商品名 |

+--------+

| 商品１ |

| 商品２ |

| 商品３ |

| 商品５ |

+--------+

※上記のIN句の例と同じ結果となる

【性能について補足】

EXISTS句と同様のことはIN句でも可能だが、性能面で違いが出ることが多い。
細かい所はRDBMSによって異なるため、最終的にはRDBMSの特性を調べたり実行計画を取得したりしながらチューニングするべきであるが、一般的に以下のようなことが言える。

１．実行順の違い

原則として件数の絞り込みはなるべく早い段階で行うべきであり、少ない件数に絞り込むことができる条件は先に実行するべきである。
例えば、100件中10件まで絞り込める条件と、100件中90件までしか絞り込めない条件がある場合、100件中10件まで絞り込める条件を先に実行すれば後に実行される条件はその10件（以下）のみ対象とすれば良くなるので、その方が性能が良くなる。
IN句とEXISTS句ではこの実行順が異なり、先に副問合せが実行されるのがIN句、後で副問合せが実行されるのはEXISTS句なので、副問合せで条件を絞り込めるか否かでどちらの句を使うのかを決めるべきである。

２．インデックスの使用有無

IN句、特にNOT IN句については、インデックスを使用しない逐次検索が行われてしまうことが多い。
そのため、一般論として、インデックスが使われやすいEXISTS句の方が性能が良くなることが多い。

いかがでしたでしょうか。

今回は、SQLでの副問合せと相関副問合せについて記事を書きました。
プログラムでSQLを発行する場合は複数回問い合わせを発行してその結果をプログラム側で突き合わせることの方が多いかもしれませんが、運用作業では重宝します。

複数の問い合わせを１つの問い合わせで済ませる文法としては、UNIONのような集合演算子も良く使います。
これは次回取り上げたいと思います！