努力したWiki

推敲の足りないメモ書き多数

ユーザ用ツール

サイト用ツール

トレース:
documents:proglang:sql:sql-0010

差分

このページの2つのバージョン間の差分を表示します。

この比較画面へのリンク

両方とも前のリビジョン前のリビジョン
documents:proglang:sql:sql-0010 [2026/05/08 12:19] – ↷ documents:database:sql-0010 から documents:proglang:sql:sql-0010 へページを移動しました。 k896951documents:proglang:sql:sql-0010 [2026/05/16 11:36] (現在) – [SQLで不足レコードを補うクエリの発行] k896951
行 1: 行 1:
 +====== 005.SQLで不足レコードを補うクエリの発行 ======
 +
 +2017年11月16日\\
 +自分用メモ。
 +
 +===== 処理対象データ =====
 +
 +PostgreSQLに以下のデータがある。
 +
 +<code sql>
 +$ psql -h 192.168.1.30 kamonohashi kamo
 +psql (9.6.5)
 +"help" でヘルプを表示します.
 +
 +kamonohashi=> select * from salelog;
 +         day         | item  | qty
 +---------------------+-------+-----
 + 2017-10-01 00:00:00 | ITEM1 |  30
 + 2017-10-02 00:00:00 | ITEM2 |  15
 + 2017-10-02 00:00:00 | ITEM3 |  20
 + 2017-10-11 00:00:00 | ITEM1 |  25
 + 2017-10-13 00:00:00 | ITEM1 |  15
 + 2017-10-13 00:00:00 | ITEM2 |   5
 +(6 行)
 +
 +kamonohashi=>
 +</code>
 +
 +これにレコードを補い、以下のように各日で必ずITEM1～ITEM3のレコードが現れるようにしたい。
 +
 +<code sql>
 +         day         | item  | qty
 +---------------------+-------+-----
 + 2017-10-01 00:00:00 | ITEM1 |  30
 + 2017-10-01 00:00:00 | ITEM2 |   0
 + 2017-10-01 00:00:00 | ITEM3 |   0
 + 2017-10-02 00:00:00 | ITEM1 |   0
 + 2017-10-02 00:00:00 | ITEM2 |  15
 + 2017-10-02 00:00:00 | ITEM3 |  20
 + 2017-10-11 00:00:00 | ITEM1 |  25
 + 2017-10-11 00:00:00 | ITEM2 |   0
 + 2017-10-11 00:00:00 | ITEM3 |   0
 + 2017-10-13 00:00:00 | ITEM1 |  15
 + 2017-10-13 00:00:00 | ITEM2 |   5
 + 2017-10-13 00:00:00 | ITEM3 |   0
 +</code>
 +
 +===== 方法1：3回のクエリ発行 =====
 +
 +単純に、ITEM1～ITEM3のいずれかの記録がされた日付に、記録がないアイテムのレコードを追加する。
 +
 +例えばITEM1の不足レコードを追加するには以下のクエリとなる。
 +<code sql>
 +with t as (
 +  select distinct day from salelog
 +)
 +insert into salelog
 +  select t.day, 'ITEM1', 0 from t
 +   where not exists(select * from salelog m where m.day=t.day and m.item='ITEM1')
 +;
 +</code>
 +
 +実行してみる。
 +
 +<code sql>
 +kamonohashi=> with t as (
 +kamonohashi(>   select distinct day from salelog
 +kamonohashi(> )
 +kamonohashi-> insert into salelog
 +kamonohashi->   select t.day, 'ITEM1', 0 from t
 +kamonohashi->    where not exists(select * from salelog m where m.day=t.day and m.item='ITEM1')
 +kamonohashi-> ;
 +INSERT 0 1
 +kamonohashi=> select * from salelog;
 +         day         | item  | qty
 +---------------------+-------+-----
 + 2017-10-01 00:00:00 | ITEM1 |  30
 + 2017-10-02 00:00:00 | ITEM2 |  15
 + 2017-10-02 00:00:00 | ITEM3 |  20
 + 2017-10-11 00:00:00 | ITEM1 |  25
 + 2017-10-13 00:00:00 | ITEM1 |  15
 + 2017-10-13 00:00:00 | ITEM2 |   5
 + 2017-10-02 00:00:00 | ITEM1 |   0
 +(7 行)
 +
 +kamonohashi=>
 +</code>
 +
 + 2017-10-02 に ITEM1 のレコードが追加された。
 +
 +続けて ITEM2,ITEM3 のクエリも実行してみる。
 +
 +<code sql>
 +kamonohashi=> with t as (
 +kamonohashi(>   select distinct day from salelog
 +kamonohashi(> )
 +kamonohashi-> insert into salelog
 +kamonohashi->   select t.day, 'ITEM2', 0 from t
 +kamonohashi->    where not exists(select * from salelog m where m.day=t.day and m.item='ITEM2')
 +kamonohashi-> ;
 +INSERT 0 2
 +kamonohashi=> with t as (
 +kamonohashi(>   select distinct day from salelog
 +kamonohashi(> )
 +kamonohashi-> insert into salelog
 +kamonohashi->   select t.day, 'ITEM3', 0 from t
 +kamonohashi->    where not exists(select * from salelog m where m.day=t.day and m.item='ITEM3')
 +kamonohashi-> ;
 +INSERT 0 3
 +kamonohashi=> select * from salelog;
 +         day         | item  | qty
 +---------------------+-------+-----
 + 2017-10-01 00:00:00 | ITEM1 |  30
 + 2017-10-02 00:00:00 | ITEM2 |  15
 + 2017-10-02 00:00:00 | ITEM3 |  20
 + 2017-10-11 00:00:00 | ITEM1 |  25
 + 2017-10-13 00:00:00 | ITEM1 |  15
 + 2017-10-13 00:00:00 | ITEM2 |   5
 + 2017-10-01 00:00:00 | ITEM2 |   0
 + 2017-10-11 00:00:00 | ITEM2 |   0
 + 2017-10-01 00:00:00 | ITEM3 |   0
 + 2017-10-11 00:00:00 | ITEM3 |   0
 + 2017-10-13 00:00:00 | ITEM3 |   0
 + 2017-10-02 00:00:00 | ITEM1 |   0
 +(12 行)
 +</code>
 +
 +3つのクエリを実行し、6レコードが追加された。
 +見易くソートをかけると、期待通りの結果であることがわかる。
 +
 +<code sql>
 +kamonohashi=> select * from salelog order by day, item;
 +         day         | item  | qty
 +---------------------+-------+-----
 + 2017-10-01 00:00:00 | ITEM1 |  30
 + 2017-10-01 00:00:00 | ITEM2 |   0
 + 2017-10-01 00:00:00 | ITEM3 |   0
 + 2017-10-02 00:00:00 | ITEM1 |   0
 + 2017-10-02 00:00:00 | ITEM2 |  15
 + 2017-10-02 00:00:00 | ITEM3 |  20
 + 2017-10-11 00:00:00 | ITEM1 |  25
 + 2017-10-11 00:00:00 | ITEM2 |   0
 + 2017-10-11 00:00:00 | ITEM3 |   0
 + 2017-10-13 00:00:00 | ITEM1 |  15
 + 2017-10-13 00:00:00 | ITEM2 |   5
 + 2017-10-13 00:00:00 | ITEM3 |   0
 +(12 行)
 +
 +kamonohashi=>
 +</code>
 +
 +===== 方法2：1回のクエリ発行 =====
 +
 +事前に以下のテーブルを用意する。
 +<code sql>
 +kamonohashi=> select * from itemlist;
 + item
 +-------
 + ITEM1
 + ITEM2
 + ITEM3
 +(3 行)
 +
 +kamonohashi=>
 +</code>
 +
 +このテーブル itemlist を組み合わせるクエリは以下となる。
 +<code sql>
 +with t as (
 +  select d.day, m.item
 +    from ( select distinct day from salelog) d
 +      cross join itemlist m
 +)
 +insert into salelog
 +  select t.day, t.item, 0 from t
 +   where not exists(select * from salelog m where m.day=t.day and m.item=t.item)
 +;
 +</code>
 +
 +実行結果は期待通りになっている。
 +
 +<code sql>
 +kamonohashi=> with t as (
 +kamonohashi(>   select d.day, m.item
 +kamonohashi(>     from ( select distinct day from salelog) d
 +kamonohashi(>       cross join itemlist m
 +kamonohashi(> )
 +kamonohashi-> insert into salelog
 +kamonohashi->   select t.day, t.item, 0 from t
 +kamonohashi->    where not exists(select * from salelog m where m.day=t.day and m.item=t.item)
 +kamonohashi-> ;
 +INSERT 0 6
 +kamonohashi=> select * from salelog order by day, item;
 +         day         | item  | qty
 +---------------------+-------+-----
 + 2017-10-01 00:00:00 | ITEM1 |  30
 + 2017-10-01 00:00:00 | ITEM2 |   0
 + 2017-10-01 00:00:00 | ITEM3 |   0
 + 2017-10-02 00:00:00 | ITEM1 |   0
 + 2017-10-02 00:00:00 | ITEM2 |  15
 + 2017-10-02 00:00:00 | ITEM3 |  20
 + 2017-10-11 00:00:00 | ITEM1 |  25
 + 2017-10-11 00:00:00 | ITEM2 |   0
 + 2017-10-11 00:00:00 | ITEM3 |   0
 + 2017-10-13 00:00:00 | ITEM1 |  15
 + 2017-10-13 00:00:00 | ITEM2 |   5
 + 2017-10-13 00:00:00 | ITEM3 |   0
 +(12 行)
 + 
 +kamonohashi=>
 +</code>
 +
 +===== 説明 =====
 +
 +キー項目でINSERT先の既存レコードの存在を確認し、レコードが存在しないならそのキー項目を持つレコードを追加している。
 +この例では、項目day、項目item、が該当する。
 +
 +レコードの存在有無は EXISTS()関数で確認できる。
 +レコードがあれば EXISTS(…)の戻り値はTRUEとなる。追加先のテーブル salelog 内にキーで示すレコードが存在しないならEXISTS(…)の結果はFALSEになるので、NOT EXISTS(…)で条件が成立する。\\
 +※EXISTS(…)=TRUE時はすでにそのキーのレコードがあるので追加してはいけない
 +
 +EXISTS()関数内のクエリで使うキー項目を持つレコードセットが t になる。
 +
 +方法２ではレコードセット t を作るためにテーブル itemlist を使ったが、このテーブルも置き換えできる。例えば
 +<code sql>
 +with t as (
 +  select d.day, m.item
 +    from ( select distinct day from salelog) d
 +      cross join ( select distinct item from salelog) m
 +)
 +insert into salelog
 +  select t.day, t.item, 0 from t
 +   where not exists(select * from salelog m where m.day=t.day and m.item=t.item)
 +;
 +</code>
 +
 +でもいい。
 +
 +また、レコードセット t は、WITHを使わずサブクエリにしても構わない。
 +<code sql>
 +insert into salelog
 +  select t.day, t.item, 0
 +    from (
 +      select d.day, m.item
 +        from ( select distinct day from salelog) d
 +          cross join ( select distinct item from salelog) m
 +    ) t
 +   where not exists(select * from salelog m where m.day=t.day and m.item=t.item)
 +;
 +</code>
 +
 +{{tag> database SQL 不足レコード補填 PostgreSQL 技術資料}}
  
documents/proglang/sql/sql-0010.txt · 最終更新: by k896951
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki