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

MySQL Q&A 31452 게시물 읽기

No. 31452

좀 더 효율적인 쿼리가 없을지요?
작성자	즐겨찾기(roejh94)	작성일	2024-04-05 18:20ⓒ 2024-04-05 18:31ⓜ	조회수	148

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는 일자별 자료로 무한정 많아 지지는 않습니다.

좀 더 효율적인 쿼리가 없을지요?

도움 좀 부탁드리겠습니다.

고맙습니다.

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

WITH data01 AS

(

SELECT 1 user_id, 4.0 stdRate, 0 con_Flag

UNION ALL SELECT 2, 5.0, 0

UNION ALL SELECT 3, 3.0, 1

)

, data02 AS

(

SELECT 1 data_id, 1 data_key, 4.5 dataRate, 'Y' send_yn

UNION ALL SELECT 2, 1, 5.5, 'N'

UNION ALL SELECT 3, 2, 3.5, 'Y'

UNION ALL SELECT 4, 2, 5.5, 'N'

UNION ALL SELECT 5, 2, 6.5, 'N'

)

SELECT *

  FROM (SELECT a.user_id

             , a.stdRate

             , a.con_Flag

             , b.data_id

             , b.send_yn

             , CASE WHEN b.dataRate < a.stdRate + 1 THEN 1 ELSE 0 END flag

             , COUNT(CASE WHEN b.dataRate < a.stdRate + 1 THEN 1 END)

                 OVER(PARTITION BY a.user_id) cnt

             , ROW_NUMBER() OVER(PARTITION BY a.user_id ORDER BY b.data_id) rn

          FROM data01 a

          JOIN data02 b

            ON a.stdRate <= b.dataRate

        ) c

 WHERE send_yn = 'N'

   AND (  (flag = 1)

       OR (cnt = 0 AND rn = 1)

       OR (con_Flag = 1)

       )

;

마농(manon94)님이 2024-04-08 17:38에 작성한 댓글입니다.
이 댓글은 2024-04-08 17:44에 마지막으로 수정되었습니다.

마뇽님.. 고맙습니다.

작성해 주신 쿼리보고 다시 생각하니 회원수가 많아지는 것은 당연히 로직상 어쩔 수 없겠네요..;

대신 물리적 테이블을 최대한 적게 사용하는 것이 좋다는 것을 다시 깨달았습니다.

고맙습니다.

※ 댓글 주신 쿼리에서 cnt를 삭제하고 flag를 사용했고, rn의 partition by에 b.data_key를 추가하였습니다.

WITH data01 AS

(

SELECT 1 user_id, 4.0 stdRate, 0 con_Flag

UNION ALL SELECT 2, 5.0, 0

UNION ALL SELECT 3, 3.0, 1

)

, data02 AS

(

SELECT 1 data_id, 1 data_key, 4.5 dataRate, 'Y' send_yn

UNION ALL SELECT 2, 1, 5.5, 'N'

UNION ALL SELECT 3, 2, 3.5, 'Y'

UNION ALL SELECT 4, 2, 5.5, 'N'

UNION ALL SELECT 5, 2, 6.5, 'N'

)

SELECT *

FROM (SELECT a.user_id

, a.stdRate

, a.con_Flag

, b.data_id

, b.send_yn

, CASE WHEN b.dataRate < a.stdRate + 1 THEN 1 ELSE 0 END flag

, ROW_NUMBER() OVER(PARTITION BY a.user_id, b.data_key ORDER BY b.data_id) rn

FROM data01 a

JOIN data02 b

ON a.stdRate <= b.dataRate

) c

WHERE send_yn = 'N'

AND ( (flag = 1)

OR (flag = 0 AND rn = 1)

OR (con_Flag = 1)

)

;

즐겨찾기(roejh94)님이 2024-04-09 15:43에 작성한 댓글입니다.

아.. 결국에는 정의 문제였네요..

마뇽님 댓글 보고 다시 정의 했습니다.

1. data01의 con_flag가 0일때는 stdRate보다 큰 최초 1개의 값

2. data01의 conflag가 1일때는 stdRate보다 큰 계속된 값

혼란 드려서 죄송합니다. 결국에는 아래로 정리했습니다.

고맙습니다. 마뇽님...

WITH data01 AS

(

SELECT 1 user_id, 4.0 stdRate, 0 con_Flag

UNION ALL SELECT 2, 5.0, 0

UNION ALL SELECT 3, 3.0, 1

)

, data02 AS

(

SELECT 1 data_id, 1 data_key, 4.5 dataRate, 'Y' send_yn

UNION ALL SELECT 2, 1, 5.5, 'N'

UNION ALL SELECT 3, 2, 3.5, 'Y'

UNION ALL SELECT 4, 2, 5.5, 'N'

UNION ALL SELECT 5, 2, 6.5, 'N'

)

SELECT *

FROM (SELECT a.user_id

, a.stdRate

, a.con_Flag

, b.data_id

, b.send_yn

, ROW_NUMBER() OVER(PARTITION BY a.user_id, b.data_key ORDER BY b.data_id) rn

FROM data01 a

JOIN data02 b

ON a.stdRate <= b.dataRate

) c

WHERE send_yn = 'N'

and case when con_Flag = 0 then rn = 1

else 1=1

end

;

즐겨찾기(roejh94)님이 2024-04-09 16:10에 작성한 댓글입니다.
이 댓글은 2024-04-09 16:18에 마지막으로 수정되었습니다.

작성하신 조건은 좀 알아보기 어렵네요.

MySQL 에서만 동작하는 구문이구요.

다른 DB 에서도 동작하는 간결한 구문으로 바꿔 봤습니다.

- 수정전 : and case when con_Flag = 0 then rn = 1 else 1=1 end

- 수정후 : AND (con_Flag = 1 OR rn = 1)

마농(manon94)님이 2024-04-09 21:58에 작성한 댓글입니다.
이 댓글은 2024-04-09 21:58에 마지막으로 수정되었습니다.

마뇽님.. 고맙습니다.

즐겨찾기(roejh94)님이 2024-04-12 09:38에 작성한 댓글입니다.

[Top]

No.	제목	작성자	작성일	조회
31454	mysql 과부하 [3]	즐겨즐겨	2024-04-14	111
31452	좀 더 효율적인 쿼리가 없을지요? [5]	즐겨찾기	2024-04-05	148
31448	왜 MySQL에서는 인덱싱에 B+Tree가 아닌 B-Tree를 사용할까요 [2]	이진호	2024-03-29	122
31447	시간범위에 해당하는 데이터 가져오기 [2]	타자치는사람	2024-03-28	106
31446	테이블 설계 관련 질문드려요. [3]	김선엽	2024-03-24	112