[MySQL] 当日と前日の売上データの差を求める方法（自己結合）
MySQLで今日の売上と昨日の売上の差を算出する方法についてです。MySQLの自己結合を使って算出します。
データの確認
テーブル名：sales
カラム：id(AUTO_INCREMENT) , product_id(製品ID) , sales_data(売上）, sales_date(販売日）
|
1 2 3 |
SELECT id, product_id, sales_data, sales_date FROM sales AS A WHERE 1 |
↓実行結果
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
+----+------------+------------+------------+ | 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」とします。
まず、１日後の日付のあるテーブルを作成してみます。
|
1 2 3 4 |
SELECT id, product_id, sales_data, sales_date, DATE( sales_date + INTERVAL 1 DAY ) AS after_day FROM sales AS B WHERE 1 |
↓実行結果
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
+----+------------+------------+------------+------------+ | 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) |
二つのテーブルを結合してみます。
|
1 2 3 4 5 6 7 8 |
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の１日後という意味です。
↓実行結果
|
1 2 3 4 5 6 7 8 9 10 11 |
+----+------------+------------+------------------+-------------------+-------- | 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
-- -- テーブルの構造 `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文を書きます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 |
関連記事
- [CakePHP] 独自SQLのAS句で新しいフィールドを作りCakePHPの形式でデータを出力する方法
- [MySQL] AUTO_INCREMENTの番号を振り直す方法
- [MySQL]1カラムに登録されたカンマ区切りデータを検索・抽出する方法（FIND_IN_SET）
- [MySQL] Incorrect datetime value: ‘0000-00-00 00:00:00’ for column エラー対処方法
- [MySQL]月・年単位でデータを抽出する方法 （DATE_FORMAT）
