【SQL】複数テーブルの結合 -集計テーブルを作成し結合-

概要

自社の従業員が各顧客のためにどのくらいの時間を費やしたかを表示するためのクエリを例示します。
実際には顧客情報の集計を行ったわけではなく、テーブル構造もより複雑でしたが、
備忘録も含め簡単な例で残しておきます。

テーブル構造

顧客情報(顧客番号、顧客名)
テーブル名：customer

c_id	c_name
100	(株)みかん商事
101	(株)りんご電気
102	バナナ銀行(株)

顧客訪問情報(訪問番号(シーケンス)、顧客訪問日時、顧客番号)
テーブル名：visit

v_no	v_date	c_id
1	2021-09-01	100
2	2021-09-01	101
3	2021-09-02	102
4	2021-09-10	103

顧客対応情報(訪問番号、対応者、対応時間(分))
テーブル名：service

v_no	s_name	s_min
1	鈴木	50
1	佐藤	10
1	田中	100
2	松本	20
2	松尾	30
3	斉藤	200
4	橋本	70
4	青木	70

上記の場合、例えば2021-09-01は鈴木さんが50分、佐藤さんが10分、田中さんが100分、
(株)みかん商事のために対応したという形です。
これを各顧客にそれぞれどのくらい時間を費やしているかを表示するクエリの発行を例示します。

クエリ

psql.exe

SELECT 
    A.c_id AS id, 
    A.c_name AS name, 
    B.time AS time
FROM 
    customer A 

    LEFT JOIN ( 
--      顧客訪問情報と顧客対応情報を基に、各顧客にどのぐらいの時間を費やしているか集計テーブルを作成
        SELECT 
            BB.c_id AS c_id, 
            sum(CC.s_min) AS time 
        FROM 
            visit BB, 
            service CC 
        WHERE 
            BB.v_no = CC.v_no 
--          この部分は、日付範囲を指定して検索したい場合にコメントアウトしてください
--          AND '2021-09-01' <= BB.v_date 
--          AND BB.v_date <='2021-09-30' 
        GROUP BY 1 
        ORDER BY 2 desc 
    ) 
    B ON A.c_id = B.c_id 

GROUP BY 
    1, 2, 3 
ORDER BY 
    3 desc;

イメージとしては、実績の集計テーブルを作成し、これを顧客マスタと結合させるような形です。

クエリ実行結果

id	name	time
100	(株)みかん商事	300
101	(株)りんご電気	200
102	バナナ銀行(株)	50