SQLを使用してシンプルかつ効果的にデータを比較する方法｜e-bellnet.com（IBM i （AS/400）ユーザーのための関連技術情報サイト）

IBM i 資料集

IBMｉ海外記事2020.06.24

SQLを使用してシンプルかつ効果的にデータを比較する方法

Ted Holt 著

年月を重ねて分かったことの1つは、どれほどテクノロジーが変化しても、尋ねられる質問はいつも同じということです。たとえば、「製品の数が45個しかないのに50個あるとコンピューター上で表示されるのはなぜでしょう」、または「オーダー番号12345の期限を変更したのは誰ですか」、あるいは「この製品の今年の販売は、昨年の同時期と比較してどうですか」などです。今回の記事では、このうちの最後の質問を取り上げようと思います。

データを比較することは、非常によく行われる処理です。営業部門、生産部門、購買部門、そしてもちろん管理部門など、データの比較は誰もが行います。データウェアハウスをセットアップするのは、そういうことのためです。幸い、SQLを使用して比較の質問に答えるときには、お決まりの公式的な答え方があります。

データを比較するには、次の5つが必要です。

2つ以上のデータセット
全外部結合
COALESCE関数
列の選択
ORDER BY節

1つずつ順番に見て行きましょう。ただし、まずは例で使用されるデータです。

create table saleshist
  (key        dec(3)    primary key,
   custid     dec(3),
   datesold   date,
   item       char(5),
   quantity   dec(3),
   price      dec(5,2));
   
insert into saleshist
(key, custid, datesold, item, quantity, price)
 values
(  1, 123, '2019-03-01', 'A15', 1, 3.00),
(  2, 456, '2019-03-01', 'A15', 2, 1.50),
(  3, 456, '2019-03-05', 'A15', 3, 1.50),
(  4, 456, '2019-03-05', 'X22', 1, 1.00),
(  5, 456, '2020-02-08', 'A15', 2, 2.00),
(  6, 456, '2020-03-08', 'A15', 2, 2.00),
(  7, 333, '2020-03-03', 'A15', 4, 1.50),
(  8, 567, '2019-03-10', 'A15', 2, 1.50),
(  9, 567, '2019-04-01', 'A15', 1, 1.50),
( 10, 567, '2020-03-01', 'D44', 2, 2.00);

今年の3月と昨年の3月の、品目A15の顧客別の売上高を比較するよう求められたとします。どこから始めましょうか。まずは、2つのデータセットです。データセットは、2つの別々の表または表のセットから作成されることもあれば、2つのビューから作成されることもあります。あるいは、同じ表での2つのSELECTによって作成される場合もあります。データセットは、データが保存されているところであれば、どこからでも作成されます。

この例では、両方の年のデータは販売履歴表SALESHISTに保存されているため、2つの共通表式、SALES2019およびSALES2020を使用します。

with sales2019 as
   (select custid, sum(quantity) as qty,
           sum(quantity * price) as sales
      from saleshist
     where item = 'A15'
       and datesold between '2019-03-01' and '2019-03-31'
     group by custid),
sales2020 as     
   (select custid, sum(quantity) as qty,
           sum(quantity * price) as sales
      from saleshist
     where item = 'A15'
       and datesold between '2020-03-01' and '2020-03-31'
     group by custid)
. . . more to come ! ! ! . . .

データを比較するということは、2つのデータセットが結合される必要があることを意味します。顧客は片方の年にのみ品目A15を購入したかもしれませんし、両方の年に購入したかもしれません。ということは、全外部結合を使用して、すべてのデータを取り出す必要があることになります。

select . . . 
  from sales2019 as s19
  full outer join sales2020 as s20
    on s19.custid = s20.custid

ひとまず素晴らしいですが、これではユーザーが必要としている情報にはなっていません。まずは、すべてのサマリー列をリストアップすることから始めます。この例では、サマリー列は顧客IDのみです。しかし、どちらの顧客IDを取得するのでしょうか。SALES2019からの顧客IDでしょうか。SALES2020からの顧客IDでしょうか。論理的な答えとしては、もちろん「はい!」しかありません。2019年と2020年の両方の年に品目A15を購入した顧客もいれば、両方の年ではなく、2019年または2020年のどちらかの年にのみ購入した顧客もいるからです。COALESCE関数は、何であろうと、顧客番号を取得してくれます。

select coalesce(s20.custid, s19.custid) as custid,
       . . . more columns . . .
  from sales2019 as s19
  full outer join sales2020 as s20
    on s19.custid = s20.custid

COALESCEは、リスト内のNULLでない最初の値を返します。1つのデータセットにあったか、両方のデータセットにあったかにかかわらず、顧客番号を取得します。COALESCEは、必ずすべてのサマリー列で使用するようにしてください。

ここまでは順調ですが、ユーザーが必要としているのは、販売高、すなわち、それぞれの顧客がいくつ購入し、いくら支払ったか、です。

select coalesce(s20.custid, s19.custid) as custid,
       s20.qty as "Qty 2020", s19.qty as "Qty 2019",
       s20.sales as "Sales 2020", s19.sales as "Sales 2019"
  from sales2019 as s19
  full outer join sales2020 as s20
    on s19.custid = s20.custid

私は、2つの数量フィールドおよび2つの販売高を、それぞれ2つにグループ化しましたが、どのように列をグループ化するかは、それぞれのタスクによって異なります。おそらく、そのような照会の出力をExcelで提供すると、必要に応じて編成し直されるのがほとんどだと思われます。

最後に行うことは、結果セットが論理的な順序で示されるようにすることです。

  order by 1

ORDER BY節を使用して、必要に応じたやり方でデータをソートします。サマリー列でソートすることが多いでしょうが、そうでなくても構いません。この例の場合、ORDER BY 1は、1番目の列、すなわち顧客IDで結果セットをソートすることになります。

すべてをまとめると、以下の照会になります。

with sales2019 as
   (select custid, sum(quantity) as qty,
           sum(quantity * price) as sales
      from saleshist
     where item = 'A15'
       and datesold between '2019-03-01' and '2019-03-31'
     group by custid),
sales2020 as     
   (select custid, sum(quantity) as qty,
           sum(quantity * price) as sales
      from saleshist
     where item = 'A15'
       and datesold between '2020-03-01' and '2020-03-31'
     group by custid)
select coalesce(s20.custid, s19.custid) as custid,
       s20.qty as "Qty 2020", s19.qty as "Qty 2019",
       s20.sales as "Sales 2020", s19.sales as "Sales 2019"
  from sales2019 as s19
  full outer join sales2020 as s20
    on s19.custid = s20.custid     
  order by 1

結果セットは以下のようになります。