mysql - 比较同一个表中的两个日期范围

Question

我有一张包含每家商店销售额的表格，如下所示:

SQL> select * from sales;

        ID ID_STORE DATE       TOTAL
---------- -------- ---------- -------------------------------
         1        1 2010-01-01    500.00
         2        1 2010-01-02    185.00
         3        1 2010-01-03    135.00
         4        1 2009-01-01    165.00
         5        1 2009-01-02    175.00
         6        5 2010-01-01    130.00
         7        5 2010-01-02    135.00
         8        5 2010-01-03    130.00
         9        6 2010-01-01    100.00
         10       6 2010-01-02     12.00
         11       6 2010-01-03     85.00
         12       6 2009-01-01    135.00
         13       6 2009-01-02    400.00
         14       6 2009-01-07     21.00
         15       6 2009-01-08     45.00
         16       8 2009-01-09    123.00
         17       8 2009-01-10    581.00

17 rows selected.

我需要做的是比较该表中的两个日期范围。假设我需要知道 2009 年 1 月 1 日至 2009 年 1 月 10 日与 2010 年 1 月 1 日至 2010 年 1 月 10 日之间的销售额差异。

我想构建一个返回如下内容的查询:

ID_STORE_A DATE_A     TOTAL_A   ID_STORE_B DATE_B     TOTAL_B
---------- ---------- --------- ---------- ---------- -------------------
         1 2010-01-01    500.00          1 2009-01-01    165.00
         1 2010-01-02    185.00          1 2009-01-02    175.00
         1 2010-01-03    135.00          1 NULL          NULL

         5 2010-01-01    130.00          5 NULL          NULL
         5 2010-01-02    135.00          5 NULL          NULL
         5 2010-01-03    130.00          5 NULL          NULL

         6 2010-01-01    100.00          6 2009-01-01    135.00
         6 2010-01-02     12.00          6 2009-01-02    400.00
         6 2010-01-03     85.00          6 NULL          NULL
         6 NULL          NULL            6 2009-01-07     21.00
         6 NULL          NULL            6 2009-01-08     45.00
         6 NULL          NULL            8 2009-01-09    123.00
         6 NULL          NULL            8 2009-01-10    581.00

因此，即使某个范围内没有销售额，它也应该用 NULL 填充空白区域。

到目前为止，我已经提出了这个快速查询，但我从 sales 到 sales2 的“日期”有时每一行都不同:

SELECT sales.*, sales2.*
  FROM sales
  LEFT JOIN sales AS sales2 
    ON (sales.id_store=sales2.id_store)
 WHERE sales.date >= '2010-01-01' 
   AND sales.date <= '2010-01-10' 
   AND sales2.date >= '2009-01-01' 
   AND sales2.date <= '2009-01-10' 
ORDER BY sales.id_store ASC, sales.date ASC, sales2.date ASC

我错过了什么？

Answer 1

使用 IBM Informix Dynamic Server 11.50.FC6，我可以使用这个 SQL 序列来获得您需要的结果:

设置

CREATE TABLE sales
(
    id       INTEGER NOT NULL,
    id_store INTEGER NOT NULL,
    date     DATE NOT NULL,
    total    DECIMAL(10,2) NOT NULL
);

INSERT INTO sales VALUES( 1, 1, '2010-01-01', 500.00);
INSERT INTO sales VALUES( 2, 1, '2010-01-02', 185.00);
INSERT INTO sales VALUES( 3, 1, '2010-01-03', 135.00);
INSERT INTO sales VALUES( 4, 1, '2009-01-01', 165.00);
INSERT INTO sales VALUES( 5, 1, '2009-01-02', 175.00);
INSERT INTO sales VALUES( 6, 5, '2010-01-01', 130.00);
INSERT INTO sales VALUES( 7, 5, '2010-01-02', 135.00);
INSERT INTO sales VALUES( 8, 5, '2010-01-03', 130.00);
INSERT INTO sales VALUES( 9, 6, '2010-01-01', 100.00);
INSERT INTO sales VALUES(10, 6, '2010-01-02',  12.00);
INSERT INTO sales VALUES(11, 6, '2010-01-03',  85.00);
INSERT INTO sales VALUES(12, 6, '2009-01-01', 135.00);
INSERT INTO sales VALUES(13, 6, '2009-01-02', 400.00);
INSERT INTO sales VALUES(14, 6, '2009-01-07',  21.00);
INSERT INTO sales VALUES(15, 6, '2009-01-08',  45.00);
INSERT INTO sales VALUES(16, 8, '2009-01-09', 123.00);
INSERT INTO sales VALUES(17, 8, '2009-01-10', 581.00);

