歯抜けになっているデータを埋めて取得する方法

# 想定ケース
3月分の売上データを売上日毎に集計して31日分の表を作りたい
→3月2日には売上があるけど3月3日は売上がない、でも出力としては3月3日分も出てきて欲しい（売上はゼロで良い）

単純に ` GROUP BY 売上日` しても歯抜けのデータを埋めることは出来ません。さてどうしましょう？

## 課題の洗い出し
1. 歯抜けを埋めるためのデータ(3月1日～31日までの日付データ)をどう作るか？
2. 売上データ側にない日付のデータをどうやって作り出すか？

当記事では2番目について考えていきます
(1番目については http://qiita.com/chisei/items/c4439adf3d0faedb65ed などが参考になると思います)

### 使用するデータ
#### 売上データの生データ
|   売上日   | 伝票番号 | 売上額 |
|:----------:|---------:|-------:|
| 2017-03-01 |      101 |    400 |
| 2017-03-01 |      102 |    100 |
| 2017-03-01 |      103 |    500 |
| 2017-03-02 |      101 |    380 |
| 2017-03-02 |      102 |    620 |
| 2017-03-05 |      101 |   3000 |
| 2017-03-07 |      101 |   1800 |
| 2017-03-07 |      102 |   2200 |
| 2017-03-08 |      101 |   5000 |

#### 売上データ(売上日で集計済み)
|   売上日   | 売上額 |
|:----------:|-------:|
| 2017-03-01 |   1000 |
| 2017-03-02 |   2000 |
| 2017-03-05 |   3000 |
| 2017-03-07 |   4000 |
| 2017-03-08 |   5000 |

#### 欲しい出力結果
| 売上日     | 売上額 |
|:----------:|-------:|
| 2017-03-01 |   1000 |
| 2017-03-02 |   2000 |
| 2017-03-03 |      0 |
| 2017-03-04 |      0 |
| 2017-03-05 |   3000 |
| 2017-03-06 |      0 |
| 2017-03-07 |   4000 |
| 2017-03-08 |   5000 |
| 2017-03-09 |      0 |
| 2017-03-10 |      0 |
| 2017-03-11 |      0 |
|      :     |      : |
|      :     |      : |
| 2017-03-31 |      0 |

※3/3, 3/4, 3/6, 3/9～ は売上データが無いが出力されて欲しい


# 実装方針
## 案1
1. 3月1日～3月31日までのデータを生成する（以降、日付マスタと記載します）
2.  `日付マスタ LEFT JOIN 売上データ` で、売上データのない日の売上はゼロにする

## 案2
1. 案1の1.と同様の日付マスタを生成する
2. 日付マスタをベースに「日付, 売上額ゼロ」でサブクエリを生成し、売上データとの和集合(UNION ALL)を取る
3. 2.の結果を日付で集約し、売上額の合計(SUM)を取る


# 実装コード
SQLServer2014で確認しています。
SELECT INTO で一時テーブルを作成する箇所やVALUES句などがMSSQL固有の書き方かも知れませんが、
考え方自体はどのRDBでも利用できると思います。

## 使用する売上データと日付マスタの定義

```sql
-- 集約済みの売上データ
SELECT
    CAST(salesdate AS DATE) AS salesdate,
    salesamount
INTO
    #sales
FROM (
    VALUES
        ('2017-03-01', 1000),
        ('2017-03-02', 2000),
        ('2017-03-05', 3000),
        ('2017-03-07', 4000),
        ('2017-03-08', 5000)
) AS t(salesdate, salesamount);

-- 日付マスタ
SELECT
    CAST(CAST('2017-03-01' AS DATETIME) + d.val AS DATE) AS value
INTO
    #sequential_date
FROM (
    SELECT
        d1.val + d2.val * 10 AS val
    FROM
        (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS d1(val)
    CROSS JOIN
        (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS d2(val)
) d
WHERE
   d.val <= 30
ORDER BY 1;
```

## 案1

```sql
-- 歯抜けを埋めて出力
SELECT
    s1.value AS salesdate,
    ISNULL(s2.salesamount, 0) AS salesamount
FROM
    #sequential_date s1
LEFT JOIN
    #sales s2
ON
    s2.salesdate = s1.value
ORDER BY 1;
```


## 案2

```sql
-- 歯抜けを埋めて出力
SELECT
    s.salesdate,
    SUM(s.salesamount) AS salesamount
FROM (
    SELECT
        s1.value AS salesdate,
        0 AS salesamount
    FROM
        #sequential_date s1

    UNION ALL

    SELECT
        s2.salesdate,
        s2.salesamount
    FROM
        #sales s2
) s
GROUP BY
    s.salesdate
ORDER BY 1;
```

----
## 書いた動機
過去に同様な要件での開発があり、その当時は案1しか思い付かずに案1で進めたのですが、現場の人間の示唆により案2に気付かされました。
この手の気付きって、実際に現場投入したり使わないとすぐ忘れてしまうので……なのでメモ代わりです。

案1は、日付マスタに無い売上日のデータが捨てられてしまうのですが、案2だとそのようなデータも拾い上げることが出来る、という点で違いがありますね。
（当記事の想定ケース＆データでは上記の違いは出力結果としては現れてきませんが）