Memoize

SQL_having_max_remove_dup

仕事でいじってるテーブルで同じアイテムなのに重複しちゃってるところがあって、ユニークを採るまでのメモ。

テーブルの仕様としては、アイテムの管理番号は同じでも追加されたら過去のものが上書きされるんじゃなく、システムIDなるプライマリーキーが発行されてデータがダブるようになっている。

でも僕の方で必要なのは最新のアイテム情報だっていう話。

SQLで見ていこう。

まず擬似的にアイテムテーブルを作る。

 
% sqlite3 sample.db
SQLite version 3.8.4.3 2014-04-03 16:53:12
Enter ".help" for usage hints.
sqlite> CREATE TABLE items (sys_id PRIMARY KEY NOT NULL, item_num NOT NULL, item_name);
-- sys_id システムID, item_num アイテム管理番号, item_name アイテム名
 
-- item_num 100 が重複している状態。sys_id 2 が最新
sqlite> INSERT INTO items VALUES ('1','100','Kind of Blue'),('2','100','Kind of Blue(Columbia)'),('3','200','Milestone');
sqlite> .header on
sqlite> SELECT * FROM items;
sys_id|item_num|item_name
1|100|Kind of Blue
2|100|Kind of Blue(Columbia)
3|200|Milestone
 

さて、クエリ結果として欲しいセットはこうである。

 
sys_id|item_num|item_name
2|100|Kind of Blue(Columbia)
3|200|Milestone
 

そこでこんな SELECT 文を書いてみた。

 
-- item_num でグループ化して sys_id の MAX を求める
sqlite> SELECT * FROM items 
    ...> WHERE sys_id IN 
    ...> (SELECT MAX(sys_id) FROM items GROUP BY item_num);
 
sys_id|item_num|item_name
2|100|Kind of Blue(Columbia)
3|200|Milestone
 

次に、item_num が重複しているアイテムの最新レコードだけを抽出してみよう。

先ほどの SELECT 文に HAVING 句を使用して item_num でグループ化したレコードの合計が 1 よりも大きいものに絞り込む。

 
sqlite> SELECT * FROM items 
    ...> WHERE sys_id IN 
    ...> (SELECT MAX(sys_id) FROM items GROUP BY item_num HAVING COUNT(*) > 1);
 
sys_id|item_num|item_name
2|100|Kind of Blue(Columbia)