source

(mysql 또는 펄엔드에서) sql 결과의 빈 날짜를 패드하는 가장 간단한 방법은 무엇입니까?

lovecheck 2023. 9. 14. 23:20
반응형

(mysql 또는 펄엔드에서) sql 결과의 빈 날짜를 패드하는 가장 간단한 방법은 무엇입니까?

다음과 같은 쿼리를 사용하여 mysql 테이블에서 빠른 csv를 만들고 있습니다.

select DATE(date),count(date) from table group by DATE(date) order by date asc;

파일에 파일을 퍼렐 단위로 버리면 다음과 같습니다.

while(my($date,$sum) = $sth->fetchrow) {
    print CSV "$date,$sum\n"
}

데이터에 날짜 차이가 있지만 다음과 같습니다.

| 2008-08-05 |           4 | 
| 2008-08-07 |          23 |

누락된 날짜를 제로 카운트 항목으로 채우기 위해 데이터를 패드에 넣고 싶습니다.

| 2008-08-05 |           4 | 
| 2008-08-06 |           0 | 
| 2008-08-07 |          23 |

저는 한 달에 몇 일씩 배열된 수학과 함께 정말 어색한(그리고 거의 확실히 버그가 있는) 해결책을 모았지만, mysql이나 perl 쪽에 더 간단한 것이 있어야 합니다.

내가 왜 그렇게 멍청한지에 대한 천재적인 아이디어가 있습니까?

다음과 같은 몇 가지 이유로 문제의 날짜 범위에 대한 임시 테이블을 생성하는 저장 프로시저를 사용하게 되었습니다.

  • 나는 내가 매번 찾을 날짜 범위를 알고 있습니다.
  • 문제의 서버는 유감스럽게도 atm에 perl 모듈을 설치할 수 있는 서버가 아니었고 원격으로 아무것도 설치되어 있지 않을 정도로 상태가 노후화되어 있었습니다. Date::-y.

perl Date/DateTime 반복 답변도 매우 좋았습니다, 여러 답변을 선택할 수 있었으면 좋겠습니다!

서버 쪽에서 그런 것이 필요할 때는 보통 두 시점 사이에 가능한 모든 날짜를 포함하는 테이블을 만든 다음 왼쪽에서 이 테이블에 쿼리 결과를 포함합니다.이와 같은 것:

create procedure sp1(d1 date, d2 date)
  declare d datetime;

  create temporary table foo (d date not null);

  set d = d1
  while d <= d2 do
    insert into foo (d) values (d)
    set d = date_add(d, interval 1 day)
  end while

  select foo.d, count(date)
  from foo left join table on foo.d = table.date
  group by foo.d order by foo.d asc;

  drop temporary table foo;
end procedure

이 경우에는 고객측에서 약간의 체크를 하는 것이 좋을 것 같습니다, 현재 날짜가 previos+1이 아니면 추가 문자열을 넣으십시오.

이 문제를 해결해야 할 때, 누락된 날짜를 채우기 위해 실제로 관심 있는 모든 날짜가 포함된 참조 테이블을 만들고 날짜 필드의 데이터 테이블에 참여했습니다.조잡하지만 효과는 있습니다.

SELECT DATE(r.date),count(d.date) 
FROM dates AS r 
LEFT JOIN table AS d ON d.date = r.date 
GROUP BY DATE(r.date) 
ORDER BY r.date ASC;

출력은 CSV를 수작업으로 생성하는 대신 SELECT INTO OUTFILE만 사용하겠습니다.특수 캐릭터 탈출에 대한 걱정도 덜어줍니다.

바보가 아닙니다. MySQL에서 하는 일이 아닙니다. 빈 날짜 값을 삽입하는 것입니다.저는 2단계 프로세스로 펄로 이 작업을 합니다.먼저 쿼리의 모든 데이터를 날짜별로 정리된 해시에 로드합니다.그런 다음 Date::EzDate 개체를 생성하고 일 단위로 증분하므로...

my $current_date = Date::EzDate->new();
$current_date->{'default'} = '{YEAR}-{MONTH NUMBER BASE 1}-{DAY OF MONTH}';
while ($current_date <= $final_date)
{
    print "$current_date\t|\t%hash_o_data{$current_date}";  # EzDate provides for     automatic stringification in the format specfied in 'default'
    $current_date++;
}

