【SQL】SQLで変化（増加/変化なし/減少）について考える

SQL

■ はじめに

SQLで変化（増加/変化なし/減少）について考える

目次

【１】ポイント
　１）過去データと比較するために自己結合する
　２）データ仕様上、歯抜けがあるかどうかを確認
【２】Oracle 11g
　例１：年間売上の変化（増加/変化なし/減少）
　例２：年間売上の変化（増加/変化なし/減少。歯抜けデータ）
【３】PostgreSQL
　例１：最新5年間の変化を表示
【４】おまけ：変化のHTMLでの表記

【１】ポイント

１）過去データと比較するために自己結合する

変化を調べるためには、過去データと比較する必要がある。
それをSQL文で実現するためには、過去データを同じ行で
扱う必要があるので、LEFT OUTER JOINで自己結合する

２）データ仕様上、歯抜けがあるかどうかを確認

2021->2020->2019っとデータが連続していれば
１）の結合条件は単純に「year - 1」とすればいいが
2021->2019（2020データなし）っとなっていた場合
結合条件が複雑になる。
 => データ仕様が不明であれば、歯抜けデータありとして
　　進めるのも一つの手かと、、、

JOIN部分抜粋 

...
FROM
 SALES S1 -- 現在のデータ用（S1）
LEFT OUTER JOIN
 SALES S2 -- 直近のデータ用（S2）
ON
 -- 【条件１】現在のデータ(S1)より前のデータ(S2)を取得
 S2.YEAR < S1.YEAR
 -- かつ
 AND
 -- 【条件２】以下の条件のデータが存在しない（取得条件として省く）
 NOT EXISTS(SELECT 1 FROM SALES S3
 -- 前のデータ(S2)より大きい かつ 現在のデータ(S1)より小さい
  WHERE S3.YEAR > S2.YEAR AND S3.YEAR < S1.YEAR)
...

/*
【条件２】において、
例えば S1=2003の場合、2002(直近), 2003, ... まで取得する。
【条件１】で「現在のデータ(S1)より前のデータ(S2)を取得」ので
2003～以降は弾かれて、結果的に、「2002(直近)」が残る。
*/

 【２】Oracle 11g

 使用テーブル 

-- 年間売上
CREATE TABLE "SALES" 
(
  "YEAR" INT,
  "SALE" INT,
  PRIMARY KEY ("YEAR")
);

# DROP TABLE SALES;

データ 

INSERT INTO SALES VALUES(2000, 1280);
INSERT INTO SALES VALUES(2001, 1220);
INSERT INTO SALES VALUES(2002, 1470);
INSERT INTO SALES VALUES(2003, 1310);
INSERT INTO SALES VALUES(2004, 1310);
INSERT INTO SALES VALUES(2005, 1920);
INSERT INTO SALES VALUES(2006, 1680);
INSERT INTO SALES VALUES(2007, 1790);
INSERT INTO SALES VALUES(2008, 2170);
INSERT INTO SALES VALUES(2009, 1810);
INSERT INTO SALES VALUES(2010, 1810);

例２用の追加データ（歯抜けデータ） 

INSERT INTO SALES VALUES(2011, 1420);
INSERT INTO SALES VALUES(2013, 1720);
INSERT INTO SALES VALUES(2014, 1640);
INSERT INTO SALES VALUES(2017, 2310);
INSERT INTO SALES VALUES(2018, 2810);

確認用 select文 

SELECT * FROM SALES;

YEAR    SALE
2000    1280
2001    1220
2002    1470
2003    1310
2004    1310
2005    1920
2006    1680
2007    1790
2008    2170
2009    1810
2010    1810

例２用の追加データ（歯抜けデータ） 

SELECT * FROM SALES;

YEAR    SALE
2000    1280
2001    1220
2002    1470
2003    1310
2004    1310
2005    1920
2006    1680
2007    1790
2008    2170
2009    1810
2010    1810
2011    1420
2013    1720
2014    1640
2017    2310
2018    2810

例１：年間売上の変化（増加/変化なし/減少）

SELECT
 S1.YEAR AS YEAR,
 CASE WHEN S1.SALE = S2.SALE THEN '→'
      WHEN S1.SALE > S2.SALE THEN '↑'
      WHEN S1.SALE < S2.SALE THEN '↓'
 ELSE '-' END AS UP_OR_DOWN,
 S1.SALE AS SALE,
 S2.SALE AS PREVIOUS_SALE,
 S1.SALE - S2.SALE AS DIFF
