데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

PostgreSQL Q&A 5733 게시물 읽기

No. 5733

퀴즈: 이달 생일 맞은 사람 목록 뽑기 :)
작성자	김상기(ioseph)	작성일	2004-12-14 15:21	조회수	4,166

사용자 구조:

사용자id, 생일(월일), 양력인지

userif, birth, issola

ioseph, 0403, 1

guest, 1203, 0

이런식입니다.

양음력변환 테이블도 있다고 하지요.

2005년도 1월달에 생일이 있는 사람들 목록을 뽑아라.

이것이 목표입니다.

한가지 생각하셔야할 것이, 윗 guest 처럼, 12월 03일이 음력일 경우 2005년에 양력 1월에 보면, 그 날짜가 있겠지요.

이 사용자도 함께 나와야하며, 정렬은 날짜 순입니다.

-----

회사 업무로 이 일을 하다가 이게 그리 쉽지 않다는 결론에 이렇게 질문해봅니다.

가장 빨리 문제를 푸는 길, 가장 적은 비용이 소요되는 경우, 가장 보편적인것...

다양한 해결책이 있겠네요.

이 글에 대한 댓글이 총 9건 있습니다.

root=# \d lunar_test
   Table "public.lunar_test"
 Column |   Type   | Modifiers
--------+----------+-----------
 userid | text     |
 birth  | text     |
 issola | smallint |

root=# \d view_lunar_test
  View "public.view_lunar_test"
  Column   |   Type   | Modifiers
-----------+----------+-----------
 userid    | text     |
 birth     | text     |
 issola    | smallint |
 birth2005 | date     |
View definition:
 SELECT lunar_test.userid, lunar_test.birth, lunar_test.issola,
        CASE
            WHEN lunar_test.issola = 1::smallint THEN to_date('2005'::text || lunar_test.birth, 'YYYYMMDD'::text)
            ELSE
            CASE
                WHEN luntosol(to_date('2005'::text || lunar_test.birth, 'YYYYMMDD'::text))::date < '2006-01-01'::date
                    THEN luntosol(to_date('2005'::text || lunar_test.birth, 'YYYYMMDD'::text))::date
                ELSE luntosol(to_date('2004'::text || lunar_test.birth, 'YYYYMMDD'::text))::date
            END
        END AS birth2005
   FROM lunar_test
  ORDER BY 4;

root=# SELECT * from view_lunar_test ;
 userid | birth | issola | birth2005
--------+-------+--------+------------
 guest  | 1203  |      0 | 2005-01-12
 ioseph | 0403  |      1 | 2005-04-03
(2 rows)

root=# SELECT * from view_lunar_test where birth2005 between '2005-01-01'::date and '2005-01-31';
 userid | birth | issola | birth2005
--------+-------+--------+------------
 guest  | 1203  |      0 | 2005-01-12
(1 row)

그냥 SQL문으로.. ㅎㅎ

신기배(소타)님이 2004-12-14 23:17에 작성한 댓글입니다.
이 댓글은 2004-12-14 23:21에 마지막으로 수정되었습니다.

음력일 경우, 1203이 갑자기 0112로 바뀌었네요.

이것을 가져오는 방법이 없군요.

양음력 변환 테이블은

mydb=> \d lunasola
           "lunasola" 테이블
 필드명 |         형태         | 기타 조건
--------+----------------------+-----------
 lunay  | integer              | not null
 lunam  | integer              | not null
 lunad  | integer              | not null
 sola   | date                 | not null
 isyun  | boolean              | not null
 ganji  | character varying(4) | not null
 solas  | character varying(8) |
인덱스들:
    "lunasola_pkey" primary key, btree (sola)
    "lunasola_solas_i" btree (solas)

자료는

paolo=> select lunay,lunam,lunad,sola,isyun,solas,ganji from lunasola limit 10;
 lunay | lunam | lunad |    sola    | isyun |  solas   | ganji
