分层随机抽样巧用窗口函数 | percent_rank()/ntile()over()

点击上方蓝色字关注置顶我们！

相关推荐：SQL分析函数，看这一篇就够了

导读：实际工作中有没有碰到过或想过类似场景：如何随机取样？比如分100组，每个组取100个，或10%？本文通过两个小栗子，妥妥的拿下这种纸老虎需求。

☞ 关注公众号『数据仓库与Python大数据』，获取更多优质资源与干货文章。

作者：吹角连营

编辑：紫霞仙子

正文

场景

在ABtest测试上，通常会将用户群按照特定的条件分成若干组，然后取每组一定数量的用户（例如从总体中随机取5000个，或者随机取10%），发送对应的内容，最后查看效果。刚开始遇见这种业务上的问题时，一般按照规则提取出用户，建立对应的表，之后用union all来进行连接形成样本的测试数据。

如果只是分几组的话，工作量并不大，但是在大用户量中，分组通常都是分几十组，这样势必会形成很多重复无意义的工作，让人头疼。那就用窗口函数，来实行一步抽样，减少无用的工作量。

案例一

现有一张表< t_user_active >，含用户ID（user_id）和激活日期（active_date）两个字段，需求：

（1）每个激活日期随机取1000个用户

（2）每个激活日期随机取10%用户

以上举例为：把每天的激活时间作为用户的筛选条件，每个条件下随机取1000个用户，或者随机取10%的用户，作为样本数据，这样利用窗口函数，和随机函数< rand >结合的方式，就可以实现题中的需求。

案例二

现有一张表< t_user_all >，含用户ID（user_id），无其他条件要求，用户不重复，需求：

（1）随机均分成100组，每组取1000个用户

（2）随机均分成100组，每组取10%个用户

案例二与案例一不同的地方在于，没有用户条件的限定，只需将用户随机均分成100组即可，这时用到了< ntile >窗口函数，其余与案例一同理。

这样，巧妙运用窗口函数，就很容易解决随机抽样的数据需求。

注：本人曾经接到过15组的数据需求，建了15张表，哭瞎。。。

☞ 公众号后台回复：实时数仓，可直达实时数仓视频

欢迎加入中台|数仓技术交流群。戳：快来加入数据交流群！

在看

备注：中台

更多精彩，请关注公众号后台"到"数仓之路"查看

！关注不迷路~ 各种干货、资源定期分享！

学习小密圈 成神大世界

你也「在看」吗？👇