하루y

https://gregorio78.tistory.com/98

https://www.oracle.com/kr/database/technologies/appdev/xe.html

end.

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

| 결과

SELECT TC.OWNER
     , TC.TABLE_NAME 
     , TC.COLUMN_NAME
     , TRANSLATE(CC.COMMENTS, CHR(10)||CHR(13), '.') AS COMMENTS
     , CASE WHEN TC.NULLABLE = 'N' THEN 'Y'
            ELSE 'N'
            END AS NOT_NULL
     , TC.DATA_TYPE
     , CASE WHEN TC.DATA_TYPE = 'NUMBER' AND TC.DATA_PRECISION IS NOT NULL THEN '' || TC.DATA_PRECISION || ',' || TC.DATA_SCALE || ''
            WHEN TC.DATA_TYPE LIKE '%CHAR%' THEN '' || TC.DATA_LENGTH || ''
            ELSE NULL
            END AS TYPE_SIZE
FROM  ALL_TAB_COLUMNS  TC
    , ALL_COL_COMMENTS CC
WHERE TC.OWNER = CC.OWNER
  AND TC.COLUMN_NAME = CC.COLUMN_NAME
  AND TC.TABLE_NAME = CC.TABLE_NAME 
--  AND TC.OWNER = '스키마명'
--  AND TC.TABLE_NAME = '테이블명'
ORDER BY TC.TABLE_NAME, TC.COLUMN_ID