그룹별 같은 비율로 추출

2011-07-28 16:41:16

---

SQL 4 Oracle 오라클용 sql 실무

▒ 그룹별 같은 비율(15.35%)로 금액 추출 하기

 

SELECT
  b1.금액 , B1.그룹필드명   ,B1.Key_필드명
FROM Table_NM B1,
  (
          SELECT
                  그룹필드명    , Key_필드명 , CNT         , sum_bal 
                 , random      , ratio     
                 , ratio_sum   , round(ratio_sum*100, 2) ratio_rt
          from
          (
                  SELECT 
                    그룹필드명  , Key_필드명  , CNT  , sum_bal
                    , random    , ratio    
                    , sum ( ratio )  over ( partition by 그룹필드명  order by 그룹필드명, random  rows between unbounded preceding  and current row) ratio_sum
                  from
                  (
                          SELECT
                                   B.그룹필드명    , B.Key_필드명    , count(*) CNT    , sum(DECODE(B.금액) sum_bal
                                   , DBMS_RANDOM.VALUE(1,100) random   , ratio_to_report(sum(DECODE(B.금액))) over(partition by B.그룹필드명 )  ratio
                          FROM BMFORC.Table_NM B
                          WHERE B.Key_필드명 is not null
                          GROUP BY B.그룹필드명, B.Key_필드명 
                  )
          )
          where round(ratio_sum*100, 2) <= 15.35  --#### 15.05% 추출 하기
  ) B2
 WHERE B1.Key_필드명 is not null
     AND B1.Key_필드명 = B2.Key_필드명