MySQL 범위 내에서 누락된 날짜를 채우려면 어떻게 해야 합니까?

MySQL 범위 내에서 누락된 날짜를 채우려면 어떻게 해야 합니까?

날짜, 점수 두 칸으로 된 테이블이 있습니다.지난 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"를 시작일과 종료일로 바꿉니다(단, 동일한 형식, YYY-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. 시간 부분에 따라 데이터 테이블에 왼쪽 참여:

      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`

저는 다른 답변은 좋아하지 않습니다.테이블 작성 등을 요구합니다.이 쿼리는 도우미 테이블을 사용하지 않고 효율적으로 수행합니다.

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;

그럼 이걸 해부해 봅시다

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

if는 점수가 없는 날짜를 검출하여 0.b로 설정합니다.일수는 현재 날짜로부터 취득하도록 선택한 최대 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

이 서브쿼리는 스택오버플로우에서 본 것입니다.현재 날짜로부터 지난 1000일 목록을 효율적으로 생성합니다.WHERE 절의 마지막 간격(현재 30)에 따라 반환되는 날짜가 결정됩니다.최대값은 1000입니다.이 쿼리는 100년치 날짜를 반환하도록 쉽게 수정할 수 있지만 대부분의 경우 1000이 좋습니다.

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

이 부분이 점수가 포함된 표를 가져오는 부분입니다.날짜 생성기 쿼리에서 선택한 날짜 범위와 비교하여 필요한 경우 0을 입력할 수 있습니다(점수는 로 설정됩니다).NULL첫 번째 이유는LEFT JOIN; 이것은 select 스테이트먼트에 고정되어 있습니다).저도 날짜별로 주문합니다.이것은 취향에 따라서는, 스코어로도 주문할 수 있습니다.

그 전에ORDER BY편집 시 언급한 사용자 정보에 대한 표를 쉽게 볼 수 있으므로 마지막 요건을 추가할 수 있습니다.

이 버전의 쿼리가 누군가에게 도움이 되었으면 합니다.읽어주셔서 감사합니다.

이 질문을 받고 나서 시간이 흘렀다.MySQL 8.0은 2018년에 출시되었으며 이 질문에 대한 우아하고 최신 솔루션을 제공하는 재귀적인 공통 테이블 표현식 지원을 추가했습니다.

예를 들어 2010년 8월 첫 15일 동안 날짜 목록을 생성하려면 다음 쿼리를 사용할 수 있습니다.

with recursive all_dates(dt) as (
    -- anchor
    select '2010-08-01' dt
    union all 
    -- recursion with stop condition
    select dt + interval 1 day from all_dates where dt < '2010-08-15'
)
select * from all_dates order by dt

그럼 할 수 있어요left join다음 결과 세트를 테이블과 함께 사용하여 예상 출력을 생성합니다.

with recursive all_dates(dt) as (
    select '2010-08-01' dt
    union all 
    select dt + interval 1 day from all_dates where dt < '2010-08-15'
)
select d.dt date, coalesce(t.score, 0) score
from all_dates d
left join mytable t on t.date = d.dt
order by d.dt

DB Fielen 데모:

date | 점수:--------- | ----:2010-08-01 |    192010-08-02 |    212010-08-03 |     02010-08-04 |    142010-08-05 |     02010-08-06 |     02010-08-07 |    102010-08-08 |     02010-08-09 |     02010-08-10 |    142010-08-11 |     02010-08-12 |     02010-08-13 |     02010-08-14 |     02010-08-15 |     0

---

재귀 CTE를 다른 간격 또는 기간에 맞게 조정하는 것은 매우 쉽습니다.예를 들어 2010년 8월 1일 오전4시부터 8시까지 15분 간격으로 행이 필요하다고 합니다.다음 작업을 수행할 수 있습니다.

with recursive all_dates(dt) as (
    select '2010-08-01 04:00:00' dt
    union all 
    select dt + interval 15 minute from all_dates where dt < '2010-08-01 08:00:00'
)
...

일정관리 표를 사용하여 이 작업을 수행할 수 있습니다.이 테이블은 한 번 만들고 날짜 범위(예: 데이터에 따라 2000-2050일당 데이터 세트 1개)로 채우는 테이블입니다.그런 다음 달력 테이블에 대해 표의 외부 결합을 만들 수 있습니다.테이블에 날짜가 없는 경우 점수에 대해 0을 반환합니다.

Michael Conard의 답변은 훌륭하지만, 저는 15분 간격으로 시간이 항상 15분마다 선두에서 시작되어야 하는 간격이 필요했습니다.

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

그러면 현재 시간이 이전 라운드 15분으로 설정됩니다.

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

15분간의 스텝으로 시간을 절약할 수 있습니다.

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

더 간단한 방법이 있으면 알려주세요.

삽입으로 시작일부터 오늘까지 직접 사용자 지정 가능

        with recursive all_dates(dt) as (
        -- anchor
        select '2021-01-01' dt
            union all 
        -- recursion with stop condition
        INSERT IGNORE  INTO mytable (date,score) VALUES (dt + interval 1 day ,0 )  where dt + interval 1 day <= curdate()
    )
    select * from all_dates

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