데이터베이스 질문과 답변
안녕하세요. AVG함수를 사용하여 평균값을 구하려고 하는데요.
대상 값에 NULL이 존재할 경우 평균값도 NULL로 표현하고 싶습니다.
SELECT A
, B
, AVG(B) OVER(PARTITION BY A) AVG
FROM (SELECT 1 A, 1 B FROM DUAL UNION
SELECT 1 A, NULL B FROM DUAL UNION
SELECT 2 A, 1 B FROM DUAL UNION
SELECT 2 A, 2 B FROM DUAL
) T
;
1 1 1
1 NULL 1
2 1 1.5
2 2 1.5
위의 결과를 아래와 같이 나오도록 할 수 있을까요?
1 1 NULL 1 NULL NULL 2 1 1.5 2 2 1.5
by jkson
[2017.03.06 18:39:09]
SELECT A
, B
, AVG(B) OVER(PARTITION BY A) / NULLIF(1,MAX(DECODE(B,NULL,1)) OVER(PARTITION BY A)) AVG
FROM (SELECT 1 A, 1 B FROM DUAL UNION
SELECT 1 A, NULL B FROM DUAL UNION
SELECT 2 A, 1 B FROM DUAL UNION
SELECT 2 A, 2 B FROM DUAL
) T
;
by 김철수
[2017.03.06 18:55:29]
감사합니다^^
by 제로
[2017.03.07 11:00:58]
select a
, b
, nvl2(min(b) keep(dense_rank first order by b nulls first) over(partition by a)
, avg(b) over(partition by a)
, null) as result
from (select 1 a, 1 b from dual union
select 1 a, null b from dual union
select 2 a, 1 b from dual union
select 2 a, 2 b from dual
) t
;
) 버튼을 클릭하여 작성 하시면 됩니다.