比较同一个表中的两个日期范围 [英] Comparing two date ranges within the same table
问题描述
我有一个表，其中每个商店的销售如下：
SQL&选择*从销售;
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行。
我需要做的是比较表中的两个日期范围。假设我需要知道2009年1月1日至2009年1月10日期间销售额的差异。A / B&B。
我想建立一个查询会返回如下所示的内容：
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。
到目前为止，我想出了这个快速查询，但我的日期从销售到销售2有时在每一行都不同：
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
我缺少什么？ / p>
使用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）;
查询
c $ c> SELECT *
FROM（SELECT s1.id AS s1id，
NVL（s1.id_store，s2.id_store）AS s1store，
NVL（s1.date，MDY 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日期）-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。日期BETWEEN'2010-01-01'和'2010-01-10'
和s2.date'2009-01-01'和'2009-01-10'
和s（s1。日期）= DAY（s2.date）
和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的内部查询为您在左侧和右侧的结果为null。在ON条款中的5部分标准似乎是必要的;否则，外部查询中的条件必须更复杂和混乱 - 如果它可以使工作。然后外部选择中的标准确保选择正确的数据。内部查询中的NVL（）表达式的一个优点是存储ID列都相同且不为null，并且两个日期列都不为空，因此order by子句可以更简单 - 对于商店ID和日期列。 / p>
在Informix中，也可以将日期表达式重做为：
NVL（s1.date，s2.date + 1 UNITS YEAR）
NVL（s2.date，s1.date - 1 UNITS YEAR）
实际上有多种类型的转换在幕后使用该符号，但它给出了相同的结果，额外的计算可能不是那么重要。 p>
在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生成错误;这不是很有帮助。你可能编写一个存储过程，可能返回NULL为2008-02-29加1年，因为没有任何日期来比较它的销售。
你应该能够相当容易地使日期算法适应MySQL;其余的代码不需要更改。
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?
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.
这篇关于比较同一个表中的两个日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