查询

SELECT *
  FROM (SELECT s1.id AS s1id,
               NVL(s1.id_store, s2.id_store) AS s1store,
               NVL(s1.date, MDY(MONTH(s2.date), DAY(s2.date),
                                YEAR(s2.date)+1)) AS s1date,
               s1.total AS s1total,
               s2.id AS s2id,
               NVL(s2.id_store, s1.id_store) AS s2store,
               NVL(s2.date, MDY(MONTH(s1.date), DAY(s1.date),
                                YEAR(s1.date)-1)) AS s2date,
               s2.total AS s2total
          FROM sales AS s1 FULL JOIN sales AS s2
            ON s1.id_store = s2.id_store
           AND s1.date BETWEEN '2010-01-01' AND '2010-01-10'
           AND s2.date BETWEEN '2009-01-01' AND '2009-01-10'
           AND DAY(s1.date)   = DAY(s2.date)
           AND MONTH(s1.date) = MONTH(s2.date)
       ) AS s3
 WHERE s1_date BETWEEN '2010-01-01' AND '2010-01-10'
   AND s2_date BETWEEN '2009-01-01' AND '2009-01-10'
 ORDER BY s1_id_store ASC, s1_date ASC;

结果

s1id s1store  s1date     s1total  s2id s2store  s2date     s2total
 1       1    2010-01-01  500.00   4       1    2009-01-01  165.00
 2       1    2010-01-02  185.00   5       1    2009-01-02  175.00
 3       1    2010-01-03  135.00           1    2009-01-03             
 6       5    2010-01-01  130.00           5    2009-01-01             
 7       5    2010-01-02  135.00           5    2009-01-02             
 8       5    2010-01-03  130.00           5    2009-01-03             
 9       6    2010-01-01  100.00  12       6    2009-01-01  135.00
10       6    2010-01-02   12.00  13       6    2009-01-02  400.00
11       6    2010-01-03   85.00           6    2009-01-03             
         6    2010-01-07          14       6    2009-01-07   21.00
         6    2010-01-08          15       6    2009-01-08   45.00
         8    2010-01-09          16       8    2009-01-09  123.00
         8    2010-01-10          17       8    2009-01-10  581.00

说明

为了获得这种“正确”，需要进行大量实验。 Informix 有一个 DATE 构造函数 MDY()，它接受三个整数参数:月、日和年(名称是助记符)。它还具有三个分析函数:DAY()、MONTH() 和 YEAR()，它们返回日期参数的日、月和年。使用 FULL JOIN 的内部查询会为您提供左右两侧都为空值的结果。 ON 条款中的 5 部分标准似乎是必要的；否则，外部查询中的标准必须更加复杂和困惑 - 如果它可以工作的话。然后外部选择中的标准确保选择了正确的数据。内部查询中 NVL() 表达式的一个优点是商店 ID 列相同且不为空，并且日期列都不为空，因此 order by 子句可以更简单 - 在商店 ID 和任一日期列上。

在 Informix 中，也可以将日期表达式重写为:

NVL(s1.date, s2.date + 1 UNITS YEAR)
NVL(s2.date, s1.date - 1 UNITS YEAR)

实际上使用该表示法在幕后进行了多种类型转换，但它会为您提供相同的结果，并且额外的计算可能并不那么重要。

Informix 中的等待也有一个小故障；您不能在任何 2 月 29 日加上或减去 1 年 - 因为下一年或上一年都没有 2 月 29 日。您需要小心处理您的数据；如果不是，您最终可能会将 2008-02-29 的数据与 2009-02-28 的数据进行比较(以及将 2008-02-28 的数据与 2009-02-28 的数据进行比较)。有一个过程称为“复式簿记”，但这不是它的意思，如果“2008-02-29 加 1 年”是 2009-02-28，您的计算可能会混淆。 Informix 生成错误；这并没有多大帮助。您可能会编写一个存储过程，以返回 2008-02-29 加 1 年的 NULL，因为没有任何日期可以与之比较其销售额。

您应该能够很容易地使日期算法适应 MySQL；其余代码无需更改。