멤버의 유형을 구분하는 쿼리 내지는 프로시져를 고민하고 있습니다. 0 2 952

by 와니와플 [SQL Query] #로열티구분 #멤버구분 [2020.11.30 17:30:26]



이렇게 테이블을 하나 등록해서 아래 excel을 올렸습니다. (create 스크립트 참조)

select * from cb_boarding_list where team = 97; 

이렇게 querying 하면 97팀에 대한 정보가 나옵니다. 여기서 4명의 사람이 나오는데

주어진 속성 정보만으로 이 팀이 가족인지, 연인인지, 동호회인지 구분하려고 합니다.

가족일 경우 F,M이 같이 있어야 하고 (성별 구분), 가장 나이가 많은 사람과 적은 사람의 차이가 20살 이상이면 그렇게 판단하기로 하였습니다.

연인은 F,M이 팀인 경우, 소규모 그룹인 경우 인원이 4명 이상이면 되도록 하려고 합니다.

물론 각각의 쿼리는 그다지 어렵지 않은데, 이것을 한 번에 team이 신규로 insert 될 때마다 쿼리를 한 번에 원쿼리로 하려니 도무지 감이 잡히질 않습니다...

원쿼리가 가능할까요? 혹시 샘플이나 가르침이 있으실 경우 부탁 드립니다! 

 

CREATE TABLE `cb_boarding_list` (
  `idx` int(11) UNSIGNED NOT NULL,                                                    -- 고유번호
  `mem_id` int(11) UNSIGNED NOT NULL DEFAULT '0',                                    -- 선사아이디
  `sdate` char(10) NOT NULL,                                                        -- 운항날짜
  `stime` varchar(4) NOT NULL,                                                        -- 운항시간
  `sname` varchar(255) NOT NULL,                                                    -- 선박명
  `service_id` int(10) UNSIGNED NOT NULL,                                            -- 선박 아이디
  `stype` varchar(10) NOT NULL DEFAULT 'public',                                    -- 운항타입 (퍼블릭투어, 프라이빗투어, 요트스테이등)
  `u_name` varchar(50) NOT NULL,                                                    -- 승선자 명
  `u_jumin` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,        -- 승선자 주민번호 7자리
  `u_birth` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,        -- 승선자 생년월일
  `u_age` int(11) NOT NULL DEFAULT '0',                                                -- 승선자 나이
  `u_sex` char(1) NOT NULL DEFAULT 'M',                                                -- 승선자 성별
  `u_country` varchar(5) NOT NULL,                                                    -- 승선자 국적
  `u_local` varchar(20) NOT NULL,                                                    -- 승선자 거주지
  `team` int(11) NOT NULL DEFAULT '0',                                                -- 예약자 승선신고 고유번호
  `is_order` char(1) NOT NULL DEFAULT 'N',                                            -- 예약자여부
  `wdate` varchar(14) NOT NULL,
  `wip` varchar(30) NOT NULL,
  `udate` varchar(14) NOT NULL,
  `uip` varchar(30) NOT NULL,
  `ddate` varchar(14) NOT NULL,
  `dip` varchar(30) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 

by 마농 [2020.12.01 01:17:57]
SELECT team
     , CASE WHEN cnt_m > 0 AND cnt_f > 0 AND age_max - age_min >= 20 THEN '가족'
            WHEN cnt_m = cnt_f THEN '연인'
            WHEN cnt  =  1 THEN '개인'
            WHEN cnt >= 10 THEN '단체'
            ELSE '기타'
        END team_type
  FROM (SELECT team
             , COUNT(*) cnt
             , COUNT(CASE u_sex WHEN 'M' THEN 1 END) cnt_m
             , COUNT(CASE u_sex WHEN 'F' THEN 1 END) cnt_f
             , MIN(age) age_min
             , MAX(age) age_max
          FROM cb_boarding_list
         GROUP BY team
        ) a
;

 


by 와니와플 [2020.12.01 10:48:36]

와... 정말 대단하십니다...

제가 짠 것은 올리기도 민망하네요...

프로시져를 일일히 짜서 이걸 크론탭에 올려서 관리하기도 애매했는데,,, 

감사 드립니다..

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입