여기서 final date는 다른 EzDate 개체 또는 날짜 범위의 끝을 포함하는 문자열입니다.

EzDate는 현재 CPAN에 없지만 날짜 비교를 수행하고 날짜 증분을 제공하는 다른 Perl 모드를 찾을 수 있습니다.

DateTime 개체를 사용할 수 있습니다.

use DateTime;
my $dt;

while ( my ($date, $sum) = $sth->fetchrow )  {
    if (defined $dt) {
        print CSV $dt->ymd . ",0\n" while $dt->add(days => 1)->ymd lt $date;
    }
    else {
        my ($y, $m, $d) = split /-/, $date;
        $dt = DateTime->new(year => $y, month => $m, day => $d);
    }
    print CSV, "$date,$sum\n";
}

의 가 은 이 된 를 하는 입니다 입니다 하는 를 의 가 된 DateTime건$dt이 될 때, 합니다, 에 가 합니다 1 이 합니다 이 에 가 $dt하루 단위로 (에 한 줄 인쇄)CSV) 현재 날짜와 동일할 때까지

이렇게 하면 별도의 테이블이 필요 없고, 모든 행을 미리 가져올 필요가 없습니다.

저는 당신이 나머지를 파악하기를 바랍니다.

select  * from (
select date_add('2003-01-01 00:00:00.000', INTERVAL n5.num*10000+n4.num*1000+n3.num*100+n2.num*10+n1.num DAY ) as date from
(select 0 as num
   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) n1,
(select 0 as num
   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) n2,
(select 0 as num
   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) n3,
(select 0 as num
   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) n4,
(select 0 as num
   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) n5
) a
where date >'2011-01-02 00:00:00.000' and date < NOW()
order by date

와 함께

select n3.num*100+n2.num*10+n1.num as date

0에서 최대(n3)까지의 숫자로 구성된 열이 나타납니다.*100+max(n2)*10+max(n1)

여기는 max n3을 3으로 가지고 있기 때문에 SELECT는 399와 0 -> 400개의 레코드(달력상 날짜)를 반환합니다.

동적 일정관리는 min(날짜)부터 제한하여 조정할 수 있습니다.

공백 위치를 알 수 없지만 목록의 첫 번째 날짜부터 마지막 날짜까지의 모든 값(아마도)을 원하므로 다음과 같은 작업을 수행합니다.

use DateTime;
use DateTime::Format::Strptime;
my @row = $sth->fetchrow;
my $countdate = strptime("%Y-%m-%d", $firstrow[0]);
my $thisdate = strptime("%Y-%m-%d", $firstrow[0]);

while ($countdate) {
  # keep looping countdate until it hits the next db row date
  if(DateTime->compare($countdate, $thisdate) == -1) {
    # counter not reached next date yet
    print CSV $countdate->ymd . ",0\n";
    $countdate = $countdate->add( days => 1 );
    $next;
  }

  # countdate is equal to next row's date, so print that instead
  print CSV $thisdate->ymd . ",$row[1]\n";

  # increase both
  @row = $sth->fetchrow;
  $thisdate = strptime("%Y-%m-%d", $firstrow[0]);
  $countdate = $countdate->add( days => 1 );
}

음, 생각보다 복잡한 것으로 드러났네요.말이 되었으면 좋겠습니다!

문제에 대한 가장 간단한 일반적인 해결책은Ordinal필요한 행 수가 가장 많은 표(31*3 = 93인 경우).

CREATE TABLE IF NOT EXISTS `Ordinal` (
  `n` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`n`)
);
INSERT INTO `Ordinal` (`n`)
VALUES (NULL), (NULL), (NULL); #etc

그다음에 에를 .LEFT JOINOrdinal데이터를 저장합니다.간단한 사례가 있습니다. 지난 한 주 동안 매일같이.

SELECT CURDATE() - INTERVAL `n` DAY AS `day`
FROM `Ordinal` WHERE `n` <= 7
ORDER BY `n` ASC

이와 관련하여 변경해야 할 두 가지 사항은 시작점과 간격입니다.저는했습니다를 .SET @var = 'value'명료함을 위한 구문

