オーロラさんの勉強帳

SESのロースキル客先常駐勤務。データベース、Excel、Excel VBA、ネットワーク、LinuxなどIT関連のことを主に書いていきます。

【SQL】GROUP BYとINNER JOINを使って最新日付のレコードを取得する

テーブルの最新年月日の情報を取得するSQLを考えたいと思います。

 

 

カラムが2つしかない場合の最新日付のレコードの抽出

以下のようなPRODUCT、STARTDATEという2つのカラムを持つテーブルを用意します。

f:id:auroralights:20200605003532p:plain

やりたいことは、PRODUCTごとの最新年月日(STARTDATEが最大)を取得することです。PRODUCTごとに最大のSTARTDATEは、以下のSQLで抽出できます。

select product,max(startdate) from test_history1 group by product;

 

<参考:test_history1テーブルの作成>

create table test_history1(
  product varchar(10)
  , startdate date
);
 
insert into test_history1 (product,startdate) values ('apple','202/01/01');
insert into test_history1 (product,startdate) values ('apple','202/01/05');
insert into test_history1 (product,startdate) values ('apple','202/01/10');
insert into test_history1 (product,startdate) values ('grape','202/01/02');
insert into test_history1 (product,startdate) values ('grape','202/01/6');
insert into test_history1 (product,startdate) values ('grape','202/01/11');

カラムが3つ以上ある場合の最新日付のレコードの抽出

以下のようなCLASSIFICATION(分類コード)、PRODUCT(商品コード)、PRICE、STARTDATEというカラムがあるテーブルで、最新日付のレコードを抽出します。

※分類コード、商品コード、価格、価格の適用日を保管している履歴テーブルとします。

f:id:auroralights:20200605004716p:plain


例えば、CLASSIFICATION、PRODUCTの組み合わせに対して、最新のSTARTDATEのレコードを抽出するSQLは、以下の通りです。

 
select
  a.classification
  , a.product
  , a.startdate
  , a.price
from
  test_history a
  inner join (
    select
      classification
      , product
      , max(startdate) maxdate
    from
      test_history
    group by
      classification
      , product
  ) b
    ON a.classification = b.classification
    and a.product = b.product
 and a.startdate = b.maxdate;

 

f:id:auroralights:20200605005738p:plain

※分類:Aの商品コード:1のSTARTDATE:2020/05/11が2件あるので、上記のような結果になっいます。同一の日をはじくようにテーブルの制約をかけないとですね。

 

INNER JOINを使って内部結合をして、抽出します。

考え方は以下の通りです。

 

SELECT 取得したい列

FROM テーブル 別名A

INNER JOIN  (SELECT グループ列, MAX(最大値を抽出する列) 別名 FROM テーブル GROUP BY グループ列) 別名 B

ON 別名A.グループ列 = 別名B.グループ列

AND 別名A.最大値を求める列 = 別名B.最大値;

 

まず、以下のようにCLASSIFICATION、PRODUCTの組み合わせで、最新日付を出すSQLを考えます。

select
  classification
  , product
  , MAX(startdate)
from
  test_history
group by
  classification
  , product ;

 

f:id:auroralights:20200605010021p:plain

 

この情報に対して、PRICEを付けるために、内部結合してデータを抽出します。

 

<参考:test_historyテーブルの作成>

create table test_history(
  classification VARCHAR2(5)
  , product NUMBER(2)
  , price NUMBER(10)
  , startdate date
);

 

データはA5SQL-mk2でExcelの情報をコピー&ペーストして挿入しました。

テーブルが不要になれば、DROP TABLE FROM テーブル名;で削除できます。