쿼리를 작성하다 조건을 여러개 걸어야 할 경우가 발생하였다.

다음과 같이 조건을 여러개 처리하기 위한 사전조건이 주어지고 해당 리스트를 뽑아야한다면

어떤방법으로 뽑을수 있을지 생각해보았다.



사전조건 


1.    a.cmpn_type_cd in('101','102','103') 캠페인유형이 이벤트1, 이벤트2, 이벤트3 가져와야함.


2.    이벤트3의 추첨유형은 c.draw_way_cd = '30' 인 랜덤추첨인것만 뽑아야함.


3.    c.draw_way_cd 는 10(추첨안함), 20(바로추첨), 30(랜덤추첨), null 등의 값이 들어감.


결론: 


이벤트1, 이벤트2는 모든추첨유형의 리스트를 가져와야하고 이벤트3은 랜덤추첨유형의 리스트를 가져와야함.




1. where case문 사용하기




1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- where 이후 case문을 사용한 처리
SELECT a.cmpn_no         -- 캠페인번호
     , a.cmpn_type_cd    -- 캠페인유형
     , a.cmpn_nm        -- 이벤트명
     , c.draw_way_cd    -- 추첨유형
  FROM cmpn_table a 
 inner join evnt_dtl c    -- 이벤트 내역
    on a.cmpn_no = c.cmpn_no
 WHERE 1=1
   and a.cmpn_type_cd in ('101', '102','103')
   -- 캠페인유형이 이벤트3일경우 추첨유형값이 랜덤추첨(30)인 경우만 뽑고
   -- 캠페인유형이 이벤트3이 아닐경우 모두 30으로 빠지므로 조건에 걸리지 않음.
   and '30' = case when a.cmpn_type_cd = '103' then c.draw_way_cd else '30' end
;
cs


2. or문 사용하기



1
2
3
4
5
6
7
8
9
10
11
12
-- where 이후 or을 사용해 대괄호로 묶어 조건걸기
SELECT a.cmpn_no         -- 캠페인번호
     , a.cmpn_type_cd    -- 캠페인유형
     , a.cmpn_nm        -- 이벤트명
     , c.draw_way_cd    -- 추첨유형
  FROM cmpn_table a         -- 캠페인테이블
 inner join evnt_dtl c    -- 이벤트 내역
    on a.cmpn_no = c.cmpn_no
 WHERE 1=1
  -- 전체 대괄호를 사용해 이벤트1, 이벤트2 리스트를 뽑고 이벤트3은 랜덤추첨유형인것으로 뽑기
   and (a.cmpn_type_cd in ('101', '102') or (a.cmpn_type_cd = 103 and c.draw_way_cd = '30')) -- or 합집합, and 교집합
;
cs


다음과 같이 쿼리를 사용하여 where 조건절 안에서 

이벤트1(모든유형), 이벤트2,(모든유형), 이벤트3(랜덤추첨유형) 의 리스트를 가져오는것을 확인할수 있었다.




'전체 > MySQL' 카테고리의 다른 글

ANSI Syntax and Conventional syntax(non-ANSI) 안시조인 사용 이유  (0) 2018.10.29
group by 이해하기  (0) 2018.10.24
쿼리에서 select해서 update하기, 쿼리에서 select한 값을 list로 받아와서 update하기  (0) 2018.06.07
MySQL 부등호 비교, 만 나이 구하는 쿼리  (0) 2018.05.15
MySQL 테이블 컬럼 커멘트 보기  (0) 2018.04.30



1. 쿼리에서 select 한 값을 List(5만건)로 받아와서 foreach로 update 하기


foreach 방식일경우


1. 같은 행사번호에 10만원~20만원 사이 구매한 고객이 5만명이고 이 5만명이 각각 다른 응모자참여번호가 들어갈때

2. 응모자 5만명을 모두 당첨처리를 하기 위해선

3. 응모자참여번호 5만명을 SELECT 하여 Java에 List로 받아오고

