« ◇JqueryのDateTimePickerメモ | トップページ | ◆熱エネルギーは質量となるか »

◇SQL;条件付きMIN/MAX

 SQLでMIN/MAXを一定の条件下で取得します

次のテーブルがあるとします。

CREATE TABLE IF NOT EXISTS 人物 (
   id       integer      NOT NULL PRIMARY KEY AUTOINCREMENT,
   姓       varchar(256) NOT NULL,
   名       varchar(256) NOT NULL,
   補助情報 varchar(256) NULL
   );
CREATE TABLE IF NOT EXISTS 施設 (
   id       integer      NOT NULL PRIMARY KEY AUTOINCREMENT,
   施設名   varchar(256) NOT NULL,
   所在地   varchar(256) NOT NULL,
   補助情報 varchar(256) NULL,
   UNIQUE(施設名)
   );
CREATE TABLE IF NOT EXISTS 利用控え (
   id       integer      NOT NULL PRIMARY KEY AUTOINCREMENT,
   人物id   integer      NOT NULL,
   施設id   integer      NOT NULL,
   利用日   date         NULL
   );

利用控えは人物と施設にリンクが張られており、誰が、どの施設を利用したかが紐づけられています。
使用控え自体には利用日情報が乗ります。

利用控えが次の様になっているとします。

姓          名          施設名      所在地      利用日
----------  ----------  ----------  ----------  ----------
山田        一郎        施設-2      渋谷        2021/08/08
鈴木        次郎        施設-2      渋谷        2021/08/09
鈴木        次郎        施設-4      渋谷        2021/08/12
山田        三郎        施設-1      吉祥寺      2021/08/13
鈴木        次郎        施設-1      吉祥寺      2021/08/13
鈴木        次郎        施設-4      渋谷        2021/08/15
山田        一郎        施設-1      吉祥寺      2021/08/16
鈴木        次郎        施設-3      新宿        2021/08/20

鈴木次郎さんが渋谷にある施設を利用した期間を取り出すSQLは次のものとなります。

SELECT MIN(利用控え.利用日),MAX(利用控え.利用日)
   FROM 利用控え
   JOIN 人物 ON 利用控え.人物id = 人物.id
   JOIN 施設 ON 利用控え.施設id = 施設.id
   GROUP BY 施設.所在地,人物.姓,人物.名
   HAVING 施設.所在地='渋谷' AND 人物.姓='鈴木' AND 人物.名='次郎';

統計情報を得るためのグルーピングにはGROUP BYを使います。
複数のフィールドを指定することができます。
グリーピング条件はWHEREではなくHAVINGで指定します。
複数の条件が指定できます。

ここでは、人物テーブル、施設テーブルをそれぞれidで紐づけしJOINしています。
JOINされたテーブルの値でグルーピングを行っています。

次の結果が得られます。

sqlite> SELECT MIN(利用控え.利用日),MAX(利用控え.利用日)
   ...>    FROM 利用控え
   ...>    JOIN 人物 ON 利用控え.人物id = 人物.id
   ...>    JOIN 施設 ON 利用控え.施設id = 施設.id
   ...>    GROUP BY 施設.所在地,人物.姓,人物.名
   ...>    HAVING 施設.所在地='渋谷' AND 人物.姓='鈴木' AND 人物.名='次郎';
MIN(利用控え.利用日)  MAX(利用控え.利用日)
-------------  -------------
2021/08/09     2021/08/15

渋谷には2つの施設(施設-2と施設-4)があり、2021/08/09に施設-2を利用、2021/08/15に施設-4を利用しており、これが利用期間となっています。
その後施設-3を利用していますが、これは新宿にあるので、渋谷の利用期間には含まれません。

 複数テーブルのSELECT結果をINSERTに利用する。

SELECTのFROMには複数のテーブルが指定できます。
WHEREも複数のテーブルに関連した複数の条件を指定することができます。

