오라클 집계함수 사용시, 사용자 Function을 같이 Select하는 경우 문의 드립니다.. 0 8 2,530

by 동동동 [SQL Query] oracle [2021.12.09 11:58:26]


안녕하세요..

Select절에 Count나 Sum등 집계함수 사용시, 사용자 함수를 같이 Select할 경우 Group By 선언여부가 달라지던 데요(?)

 

--1
SELECT FN_GETCDNM('CD101') AS NM
     , COUNT(A.CODE) CNT
  FROM TMP_TBL A;

--2
SELECT (SELECT FN_GETCDNM('CD101') FROM DUAL) AS NM
     , COUNT(A.CODE) CNT
  FROM TMP_TBL A
 GROUP BY FN_GETCDNM('CD101');

1번인 경우 처럼 그냥 함수를 호출했을 시에는 Group By가 필요 없고,

2번과 같이 Select From Dual 로 하는 경우는 Group By를 해줘야 하는데..

이유(?)..동작 원리가 궁금합니다...

조언 부탁드립니다..감사합니다...

by 마농 [2021.12.09 12:55:57]

쿼리가 뭔가 좀 이상한데요?
함수 인자로 컬럼이 아닌 상수값 주는 것 맞나요?
Select 절의 알리아스 nm 으로 Group by 하는 것은 불가능한데요?
테이블에 nm 항목이 별도로 있는 건지?
DB 종류가 뭔가요?


by 동동동 [2021.12.09 13:08:44]

앗..마농님 죄송합니다..

예제로 만들다 보니 쿼리가 잘못 됬네요...쿼리 수정 했습니다..DB는 오라클 입니다...

함수 인자로 상수를 주어 상수에 해당하는 값을 가져오게 됩니다...

실제 쿼리에서는 여러 칼럼을 Select하지만 예제 용으로 작성했습니다..

 

추가로 예제를 좀 만들어 테스트 해보고 있는데 결과가 왜 이렇게 되는지 이해가 좀 안되네요..

 

CREATE OR REPLACE FUNCTION FN_GETCDNM_TEST(
    v_cd  VARCHAR2 
)
    RETURN VARCHAR2
IS
    v_nm  VARCHAR2(20);
BEGIN
    v_nm := '가나다라';
    RETURN v_nm;
END;


--1번
WITH TMP AS (
    SELECT 'AAA' AS CODE, 'Y' AS DEL_YN FROM DUAL
)
SELECT FN_GETCDNM_TEST('CD101') AS NM
     , COUNT(1) CNT
  FROM TMP
 WHERE DEL_YN = 'N';
 
--2번 
WITH TMP AS (
    SELECT 'AAA' AS CODE, 'Y' AS DEL_YN FROM DUAL
)
SELECT (SELECT FN_GETCDNM_TEST('CD101') FROM DUAL) AS NM
     , COUNT(1) CNT
  FROM TMP
 WHERE DEL_YN = 'N'
 GROUP BY FN_GETCDNM_TEST('CD101');

 

1번의 경우

NM CNT
가나다라 0

 

결과를 가져오는데 2번은 조회된 Row가 없게 되네요...

 


by 마농 [2021.12.09 13:44:00]

사용자 함수와 연관이 없어 보입니다.
서브쿼리와 연관이 있어 보이네요.
다만, 딱히 이유는 없어 보입니다.
버그일지도?

-- 오류 --
SELECT (SELECT 1 FROM dual) x
     , COUNT(*) cnt
  FROM emp
;
-- 정상 --
SELECT (SELECT 1 FROM dual) x
     , COUNT(*) cnt
  FROM emp
 GROUP BY '아무거나'
;
-- 정상 --
SELECT MIN((SELECT 1 FROM dual)) x
     , COUNT(*) cnt
  FROM emp
;
SELECT * FROM v$version;
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
"CORE	11.2.0.2.0	Production"
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

 


by 동동동 [2021.12.09 14:14:54]

헉..그러네요...

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE    12.2.0.1.0    Production
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

에서도 올려주신 쿼리 결과상태가 동일 하네요...

 

그런데 서브쿼리로 했을 경우와 그냥 함수를 사용했을경우 조회되는 Row가 다른데...이건 다른 이유인지요??

사용자 함수를 그냥 사용하지 말고 Select From Dual 로 스칼라서브쿼리 형태로 변경해야 한다고 해서요..

 


by 마농 [2021.12.09 14:29:06]

서브쿼리로 묶으라고 하는 것은
- 서브쿼리 캐싱 기능을 활용하고자 할때 사용되는 방법입니다.
굳이 없던 그룹바이를 추가 할 필요 없이 MIN 으로 한번 더 감싸 주세요.
- 그룹바이가 있던 쿼리는 문제 없겠네요.


by 동동동 [2021.12.09 14:45:27]

답변 감사드립니다..


by 모래가흙흙 [2021.12.09 14:12:38]

(1) 의 경우 group by 절 없이 집계함수(sum,max,min등) 을 사용하여  만족하는 결과가 없어도 최소 1건을 무조건 가져오는 상태에서  NM을 추가해준거고,
 
(2) 의 경우는 group by절을 넣음으로써 만족하는 데이터가 없으면 0건이 반환되므로 안나오는 거 아닌가요??


by 동동동 [2021.12.09 14:45:41]

답변 감사드립니다..

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