그룹별 균등하게 금액 추출

2011-07-28 16:43:06

---

SQL 4 Oracle 오라클용 sql 실무

▒ 그룹별 균등하게 금액 5억씩 추출 하기

 

SELECT  B1.금액, B1.그룹필드명, B1.Key_필드명  
FROM Table_NM B1,
 (
          SELECT
              그룹필드명, Key_필드명, CNT, sum_bal 
             , random   , ratio 
             , ratio_sum  
          from
          (
                  SELECT 
                    그룹필드명, Key_필드명  , CNT  , sum_bal
                  , random    , ratio  
                  , sum ( sum_bal )  over ( partition by 그룹필드명  order by 그룹필드명, random  rows between unbounded preceding  and current row) ratio_sum
                  from
                  (
                          SELECT
                                   B.그룹필드명   , B.Key_필드명  , count(*) CNT  , sum(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 ratio_sum <=  500000000  --#### 5억 추출 하기
 ) B2
 WHERE B1.Key_필드명 is not null
     AND B1.Key_필드명 = B2.Key_필드명