SQLの結合3パターン。クロスジョインはカラム列からレコード行への変換で使おう。

データベース

SQLのテーブル結合は、以下の3種類の方法があります。


  • 内部結合
  • 外部結合
  • クロス結合

取得したいデータの内容によって、結合の方法を使い分ける必要があります。


今回は、3種類の結合方法についてPostgreSQLを使って解説していきます。


環境情報


  • DB:PostgreSQL9.6

テーブル定義と格納データ


解説で使用するテーブル定義と格納データは以下になります。

『役職テーブル』と『社員テーブル』の、2つのテーブルを想定します。


create table yakusyoku (
  yakusyoku_id serial not null
  , yakusyoku_name varchar(10) not null
  , constraint yakusyoku_pkey primary key (yakusyoku_id)
) ;

create table syain (
  syain_id serial not null
  ,yakusyoku_id integer
  , syain_name varchar(10) not null
  , constraint syain_pkey primary key (syain_id)
) ;
"yakusyoku_id"	"yakusyoku_name"
-----------------------------------
1				"部長"
2				"課長"
3				"係長"
4				"主任"
"syain_id"	"yakusyoku_id"	"syain_name"
----------------------------------------------
1			1				"山田部長"
2			2				"鈴木課長"
3			3				"佐藤係長"
4			3				"佐藤係長"

内部結合/依存関係ありのデータを取得する


内部結合は、依存関係(レコード間のリレーション)があるデータのみを取得します。
今回の例では、役職が付いている社員のみが取得するSQLを実現することができます。


SELECT
    A.yakusyoku_id	
    A.yakusyoku_name	
    B.syain_id	
    B.syain_name
FROM
    yakusyoku as A INNER JOIN syain as B 
ON 
    A.yakusyoku_id = B.yakusyoku_id;
"yakusyoku_id"	"yakusyoku_name"	"syain_id"	"syain_name"
-----------------------------------------------------------
1				"部長"				1			"山田部長"
2				"課長"				2			"鈴木課長"
3				"係長"				3			"佐藤係長"
3				"係長"				4			"佐藤係長"

外部結合/依存関係なしのデータも取得する


内部結合は、依存関係がないデータのみも取得します。
今回の例では、主任の社員はいないのですが、役職テーブルのメインテーブルとして外部結合をおこなっているので、主任レコードも取得できます。


SELECT
    A.yakusyoku_id	
    A.yakusyoku_name	
    B.syain_id	
    B.syain_name
FROM
    yakusyoku as A LEFT OUTER JOIN syain as B 
ON 
    A.yakusyoku_id = B.yakusyoku_id;
"yakusyoku_id"	"yakusyoku_name"	"syain_id"	"syain_name"
-----------------------------------------------------------------
1				"部長"				1			"山田部長"
2				"課長"				2			"鈴木課長"
3				"係長"				3			"佐藤係長"
3				"係長"				4			"佐藤係長"
4				"主任"				""			""

クロス結合/組み合わせを取得する


クロス結合の基本文法


クロス結合は、レコード同士の組み合わせを取得します。
今回の例では、全ての役職と全ての社員の組み合わせを取得できます。


結合条件も指定しないので、役職テーブルの主任レコードに対する組み合わせについても取得できます。


SELECT
    A.yakusyoku_id	
    A.yakusyoku_name	
    B.syain_id	
    B.syain_name
FROM
    yakusyoku as A CROSS JOIN syain as B;
"yakusyoku_id"	"yakusyoku_name"	"syain_id"	"syain_name"
--------------------------------------------------------------
1				"部長"				1			"山田部長"
1				"部長"				2			"鈴木課長"
1				"部長"				3			"佐藤係長"
1				"部長"				4			"佐藤係長"
2				"課長"				1			"山田部長"
2				"課長"				2			"鈴木課長"
2				"課長"				3			"佐藤係長"
2				"課長"				4			"佐藤係長"
3				"係長"				1			"山田部長"
3				"係長"				2			"鈴木課長"
3				"係長"				3			"佐藤係長"
3				"係長"				4			"佐藤係長"
4				"主任"				1			"山田部長"
4				"主任"				2			"鈴木課長"
4				"主任"				3			"佐藤係長"
4				"主任"				4			"佐藤係長"

