如何左加入,以创建日期范围? [英] How to LEFT JOIN, to create a Date Range?
问题描述
我有这个查询
SELECT userId, orgQuery.timeUnit,
@SUM := @SUM + orgQuery.orderValue AS sum,
@COUNT := @COUNT + 1 AS count,
@AVG := @SUM / @COUNT AS avg
FROM (
SELECT userid, orderValue,
DATE_FORMAT(`acceptDate`, '%Y%M') AS timeUnit
FROM `agreements`
WHERE userId = 4
AND acceptDate > 2000-00-00
GROUP BY timeUnit
)
AS orgQuery,
(SELECT @COUNT := 0, @SUM := 0,@AVG :=0)
AS extra GROUP BY timeUnit
输出:
userId timeUnit sum count avg
4 201001 6000 1 6000.0000
4 201003 12000 2 6000.0000
4 201004 19500 3 6500.0000
但是如您所见,某些日期之间有间隔,我希望输出为连续范围,例如:
But as you can see, there is a gap between some dates and I want the output to be a contiguous range eg:
userId timeUnit sum count avg
4 201001 6000 1 6000.0000
4 201002 0 2 3000.0000
4 201003 12000 3 6000.0000
4 201004 19500 4 4875.0000
此查询
(SELECT DATE_FORMAT(`acceptDate`, '%Y%M') AS timeUnit FROM `agreements` GROUP BY timeUnit )
输出完整的日期范围,但是当我尝试左联接两个查询时,count和avg都搞砸了.如何获得想要的结果?
Outputs the full date range, but when I try to LEFT JOIN the two Querys, the count and avg gets all messed up. How can I get the result am looking for?
推荐答案
假设您缺少2012-02年度协议表中的条目,那么最好只包含一个包含日期的表.
Assuming you are missing an entry in your agreements table for 2012-02, it's always good to have a table containing simply dates.
CREATE TABLE dates(`date` date primary key);
DROP PROCEDURE IF EXISTS insertDates;
DELIMITER $$
CREATE PROCEDURE insertDates()
BEGIN
SET @start_date = '2010-01-01';
WHILE (@start_date <= '2010-12-31') DO
INSERT INTO dates VALUES (@start_date);
SET @start_date:=DATE_ADD(@start_date, INTERVAL 1 DAY);
END WHILE;
END $$
DELIMITER ;
CALL insertDates();
根据需要调整日期范围.
Adjust the date range according to your needs.
然后,您可以像下面这样编写查询.我简化了一点,因为我看不到需要变量或子查询.
Then you can write your query like the following. I simplified it a bit, since I saw no need for your variables or the subquery.
SELECT userId, DATE_FORMAT(dates.`date`, '%Y%M') AS timeUnit,
SUM(orderValue),
COUNT(orderValue),
AVG(orderValue)
FROM
dates LEFT JOIN
`agreements` ON dates.date = agreements.acceptDate
WHERE userId = 4
AND acceptDate > '2000-00-00'
GROUP BY userId, timeUnit
更新:
SELECT userId, orgQuery.timeUnit,
@SUM := @SUM + orgQuery.orderValue AS sum,
@COUNT := @COUNT + 1 AS count,
@AVG := @SUM / @COUNT AS avg
FROM (
SELECT userid, orderValue,
DATE_FORMAT(dates.`date`, '%Y%M') AS timeUnit
FROM dates LEFT JOIN
`agreements` ON dates.date = agreements.acceptDate
WHERE userId = 4
AND acceptDate > '2000-00-00'
GROUP BY timeUnit
)
AS orgQuery,
(SELECT @COUNT := 0, @SUM := 0,@AVG :=0)
AS extra GROUP BY timeUnit
这篇关于如何左加入,以创建日期范围?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!