FROM
 SALES S1
LEFT OUTER JOIN
 SALES S2
ON
 S1.YEAR = S2.YEAR + 1
ORDER BY
 YEAR;

出力結果 

YEAR　UP_OR_DOWN　SALE PREVIOUS_SALE DIFF
2000　- 　　　　　1280 « NULL »      « NULL »
2001　↓  　　　　1220 1280          -60
2002　↑　　　　　1470 1220          250
2003　↓　　　　　1310 1470          -160
2004　→　　　　　1310 1310          0
2005　↑　　　　　1920 1310          610
2006　↓　　　　　1680 1920          -240
2007　↑　　　　　1790 1680          110
2008　↑　　　　　2170 1790          380
2009　↓　　　　　1810 2170          -360
2010　→　　　　　1810 1810          0

例２：年間売上の変化（増加/変化なし/減少。歯抜けデータ）

歯抜けデータが存在する場合、
例１のSQLでは対応できないので、代わりに以下のようになる

SQL文 

SELECT
 S1.YEAR AS YEAR,
 CASE WHEN S1.SALE = S2.SALE THEN '→'
      WHEN S1.SALE > S2.SALE THEN '↑'
      WHEN S1.SALE < S2.SALE THEN '↓'
 ELSE '-' END AS UP_OR_DOWN,
 S1.SALE AS SALE,
 S2.SALE AS PREVIOUS_SALE,
 S1.SALE - S2.SALE AS DIFF
FROM
 SALES S1
LEFT OUTER JOIN
 SALES S2
ON
 S2.YEAR < S1.YEAR
 AND NOT EXISTS(SELECT 1 FROM SALES S3
  WHERE S3.YEAR > S2.YEAR AND S3.YEAR < S1.YEAR)
ORDER BY YEAR;

出力結果 

YEAR　UP_OR_DOWN　SALE　PREVIOUS_SALE　DIFF
2000　-    　　　　　1280　« NULL »       « NULL »
2001　↓　　　　　1220　1280           -60
2002　↑　　　　　1470　1220           250
2003　↓　　　　　1310　1470           -160
2004　→　　　　　1310　1310           0
2005　↑　　　　　1920　1310           610
2006　↓　　　　　1680　1920           -240
2007　↑　　　　　1790　1680           110
2008　↑　　　　　2170　1790           380
2009　↓　　　　　1810　2170           -360
2010　→　　　　　1810　1810           0
2011　↓　　　　　1420　1810           -390
2013　↑　　　　　1720　1420           300
2014　↓　　　　　1640　1720           -80
2017　↑　　　　　2310　1640           670
2018　↑　　　　　2810　2310           500

【３】PostgreSQL

* 「【１】Oracle 11g」と全く変わらないSQL文で取得できた
* もう少し複雑なテーブルでの年間売上の変化を
　以下の関連記事の「例２：最新データによる年間売上の増減」に記載。

https://dk521123.hatenablog.com/entry/2012/07/26/013620

 使用テーブル 

-- 年間売上
CREATE TABLE sales 
(
  year INT,
  sales INT,
  PRIMARY KEY ("year")
);

データ 

INSERT INTO sales VALUES(2000, 1280);
INSERT INTO sales VALUES(2001, 1220);
INSERT INTO sales VALUES(2002, 1470);
INSERT INTO sales VALUES(2003, 1310);
INSERT INTO sales VALUES(2004, 1310);
INSERT INTO sales VALUES(2005, 1920);
INSERT INTO sales VALUES(2006, 1680);
INSERT INTO sales VALUES(2007, 1790);
INSERT INTO sales VALUES(2008, 2170);
INSERT INTO sales VALUES(2009, 1810);
INSERT INTO sales VALUES(2010, 1810);
INSERT INTO sales VALUES(2011, 1420);
INSERT INTO sales VALUES(2013, 1720);
INSERT INTO sales VALUES(2014, 1640);
INSERT INTO sales VALUES(2017, 2310);
INSERT INTO sales VALUES(2018, 2810);
INSERT INTO sales VALUES(2020, 2710);
INSERT INTO sales VALUES(2021, 2710);

例１：最新5年間の売上増減を表示

書き方１ 