クロス結合の使いどころ。横→縦変換


クロスジョインはあまり用途がないように思いますが、よく使う用途としては横→縦変換になります。


横→縦変換とは、カラム毎に格納されている値をレコード事に格納することです。
以下のようなイメージですね。



以下に具体的を使って説明しています。

テーブルの横→縦変換をおこなってみます。


create table col_test (
  col_test_id serial not null
  , name1 varchar(10) not null
  , name2 varchar(10) not null
  , name3 varchar(10) not null
  , name4 varchar(10) not null
  , name5 varchar(10) not null
  , constraint col_test_id_pkey primary key (col_test_id)
) ;
	"name1",	"name2",	"name3",	"name4",	"name5"
--------------------------------------------------------------
1,	"名称1_A",	"名称2_B",	"名称3_C",	"★名称4_D","名称5_E"
2,	"★名称1_B","名称2_B",	"名称3_B",	"名称4_B",	"名称5_B"
3,	"名称1_C",	"★名称2_C","★名称3_C","名称4_C",	"名称5_C"

このテーブルから、名称の先頭に「★」が付いている値を行データとして取得するとします。 この場合、縦→横変換をおこなうと便利です。


縦→横変換をおこなうために、テンポラリテーブル(TEMPORARY TABLE)
を使っています。
テンポラリテーブルで、1~5の値が入っているのみのテーブルを作成しています。


このテンポラリテーブルと対象テーブル(ここではpivot)をCROSS JOINを使って組み合わせを作成し、その組み合わせで名称の先頭に「★」ついているレコードのみを取得しています。


-- テンポラリテーブルの作成
CREATE TEMPORARY TABLE pivot (
 val integer PRIMARY KEY
);

-- テンポラリテーブルに1~5の値が入った状態にする
INSERT INTO pivot with recursive rec(val) as (
 values(1)
 union all
 select 
    Val+1
 from 
    rec
 where 
    Val+1 <= 5
) select Val from rec;

-- 名称の先頭に「★」がついたレコードのみを抽出
SELECT * FROM (
    SELECT
        case P.val
            when 1 then A.name1
            when 2 then A.name2
            when 3 then A.name3
            when 4 then A.name4
            when 5 then A.name5
        end as cross
    FROM
        col_test as A CROSS JOIN pivot P) AS X
WHERE
    X.cross like '★%';
"cross"
-------------------
★名称1_B
★名称2_C
★名称3_C
★名称4_D


PythonをApache上でCGIとして動作させる方法

apache

Pythonを使ってWEBアプリケーションを構築する場合、Apacheを使ってCGIとしてPythonを動作させるのが一般的です。
PythonをApache上で動作させるには、PythonとApacheをインストールして、Apacheの設定ファイルを環境にあわせて修正するだけでよいです。


今回は、PythonをApache上で動作させる方法について紹介していきます。


環境情報


  • OS:Windows10
  • Apache:Apache 2.4.41
  • Python:Phton3.9

Pythonスクリプトの格納


まず、使用するPythonスクリプトファイルをApacheの所定の場所に格納します。
ApacheのCGIとしてPythonを動かすので、Apacheのルートディレクトリに存在する「cgi-bin」に格納します。


PythonをCGIとして動作させる場所

今回は、以下の”Hello,World!”を表示するだけのスクリプトで動作確認をおこないます。


#! C:\Python34\python

print('Content-Type: text/html')
print('')
print('Hello,World!')

1行目は、pythonのインストールパスを記載する必要があります。
筆者のマシンでは、「C:\Python34\python」にPythonをインストールしています。


Apacheのインストールについては省略しましたが、以前書いた記事があるので、そちらを参照してください。


Apache設定ファイルの修正


次に、PythonをApacheのCGIとして動かすためにApacheの設定ファイルを修正します。
修正するのは、Apacheの設定ファイルである「httpd.conf」になります。


筆者のマシンのApacheは「C:\Apache24」にインストールしているのですが、「httpd.conf」の格納先は以下になります。


・C:\Apache24\conf\httpd.conf

「httpd.conf」の修正箇所は大きくわけて2カ所になります。 まず、Scriptエイリアスの設定で、「cg-bin」フォルダをcgi実行スクリプトフォルダとして有効にします。


