본문 바로가기
프로그래밍/DB/DB

[MYSQL] 10년치 날짜 데이터 테이블 만들기, 달력 쿼리 등

by 아유카와 2015. 9. 30.

    DB관련 쿼리는 얼마나 많은 함수를 아느냐와 활용도도 중요하지만 얼마나 많이 찾아보고 과거 히스트리를 잘 응용하느냐도 중요한것 같다. 그러므로 나는 후자로서 활용 하길 원한다. 매일 매일 쿼리를 사용하지 않으면 독창적인 쿼리는 쉽게 나오지 않으므로... 남의 것을 가져다 쓰거나 응용 하는 것이 현재로서 최상 아닐까 싶다.


1. MYSQL의 쿼리쿼리쿼리!!!

    - 10년치의 날짜를 1월 1일부터 12월 31일까지 넣는 방법

create table t (n int);

 
insert into t values (1);
 
insert into t select * from t; -- 이걸 13번 반복하면 4096행이 생성됨. 10년치 데이터라면 대략 3650일이므로 이정도면 충분
 
create table date_t (d date, ds char(8)); -- 날짜를 저장할 테이블
 
insert into date_t
select d, date_format(d, '%Y%m%d') from (
  select @rnum:=@rnum+1 as rownum, date(adddate('2009-01-01', interval @rnum day)) as d
  from (select @rnum:=-1) r, t
  ) t
where year(d) < 2019;
 

참고로, 날짜는 date 타입에 저장하는 것이 yyyymmdd형식의 문자열로 저장하는 것보다 좋습니다.

    

     - 쿼리로 달력을 만들어보자!!( 와우!! )

SELECT dt + INTERVAL lv-1 DAY dt
  FROM (-- connect by level 대신 시스템정보 테이블 columns 를 이용
        SELECT ordinal_position lv
             , CONCAT('201507', '01') dt
          FROM information_schema.columns
         WHERE table_schema = 'mysql'
           AND table_name = 'user'
        ) a
 WHERE lv <= DAY(LAST_DAY(dt))

;


    - 쿼리로 달력을 만들어보자 02

SELECT ym
     , MIN(CASE dw WHEN 1 THEN d END) Sun
     , MIN(CASE dw WHEN 2 THEN d END) Mon
     , MIN(CASE dw WHEN 3 THEN d END) Tue
     , MIN(CASE dw WHEN 4 THEN d END) Wed
     , MIN(CASE dw WHEN 5 THEN d END) Thu
     , MIN(CASE dw WHEN 6 THEN d END) Fri
     , MIN(CASE dw WHEN 7 THEN d END) Sat
  FROM (SELECT date_format(dt,'%Y%m') ym
             , Week(dt) w
             , Day(dt) d
             , DayofWeek(dt) dw
          FROM (SELECT CONCAT(y, '0101') + INTERVAL a*100 + b*10 + c DAY dt
                  FROM (SELECT 0 a
                        UNION ALL SELECT 1
                        UNION ALL SELECT 2
                        UNION ALL SELECT 3
                        ) a
                     , (SELECT 0 b
                        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
                        ) b
                     , (SELECT 0 c
                        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
                        ) c
                     , (SELECT '2015' y) d
                 WHERE a*100 + b*10 + c < DayOfYear(CONCAT(y, '1231'))
                ) a
        ) a
 GROUP BY ym, w

;


    - mysql에서 현재 날짜와 date_format 사용 방법

    - mysql에서 날짜 포맷 변화 date_format 사용 방법