SQLの初歩を忘れていた…失敗を糧に

# はじめに
- 非常に久しぶりにSQLを書く機会があり、書いたのですがパッとは書くことができずだいぶ忘れてしまっていた
- そこで、今回は、やりたかったことに対して、どう考えれば行き着くのか改めて整理しておこうと思います


# やりたかったこと
実際に書く機会を得たSQLを書くことはできないので、要素を抜き出した適当な命名のテーブルとして以下のように定義します。
- 発券したチケットを管理するテーブル
- 発券されたチケットが利用されたチケットを管理するテーブル
があったとします。
この2つのテーブルの定義は、以下の項目を持っています。
- 発券したことを管理するテーブル（TicketingTbl）
    - 店舗、受付番号、登録日
- 発券されたチケットが利用されたことを管理するテーブル（UsedTbl）
    - 受付番号、登録日

データはこんな感じで各テーブルに入っていました。
　　　　　<img width="50%" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2518581/a4039bdf-4913-d97d-c511-d08e29c765a6.png">

**この2つのテーブルからある期間における発券枚数と利用されたチケットの件数を店舗毎に抽出する**
ここでは1月～3月分のデータが入っていて、2月に発券された件数と利用された件数のそれぞれを抽出する
データを具体的にみると対象としたいデータはこうなります。
　　　　　<img width="50%" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2518581/23149b13-ec38-9553-0bdd-3b162a472b5c.png">

# やったこと
Excelでやるなら`VLOOKUP`を使って表を結合して、ある期間⇒フィルター使って絞り込むを何回か繰り返すと簡単にできると思うが、
DBの場合、これをまずやるには、`LEFT JOIN`を使って書くことになる
``` SQL
SELECT
  TT.店舗,
  TT.受付番号,
  TT.登録日,
  UT.受付番号,
  UT.登録日
FROM
  TicketingTbl TT
LEFT JOIN
  UsedTbl UT
ON
  TT.受付番号=UT.受付番号
```
Accessを使って今回は試して、こんな結果を得られました。
　　　　<img width="50%" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2518581/780eee62-13ab-6f8c-2ed4-950e627a0af9.png">

これに条件を付けて発券された日付が2/1～2/28、利用された日付が2/1～2/28であるデータを抜こうとすると、OR条件で`TT.登録`に対して22/2/1～22/2/28あるいは`UT.登録日`に対して22/2/1～22/2/28を条件にすれば、期待するデータは抽出することができる。今回はAcceSSを使って検証したので、SQLとしては以下になる。

``` SQL
SELECT 
  店舗,
  TT受付番号,
  TT登録日,
  UT受付番号,
  UT登録日 
FROM
   (SELECT
    TT.店舗 as 店舗,
    TT.受付番号 as TT受付番号,
    TT.登録日 as TT登録日,
    UT.受付番号 as UT受付番号,
    UT.登録日 as UT登録日
  FROM
    TicketingTbl TT
  LEFT JOIN
    UsedTbl UT
  ON
    TT.受付番号=UT.受付番号)  AS FebData
WHERE 
  (TT登録日>=CDate("2022/2/1") And TT登録日<=CDate("2022/2/28")) 
  OR
  (UT登録日>=CDate("2022/2/1") And UT登録日<=CDate("2022/2/28"));
```
データは無事に抽出ができて以下の結果が一部省略していますが、以下のように抽出できました。
　　　　<img width="50%" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2518581/6cb32576-44be-5b67-4326-ce2d318bd68e.png">

が、ふと気づく。データは抜けたが、1回のSQL発行で、店舗ごとの発券数と利用数が知りたい…が目的だと。。
ここから件数を抽出するには、
1. `TT登録日`で2022/2/1～2022/2/28で抽出して、`Group By 店舗`でグルーピングして発券件数を抽出
　　　結果は、以下の結果になる
　　　　　　　![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2518581/b1ff24dc-c37a-84d5-3708-62ed7569340a.png)

1. `UT登録日`で2022/2/1～2022/2/28で抽出して、`Group By 店舗`でグルーピングして利用件数を抽出
　　　結果は、以下の結果になる
　　　　　　　![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2518581/cfc14d88-d351-7bc9-2aea-bf3322570c3a.png)


を実施する必要がある。
これでは、2回SQLを発行すれば抽出できますが、一回では抽出することができない。

が、ここで気づく…データは抽出できて、上記1．と上記2.の条件でデータを抽出することができるのであれば、データ抽出した結果を連携すれば、やりたいことができるのでは？と。
そこで、発券数の抽出と、利用件数を抽出してみる。

**まずは、発券件数の抽出**
店舗と発券件数のカラムを持つテーブルとして抽出
``` SQL
SELECT 
  TicketingTbl.店舗 as 店舗,
  count(TicketingTbl.受付番号) as 発行件数
FROM 
  TicketingTbl
WHERE (
  ((TicketingTbl.登録日)>=CDate("2022/2/1") And (TicketingTbl.登録日)<=CDate("2022/2/28"))
  )
group by 店舗

```

**つぎに、利用件数の抽出**
店舗と利用件数のカラムを持つテーブルとして抽出
``` SQL
SELECT 
  TicketingTbl.店舗 as 店舗,
  count(TicketingTbl.受付番号) as 利用件数 
FROM 
  TicketingTbl 
 LEFT JOIN 
  UsedTbl 
 ON 
  TicketingTbl.受付番号 = UsedTbl.受付番号
WHERE (
  ((UsedTbl.登録日)>=CDate("2022/2/1") And (UsedTbl.登録日)<=CDate("2022/2/28")) 
  And
  ((UsedTbl.受付番号)=TicketingTbl.受付番号)
  )
Group by 店舗

```
**この2つのテーブルを`LEFT JOIN`で連結することで、発行件数と利用件数を1回で抽出することができるようになる**
``` SQL
SELECT
  発券件数TBL.店舗,
  発行件数,
  利用件数
FROM　(
  SELECT TicketingTbl.店舗 as 店舗, count(TicketingTbl.受付番号) as 発行件数
  FROM TicketingTbl
  WHERE (((TicketingTbl.登録日)>=CDate("2022/2/1") And (TicketingTbl.登録日)<=CDate("2022/2/28")))
  group by 店舗
  ) AS 発券件数TBL
LEFT JOIN (
  SELECT 
    TicketingTbl.店舗 as 店舗,
    count(TicketingTbl.受付番号) as 利用件数 
  FROM TicketingTbl LEFT JOIN UsedTbl ON TicketingTbl.受付番号 = UsedTbl.受付番号
  WHERE (((UsedTbl.登録日)>=CDate("2022/2/1") And (UsedTbl.登録日)<=CDate("2022/2/28")) And ((UsedTbl.受付番号)=TicketingTbl.受付番号))
  Group by 店舗
  ) AS 利用件数TBL
ON 
  発券件数TBL.店舗 = 利用件数TBL.店舗
```
結果は、以下のように得られて、やりたいことに行きつけた
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2518581/3028ec6e-7eae-7398-1614-322f9f018bd4.png)

# さいごに
そこまで複雑ではない内容でしたが、データを抽出したいときのSQLと違い、件数を抽出したいときのSQLでは、時として件数だけを個別に抽出したテーブルを作成した上で、その結果を連結した方がスムーズにいくことを気づくことができました。

今後もSQLを作成する機会があると思うので、悩んだポイントなんかを纏めていけたらと思います。