Posted in MySQL

[MySQL] 当日と前日の売上データの差を求める方法（自己結合）

   Published Date: 2017年4月27日

MySQLで今日の売上と昨日の売上の差を算出する方法についてです。MySQLの自己結合を使って算出します。

データの確認

テーブル名：sales
カラム：id(AUTO_INCREMENT) , product_id(製品ID) , sales_data(売上）, sales_date(販売日）

SELECT id, product_id, sales_data, sales_date
FROM  sales AS A 
WHERE 1

↓実行結果

+----+------------+------------+------------+
| id | product_id | sales_data | sales_date |
+----+------------+------------+------------+
|  9 |          1 |        250 | 2017-04-01 |
| 10 |          1 |        380 | 2017-04-02 |
| 11 |          1 |        120 | 2017-04-03 |
| 12 |          1 |        540 | 2017-04-04 |
| 13 |          2 |        150 | 2017-04-01 |
| 14 |          2 |        260 | 2017-04-02 |
| 15 |          2 |        450 | 2017-04-03 |
| 16 |          2 |        190 | 2017-04-04 |
+----+------------+------------+------------+
8 rows in set (0.00 sec)

算出方法

salesテーブルをもとに、もう一つafter_dayという１日後の日付のカラムがあるテーブルを作成します。
1日ずれた日をキーとして結合して、売上の差額を計算します。

salesテーブル（そのままを）　エイリアス「A」
１日後の日付のカラムがあるテーブルを エイリアス「B」とします。

まず、１日後の日付のあるテーブルを作成してみます。

SELECT id, product_id, sales_data, sales_date, DATE( sales_date + INTERVAL 1 
DAY ) AS after_day
FROM  sales AS B
WHERE 1

↓実行結果

+----+------------+------------+------------+------------+
| id | product_id | sales_data | sales_date | after_day  |
+----+------------+------------+------------+------------+
|  9 |          1 |        250 | 2017-04-01 | 2017-04-02 |
| 10 |          1 |        380 | 2017-04-02 | 2017-04-03 |
| 11 |          1 |        120 | 2017-04-03 | 2017-04-04 |
| 12 |          1 |        540 | 2017-04-04 | 2017-04-05 |
| 13 |          2 |        150 | 2017-04-01 | 2017-04-02 |
| 14 |          2 |        260 | 2017-04-02 | 2017-04-03 |
| 15 |          2 |        450 | 2017-04-03 | 2017-04-04 |
| 16 |          2 |        190 | 2017-04-04 | 2017-04-05 |
+----+------------+------------+------------+------------+
8 rows in set (0.00 sec)

二つのテーブルを結合してみます。

SELECT A.id, A.product_id, A.sales_date, A.sales_data AS today_sales_data, B.sales_data AS before_sales_data, (
A.sales_data - B.sales_data
) AS spread
FROM sales AS A
INNER JOIN sales AS B ON A.product_id = B.product_id
AND A.sales_date = DATE( B.sales_date + INTERVAL 1 
DAY ) 
WHERE 1

DATE( B.sales_date + INTERVAL 1 DAY ) はテーブル名Bのsales_dateの１日後という意味です。

↓実行結果


+----+------------+------------+------------------+-------------------+--------
| id | product_id | sales_date | today_sales_data | before_sales_data | spread 
+----+------------+------------+------------------+-------------------+--------
| 10 |          1 | 2017-04-02 |              380 |               250 |    130 
| 11 |          1 | 2017-04-03 |              120 |               380 |   -260 
| 12 |          1 | 2017-04-04 |              540 |               120 |    420 
| 14 |          2 | 2017-04-02 |              260 |               150 |    110 
| 15 |          2 | 2017-04-03 |              450 |               260 |    190 
| 16 |          2 | 2017-04-04 |              190 |               450 |   -260 
+----+------------+------------+------------------+-------------------+--------
6 rows in set (0.00 sec)

当日の売上、前日の売上、spread(当日の売上-前日の売上）がうまく表示されました。

テストデータ

テーブル名:sales

--
-- テーブルの構造 `sales`
--

CREATE TABLE IF NOT EXISTS `sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `sales_data` int(11) NOT NULL,
  `sales_date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=17 ;

--
-- テーブルのデータのダンプ `sales`
--

INSERT INTO `sales` (`id`, `product_id`, `sales_data`, `sales_date`) VALUES
(9, 1, 250, '2017-04-01'),
(10, 1, 380, '2017-04-02'),
(11, 1, 120, '2017-04-03'),
(12, 1, 540, '2017-04-04'),
(13, 2, 150, '2017-04-01'),
(14, 2, 260, '2017-04-02'),
(15, 2, 450, '2017-04-03'),
(16, 2, 190, '2017-04-04');

5日間の売上を比較

５日間の営業成績の推移を算出したい場合は以下のようなSQL文を書きます。

SELECT A.id, A.product_id, A.sales_date, A.sales_data, B.sales_data AS salesB, C.sales_data AS salesC , D.sales_data AS salesD , E.sales_data AS salesE
FROM sales AS A
INNER JOIN sales AS B ON A.product_id = B.product_id
AND A.sales_date = DATE( B.sales_date + INTERVAL 1 
DAY ) 
INNER JOIN sales AS C ON A.product_id = C.product_id
AND A.sales_date = DATE( C.sales_date + INTERVAL 2 
DAY ) 
INNER JOIN sales AS D ON A.product_id = D.product_id
AND A.sales_date = DATE( D.sales_date + INTERVAL 3 
DAY ) 
INNER JOIN sales AS E ON A.product_id = E.product_id
AND A.sales_date = DATE( E.sales_date + INTERVAL 4 
DAY ) 
ORDER BY A.product_id ASC , A.sales_date DESC



Author: webmaster