-------+-------+-------+------------+-------+----------+-------
  1899 |    12 |     1 | 1900-01-01 | f     | 19000101 | 甲戌
  1899 |    12 |     2 | 1900-01-02 | f     | 19000102 | 乙亥
  1899 |    12 |     3 | 1900-01-03 | f     | 19000103 | 丙子
  1899 |    12 |     4 | 1900-01-04 | f     | 19000104 | 丁丑
  1899 |    12 |     5 | 1900-01-05 | f     | 19000105 | 戊寅
  1899 |    12 |     6 | 1900-01-06 | f     | 19000106 | 己卯
  1899 |    12 |     7 | 1900-01-07 | f     | 19000107 | 庚辰
  1899 |    12 |     8 | 1900-01-08 | f     | 19000108 | 辛巳
  1899 |    12 |     9 | 1900-01-09 | f     | 19000109 | 壬午
  1899 |    12 |    10 | 1900-01-10 | f     | 19000110 | 癸未
(10건 있음)

이런식입니다.

테스트용으로 샘플 자료도 만들어 보았습니다.

mydb=# select * from users;
     id     | birth | issola
------------+-------+--------
 mylove     | 0210  |      0
 cometome   | 0718  |      1
 weare      | 1019  |      0
 holykiss   | 0418  |      0
 pigi       | 0211  |      1
 chikichiki | 1103  |      0
 mamamia    | 1102  |      1
 papa       | 1205  |      1
 abba       | 0801  |      1
 queen      | 0603  |      0
 kingcrab   | 0609  |      1
 zuzu       | 0510  |      1
 bong       | 0717  |      0
 ssong      | 0319  |      1
 k2         | 0927  |      0
 kcc        | 0513  |      0
 sktel      | 1202  |      0
 lgtel      | 0809  |      0
 ktf        | 1006  |      1
 haeo       | 0110  |      1
 haha       | 0915  |      1
 seesea     | 0108  |      0
 seadragon  | 0306  |      0
 asdf       | 0408  |      1

고견을 부탁드립니다.

김상기(ioseph)님이 2004-12-15 00:02에 작성한 댓글입니다.

자료실에 있는 C로 된 양음력 변환하는 눔을 가져다 썼습니다.

회원이 많다면 차라리 매달 임시 테이블을 맹거서 거기에 쿼리 몇개를 조합해서 자동화 해서 이달에 생일인 사람들을 담아두는게 나을거 같습니다 -.-; 아무래도 실시간으로 많은 사람들의 생일을 걸러 내는게 보통 비용이 아닌거 같습니다 =_=;

글구 상기님 위키 디자인이 바껴서.. 로고 새로 맹거봤습니다 ㅎㅎ

http://www.postgresql.or.kr/wiki.php/nonun/gallery

전에 테스트 삼아 올린 이미지 좀 지워주세요 ㅠ_ㅠ;;;;;;;

신기배(소타)님이 2004-12-15 00:34에 작성한 댓글입니다.
이 댓글은 2004-12-15 00:35에 마지막으로 수정되었습니다.

pgsql=# select * from lunasola ;
 lunay | lunam | lunad |    sola    | isyun |  solas
-------+-------+-------+------------+-------+----------
  2004 |    11 |    21 | 2005-01-01 | f     | 20050101
  2004 |    11 |    22 | 2005-01-02 | f     | 20050102
  2004 |    11 |    23 | 2005-01-03 | f     | 20050103
  2004 |    11 |    24 | 2005-01-04 | f     | 20050104
  2004 |    11 |    25 | 2005-01-05 | f     | 20050105
  2004 |    11 |    26 | 2005-01-06 | f     | 20050106
  2004 |    11 |    27 | 2005-01-07 | f     | 20050107
  2004 |    11 |    28 | 2005-01-08 | f     | 20050108
  2004 |    11 |    29 | 2005-01-09 | f     | 20050109
  2004 |    12 |    22 | 2005-01-31 | f     | 20050131
(10 rows)
Time: 0.930 ms

pgsql=# select * from users ;
  userid  | birth | issola
