読者です 読者をやめる 読者になる 読者になる

一所懸命に手抜きする

監査の仕事をしています。ITを利用し、良い意味で「手抜き」することは効率化と精度アップに役立つと思うんです。部下への引き継ぎのためにまずは諸々頭から引っ張り出そうとブログを始めました。

[ Main ]  [ 別館 ]
当ブログで取り上げられている事案やデータベース、人物等はすべてフィクションです。

重複行削除の際、重複判定に指定したキー列項目以外の列も取得するSQL

SQL

SELECT DISTINCTでは重複判定に指定したキー項目しか返されない

 指定されたカラム(列)で重複のないデータを返すSQL、SELECT DISTINCT(重複する行を削除するSELECT DISTINCT - 一所懸命に手抜きする)ですが

SELECT DISTINCT 列a,列b FROM テーブルX

とすると、返されるのは列a、列bだけです。

サンプルテーブル buyList
入荷日付 商品番号  商品名 当社原価 相手原価
-------- --------  ------ -------- --------
20170102    12345   XA-55     3500     3500
20170103   132977    RX98     2980     3280
20170103   349401 NOK0655     2500     2500
20170103   349401 NOK0655     2500     2500
20170104   227291 NOK2355     3500    35000
20170104   227291 NOK2355     3500    35000
20170104   349401 NOK0655     2500     2500
20170105  5288923  JJ2CAN     6000     6200
20170105   132977    RX98     2780     2980
-- SQlite3
SELECT DISTINCT 商品番号,商品名 FROM buyList ORDER BY 商品番号;
/*          実行結果
商品番号  商品名
-------- --------
   12345   XA-55
  132977    RX98
  227291 NOK2355
  349401 NOK0655
 5288923  JJ2CAN
*/

 このように重複判定に指定したキー項目以外は返されません。

キー項目以外にも重要情報があることも

 例えば、132977 RX98は次の2レコードが重複しています。この2行は日付とともに原価が異なっています。原価が引き下げられたということです。商品番号・商品名は重複していても原価の違いは重要です。

入荷日付 商品番号  商品名 当社原価 相手原価
20170103   132977    RX98     2980     3280
20170105   132977    RX98     2780     2980

 最新原価を知りたいという場合には商品番号 商品名だけでなく他の項目も必要ですが、SELECT DISTINCTではそれはできません。 

重複判定に指定したキー項目以外の列も取得するSQL

まず重複を削除した後に残すレコードの条件を指定する

 重複を削除した後に残すレコードはどれでも良いというものではなく、その商品の最終仕入明細データにしたいとします。

まずは仕入順にソートします
商品毎にグループ化し
残すのはグループ内の仕入順の最後(最大行番号)のレコード

 この条件に従うと…

サンプルテーブル buyList
入荷日付 商品番号  商品名 当社原価 相手原価
-------- --------  ------ -------- --------
20170102    12345   XA-55     3500     3500  ←この商品の当月最終仕入
20170103   132977    RX98     2980     3280
20170103   349401 NOK0655     2500     2500
20170103   349401 NOK0655     2500     2500
20170104   227291 NOK2355     3500    35000
20170104   227291 NOK2355     3500    35000  ←最終
20170104   349401 NOK0655     2500     2500  ←最終
20170105  5288923  JJ2CAN     6000     6200  ←最終
20170105   132977    RX98     2780     2980 ←最終

サブクエリで重複行を削除し、メインクエリで項目をselectする

 この抽出ロジックをSQLにすると

-- SQlite3
SELECT * FROM buyList WHERE rowid in
       (SELECT max(rowid) FROM buyList GROUP BY 商品番号 ORDER BY 入荷日付);
/*          実行結果
入荷日付 商品番号  商品名 当社原価 相手原価
-------- --------  ------ -------- --------
20170102    12345   XA-55     3500     3500
20170104   227291 NOK2355     3500    35000
20170104   349401 NOK0655     2500     2500
20170105  5288923  JJ2CAN     6000     6200
20170105   132977    RX98     2780     2980
*/

サブクエリでは残す行を条件指定し、残す行の行番号を抽出しています。
商品番号ごとにグループ化して…GROUP BY 商品番号
その中で日付順(ORDER BY 入荷日付)の最大行番号…max(rowid)を求めます。
主クエリでは、サブクエリで求めた行番号に合致するレコードから全項目を抽出しています。
SELECT * の部分に必要な列を指定することで抽出項目を指定することもできます。

In SQL,
SELECT DISTINCT eliminates duplicated records and returns only distinct/unique columns.
If you want to get non-selected columns' value, it's useless to use SELECT DISTINCT.

Indeed, in some case, grouping by columns combination , some other columns' values are needed .
Using sub-query can accomplish such a task.
In sub-query, grouping by some conditions ,SQL extracts representative rows of every group.
In main-query, SQL can return any columns of the representative rows.

example: SQL to extract last purchases of every item
SELECT pchDate,itemNo,itemName,price,mount FROM buyList WHERE rowid in
(SELECT max(rowid) FROM buyList GROUP BY itemNo ORDER BY pchDate);

1.In sub-query ,
grouping by itemNo(itemName) and
sorting purchased date in the ascending order,
SQL returns the rownumber of the last purchase of every item.
2.In main-query ,
SQL returns some selected columns' values of the represented rows - the last purchase of every item- .
広告を非表示にする