결과 집합에 날짜 공백이 없는 날짜 범위의 날짜별 데이터 집계

결과 집합에 날짜 공백이 없는 날짜 범위의 날짜별 데이터 집계

판매 주문서가 있는 테이블이 있는데 목록으로 작성하려고 합니다.COUNT날짜 간격을 두지 않고 두 날짜 사이에 판매 주문 건수를 기록했습니다.

현재 가지고 있는 것은 다음과 같습니다.

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

그게 가능한가요?

다양한 날짜를 즉시 작성하고 주문 테이블에 참여:-

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일의 날짜 범위에 대응합니다.

날짜에 사용하는 필드 유형에 따라 더 쉽게 효율적으로 만들 수 있습니다.

편집 - 요청에 따라 월별 주문 수를 가져옵니다.

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

범위의 모든 날짜를 포함하는 가상(또는 물리적) 테이블을 생성해야 합니다.

시퀀스 테이블을 사용하여 다음과 같이 수행할 수 있습니다.

SELECT mintime + INTERVAL seq.seq DAY AS orderdate
  FROM (
        SELECT CURDATE() - INTERVAL 1 MONTH AS mintime,
               CURDATE() AS maxtime
          FROM obs
       ) AS minmax
  JOIN seq_0_to_999999 AS seq ON seq.seq < TIMESTAMPDIFF(DAY,mintime,maxtime)

그런 다음 다음과 같이 이 가상 테이블을 쿼리에 결합합니다.

SELECT IFNULL(orders.Norders,0) AS Norders, /* show zero instead of null*/
       DATE_FORMAT(alldates.orderdate, "%M %e") as sdate 
  FROM (
        SELECT mintime + INTERVAL seq.seq DAY AS orderdate
          FROM (
                SELECT CURDATE() - INTERVAL 1 MONTH AS mintime,
                       CURDATE() AS maxtime
                  FROM obs
               ) AS minmax
          JOIN seq_0_to_999999 AS seq 
                        ON seq.seq < TIMESTAMPDIFF(DAY,mintime,maxtime)
       ) AS alldates
  LEFT JOIN (
    SELECT COUNT(*) as Norders, DATE(date) AS orderdate
      FROM ORDERS 
    WHERE date <= NOW() 
      AND date >= NOW() - INTERVAL 1 MONTH 
    GROUP BY DAY(date) 
       ) AS orders ON alldates.orderdate = orders.orderdate
ORDER BY alldates.orderdate ASC

필요한 것은,LEFT JOIN따라서 출력 결과 세트의 행은 데이터에 데이터가 없어도 유지됩니다.ORDERS테이블.

이 시퀀스 표는 어디서 구할 수 있나요?seq_0_to_999999이렇게 만들 수 있어요.

DROP TABLE IF EXISTS seq_0_to_9;
CREATE TABLE seq_0_to_9 AS
   SELECT 0 AS seq 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;

DROP VIEW IF EXISTS seq_0_to_999;
CREATE VIEW seq_0_to_999 AS (
SELECT (a.seq + 10 * (b.seq + 10 * c.seq)) AS seq
  FROM seq_0_to_9 a
  JOIN seq_0_to_9 b
  JOIN seq_0_to_9 c
);

DROP VIEW IF EXISTS seq_0_to_999999;
CREATE VIEW seq_0_to_999999 AS (
SELECT (a.seq + (1000 * b.seq)) AS seq
  FROM seq_0_to_999 a
  JOIN seq_0_to_999 b
);

자세한 설명은 http://www.plumislandmedia.net/mysql/filling-missing-data-sequences-cardinal-integers/ 에서 보실 수 있습니다.

MariaDB 버전 10+ 를 사용하고 있는 경우는, 이러한 시퀀스 테이블이 짜넣어져 있습니다.

먼저 캘린더 테이블 작성

SELECT coalesce(COUNT(O.*),0) as Norders, DATE_FORMAT(C.date, "%M %e") as sdate 
FROM Calendar C 
  LEFT JOIN ORDERS O ON C.date=O.date
WHERE O.date <= NOW() AND O.date >= NOW() - INTERVAL 1 MONTH 
GROUP BY DAY(date) 
ORDER BY date ASC;

언급URL : https://stackoverflow.com/questions/24533485/aggregating-data-by-date-in-a-date-range-without-date-gaps-in-result-set