SET @end = CURDATE() - INTERVAL DAY(CURDATE()) DAY;
SET @begin = @end - INTERVAL 3 MONTH;
SET @period = DATEDIFF(@end, @begin);

SELECT @begin + INTERVAL (`n` + 1) DAY AS `date`
FROM `Ordinal` WHERE `n` < @period
ORDER BY `n` ASC;

따라서 지난 3개월 동안 하루 메시지 수를 얻기 위해 가입할 경우 최종 코드는 다음과 같습니다.

SELECT COUNT(`msg`.`id`) AS `message_count`, `ord`.`date` FROM (
    SELECT ((CURDATE() - INTERVAL DAY(CURDATE()) DAY) - INTERVAL 3 MONTH) + INTERVAL (`n` + 1) DAY AS `date`
    FROM `Ordinal`
    WHERE `n` < (DATEDIFF((CURDATE() - INTERVAL DAY(CURDATE()) DAY), ((CURDATE() - INTERVAL DAY(CURDATE()) DAY) - INTERVAL 3 MONTH)))
    ORDER BY `n` ASC
) AS `ord`
LEFT JOIN `Message` AS `msg`
  ON `ord`.`date` = `msg`.`date`
GROUP BY `ord`.`date`

팁 및 코멘트:

  • 은 에서 은 할 할 를 입니다 하는 이었을 의 입니다 이었을 하는 를 할 의 에서 할 Ordinal비해 쉬웠습니다. 이에 비해 정수 시퀀스를 날짜로 변환하는 것은 쉬웠습니다.
  • 을 사용할 수 .Ordinal모든 중단 없는 시퀀스 요구사항을 충족할 수 있습니다.가장 긴 시퀀스보다 더 많은 행을 포함해야 합니다.
  • 를 할 의 에서 여러 쿼리를 사용할 수 .Ordinal예를 들어, 지난 7주 동안 매주 평일(1-5)에 나열하는 것과 같이, 다중 시퀀스의 경우.
  • 당신은 당신의 컴퓨터에 날짜를 저장함으로써 그것을 더 빠르게 만들 수 있습니다.Ordinal테이블, 하지만 유연성이 떨어집니다.이런 식으로 당신은 오직 하나만 필요합니다.Ordinal테이블, 당신이 그것을 아무리 많이 사용해도.그래도, 속도가 가치가 있다면, 시도해보세요.INSERT INTO ... SELECT통사론

권장 DateTime 또는 Time:과 같은 Perl 모듈을 사용하여 날짜 계산을 수행합니다.조각(5.10부터 코어).날짜 및 인쇄 날짜만 증분하고 날짜까지 0이면 현재와 일치합니다.

이 방법이 효과가 있을지는 모르겠지만 가능한 모든 날짜가 포함된 새 테이블을 만든 후(이 아이디어의 문제일 수 있습니다. 날짜 범위가 예상치 못하게 변경될 경우...) 두 테이블에 왼쪽 조인을 수행하면 어떨까요?가능한 날짜가 방대하거나, 처음과 마지막 날짜를 예측할 방법이 없다면 말도 안 되는 해결책이라고 생각합니다만, 날짜의 범위가 고정되어 있거나, 쉽게 해결될 수 있다면, 이 방법은 효과가 있을 것입니다.

언급URL : https://stackoverflow.com/questions/75752/what-is-the-most-straightforward-way-to-pad-empty-dates-in-sql-results-on-eithe

반응형

'source' 카테고리의 다른 글

워드프레스 블로그에서 MS SQL에서 외부 데이터를 검색하려면 어떻게 해야 합니까?  (0) 2023.09.14
자바스크립트 아이드롭퍼(마우스 커서 아래 픽셀 색상 표시)  (0) 2023.09.14
Wordpress 플러그인이 두 번 설치됨  (0) 2023.09.14
통화 최적화를 위한 최적화  (0) 2023.09.14
API 응답에서 응답 헤더 읽기 - Angular 5 + TypeScript  (0) 2023.09.14

'source'의 다른글

  • 현재글(mysql 또는 펄엔드에서) sql 결과의 빈 날짜를 패드하는 가장 간단한 방법은 무엇입니까?

관련글

댓글

티스토리툴바