<IfModule alias_module>
    #
    # Redirect: Allows you to tell clients about documents 
    # exist in your server's namespace, but do not anymore.  
    # will make a new request for the document at its location.
    # Example:
    # Redirect permanent /foo http://www.example.com/bar

    #
    # Alias: Maps web paths into filesystem paths and is used to
    # access content that does not live under the DocumentRoot.
    # Example:
    # Alias /webpath /full/filesystem/path/
    #
    # If you include a trailing / on /webpath then the server will
    # require it to be present in the URL.  You will also likely
    # need to provide a <Directory> section to allow access to
    # the filesystem path.

    #
    # ScriptAlias: This controls which directoriesscripts. 
    # ScriptAliases are essentially the same as
    # documents in the target directory are treated 
    # run by the server when requested rather than as 
    # client.  The same rules about trailing "/" apply
    # directives as to Alias.
    #
    ScriptAlias /cgi-bin/ "${SRVROOT}/cgi-bin/"
</IfModule>

通常、上記の設定はデフォルトで有効になっているので、特に設定変更する必要はないかもしれません。


もう1か所の変更箇所が、「cgi-bin」フォルダのディレクトリ設定です。
拡張子「.py」をスクリプトファイルとして認識します。

以下の例では、「.py」ファイルだけではなく「.cgi」「.pl」もスクリプトファイルとして設定しています。


<Directory "${SRVROOT}/cgi-bin">
   AllowOverride none
   AddHandler cgi-script .cgi .pl .py
   Options +ExecCGI
   Require all granted
</Directory>

上記の設定をおこなった後にApacheを再起動し、ブラウザでアクセスして格納したPythonスクリプトが動作するかを確認します。


以下の画面が表示されれば設定成功です。



PythonスクリプトとHTMLを共存させる場合


「cgi-bin」下にはスクリプトファイル(上記例では「.cgi」「.pl」「.py」)だけではなく、通常のHTMLファイルも格納したいことはあると思います。
その場合は、「cgi-bin」のディレクトリ設定で「AddHandler text/html」の設定をおこない、.htmlも有効にする必要があります。


以下の例では、「cgi-bin」下に「.html」「.htm」「.txt」「.css」「.js」の配置を許可しています。


<Directory "${SRVROOT}/cgi-bin">
   AllowOverride none
   AddHandler cgi-script .cgi .pl .py
   AddHandler text/html .html .htm .txt .css .js
   Options +ExecCGI
   Require all granted
</Directory>

この設定をいれることで、「cgi-bin」下にスクリプトファイル以外の静的HTMLについても格納可能になります。




画像からExif情報を抜き出すJavaプログラム

Java

スマートフォンやデジタルカメルで撮影した画像のメタ情報として、Exifという情報があります。
このExif情報には、画像を取った位置情報や画像の確度といった、様々なメタ情報が格納されています。


今回は、このExif情報をJavaで取得する方法を紹介していきます。


環境情報


Java:Java1.8.0_281


Exif情報の取得プログラム


Exif情報の取得サンプルプログラムは以下になります。
出力結果が解り易いように、出力結果を「Exif IFD0」「Exif SubIFD」「GPS」の3つのカテゴリ毎に出力をおこなっています。


サンプルプログラムではExif情報を「Metadata」に格納し、「getDirectories()」でメタ情報を取得していきます。
Exif情報の代表的な以下3つの情報を、標準出力しています。


項目

Tagクラスのメソッド

出力内容

タグタイプ

getTagType()

Exif情報のID

タグ名

getTagName()

タグのタイプ(名称)

説明

getDescription()

設定内容


import java.io.File;
import com.drew.imaging.ImageMetadataReader;
import com.drew.imaging.ImageProcessingException;
import com.drew.metadata.Directory;
import com.drew.metadata.Metadata;
import com.drew.metadata.Tag;

public class Exif {

    /** Exif IFD0 */
    private static final String STR_IFD = "Exif IFD0";

    /** Exif SubIFD */
    private static final String STR_SUBIFD = "Exif SubIFD";

    /** GPS */
    private static final String STR_GPS = "GPS";