4. List에 저장된 5만개의 응모자참여번호를 foreach를 통해 update를 5만번 실시함.

5. 같은 쿼리를 5만번이나 실행하니 비효율적이고 실행시간이 매우 오래걸리는 단점



1
2
3
4
5
//  1. 응모자참여번호 5만명을 SELECT 하여 Java에 List로 받아온다.
 
List<PrEvntTkpt> prEvntTkptList = commonEventsReadOnlyDAO.selectWinnerEvntTkptList( param);
 
//  prEvntTkptList에는 5만건의 데이터 가지고 있음.
cs


1
2
3
4
5
6
7
8
9
10
-- 2. 응모자 5만명을 모두 당첨처리를 하기 위해선
-- 5만명에 대한 각각의 응모자참여번호를 쿼리로 구해오고
-- <id= selectWinnerEvntTkptList>
 SELECT a.EVNT_PTCP_NO -- 이벤트 참여 번호
   FROM pr_evnt_ptcp_ord a -- 응모참여 주문테이블
  WHERE a.CMPN_NO = '1'
  GROUP BY a.EVNT_PTCP_NO, a.CMPN_NO
 HAVING SUM(a.PUR_RCGN_AMT) >= 100000
    AND SUM(a.PUR_RCGN_AMT) < 200000 
-- result return rows  50000
cs


1
2
3
4
5
6
7
8
9
10
11
// 3. foreach를 통해 List길이만큼 update를 5만번 실시함.
for( PrEvntTkpt prEvntTkpt : prEvntTkptList) { 
 
    data = new PrEvntTkpt();
    data.setEvntPtcpNo(prEvntTkpt.getEvntPtcpNo()); // 1 ~ 50000
    data.setModrId(param.getModrId()); // shlee0882
    data.setEvntTkptStatCd("10");    //당첨확정
 
    // 5만건 반복하며 update 실시
    result += commonEventsReadWriteDAO.updateEventWin( data );
}    
cs


1
2
3
4
5
6
7
-- 4. 아래의 같은 update 쿼리를 5만번이나 실행하니 비효율적이고 실행시간이 매우 오래걸리는 단점이 생김.
-- <id= updateEventWin>
    UPDATE pr_evnt_tkpt a
       SET EVNT_TKPT_STAT_CD = #{evntTkptStatCd} -- 10
         , MODR_ID = 'shlee0882'
     WHERE a.CMPN_NO = '2'
       and evnt_ptcp_no = #{evntPtcpNo} -- 1~50000 업데이트문 반복
cs



2.  쿼리에서 select 한 값(5만건)을 한번에 바로 update하기


1. 같은 행사번호에 10만원~20만원 사이 구매한 고객이 5만명이고 이 5만명이 각각 다른 응모자참여번호가 들어갈때

2. 응모자 5만명을 모두 당첨처리를 하기 위해선

3. 5만명에 대한 각각의 응모자참여번호를 서브쿼리로 구해와 

4. 응모자참여테이블의 응모자참여번호에 해당하는 당첨상태 코드를 update 시킨다.

5. select한 값을 한번의 쿼리 수행을 통해 5만건 모두 update 시키고 있다.

6. select한 값을 update치면서 DB Lock이 걸릴수 있다.



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 1. 같은 행사번호에 10만원~20만원 사이 구매한 고객이 5만명이고 이 5만명이 각각 다른 응모자참여번호가 들어갈때
-- 2. 응모자 5만명을 모두 당첨처리를 하기 위해선
-- 3. 5만명에 대한 각각의 응모자참여번호를 서브쿼리로 구해와 
-- 4. 응모자참여테이블의 응모자참여번호에 해당하는 당첨상태 코드를 update 시킨다.    
-- 5. select한 값을 한번의 쿼리를 수행을 통해 5만건 모두 update 시키고 있다.
UPDATE  pr_evnt_tkpt a -- 응모참여 테이블
         INNER JOIN (SELECT b.EVNT_PTCP_NO
                          , SUM(b.PUR_RCGN_AMT) as PUR_RCGN_AMT
                       FROM pr_evnt_ptcp_ord b -- 응모참여 주문테이블
                      WHERE b.CMPN_NO = '1'
                      GROUP BY b.EVNT_PTCP_NO, b.CMPN_NO
                     HAVING SUM(b.PUR_RCGN_AMT) >= 100000
                        AND SUM(b.PUR_RCGN_AMT) < 200000 
         ) b
            ON a.EVNT_PTCP_NO = b.EVNT_PTCP_NO
           SET a.EVNT_TKPT_STAT_CD = '10'        -- 당첨확정
             , a.MODR_ID = 'shlee0882'
         WHERE a.CMPN_NO = '2'
