Oracle 중간값 계산하기

Oralce 및 기타 여러 DB에서 중앙값 함수를 제공하지 않아서 로직 샘플 짬.

WITH GOODS AS (
          SELECT 'A' AS GOOD_NM, 'SELLER_1' AS SELLER, 110 AS PRICE_MIN, 310 AS PRICE_MAX FROM DUAL
UNION ALL SELECT 'A' AS GOOD_NM, 'SELLER_2' AS SELLER, 120 AS PRICE_MIN, 330 AS PRICE_MAX FROM DUAL
UNION ALL SELECT 'A' AS GOOD_NM, 'SELLER_3' AS SELLER, 130 AS PRICE_MIN, 320 AS PRICE_MAX FROM DUAL
UNION ALL SELECT 'A' AS GOOD_NM, 'SELLER_4' AS SELLER, 140 AS PRICE_MIN, 340 AS PRICE_MAX FROM DUAL
UNION ALL SELECT 'A' AS GOOD_NM, 'SELLER_5' AS SELLER, 150 AS PRICE_MIN, 350 AS PRICE_MAX FROM DUAL
UNION ALL SELECT 'B' AS GOOD_NM, 'SELLER_1' AS SELLER, 210 AS PRICE_MIN, 460 AS PRICE_MAX FROM DUAL
UNION ALL SELECT 'B' AS GOOD_NM, 'SELLER_2' AS SELLER, 220 AS PRICE_MIN, 410 AS PRICE_MAX FROM DUAL
UNION ALL SELECT 'B' AS GOOD_NM, 'SELLER_3' AS SELLER, 230 AS PRICE_MIN, 420 AS PRICE_MAX FROM DUAL
UNION ALL SELECT 'B' AS GOOD_NM, 'SELLER_4' AS SELLER, 240 AS PRICE_MIN, 450 AS PRICE_MAX FROM DUAL
UNION ALL SELECT 'B' AS GOOD_NM, 'SELLER_5' AS SELLER, 250 AS PRICE_MIN, 430 AS PRICE_MAX FROM DUAL
UNION ALL SELECT 'B' AS GOOD_NM, 'SELLER_6' AS SELLER, 260 AS PRICE_MIN, 440 AS PRICE_MAX FROM DUAL
) 
SELECT GOOD_NM
     , SUM(CASE WHEN MOD(GROUP_CNT, 2) <> 0 AND PRICE_MIN_SEQ = (TRUNC(GROUP_CNT/2) + 1)                THEN PRICE_MIN -- 홀수계산. 중앙값 계산
                WHEN MOD(GROUP_CNT, 2) =  0 AND PRICE_MIN_SEQ BETWEEN GROUP_CNT/2 AND (GROUP_CNT/2 + 1) THEN PRICE_MIN -- 짝수계산
               END) 
     / SUM(CASE WHEN MOD(GROUP_CNT, 2) <> 0 AND PRICE_MIN_SEQ = (TRUNC(GROUP_CNT/2) + 1)                THEN 1
                WHEN MOD(GROUP_CNT, 2) =  0 AND PRICE_MIN_SEQ BETWEEN GROUP_CNT/2 AND (GROUP_CNT/2 + 1) THEN 1
                END) AS PRICE_MIN_MEAN
     , SUM(CASE WHEN MOD(GROUP_CNT, 2) <> 0 AND PRICE_MAX_SEQ = (TRUNC(GROUP_CNT/2) + 1)                THEN PRICE_MAX -- 홀수계산. 중앙값 계산
                WHEN MOD(GROUP_CNT, 2) =  0 AND PRICE_MAX_SEQ BETWEEN GROUP_CNT/2 AND (GROUP_CNT/2 + 1) THEN PRICE_MAX -- 짝수계산
               END) 
     / SUM(CASE WHEN MOD(GROUP_CNT, 2) <> 0 AND PRICE_MAX_SEQ = (TRUNC(GROUP_CNT/2) + 1)                THEN 1
                WHEN MOD(GROUP_CNT, 2) =  0 AND PRICE_MAX_SEQ BETWEEN GROUP_CNT/2 AND (GROUP_CNT/2 + 1) THEN 1
                END) AS PRICE_MAX_MEAN
  FROM (
      SELECT A.*
           , ROW_NUMBER() OVER(PARTITION BY GOOD_NM ORDER BY PRICE_MIN) AS PRICE_MIN_SEQ
           , ROW_NUMBER() OVER(PARTITION BY GOOD_NM ORDER BY PRICE_MAX) AS PRICE_MAX_SEQ
           , COUNT(*)     OVER(PARTITION BY GOOD_NM)                    AS GROUP_CNT
        FROM GOODS A
       ) A
GROUP BY GOOD_NM

| 결과

GOOD_NM	PRICE_MIN_MEAN	PRICE_MAX_MEAN
A	130	330
B	235	435