在日期范围内按日期聚合数据，结果集中没有日期间隔

2022-01-11 00:00:00 time-series sql mariadb mysql

我有一张卖单的表格，我想列出两个日期之间每天的 COUNT 个卖单，不留日期间隔.

I have a table with sell orders and I want to list the COUNT of sell orders per day, between two dates, without leaving date gaps.

这是我目前拥有的:

SELECT COUNT(*) as Norders, DATE_FORMAT(date, "%M %e") as sdate 
FROM ORDERS 
WHERE date <= NOW() 
  AND date >= NOW() - INTERVAL 1 MONTH 
GROUP BY DAY(date) 
ORDER BY date ASC;

我得到的结果如下:

6     May 1
14    May 4
1     May 5
8     Jun 2
5     Jun 15

但我想得到的是:

6     May 1
0     May 2
0     May 3
14    May 4
1     May 5
0     May 6
0     May 7
0     May 8
.....
0     Jun 1
8     Jun 2
.....
5     Jun 15

这可能吗?

推荐答案

动态创建日期范围并将其加入到您的订单表中:-

Creating a range of dates on the fly and joining that against you orders table:-

SELECT sub1.sdate, COUNT(ORDERS.id) as Norders
FROM
(
    SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL units.i + tens.i * 10 + hundreds.i * 100 DAY), "%M %e") as sdate 
    FROM (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)units
    CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)tens
    CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)hundreds
    WHERE DATE_SUB(NOW(), INTERVAL units.i + tens.i * 10 + hundreds.i * 100 DAY) BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW()
) sub1
LEFT OUTER JOIN ORDERS
ON sub1.sdate = DATE_FORMAT(ORDERS.date, "%M %e")
GROUP BY sub1.sdate

这可以处理长达 1000 天的日期范围.

This copes with date ranges of up to 1000 days.

请注意，根据您用于日期的字段类型，它可以更容易地提高效率.

Note that it could be made more efficient easily depending on the type of field you are using for your dates.

编辑 - 根据要求，获取每月的订单数量:-

EDIT - as requested, to get the count of orders per month:-

SELECT aMonth, COUNT(ORDERS.id) as Norders
FROM
(
    SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL months.i MONTH), "%Y%m") as sdate, DATE_FORMAT(DATE_SUB(NOW(), INTERVAL months.i MONTH), "%M") as aMonth 
    FROM (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11)months
    WHERE DATE_SUB(NOW(), INTERVAL months.i MONTH) BETWEEN DATE_SUB(NOW(), INTERVAL 12 MONTH) AND NOW()
) sub1
LEFT OUTER JOIN ORDERS
ON sub1.sdate = DATE_FORMAT(ORDERS.date, "%Y%m")
GROUP BY aMonth

相关文章