SQLで重複キーを探して重複していないキーを合わせた後、削除

Question

前提・実現したいこと

PostgreSQLで一部キーが重複していた場合
重複していないキー(数字)を足す処理を行いたいです

例えばtestテーブルが存在し

id,date,num0,num1,num2
1,2019-02-25,0,1,0
1,2019-02-25,1,0,0
1,2019-02-26,3,1,2
2,2019-02-25,0,0,1
2,2019-02-25,0,2,0

というテーブルがあったとして、実行後

id,date,num0,num1,num2
1,2019-02-25,1,1,0
1,2019-02-26,3,1,2
2,2019-02-25,0,2,1

上記の様に、id,dateを重複キーとして絞り出し重複していない箇所を合わせるようにし、
重複していたレコードは削除するように行いたいと思っております。

id,dateが重複しているのにも関わらず同じnumに0以外の数字が入ることはありません
例えば

id,date,num0,num1,num2
1,2019-02-25,0,1,0
1,2019-02-25,1,1,0

このように`id`と`data`は重複しているが、両方とも`num1`に0以外の数字が入っている
というようなレコードは存在しません。

上記SQLの作成にご教授頂けないでしょうか。


### 該当のソースコード

```sql
SELECT id,date
FROM test
GROUP BY id,date
HAVING COUNT(*)>1

試したこと

Update文を使って更新処理を行おうとしましたが知恵が足らずうまく動きませんでした。

補足情報（FW/ツールのバージョンなど）

Windows 10
PostgreSQL11

Answer 1

既に元のテーブルがない場合を考慮し、一応普通に処理するSQLも書いておきました。

個人的に同一表の書き換えは順序を保証できる方法がない限り、無理だと思っています。今回だと全カラムでソートしたものを使う他ありませんが、内部に相当大きな一時表をハッシュ込みで複数作らせるだけなので、別表のアプローチにしています。要件からは外れているので、気に入らない場合は破棄してください。
データ量が多い場合は、ctas後にtestchangeのID/日付にインデックスを付けといた方がいいです。

create table test(id int,date date,num0 int,num1 int,num2 int);

insert into test values
(1,'2019-02-25',0,1,0),
(1,'2019-02-25',1,0,0),
(1,'2019-02-26',3,1,2),
(2,'2019-02-25',0,0,1),
(2,'2019-02-25',0,2,0);
commit;

create table testchange as select id, date, max(num0) as num0, max(num1) as num1, max(num2) as num2 from test group by id, date having count(*) > 1;

delete from test a where exists(select 'x' from testchange where id=a.id and date=a.date);
insert into test select * from testchange;
commit;

drop table testchange;
vacuum test;

Answer 2

それって、元質問（PostgreSQLで別テーブルに変換するInsert文）の回答の不備ですね。

group byの考慮が漏れてました。
NUMはidとdatetime::dateと時間帯で複数はない前提でMAX()としています。

insert into b
select id, datetime ::date
     , max(case  when date_part('hour',datetime)=0 then num else 0 end) -- num_00
-- ～略～
     , max(case  when date_part('hour',datetime)=23 then num else 0 end) -- num_23
from a
group by id, datetime ::date
order by id, datetime

Answer 3

データを更新しつついらないデータを削除することはできないので

• 更新してから削除するか
• viewを利用して削除処理はしない

のどちらかが有効だと思います
単純に表示だけならこんな感じ

create table tbl(pkey int primary key,id int,d date,num0 int,num1 int,num2 int);
insert into tbl values
(1,1,'2019-02-25',0,1,0),
(2,1,'2019-02-25',1,0,0),
(3,1,'2019-02-26',3,1,2),
(4,2,'2019-02-25',0,0,1),
(5,2,'2019-02-25',0,2,0);

select id,d,sum(num0) as num0,sum(num1) as num1,sum(num2) as num2
from tbl
group by id,d;