Hive SQL随机抽样

个人公众号: Data Science数据科学之美

使用Hive随机抽样

    <!-- 文章内容 -->
    <div data-note-content="" class="show-content">
      <div class="show-content-free">
        <h1>1 在hive中使用rand简单随机抽样</h1>

select a.*,rand(12345) as random
from tripdata a;

country       city  visitors      random
阿联酋  阿布扎比        137     0.3618031071604718
阿联酋  阿布扎比        146     0.932993485288541
阿联酋  阿布扎比        178     0.8330913489710237
阿联酋  阿布扎比        337     0.32647575623792624
阿联酋  阿布扎比        178     0.2355237906476252
阿联酋  阿布扎比        227     0.34911535662488336
阿联酋  阿布扎比        157     0.4480776326931518
阿联酋  迪拜    144     0.6381529437838686
阿联酋  迪拜    268     0.1582665432952023
阿联酋  迪拜    103     0.768888060192009
阿联酋  迪拜    141     0.9450734577298074
阿联酋  迪拜    108     0.06558504072066074
阿联酋  迪拜    266     0.8102312734654696
澳大利亚        悉尼    141     0.47791537253375116
澳大利亚        悉尼    122     0.3325863969722369
澳大利亚        悉尼    153     0.6925313420371904
澳大利亚        悉尼    128     0.9162288670686813
澳大利亚        墨尔本  294     0.35086223384270854
澳大利亚        墨尔本  230     0.09024327831371282
澳大利亚        墨尔本  159     0.5554002739128288
澳大利亚        墨尔本  188     0.10277490055301586
澳大利亚        堪培拉  249     0.9443583363476495
澳大利亚        堪培拉  378     0.19418323997969733
澳大利亚        堪培拉  255     0.2535159880591803
澳大利亚        堪培拉  240     0.6960683253803703

select a.* 
from (
select a.*,rand(12345) as random
from tripdata a
) a
where random between 0 and 0.2;

a.country       a.city  a.visitors      a.random
阿联酋  迪拜    268     0.3618031071604718
阿联酋  迪拜    108     0.932993485288541
澳大利亚        墨尔本  230     0.8330913489710237
澳大利亚        墨尔本  188     0.32647575623792624
澳大利亚        堪培拉  378     0.2355237906476252

select distinct a.*
from tripdata a
order by rand(12345) 
limit 5;

country       city  visitors
阿联酋  阿布扎比        157
阿联酋  阿布扎比        137
阿联酋  迪拜    144
阿联酋  阿布扎比        227
澳大利亚        堪培拉  240

2、数据块取样（Block Sampling）--来源于网路

--数据块取样（Block Sampling）
SELECT * FROM lxw1 TABLESAMPLE (50 PERCENT);

–将会从表lxw1中取样30M的数据：
SELECT * FROM lxw1 TABLESAMPLE (30M);

–这种方式可以根据行数来取样，但要特别注意：
这里指定的行数，是在每个InputSplit中取样的行数，也就是，每个Map中都取样n ROWS。
SELECT COUNT(1) FROM (SELECT * FROM lxw1 TABLESAMPLE (200 ROWS)) x;

–分桶表取样（Sampling Bucketized Table）
SELECT COUNT(1)
FROM lxw1 TABLESAMPLE (BUCKET 1 OUT OF 10 ON rand());

系统抽样 --来源于网路

mod,rand() 依照userrid取模，分5组，每组随机抽取100个用户，实现如：

• 依据user_id，取模，获取 mod_numd
• 在mod_num组内然后随机排序，
• 从各组取出20条

select *  
  from(  
      select refund_id,user_id,mod_num,rank_num from 
      (select refund_id,user_id,cast(10+rand()*100 as double) rank_num,
        user_id%5 as mod_num 
        from songpo_test)   
      distribute by mod_num sort by mod_num,rank_num desc  
      ) a  
where row_number(mod_num)<=20;

分层抽样 --来源于网路

按照每个组的记录数来分层抽样。假设需要抽取EXTRA_NUM条记录

• 计算每个分区需要抽记录条数
• 在mod_num组内然后随机排序
• 从各组取出cat_extra_num条

drop table test_data_extra_indexs;  
      create table test_data_extra_indexs as  
      select a.cat_id,cat_num,all_num,cat_num/all_num as extra_lv,(cat_num/all_num)*'EXTRA_NUM'  as cat_extra_num,c.refund_id,c.user_id,c.org_id from   
      (select cat_id,count(1) as cat_num,'1' as key from songpo_test group by cat_id) a   
      join  
      (select '1' as key,count(1) as all_num from songpo_test) b   
      on a.key=b.key  
      join  
      (select * from songpo_test) c   
      on a.cat_id=c.cat_id;  
  select *  
  <span class="hljs-keyword">from</span>(  
    select refund_id,user_id,cat_id,mod_num,rank_num <span class="hljs-keyword">from</span>   
    select refund_id,user_id,cat_id,cast(<span class="hljs-number">10</span>+rand()*<span class="hljs-number">100</span> <span class="hljs-keyword">as</span> double) rank_num,user_id%<span class="hljs-number">5</span> <span class="hljs-keyword">as</span> mod_num,cat_extra_num <span class="hljs-keyword">from</span>(  
    (select refund_id,user_id,cat_id,cast(<span class="hljs-number">10</span>+rand()*<span class="hljs-number">100</span> <span class="hljs-keyword">as</span> double) rank_num,user_id%<span class="hljs-number">5</span> <span class="hljs-keyword">as</span> mod_num <span class="hljs-keyword">from</span> test_data_extra_indexs)  x  
    )  
    distribute by mod_num sort by mod_num,rank_num desc  
  )a  

where row_number(mod_num)<=20;

      </div>
    </div>
</div>