あいつの日誌β

働きながら旅しています。

それぞれの商品の販売数の前日比を SQL で求める

SQL

あらすじ

課題: それぞれの商品の前日の販売数と前々日の販売数の差を知りたい。

> select * from purchase_logs;
+----+------------+-------------+------------+
| id | product_id | sales_count | created_on |
+----+------------+-------------+------------+
|  1 |          1 |         100 | 2014-12-01 |
|  2 |          2 |         100 | 2014-12-01 |
|  3 |          3 |         100 | 2014-12-01 |
|  4 |          4 |         100 | 2014-12-01 |
|  5 |          1 |         100 | 2014-12-02 |
|  6 |          2 |         200 | 2014-12-02 |
|  7 |          3 |         300 | 2014-12-02 |
|  8 |          4 |         400 | 2014-12-02 |
+----+------------+-------------+------------+
8 rows in set (0.00 sec)

やりたいこと

以下のような結果を求めたい

+------------+------------------+-----------+------------+------------+
| product_id | day_before_after | yesterday | from_date  | to_date    |
+------------+------------------+-----------+------------+------------+
|          1 |              100 |       100 | 2014-12-01 | 2014-12-02 |
|          2 |              100 |       200 | 2014-12-01 | 2014-12-02 |
|          3 |              100 |       300 | 2014-12-01 | 2014-12-02 |
|          4 |              100 |       400 | 2014-12-01 | 2014-12-02 |
+------------+------------------+-----------+------------+------------+

データ準備

> CREATE DATABASE test_query;
> USE test_query;
> CREATE TABLE purchase_logs (id int primary key auto_increment, product_id int, sales_count int, created_on date);
> INSERT INTO purchase_logs (product_id, sales_count, created_on) VALUES 
(1, 100, '2014-12-01'), (2, 100, '2014-12-01'), (3, 100, '2014-12-01'), (4, 100, '2014-12-01'),
(1, 100, '2014-12-02'), (2, 200, '2014-12-02'), (3, 300, '2014-12-02'), (4, 400, '2014-12-02');

考え方

最初に前日の売り上げ表を取得するクエリを考える。この場合は2014-12-02が前日。この表をAとします。

> select product_id, sales_count, created_on from purchase_logs where created_on = '2014-12-02'
+------------+-------------+------------+
| product_id | sales_count | created_on |
+------------+-------------+------------+
|          1 |         100 | 2014-12-02 |
|          2 |         200 | 2014-12-02 |
|          3 |         300 | 2014-12-02 |
|          4 |         400 | 2014-12-02 |
+------------+-------------+------------+
4 rows in set (0.00 sec)

次に前々日の売り上げ表を取得する。この表をBとします

mysql> select product_id, sales_count, created_on from purchase_logs where created_on = '2014-12-01';
+------------+-------------+------------+
| product_id | sales_count | created_on |
+------------+-------------+------------+
|          1 |         100 | 2014-12-01 |
|          2 |         100 | 2014-12-01 |
|          3 |         100 | 2014-12-01 |
|          4 |         100 | 2014-12-01 |
+------------+-------------+------------+
4 rows in set (0.00 sec)

この表をうまく結合させるために、日付の取得を工夫します。この表をCとします

> select product_id, sales_count, date_sub(created_on , interval 1 day) as 'from', created_on as 'to' from purchase_logs where created_on = '2014-12-02'
+------------+-------------+------------+------------+
| product_id | sales_count | from       | to         |
+------------+-------------+------------+------------+
|          1 |         100 | 2014-12-01 | 2014-12-02 |
|          2 |         200 | 2014-12-01 | 2014-12-02 |
|          3 |         300 | 2014-12-01 | 2014-12-02 |
|          4 |         400 | 2014-12-01 | 2014-12-02 |
+------------+-------------+------------+------------+
4 rows in set (0.00 sec)

このBとCを内部結合させるとなんとなくやりたい事ができる気がしてきます。

具体的なクエリ

この2つのテーブルを product_id と 日付 join すると仮定してクエリを考える。 ※とりあえず分かりやすくするため一旦テーブル名を日本語にしています。

> SELECT 前日.product_id, 前々日.sales_count as '前々日', 前日.sales_count as '前日', 前々日.created_on AS from_date, 前日.created_on AS to_date
  FROM purchase_logs 前日 
    INNER JOIN purchase_logs 前々日 
    ON 前日.product_id = 前日.product_id 
    AND date_sub(前日.created_on , interval 1 day) = 前々日.created_on ;

これを 前日を a, 前々日を b に変更してクエリを発行してみます。

SELECT a.product_id, b.sales_count as 'day_before_after', a.sales_count as 'yesterday', b.created_on AS from_date, a.created_on AS to_date
  FROM purchase_logs a 
  INNER JOIN purchase_logs b 
  ON a.product_id = b.product_id 
  AND date_sub(a.created_on , interval 1 day) = b.created_on;
+------------+------------------+-----------+------------+------------+
| product_id | day_before_after | yesterday | from_date  | to_date    |
+------------+------------------+-----------+------------+------------+
|          1 |              100 |       100 | 2014-12-01 | 2014-12-02 |
|          2 |              100 |       200 | 2014-12-01 | 2014-12-02 |
|          3 |              100 |       300 | 2014-12-01 | 2014-12-02 |
|          4 |              100 |       400 | 2014-12-01 | 2014-12-02 |
+------------+------------------+-----------+------------+------------+
4 rows in set (0.00 sec)

enjoy:)