本例は次のようにして利用控えテーブルのレコードをINSERTしました。

INSERT INTO 利用控え ( 人物id,施設id,利用日 )
   SELECT 人物.id,施設.id,'2021/08/08'
      FROM  人物,施設
      WHERE 人物.姓='山田' AND 人物.名='一郎' AND 施設.施設名='施設-2';
INSERT INTO 利用控え ( 人物id,施設id,利用日 )
   SELECT 人物.id,施設.id,'2021/08/09'
      FROM  人物,施設
      WHERE 人物.姓='鈴木' AND 人物.名='次郎' AND 施設.施設名='施設-2';
INSERT INTO 利用控え ( 人物id,施設id,利用日 )
   SELECT 人物.id,施設.id,'2021/08/12'
      FROM  人物,施設
      WHERE 人物.姓='鈴木' AND 人物.名='次郎' AND 施設.施設名='施設-4';
INSERT INTO 利用控え ( 人物id,施設id,利用日 )
   SELECT 人物.id,施設.id,'2021/08/13'
      FROM  人物,施設
      WHERE 人物.姓='山田' AND 人物.名='三郎' AND 施設.施設名='施設-1';
INSERT INTO 利用控え ( 人物id,施設id,利用日 )
   SELECT 人物.id,施設.id,'2021/08/13'
      FROM  人物,施設
      WHERE 人物.姓='鈴木' AND 人物.名='次郎' AND 施設.施設名='施設-1';
INSERT INTO 利用控え ( 人物id,施設id,利用日 )
   SELECT 人物.id,施設.id,'2021/08/15'
      FROM  人物,施設
      WHERE 人物.姓='鈴木' AND 人物.名='次郎' AND 施設.施設名='施設-4';
INSERT INTO 利用控え ( 人物id,施設id,利用日 )
   SELECT 人物.id,施設.id,'2021/08/16'
      FROM  人物,施設
      WHERE 人物.姓='山田' AND 人物.名='一郎' AND 施設.施設名='施設-1';
INSERT INTO 利用控え ( 人物id,施設id,利用日 )
   SELECT 人物.id,施設.id,'2021/08/20'
      FROM  人物,施設
      WHERE 人物.姓='鈴木' AND 人物.名='次郎' AND 施設.施設名='施設-3';

人物,施設テーブルの値は次のように設定されているものとしています。

sqlite> SELECT * FROM 人物;
id          姓           名           補助情報
----------  ----------  ----------  ----------
1           山田          一郎
2           鈴木          次郎
3           山田          三郎
sqlite> SELECT * FROM 施設;
id          施設名         所在地         補助情報
----------  ----------  ----------  ----------
1           施設-1        吉祥寺
2           施設-2        渋谷
3           施設-3        新宿
4           施設-4        渋谷

 コピペ用SQL

.headers on
.mode column
-- テーブル生成
DROP TABLE 人物;
CREATE TABLE IF NOT EXISTS 人物 (
   id       integer      NOT NULL PRIMARY KEY AUTOINCREMENT,
   姓       varchar(256) NOT NULL,
   名       varchar(256) NOT NULL,
   補助情報 varchar(256) NULL
   );
DROP TABLE 施設;
CREATE TABLE IF NOT EXISTS 施設 (
   id       integer      NOT NULL PRIMARY KEY AUTOINCREMENT,
   施設名   varchar(256) NOT NULL,
   所在地   varchar(256) NOT NULL,
   補助情報 varchar(256) NULL,
   UNIQUE(施設名)
   );
DROP TABLE 利用控え;
CREATE TABLE IF NOT EXISTS 利用控え (
   id       integer      NOT NULL PRIMARY KEY AUTOINCREMENT,
   人物id   integer      NOT NULL,
   施設id   integer      NOT NULL,
   利用日   date         NULL
   );