cs


'전체 > MySQL' 카테고리의 다른 글

ANSI Syntax and Conventional syntax(non-ANSI) 안시조인 사용 이유  (0) 2018.10.29
group by 이해하기  (0) 2018.10.24
where case문, or문 사용하기  (1) 2018.06.20
MySQL 부등호 비교, 만 나이 구하는 쿼리  (0) 2018.05.15
MySQL 테이블 컬럼 커멘트 보기  (0) 2018.04.30



1. MySQL 만 나이 구하는 쿼리




1
2
3
4
5
6
7
8
9
10
11
12
13
14
 -- 만 나이 구하기
select 2 > 1 as compareNum1 -- true return 1 
     , 2 > 3 as compareNum2 -- false return 0
     , a.birthdt -- 생년 월일은 19900804
     , year(a.birthdt) as birthYY -- 생년 구하기 1990
     , date_format(a.birthdt, '%m%d') as birthMMDD -- 생일 월일 구하기 0804
     , date_format(now(), '%m%d') as nowMMDD -- 현재 월일 구하기 0515
     , year(now()) - year(a.birthdt) as unconfirmedAge -- 생일이 지났는지 확인하지 않는 나이 28 
     , (date_format(a.birthdt, '%m%d') > date_format(now(), '%m%d')) as calPassBirth -- 1
    --  생일월일 > 현재월일 true 1 false 0 true일경우 생일 안지난것. false일 경우 생일 지난것.
     , (year(now()) - year(a.birthdt)) - (date_format(a.birthdt, '%m%d') > date_format(now(), '%m%d')) as confirmedAge -- 만 나이 27       
  from pr_evnt_win a
 where a.cmpn_no = '20335' -- 조건1
   and a.evnt_tkpt_stat_cd IN ('175', '180', '190', '200') -- -- 조건2
cs



결과


 compareNum1

 compareNum2

 birthdt

 birthYY

 birthMMDD

 nowMMDD

 unconfirmedAge

 calPassBirth

 confirmedAge

 1

 0

 19900804

 1990

 0804

 0515

 28

 1

 27




'전체 > MySQL' 카테고리의 다른 글

ANSI Syntax and Conventional syntax(non-ANSI) 안시조인 사용 이유  (0) 2018.10.29
group by 이해하기  (0) 2018.10.24
where case문, or문 사용하기  (1) 2018.06.20
쿼리에서 select해서 update하기, 쿼리에서 select한 값을 list로 받아와서 update하기  (0) 2018.06.07
MySQL 테이블 컬럼 커멘트 보기  (0) 2018.04.30



1. MySQL 테이블 컬럼 커멘트 보기



1
 SHOW FULL COLUMNS FROM 테이블명;
cs


'전체 > MySQL' 카테고리의 다른 글

ANSI Syntax and Conventional syntax(non-ANSI) 안시조인 사용 이유  (0) 2018.10.29
group by 이해하기  (0) 2018.10.24
where case문, or문 사용하기  (1) 2018.06.20
쿼리에서 select해서 update하기, 쿼리에서 select한 값을 list로 받아와서 update하기  (0) 2018.06.07
MySQL 부등호 비교, 만 나이 구하는 쿼리  (0) 2018.05.15

+ Recent posts

티스토리툴바