with data01 as (
select 1 as user_id, 4.0 as stdRate, 0 as con_Flag
union all
select 2 as user_id, 5.0 as stdRate, 0 as con_Flag
union all
select 3 as user_id, 3.0 as stdRate, 1 as con_Flag
)
, data02 as (
select 1 as data_id, 1 as data_key, 4.5 as dataRate, 'Y' as send_yn
union all
select 2 as data_id, 1 as data_key, 5.5 as dataRate, 'N' as send_yn
union all
select 3 as data_id, 2 as data_key, 3.5 as dataRate, 'Y' as send_yn
union all
select 4 as data_id, 2 as data_key, 5.5 as dataRate, 'N' as send_yn
union all
select 5 as data_id, 2 as data_key, 6.5 as dataRate, 'N' as send_yn
)
, data03 as (
select a.*, b.data_key, min(b.data_id) as min_data_id
, case when b.dataRate >= a.stdRate and b.dataRate < a.stdRate + 1 then '1'
else '0'
end as is_flag
from data01 a
, data02 b
where 1=1
and a.stdRate <= b.dataRate
group by a.user_id, a.stdRate, a.con_Flag, b.data_key
)
select *
from data03 a
, data02 b
where 1=1
and b.send_yn = 'N'
and case when con_Flag = 0 then a.min_data_id = b.data_id
else a.data_key = b.data_key
end
;
data01은 회원조건설정테이블
data02는 발송데이터테이블
1. 기본발송기준
1.1. data01의 stdRate를 기준으로 +1.0 범위까지 발송
2. 추가발송기준
2.1. data01의 +1.0 범위에 없을 경우 stdRate보다 큰 1개 데이터를 발송
2.2. data01의 con_flag가 1인 경우 stdRate보다 큰 모든 데이터를 발송
위와 같이 쿼리하면 원하는 값은 나오는데.. data01이 커지면 커질수록 쿼리가 느려질 것 같습니다. data02는 일자별 자료로 무한정 많아 지지는 않습니다.
좀 더 효율적인 쿼리가 없을지요?
도움 좀 부탁드리겠습니다.
고맙습니다.
|