복붙노트

[SQL] MySQL은 어떻게 범위에없는 날짜를 채우기 위해?

SQL

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의에서 할이 가능하다.

편집 : 나는 30.000 사용자를 가지고 있고, 그들 중 일부는이 표에 점수가 있으므로이 표에서 사용자 ID라는 이름의 다른 열이 있습니다. 나는 30 일 각 사용자에 대해 점수를 지속해야하기 때문에 나는 매일 삼십일 전에 경우 날짜를 <날짜를 삭제합니다. 그 이유는 내가 지난 30 일 동안 사용자 활동의 그래프를 만들고, 그리고 난 쉼표로 구분하여 30 개 개의 값을 필요로 차트를 그릴 것입니다. 내가 쿼리에서 말할 수 그래서 나에게 USERID = 10,203 활동을 얻고 쿼리는 나에게 30 개 점수, 지난 30 일 각각에 대해 하나를 얻을 것입니다. 나는 이제 더 명확 오전 바랍니다.

해결법

  1. ==============================

    1.당신이 NUMBERS 테이블의 트릭을 사용하여 남겨 그래서 MySQL은, 재귀 기능이 없습니다 -

    당신이 NUMBERS 테이블의 트릭을 사용하여 남겨 그래서 MySQL은, 재귀 기능이 없습니다 -

    날짜 형식을 유지하려면 DATE_FORMAT 함수를 사용합니다 :

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

    2.당신은 달력 표를 사용하여이 작업을 수행 할 수 있습니다. (; 데이터에 따라 매일 2,000에서 2,050 사이에 대한 예를 들어, 하나 개의 데이터 세트) 즉, 날짜 범위로 한 번 충전 만들 테이블입니다. 그럼 당신은 외부 달력 테이블에 대해 테이블을 조인 할 수 있습니다. 날짜가 테이블에없는 경우, 당신은 점수 0을 반환합니다.

    당신은 달력 표를 사용하여이 작업을 수행 할 수 있습니다. (; 데이터에 따라 매일 2,000에서 2,050 사이에 대한 예를 들어, 하나 개의 데이터 세트) 즉, 날짜 범위로 한 번 충전 만들 테이블입니다. 그럼 당신은 외부 달력 테이블에 대해 테이블을 조인 할 수 있습니다. 날짜가 테이블에없는 경우, 당신은 점수 0을 반환합니다.

  3. ==============================

    3.나는 테이블 생성 및 될 필요, 다른 답변의 팬이 아니에요. 이 쿼리는 도우미 테이블없이 효율적으로 수행합니다.

    나는 테이블 생성 및 될 필요, 다른 답변의 팬이 아니에요. 이 쿼리는 도우미 테이블없이 효율적으로 수행합니다.

    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

    경우에는 점수를 없었고 0 b.Days로 설정 한 일을 감지 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

    이 하위 쿼리에 유래에 뭔가 I 톱이다. 그것은 효율적으로 현재 날짜로부터 지난 천일의 목록을 생성합니다. 일 반품 된 단부를 판정 WHERE 절에 간격 (현재 30); 최대 값은이 쿼리는 쉽게 날짜의 년 가치가 100 단위를 반환하도록 수정 될 수있는 1000이지만, 1000 대부분의 것들에 대한 좋은해야한다.

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

    이 그것으로 점수가 포함 된 테이블을 가져 오는 부분입니다. 필요한 경우 (점수가 처음 NULL로 설정됩니다 그것은 좌 가입하기 때문에,이 SELECT 문에서 해결) 당신은 0을 기입 할 수 있도록 날짜 발전기 쿼리에서 선택한 기간에 비교합니다. 또한, 때문에 날짜하여 주문한다. 이것은 또한 점수로 주문할 수, 선호합니다.

    당신 BY 순서는 쉽게 당신의 편집으로 언급 한 사용자 정보에 대한 테이블과 조인 할 수 있기 전에, 마지막 요구 사항을 추가 할 수 있습니다.

    나는 쿼리의이 버전은 사람을 도움이되기를 바랍니다. 읽어 주셔서 감사합니다.

  4. ==============================

    4.마이클 Conard의 대답은 훌륭하지만 시간이 항상 매 15 분의 상단에 시작해야 15 분 간격으로 필요 :

    마이클 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

    그것을 할 수있는 간단한 방법이 있으면 알려 주시기 바랍니다.

  5. ==============================

    5.시간이 질문은 질문 이후로 갔다. MySQL의 8.0은 2018 년에 발표하고이 문제를 해결하기 우아한, 최첨단 방법을 제공 재귀 공통 테이블 표현식에 대한 지원이 추가되었습니다.

    시간이 질문은 질문 이후로 갔다. 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 + interval 1 day <= '2010-08-15'
)
select * from all_dates

    그런 다음 왼쪽 예상 출력을 생성하는 테이블에이 결과 집합에 가입 할 수 있습니다 :

    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 + interval 1 day <= '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 바이올린에 데모 :

    date       | score
:--------- | ----:
2010-08-01 |    19
2010-08-02 |    21
2010-08-03 |     0
2010-08-04 |    14
2010-08-05 |     0
2010-08-06 |     0
2010-08-07 |    10
2010-08-08 |     0
2010-08-09 |     0
2010-08-10 |    14
2010-08-11 |     0
2010-08-12 |     0
2010-08-13 |     0
2010-08-14 |     0
2010-08-15 |     0

    6. from https://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 어떻게 SQL에서 임의의 행을 요청하는?  (0) 2020.03.06
[SQL] MySQL은 하나를 제외한 모든 중복 행을 삭제 하시겠습니까? [복제]  (0) 2020.03.06
[SQL] 신원 증가는 SQL Server 데이터베이스에 점프  (0) 2020.03.06
[SQL] 복수와 LINQ에 SQL 조인, 왼쪽 카운트 조인  (0) 2020.03.06
[SQL] 오라클에서 여러 행에서 CONCATENATE 열 값에 SQL 쿼리  (0) 2020.03.06
,

티스토리툴바