45079

[code]

    create table mydata (
      shopname varchar(10),
      targetdate varchar(8),
      salescount int
    );
    
[/code]

[code]

    '店舗A', '20180626', 100
    '店舗A', '20180625', 105
    '店舗B', '20180625', 110
    '店舗C', '20180625', 80
    '店舗A', '20180624', 90
    '店舗B', '20180624', 100
    '店舗C', '20180624', 80
    
[/code]

上記のように 店舗 A/B/C それぞれについて、日付別に販売数があるとして、  
直近の 20180626 については店舗B/C からはまだデータが未着のため、  
レコードがありません。

このようなテーブルについて、

[code]

    '店舗B', '20180626', 0 (null でも可)
    '店舗C', '20180626', 0 (null でも可)
    
[/code]

を補完するような view 1つだけを作りたいのですが、可能でしょうか。目的は  
BI ツールで各店舗の推移を確認する際に各店舗で日付をそろえたいのです。

直近N日の日付を持ったテーブルを作成し、それと left outer join すればよいかと  
思うのですが、店舗B だけ、あるいは 店舗C だけならできるものの、店舗B/C 両方を  
実現するやり方がわかりません。

使っているのは BigQuery なのですが、Oracle でも MySQL でも PostgreSQL でも  
SQL 一般でご回答いただければと思います。元テーブルはいじらず、ストアド  
ファンクションなどはナシ、作成する view は 1つ (店舗別に view を作らない)  
という前提でお願いしたいです。

なお、データ未着は直近 1～3日程度はありえます。  
よろしくお願いいたします。


CC BY-SA 4.0
2018-06-26T02:44:23.267
45070
2018-06-26T06:23:53.317
2018-06-26T06:23:53.317
19110
25519
question
mysql sql postgresql oracle
SQLで欠損日付のレコードを補完したい


`shopname`と`targetdate`のあるべき組み合わせを直積で求めて、その組み合わせに該当する`salescount`を結合する(無ければ0)、でどうでしょうか。

(以下PostgreSQL9.2の実行結果)

[code]

    create view myview(shopname, targetdate, salescount) as 
    select t1.shopname, t2.targetdate, coalesce(t3.salescount, 0)
    from (select distinct shopname from mydata) as t1 cross join (select distinct targetdate from mydata) as t2
    left join mydata as t3 on t1.shopname = t3.shopname and t2.targetdate = t3.targetdate
    ;
    
[/code]



[code]

    select * from myview order by targetdate desc,shopname;
    
     shopname | targetdate | salescount
    ----------+------------+------------
     店舗A  | 20180626   |        100
     店舗B  | 20180626   |          0
     店舗C  | 20180626   |          0
     店舗A  | 20180625   |        105
     店舗B  | 20180625   |        110
     店舗C  | 20180625   |         80
     店舗A  | 20180624   |         90
     店舗B  | 20180624   |        100
     店舗C  | 20180624   |         80
    (9 rows)
    
[/code]


CC BY-SA 4.0
2018-06-26T05:06:43.017
45079
2018-06-26T05:18:45.913
2018-06-26T05:18:45.913
2808
2808
45070
answer


ご教示いただいた方法で無事できました！ありがとうございます！  
一応 BigQuery StandardSQL 版も記載しておきます。

[code]

    #standardsql
    WITH t_datelist AS (
      SELECT FORMAT_DATETIME('%Y%m%d',DATETIME_SUB(CURRENT_DATETIME('+09:00'), INTERVAL 0 DAY)) as t_date UNION ALL
      SELECT FORMAT_DATETIME('%Y%m%d',DATETIME_SUB(CURRENT_DATETIME('+09:00'), INTERVAL 1 DAY)) as t_date UNION ALL
      SELECT FORMAT_DATETIME('%Y%m%d',DATETIME_SUB(CURRENT_DATETIME('+09:00'), INTERVAL 2 DAY)) as t_date UNION ALL
      SELECT FORMAT_DATETIME('%Y%m%d',DATETIME_SUB(CURRENT_DATETIME('+09:00'), INTERVAL 3 DAY)) as t_date UNION ALL
      SELECT FORMAT_DATETIME('%Y%m%d',DATETIME_SUB(CURRENT_DATETIME('+09:00'), INTERVAL 4 DAY)) as t_date UNION ALL
      SELECT FORMAT_DATETIME('%Y%m%d',DATETIME_SUB(CURRENT_DATETIME('+09:00'), INTERVAL 5 DAY)) as t_date UNION ALL
      SELECT FORMAT_DATETIME('%Y%m%d',DATETIME_SUB(CURRENT_DATETIME('+09:00'), INTERVAL 6 DAY)) as t_date UNION ALL
      SELECT FORMAT_DATETIME('%Y%m%d',DATETIME_SUB(CURRENT_DATETIME('+09:00'), INTERVAL 7 DAY)) as t_date UNION ALL
      SELECT FORMAT_DATETIME('%Y%m%d',DATETIME_SUB(CURRENT_DATETIME('+09:00'), INTERVAL 8 DAY)) as t_date UNION ALL
      SELECT FORMAT_DATETIME('%Y%m%d',DATETIME_SUB(CURRENT_DATETIME('+09:00'), INTERVAL 9 DAY)) as t_date 
    )
    select * from 
    (select distinct shopname as t_shopname
       from mydataset.mydata
    ) as t_shop
    cross join t_datelist
    left join mydataset.mytable
      on t_shop.shopname = mydata.shopname
      and t_datelist.t_date = mydata.targetdate
    
[/code]


CC BY-SA 4.0
2018-06-26T06:02:55.700
45084
2018-06-26T06:02:55.700
25519
45070
answer