안녕하세요.
아래와 같은 데이터의 X1~X5 행의 평균은 어떻게 구하나요?
X값은 2개가 될수도 5개가 될수도 있기 때문에 count를 5로 고정 할 수 없습니다.
열의 평균은 avg로 구하면 되는데 행의 평균은 어떻게 해야 할지 모르겠습니다.
조언 부탁드립니다.
감사합니다.
DATE | X1 | X2 | X3 | X4 | X5 | NO | 평균 |
20180318 | 30.1 | 30.1 | 29.8 | 1 | |||
20180318 | 30.2 | 29.7 | 29.6 | 2 | |||
20180318 | 30.0 | 30.2 | 30.1 | 30.1 | 29.6 | 3 | |
20180318 | 30.0 | 29.8 | 30.2 | 29.7 | 4 | ||
20180406 | 29.5 | 30.2 | 30.0 | 30.2 | 5 | ||
20180618 | 29.2 | 29.4 | 30.0 | 29.8 | 6 | ||
20180619 | 29.9 | 29.8 | 30.1 | 30.1 | 7 | ||
20180619 | 29.3 | 29.6 | 30.2 | 29.7 | 8 | ||
20180620 | 29.2 | 29.6 | 30.0 | 30.2 | 9 | ||
20180620 | 29.6 | 29.7 | 30.0 | 29.8 | 10 |
decode 나 case, 기타 등등의 함수들을 이용해 개수를 구해보세요.
여러가지 다양한 방법이 있을 것입니다.
-- Oracle -- WITH t AS ( SELECT '20180318' dt, 30.1 x1, 30.1 x2, null x3, null x4, 29.8 x5, 1 no FROM dual UNION ALL SELECT '20180318', 30.2, 29.7, null, null, 29.6, 2 FROM dual UNION ALL SELECT '20180318', 30.0, 30.2, 30.1, 30.1, 29.6, 3 FROM dual UNION ALL SELECT '20180318', 30.0, 29.8, 30.2, 29.7, null, 4 FROM dual UNION ALL SELECT '20180406', 29.5, 30.2, 30.0, 30.2, null, 5 FROM dual UNION ALL SELECT '20180618', 29.2, 29.4, 30.0, 29.8, null, 6 FROM dual UNION ALL SELECT '20180619', 29.9, 29.8, null, 30.1, 30.1, 7 FROM dual UNION ALL SELECT '20180619', 29.3, 29.6, null, 30.2, 29.7, 8 FROM dual UNION ALL SELECT '20180620', 29.2, 29.6, null, 30.0, 30.2, 9 FROM dual UNION ALL SELECT '20180620', 29.6, 29.7, null, 30.0, 29.8, 10 FROM dual UNION ALL SELECT '20180621', null, null, null, null, null, 11 FROM dual ) SELECT dt , x1, x2, x3, x4, x5 , no , x_sum , x_cnt , ROUND(x_sum / NULLIF(x_cnt, 0), 1) x_avg FROM (SELECT dt , x1, x2, x3, x4, x5 , no , NVL(x1, 0) + NVL(x2, 0) + NVL(x3, 0) + NVL(x4, 0) + NVL(x5, 0) x_sum , NVL2(x1, 1, 0) + NVL2(x2, 1, 0) + NVL2(x3, 1, 0) + NVL2(x4, 1, 0) + NVL2(x5, 1, 0) x_cnt -- , LENGTH(SIGN(x1)||SIGN(x2)||SIGN(x3)||SIGN(x4)||SIGN(x5)) x_cnt FROM t ) ;
-- MSSQL -- WITH t AS ( SELECT '20180318' dt, 30.1 x1, 30.1 x2, null x3, null x4, 29.8 x5, 1 no UNION ALL SELECT '20180318', 30.2, 29.7, null, null, 29.6, 2 UNION ALL SELECT '20180318', 30.0, 30.2, 30.1, 30.1, 29.6, 3 UNION ALL SELECT '20180318', 30.0, 29.8, 30.2, 29.7, null, 4 UNION ALL SELECT '20180406', 29.5, 30.2, 30.0, 30.2, null, 5 UNION ALL SELECT '20180618', 29.2, 29.4, 30.0, 29.8, null, 6 UNION ALL SELECT '20180619', 29.9, 29.8, null, 30.1, 30.1, 7 UNION ALL SELECT '20180619', 29.3, 29.6, null, 30.2, 29.7, 8 UNION ALL SELECT '20180620', 29.2, 29.6, null, 30.0, 30.2, 9 UNION ALL SELECT '20180620', 29.6, 29.7, null, 30.0, 29.8, 10 UNION ALL SELECT '20180621', null, null, null, null, null, 11 ) SELECT dt , x1, x2, x3, x4, x5 , no , x_sum , x_cnt , CAST(x_sum / NULLIF(x_cnt, 0) AS NUMERIC(5, 1)) x_avg FROM (SELECT dt , x1, x2, x3, x4, x5 , no , ISNULL(x1, 0) + ISNULL(x2, 0) + ISNULL(x3, 0) + ISNULL(x4, 0) + ISNULL(x5, 0) x_sum , ISNULL(SIGN(x1), 0) + ISNULL(SIGN(x2), 0) + ISNULL(SIGN(x3), 0) + ISNULL(SIGN(x4), 0) + ISNULL(SIGN(x5), 0) x_cnt FROM t ) a ;