----------+-------+--------
 mylove   | 0101  |      1
 cometome | 0103  |      1
 wear     | 0108  |      1
 haha     | 1121  |      1
 hehe     | 1221  |      1
 zuzu     | 1128  |      0
 zuzu2    | 1125  |      0
 zkes     | 1225  |      0
 hoho     | 0102  |      0
 a        | 1204  |      0
 aa       | 0124  |      1
(11 rows)
Time: 0.617 ms

pgsql=# SELECT * FROM users
pgsql-# WHERE (birth BETWEEN '0101' AND '0131' AND issola = '1') OR
pgsql-#       (birth BETWEEN
pgsql(#     (select substr(replace(date('2004'||lunam||lunad),'-',''),5,4) from lunasola where solas = '20050101')
pgsql(#             AND
pgsql(#     (select substr(replace(date('2004'||lunam||lunad),'-',''),5,4) from lunasola where solas = '20050131')
pgsql(#       AND issola = '0')
pgsql-# ORDER BY birth;
  userid  | birth | issola
----------+-------+--------
 mylove   | 0101  |      1
 cometome | 0103  |      1
 wear     | 0108  |      1
 aa       | 0124  |      1
 zuzu2    | 1125  |      0
 zuzu     | 1128  |      0
 a        | 1204  |      0
(7 rows)
Time: 2.418 ms
pgsql=#

음.. 이렇게 해도 나오는것 같은데요...
이게 원하시는게 맞는지는 모르겠네요.. 잘이해가 안되어서..
그리고 birth 에 년도가 없는데 어찌 2005년도 생일자를 찾을수
있는지도 모르겠고..
자료가 모두 2004년 자료라고 가정하면 모르겠지만 말이죠..

그리고 양음력변환 테이블도 따로 있는데,
필요해서 만들어 두신거겠지만, 기배님처럼 양음력변환 함수를 쓰면
더 간단하게 될 것 같지요?

또, 양음력변환 테이블에 해당 날짜의 양음력 변환 정보가 없으면
위의 쿼리로는 찾아지지 않습니다 큭.. (이래서 함수를..;;)

테스트용으로 몇줄만 넣어서 해본거라
성능은 전혀 고려하지 못했습니다 :-)

조언 주세요 ^^

장현성(siche)님이 2004-12-15 01:41에 작성한 댓글입니다.
이 댓글은 2004-12-15 01:51에 마지막으로 수정되었습니다.

자료실에 있는 양음력 변환 함수는 버그가 많습니다. 실무에서 쓰기 힘들 정도입니다.

그래서, 나도는 1900년부터 2200년까지의 양음력, 변환 테이블을 씁니다.

한편, 년도가 빠져있는 것은 그 사람의 나이랑 상관 없이 이달 생일인 사람들을 뽑기 위함입니다.

이달 생일은 사람은

양력 생일이, 12월 1일부터 12월 31일까지 하고,

음력 생일이, 10월 19일부터 11월 20일까지

사용자를 의미합니다. 그 사용자의 생년에 상관없이.

즉, 범용적인 문제라면, 특정년, 특정달 생일인 사용자들의 목록이 되는 샘이지요.

여기서 현성님의 between 구문의 서브쿼리를 사용할 경우, 음력의 시작, 끝값이 이상해 집니다.

between '1203' and '0125' 이런식의 값이 사용될 수도 있습니다.

문제가 그리 안쉽지요?

김상기(ioseph)님이 2004-12-15 02:16에 작성한 댓글입니다.

SELECT * FROM users
WHERE (birth BETWEEN '0201' AND '0228' AND issola = '1') OR
CASE WHEN (SELECT CASE WHEN lunam < 10 THEN '0'||lunam::text ELSE lunam::text END||lunad 
           FROM lunasola WHERE solas = '20050201')
          >
          (SELECT CASE WHEN lunam < 10 THEN '0'||lunam::text ELSE lunam::text END||lunad 
           FROM lunasola WHERE solas = '20050228')
     THEN
          (
           (birth BETWEEN (SELECT CASE WHEN lunam < 10 THEN '0'||lunam::text ELSE lunam::text END||lunad 
                           FROM lunasola WHERE solas = '20050201')
                     AND '1231')
                 OR
           (birth BETWEEN '0101' 
                     AND (SELECT CASE WHEN lunam < 10 THEN '0'||lunam::text ELSE lunam::text END||lunad 
                          FROM lunasola WHERE solas = '20050228'))
                 AND 
          issola = '0')
     ELSE
          (birth BETWEEN
          (SELECT CASE WHEN lunam < 10 THEN '0'||lunam::text ELSE lunam::text END||lunad 
           FROM lunasola WHERE solas = '20050201')
                 AND
          (SELECT CASE WHEN lunam < 10 THEN '0'||lunam::text ELSE lunam::text END||lunad 
           FROM lunasola WHERE solas = '20050228')
          AND issola = '0')
     END
ORDER BY birth;

흠.. 그런 문제가 있군요..
그래서 이렇게 바꿔봤습니다..
너무 길어서 어떻게 정렬을 해야 할지 모르겠더군요..쩝

위에꺼에서 바뀐점은
조건문으로 between '1220' and '0119' 같은 상황이 나올때
쿼리를 바꿔주는것 하고,

lunasola 테이블에 lunam 가 int 형이라서
1-9월까지는 01,02 이런식이 안되어서 앞에 0붙여준거..
정도??

between 에서 '1220' and '0119' 같은 상황일때가
2005년도에서 2월달에 나오길래 2월달 자료로 해봤습니다.

이번엔 또 어떤점이 문제일지...
점점 배보다 배꼽이 더커지고 있는듯한 느낌이...

ps. 수정사항!! 헉.. substr(replace(date <<이건 왜쓴거지?? @_@..
열심히 날짜형 만들어서 다시 지우고 있었군요.. 삭제했습니다 -0-

장현성(siche)님이 2004-12-15 11:15에 작성한 댓글입니다.
이 댓글은 2004-12-15 12:54에 마지막으로 수정되었습니다.

정렬할 때 필드 이름이 길거나 하면 ^^;

order by 4 처럼 필드의 순서 번호를 입력하면 됩니다.

group by 도 마찬가지로요. group by 1,2 order by 3 이런식으로 쓰면 됩니다~

신기배(소타)님이 2004-12-15 12:40에 작성한 댓글입니다.

아~ 제가 말한 길어서 정렬이 힘들다는 말이

쿼리의 order by를 말한게 아니구요 ^^

위에 sql 문이 옆으로 너무 길어서

보기좋게 나누어야 하는데 어떻게 해야

잘 보이는지 그 정렬을 말한것이었습니다 :-)

장현성(siche)님이 2004-12-15 12:43에 작성한 댓글입니다.

아 그러쿤여 ^^; 제가 쓸데없이 아는척을 -.-;;;;

저도 쿼리 플랜 올릴때 가로로 쫘악 스크롤이 생겨서 중간에 이쁘게 줄바꿈 해주니라고 ㅋㅋ

신기배(소타)님이 2004-12-15 14:54에 작성한 댓글입니다.

[Top]

No.	제목	작성자	작성일	조회
5736	win32 LANG 값 설정 방법은? [11]	김상기	2004-12-16	7552
5735	일본어 지원 DB 관련되어서 더 질문 드립니다. [6]	최윤진	2004-12-14	3553
5734	테이블이 없어지는 현상 [2]	채희범	2004-12-14	2524
5733	퀴즈: 이달 생일 맞은 사람 목록 뽑기 :) [9]	김상기	2004-12-14	4166
5730	일본어지원 디비를 생성하고자 합니다. [2]	최윤진	2004-12-11	2672
5729	DB 복구후 시퀀스.. [3]	장현성	2004-12-10	2823
5728	향상된 wal의 작동원리 [1]	wal	2004-12-10	3708