odps 中的抽样

分组抽样

希望按照性别年龄分组, 去抽样查看他们的身高体重, 那么mysql可以这么写:

select height, weight from student_table group by gender, age

可odps 不支持这样的语法, 于是找到了cluster_sample() 这个函数.

• boolean cluster_sample(bigint x[, bigint y]) over(partition by col1[, col2..])
  按照若干column分组, 每组取x个样本.

 select height, weight
    from (
        select height, weight, cluster_sample(1) over(partition by  gender, age) as flag
        from student_table 
        ) sub
    where flag = true;

同样sql执行多次结果不一, 印证了在抽样时内部会有不同的随机数种子. 该函数可以满足需求.

应用: id去重

有些表, id列没有被定义为主键, 所以会有些重复, 有些脏数据, 那么怎么得到一个新的表, 干掉重复呢?

 select *
    from (
        select *, cluster_sample(1) over(partition by  id) as flag
        from student_table 
        ) sub
    where flag = true;

随机抽样

• double rand(bigint seed)
  以seed为种子返回double类型的随机数，返回值区间是的[0,1).
  seed可以不填, 取默认值. 此时产生的序列为:

0.00047147460303803655
0.8346089547661175
0.763442322501653
0.4278550224508415
0.672133090287509
0.25099721842026207
0.24351493979036573
0.5438010611309675
0.7853498113273409
0.25878882699589656

每次执行结果都是固定的.

• double trunc(double number, bigint n)
  将小数number截断, 只保留小数点后n位.

那么随机抽样10%的数据, 就可以这么写:

select * from
(select *, 
        trunc(rand(),2)*100 as random 
from  student_table ) tmp
where random between 1 and 10 ; --[1,10] 约10%的数据

负采样

机器学习中, 在构建样本时常常需要负采样, 比如 DSSM 深度语义匹配模型中, 做标签匹配任务, 数据集为 < doc, 一个有点击的tag, 三个无点击的tag >.
无点击的tag可以用随机抽样来获得.
思想是用随机数发生器得到[0,1]的随机小数, 然后乘以数据集的大小, 然后转换为int, 这个数就是负样本的tag_id.
需要注意用上不同的种子.

 SELECT  t1.doc_id
                    ,click_tag_id
                    ,t2.*
            FROM    doc_and_click_tags t1
            JOIN    (
                        SELECT  tag_id
                                -- 71140 为 all_tags.size()
                                ,CAST ((RAND(1) * 71140) AS BIGINT ) AS neg_samp_tag_id_1
                                ,CAST ((RAND(2) * 71140) AS BIGINT ) AS neg_samp_tag_id_2
                                ,CAST ((RAND(3) * 71140) AS BIGINT ) AS neg_samp_tag_id_3
                        FROM    all_tags
                        WHERE   ds = '${ds}'
                    ) t2
            ON      t1.pos_tag_id = t2.tag_id
            AND     t1. ds = '${ds}'