-- 人物データINSERT
DELETE FROM 人物;
INSERT INTO 人物 ( 姓,名 ) VALUES ( '山田','一郎' );
INSERT INTO 人物 ( 姓,名 ) VALUES ( '鈴木','次郎' );
INSERT INTO 人物 ( 姓,名 ) VALUES ( '山田','三郎' );

SELECT * FROM 人物;

-- 施設データINSERT
DELETE FROM 施設;
INSERT INTO 施設 ( 施設名,所在地 ) VALUES ( '施設-1','吉祥寺' );
INSERT INTO 施設 ( 施設名,所在地 ) VALUES ( '施設-2','渋谷' );
INSERT INTO 施設 ( 施設名,所在地 ) VALUES ( '施設-3','新宿' );
INSERT INTO 施設 ( 施設名,所在地 ) VALUES ( '施設-4','渋谷' );

SELECT * FROM 施設;

-- 利用控えデータINSERT
DELETE FROM 利用控え;
INSERT INTO 利用控え ( 人物id,施設id,利用日 )
   SELECT 人物.id,施設.id,'2021/08/08'
      FROM  人物,施設
      WHERE 人物.姓='山田' AND 人物.名='一郎' AND 施設.施設名='施設-2';
INSERT INTO 利用控え ( 人物id,施設id,利用日 )
   SELECT 人物.id,施設.id,'2021/08/09'
      FROM  人物,施設
      WHERE 人物.姓='鈴木' AND 人物.名='次郎' AND 施設.施設名='施設-2';
INSERT INTO 利用控え ( 人物id,施設id,利用日 )
   SELECT 人物.id,施設.id,'2021/08/12'
      FROM  人物,施設
      WHERE 人物.姓='鈴木' AND 人物.名='次郎' AND 施設.施設名='施設-4';
INSERT INTO 利用控え ( 人物id,施設id,利用日 )
   SELECT 人物.id,施設.id,'2021/08/13'
      FROM  人物,施設
      WHERE 人物.姓='山田' AND 人物.名='三郎' AND 施設.施設名='施設-1';
INSERT INTO 利用控え ( 人物id,施設id,利用日 )
   SELECT 人物.id,施設.id,'2021/08/13'
      FROM  人物,施設
      WHERE 人物.姓='鈴木' AND 人物.名='次郎' AND 施設.施設名='施設-1';
INSERT INTO 利用控え ( 人物id,施設id,利用日 )
   SELECT 人物.id,施設.id,'2021/08/15'
      FROM  人物,施設
      WHERE 人物.姓='鈴木' AND 人物.名='次郎' AND 施設.施設名='施設-4';
INSERT INTO 利用控え ( 人物id,施設id,利用日 )
   SELECT 人物.id,施設.id,'2021/08/16'
      FROM  人物,施設
      WHERE 人物.姓='山田' AND 人物.名='一郎' AND 施設.施設名='施設-1';
INSERT INTO 利用控え ( 人物id,施設id,利用日 )
   SELECT 人物.id,施設.id,'2021/08/20'
      FROM  人物,施設
      WHERE 人物.姓='鈴木' AND 人物.名='次郎' AND 施設.施設名='施設-3';

-- 利用控え一覧
SELECT 人物.姓,人物.名,施設.施設名,施設.所在地,利用控え.利用日
   FROM 利用控え
   JOIN 人物 ON 利用控え.人物id = 人物.id
   JOIN 施設 ON 利用控え.施設id = 施設.id;

-- 鈴木 次郎 さんが 渋谷にある施設を利用した期間
SELECT MIN(利用控え.利用日),MAX(利用控え.利用日)
   FROM 利用控え
   JOIN 人物 ON 利用控え.人物id = 人物.id
   JOIN 施設 ON 利用控え.施設id = 施設.id
   GROUP BY 施設.所在地,人物.姓,人物.名
   HAVING 施設.所在地='渋谷' AND 人物.姓='鈴木' AND 人物.名='次郎';

|

« ◇JqueryのDateTimePickerメモ | トップページ | ◆熱エネルギーは質量となるか »