    // メイン処理
    public static void main(String[] args){

        System.out.print("start: Exif\r\n");

        try {

            /** 対象ファイルを読み込み */
            File file = new File("./sample.jpg");
            Metadata metadata = 
                ImageMetadataReader.readMetadata(file);

            /** ディレクトリ情報でループ */
            for (Directory directory : metadata.getDirectories()) {
                String dirName = directory.getName();

                /** Exif IFD0 */
                if (STR_IFD.equals(dirName)) {
                    System.out.print(
                        "------directoryName=" + dirName + 
                        "------\r\n");
                    for (Tag tag : directory.getTags()) {
                        String tagName = tag.getTagName();
                        int tagType = tag.getTagType();
                        String desc = tag.getDescription();
                        System.out.print(
                            Integer.toString(tagType) + ":" + 
                            tagName + "=" + desc + "\r\n");
                    }
                }

                /** Exif SubIFD */
                if (STR_SUBIFD.equals(dirName)) {
                    System.out.print(
                        "------directoryName=" + dirName + 
                        "------\r\n");
                    for (Tag tag : directory.getTags()) {
                        String tagName = tag.getTagName();
                        int tagType = tag.getTagType();
                        String desc = tag.getDescription();
                        System.out.print(
                            Integer.toString(tagType) + ":" + 
                            tagName + "=" + desc + "\r\n");
                    }
                }

                /** GPS */
                if (STR_GPS.equals(dirName)) {
                    System.out.print(
                        "------directoryName=" + dirName + 
                        "------\r\n");
                    for (Tag tag : directory.getTags()) {
                        String tagName = tag.getTagName();
                        int tagType = tag.getTagType();
                        String desc = tag.getDescription();
                        System.out.print(
                            Integer.toString(tagType) + ":" + 
                            tagName + "=" + desc + "\r\n");
                    }
                }
            }
        } catch (Exception e) {
            System.out.print("occured exception \r\n");
        }

        System.out.print("end: Exif\r\n");
    }
}

Exif情報の取得例


Exif情報の取得例を以下に説明します。
今回は、ネットで公開しているExif情報が格納されている画像に対してサンプルプログラムを実行しています。


Exif情報を確認すると、画像を撮影した位置情報について格納されていることが確認できます。
一番下に出力されている「0:GPS」~「27:GPS」の情報になります。



start: Exif
------directoryName=Exif IFD0------
270:Image Description=SA390025
271:Make=KDDI-SA
272:Model=W51SA
274:Orientation=Top, left side (Horizontal / normal)
282:X Resolution=72 dots per inch
283:Y Resolution=72 dots per inch
296:Resolution Unit=Inch
306:Date/Time=2008:07:08 15:32:08
531:YCbCr Positioning=Center of pixel array
------directoryName=Exif SubIFD------
33437:F-Number=f/3.0
36864:Exif Version=2.20
36867:Date/Time Original=2008:07:08 15:32:08
36868:Date/Time Digitized=2008:07:08 15:32:08
37121:Components Configuration=YCbCr
37378:Aperture Value=f/2.8
37379:Brightness Value=1
37381:Max Aperture Value=f/2.8
37382:Subject Distance=0.0 metres
37383:Metering Mode=Average
37384:White Balance=Unknown
37385:Flash=Flash did not fire, auto
37386:Focal Length=4.8 mm
40960:FlashPix Version=1.00
40961:Color Space=sRGB
40962:Exif Image Width=1600 pixels
40963:Exif Image Height=1200 pixels
41729:Scene Type=Directly photographed image
41985:Custom Rendered=Normal process
41986:Exposure Mode=Auto exposure
41987:White Balance Mode=Auto white balance
41988:Digital Zoom Ratio=1
41989:Focal Length 35=28 mm
41990:Scene Capture Type=Standard
41992:Contrast=None
41993:Saturation=None
41994:Sharpness=None
41996:Subject Distance Range=Unknown
------directoryName=GPS------
0:GPS Version ID=2.200
1:GPS Latitude Ref=N
2:GPS Latitude=35° 42' 2.91"
3:GPS Longitude Ref=E
4:GPS Longitude=139° 45' 57.52"
18:GPS Map Datum=WGS-84
27:GPS Processing Method=65 83 67 73 73 0 0 0 71 80 83 45 70 73 88
end: Exif