SELECT
 s1.year AS year,
 CASE
   WHEN s1.sales = s2.sales THEN '→'
   WHEN s1.sales > s2.sales THEN '↑'
   WHEN s1.sales < s2.sales THEN '↓'
   ELSE '-'
 END AS up_or_down,
 s1.sales AS sales,
 s2.sales AS previous_sales,
 s1.sales - s2.sales AS diff
FROM sales AS s1, sales AS s2
WHERE
 s2.year=(SELECT MAX(year) FROM sales AS s3
  WHERE s1.year > s3.year)
ORDER BY
 s1.year DESC
LIMIT 5;

書き方２ 

SELECT
 s1.year AS year,
 CASE WHEN s1.sales = s2.sales THEN '→'
      WHEN s1.sales > s2.sales THEN '↑'
      WHEN s1.sales < s2.sales THEN '↓'
 ELSE '-' END AS up_or_down,
 s1.sales AS sales,
 s2.sales AS previous_sales,
 s1.sales - s2.sales AS diff
FROM
 sales AS s1
LEFT OUTER JOIN
 sales AS s2
ON
 s2.year = (SELECT MAX(year) FROM sales AS s3
  WHERE s1.year > s3.year)
ORDER BY
 s1.year DESC
LIMIT 5;

書き方３ 

SELECT
 S1.year AS year,
 CASE WHEN S1.sales = S2.sales THEN '→'
      WHEN S1.sales > S2.sales THEN '↑'
      WHEN S1.sales < S2.sales THEN '↓'
 ELSE '-' END AS up_or_down,
 S1.sales AS sales,
 S2.sales AS previous_sales,
 S1.sales - S2.sales AS diff
FROM
 sales S1
LEFT OUTER JOIN
 sales S2
ON
 S2.year < S1.year
 AND NOT EXISTS(SELECT 1 FROM sales S3
  WHERE S3.year > S2.year AND S3.year < S1.year)
ORDER BY year DESC
LIMIT 5;

出力結果 

year,up_or_down,sales,previous_sales,diff
2021,→,2710,2710,0
2020,↓,2710,2810,-100
2018,↑,2810,2310,500
2017,↑,2310,1640,670
2014,↓,1640,1720,-80

【４】おまけ：変化のHTMLでの表記

変化（増加/変化なし/減少）のHTMLでの表記は
以下の表の特殊文字を使うといいかも。

https://gray-code.com/html_css/list-of-symbols-and-special-characters/

記号 数値参照(10進数) 数値参照(16進数) 説明
⇧ ＆#8679; ＆#x21E7; 上向き二重矢印
⇨ ＆#8680; ＆#x21E8; 右向き二重矢印
⇩ ＆#8681; ＆#x21E9; 下向き二重矢印

参考文献

https://codezine.jp/article/detail/907
https://codezine.jp/article/detail/907?p=2

関連記事

サブクエリ / 副問合せ
https://dk521123.hatenablog.com/entry/2015/12/21/002727
相関サブクエリ / 自己相関サブクエリ
https://dk521123.hatenablog.com/entry/2016/01/23/230608
EXISTS / NOT EXISTS文 ～ 存在有無を確認 ～
https://dk521123.hatenablog.com/entry/2010/07/09/214044
過去直近データを取得するには
https://dk521123.hatenablog.com/entry/2016/01/05/234938
最新日付データを取得するには
https://dk521123.hatenablog.com/entry/2014/10/09/004105
期間が重なるデータを取得するには
https://dk521123.hatenablog.com/entry/2011/12/17/025502
順位付け・ランキング ～ RANK / DENSE_RANK ～
https://dk521123.hatenablog.com/entry/2012/08/15/225233
初めの数文字が一致してたら同一データと見なすSQLを考える
https://dk521123.hatenablog.com/entry/2018/04/11/225229
複数の集計を1回のSQL文で行う方法を考える
https://dk521123.hatenablog.com/entry/2018/04/12/210108
SQLで階層構造データを扱うことを考える
https://dk521123.hatenablog.com/entry/2013/02/21/235841
グループ内のMAXデータを取得する
https://dk521123.hatenablog.com/entry/2018/10/06/230841
SQLで変化（増加/変化なし/減少）について考える
https://dk521123.hatenablog.com/entry/2021/08/26/222043
促音・拗音を区別せず検索する
https://dk521123.hatenablog.com/entry/2012/11/09/010214
WITH句 ～ 共通テーブル式 ～
https://dk521123.hatenablog.com/entry/2012/07/26/013620