当前位置： 文江博客话题详情

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

发布于 2024-08-28 06:18:55 字数 2522 浏览 5 评论 0原文

我有一个表，其中包含每个商店的销售额，如下所示：

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

因此，即使 2010 年 1 月 1 日至 10 日之间没有销售额无论是一个范围还是另一个范围，它都应该用 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

我错过了什么？

原文

I have a table with sales per store as follows:

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.

What I need to do is to compare two date ranges within that table. Lets say I need to know the differences in sales between 01 Jan 2009 to 10 Jan 2009 AGAINST 01 Jan 2010 to 10 Jan 2010.

I'd like to build a query that returns something like this:

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

So, even if there are no sales in one range or another, it should just fill the empty space with NULL.

So far, I've come up with this quick query, but I the "dates" from sales to sales2 sometimes are different in each row:

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

What am I missing?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（2）

凉墨 2024-09-04 06:18:55

使用 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；其余代码不需要更改。

Using IBM Informix Dynamic Server 11.50.FC6, I can use this SQL sequence to get the result you require:

Setup

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);

Query

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;

Result

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

Explanation

It took a fair amount of experimentation to get this 'right'. Informix has a DATE constructor function MDY() which takes three integer arguments: the month, day and year (the name is mnemonic). It also has three analysis functions: DAY(), MONTH() and YEAR() which return the day, month and year of the date argument. The inner query with the FULL JOIN gives you the results with nulls on both left and right sides. The 5-part criterion in the ON clause seems to be necessary; otherwise, the criteria in the outer query has to be more complex and confusing - if it can be made to work at all. Then the criteria in the outer selection ensure that the right data is chosen. One advantage of the NVL() expressions in the inner query is that the store ID columns are both the same and not null and neither date column is null, so the order by clause can be simpler - on store ID and either date column.

In Informix, it would also be possible to to rework the date expressions as:

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

There are actually multiple type conversions going on behind the scenes with that notation, but it gives you the same result and the extra calculation is probably not all that significant.

There is also a glitch in waiting in Informix; you cannot add or subtract 1 year to or from any February 29th - because there is no 29th February in the following or previous year. You would need to be careful with your data; if you're not, you could end up comparing the data for 2008-02-29 with 2009-02-28 (as well as comparing the data for 2008-02-28 with 2009-02-28). There is a process called 'double entry bookkeeping', but this isn't what is meant by it, and your computations could be confused if '2008-02-29 plus 1 year' is 2009-02-28. Informix generates an error; that isn't very much more helpful. You might code a stored procedure, probably, to return NULL for 2008-02-29 plus 1 year since there isn't any date to compare its sales with.

You should be able to adapt the date arithmetic to MySQL fairly easily; the rest of the code does not need to to change.

回复 原文
深居我梦 2024-09-04 06:18:55

我认为问题出在你的加入条件上。我还没有测试过，但我想你可以尝试一下

... ON (    sales.id_store = sales2.id_store 
        AND sales.date = ADDDATE(sales2.date, INTERVAL 1 YEAR) 
        ...
       )

I think the problem is in your join condition. I haven't tested it but I think you could try sth like

... ON (    sales.id_store = sales2.id_store 
        AND sales.date = ADDDATE(sales2.date, INTERVAL 1 YEAR) 
        ...
       )
回复 原文
~没有更多了~

关于作者

淡忘如思

暂无简介

0 文章
0 评论
24 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

玍銹的英雄夢

文章 0 评论 0

我不会写诗

文章 0 评论 0

十六岁半

文章 0 评论 0

浸婚纱

文章 0 评论 0

qq_kJ6XkX

文章 0 评论 0

旧伤还要旧人安

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文