MySQL 범위에서 누락 된 날짜를 채우는 방법은 무엇입니까?

MySQL 범위에서 누락 된 날짜를 채우는 방법은 무엇입니까?

---

2 개의 열, 날짜 및 점수가있는 테이블이 있습니다. 지난 30 일 동안 각 항목에 대해 최대 30 개의 항목이 있습니다.

date      score
-----------------
1.8.2010  19
2.8.2010  21
4.8.2010  14
7.8.2010  10
10.8.2010 14

내 문제는 일부 날짜가 누락되었다는 것입니다.보고 싶습니다.

date      score
-----------------
1.8.2010  19
2.8.2010  21
3.8.2010  0
4.8.2010  14
5.8.2010  0
6.8.2010  0
7.8.2010  10
...

단일 쿼리에서 필요한 것은 19,21,9,14,0,0,10,0,0,14 ... 즉, 누락 된 날짜가 0으로 채워져 있음을 의미합니다.

나는 모든 값을 얻는 방법과 날짜를 반복하고 공백을 누락하는 서버 측 언어로 알고 있습니다. 그러나 이것이 mysql에서 가능하므로 결과를 날짜별로 정렬하고 누락 된 부분을 얻습니다.

편집 :이 테이블에는 UserID라는 또 다른 열이 있으므로 30.000 명의 사용자가 있고 그중 일부는이 테이블에 점수가 있습니다. 각 사용자에 대해 지난 30 일 점수가 필요하기 때문에 날짜가 30 일 미만이면 매일 날짜를 삭제합니다. 그 이유는 지난 30 일 동안의 사용자 활동 그래프를 만들고 차트를 그리려면 쉼표로 구분 된 30 개의 값이 필요하기 때문입니다. 따라서 쿼리에서 USERID = 10203 활동을 가져 오면 쿼리는 지난 30 일 동안 각각 하나씩 30 개의 점수를 얻습니다. 이제 더 명확 해 졌으면합니다.

---

MySQL에는 재귀 기능이 없으므로 NUMBERS 테이블 트릭을 사용해야합니다.

1. 증가하는 숫자 만 포함하는 테이블을 만듭니다. auto_increment를 사용하면 쉽게 할 수 있습니다.

   DROP TABLE IF EXISTS `example`.`numbers`;
   CREATE TABLE  `example`.`numbers` (
     `id` int(10) unsigned NOT NULL auto_increment,
      PRIMARY KEY  (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   

2. 다음을 사용하여 테이블을 채 웁니다.

   INSERT INTO `example`.`numbers`
     ( `id` )
   VALUES
     ( NULL )
   

   ... 필요한만큼 많은 값을 얻을 수 있습니다.

3. DATE_ADD 를 사용 하여 날짜 목록을 구성하고 NUMBERS.id 값에 따라 날짜를 늘립니다. "2010-06-06"및 "2010-06-14"를 각각의 시작 및 종료 날짜로 바꿉니다 (하지만 동일한 형식, YYYY-MM-DD 사용).-

   SELECT `x`.*
     FROM (SELECT DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY)
             FROM `numbers` `n`
            WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` -1 DAY) <= '2010-06-14' ) x
   

4. 시간 부분에 따라 데이터 테이블에 LEFT JOIN :

      SELECT `x`.`ts` AS `timestamp`,
             COALESCE(`y`.`score`, 0) AS `cnt`
        FROM (SELECT DATE_FORMAT(DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY), '%m/%d/%Y') AS `ts`
                FROM `numbers` `n`
               WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY) <= '2010-06-14') x
   LEFT JOIN TABLE `y` ON STR_TO_DATE(`y`.`date`, '%d.%m.%Y') = `x`.`ts`
   

날짜 형식을 유지하려면 DATE_FORMAT 함수를 사용하십시오 .

DATE_FORMAT(`x`.`ts`, '%d.%m.%Y') AS `timestamp`

---

Calendar Table 을 사용하여이 작업을 수행 할 수 있습니다 . 이 테이블은 한 번 만들고 날짜 범위로 채우는 테이블입니다 (예 : 2000-2050 일에 대한 데이터 세트 하나, 데이터에 따라 다름). 그런 다음 달력 테이블에 대해 테이블의 외부 조인을 만들 수 있습니다. 테이블에 날짜가 없으면 점수로 0을 반환합니다.

---

I'm not a fan of the other answers, requiring tables to be created and such. This query does it efficiently without helper tables.

SELECT 
    IF(score IS NULL, 0, score) AS score,
    b.Days AS date
FROM 
    (SELECT a.Days 
    FROM (
        SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Days
        FROM       (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
    ) a
    WHERE a.Days >= curdate() - INTERVAL 30 DAY) b
LEFT JOIN your_table
    ON date = b.Days
ORDER BY b.Days;

So lets dissect this.

SELECT 
    IF(score IS NULL, 0, score) AS score,
    b.Days AS date

The if will detect days that had no score and set them to 0. b.Days is the configured amount of days you chose to get from the current date, up to 1000.

    (SELECT a.Days 
    FROM (
        SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Days
        FROM       (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
    ) a
    WHERE a.Days >= curdate() - INTERVAL 30 DAY) b

This subquery is something I saw on stackoverflow. It efficiently generates a list of the past 1000 days from the current date. The interval (currently 30) in the WHERE clause at the end determines which days are returned; the maximum is 1000. This query could be easily modified to return 100s of years worth of dates, but 1000 should be good for most things.

LEFT JOIN your_table
    ON date = b.Days
ORDER BY b.Days;

This is the part that brings your table that contains the score into it. You compare to the selected date range from the date generator query to be able to fill in 0s where needed (the score will be set to NULL initially, because it is a LEFT JOIN; this is fixed in the select statement). I also order it by the dates, just because. This is preference, you could also order by score.

Before the ORDER BY you could easily join with your table about user info you mentioned with your edit, to add that last requirement.

I hope this version of the query helps someone. Thanks for reading.

---

Michael Conard answer is great but I needed intervals of 15 minutes where the time must always start at the top of every 15th minute:

SELECT a.Days 
FROM (
    SELECT FROM_UNIXTIME( FLOOR( UNIX_TIMESTAMP() / (15 * 60) ) * (15 * 60)) - INTERVAL 15 * (a.a + (10 * b.a) + (100 * c.a)) MINUTE AS Days
    FROM       (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
) a
WHERE a.Days >= curdate() - INTERVAL 30 DAY

This will set the current time to the previous round 15th minute:

FROM_UNIXTIME( FLOOR( UNIX_TIMESTAMP() / (15 * 60) ) * (15 * 60))

And this will remove time with a 15 minute step:

- INTERVAL 15 * (a.a + (10 * b.a) + (100 * c.a)) MINUTE

If there's a simpler way to do it, please let me know.

참고URL : https://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range