首页 > 其他分享 >数仓之TABLESAMPLE采样

数仓之TABLESAMPLE采样

时间:2024-08-28 20:21:24浏览次数:12  
标签:采样 数仓 抽样 pt TABLESAMPLE count yyyymmdd where select

前言

在日常工作中,我们会对表中记录随机抽样然后探查,如何进行抽样就是本章要讲的重点。数仓中的抽样方法有很多,这里主要介绍一下:随机抽样、块抽样、桶抽样、分组抽样这四种,话不多说,直接上干货。

随机抽样

随机抽样就是给每行数据赋值一个随机数 ,排序之后进行抽样,主要分为:

  • order by,直接全局排序。
  • distribute by + sort by,先分组再排序。

orderBy

select *
  from userinfo 
 where pt = '${yyyymmdd}'
 order by `column` -- 全局排序 
 limit 100;

sortBy

select *
  from userinfo 
 where pt = '${yyyymmdd}'
 distribute by `column1` -- 按照 某字段 分组
 sort by `column2`       -- 组内按照某字段 排序
 limit 100;

桶采样

  • TABLESAMPLE (BUCKET x OUT OF y [ON <col_name> | rand()])
    • x,y:必填。将源表中的数据划分为y个桶,取其中的第x个桶,桶从1开始编号。
    • col_name:分桶列名即要进行采样的列名。当表不是聚簇表时,col_name与rand()函数必须二选一,当使用rand()函数时表示对输入的数据随机进行分桶。ON语句中最多支持指定10个列。

现在有一张分区用户表 usr_info ,总用户量 1700W 左右,想从中抽取 10% 的用户进行分析,具体代码如下:

select count(1)
from userinfo 
tablesample(bucket 1 out of 10 on rand())
where pt = '${yyyymmdd}'
;

块采样

指定记录数抽样

  • TABLESAMPLE (m ROWS) ,其中 m 为指定随机返回的记录数。

切记,此时要考虑 HDFS中block 个数,因为 TABLESAMPLE 本身是基于 block 抽样的,如果此时有 10个 block ,那么如下代码最终返回结果等于:m x block 个数

select count(1)
from (select uid
        from userinfo 
       where pt = '${yyyymmdd}'
       ) t 
tablesample(1000 rows)
;

指定百分比采样

  • TABLESAMPLE ( PERCENT),其中n为采样百分比,取其中n%的数据,即采样返回的数据记录个数和源表中总记录个数之比大概是n%,非精确值。
select count(1)
from (select uid
        from userinfo 
       where pt = '${yyyymmdd}'
       ) t 
tablesample(20 percent)
;

按照数据大小抽样

  • TABLESAMPLE(M MB/GB/GB)
select count(1)
from (select uid
        from userinfo 
       where pt = '${yyyymmdd}'
       ) t 
tablesample(100M) -- 抽取 100MB 大小的数据,抽取的是数据块,是压缩的数据。
;

分组抽样

ClusterSample

此函数并非 Hive 原生函数,而是 MaxCompute 独有。

用户随机抽样。返回 True 表示该行数据被抽中。命令如下:

  • cluster_sample(bigint ) OVER ([partition_clause]) ,表示随机抽取N条数据。
  • cluster_sample(bigint , bigint ) OVER ([partition_clause]),表示按比例(M/N)随机抽取。即抽取partition_row_count×M / N条数据。partition_row_count 指分区中的数据行数。

案例: 按照不同意向职位(软开,硬件,机械,产运,金融,市场营销)、是否新老用户(1;0)分组,各抽取 10 个用户入样,代码如下:

with usr as (
select *
       ,CLUSTER_SAMPLE(10) over(partition by careerjob,isnew) as flag
from userinfo 
where pt = '${yyyymmdd}'
and careerjob in ('软开','硬件','机械','产运','金融','市场营销')
)
select count(uid)
from usr 
where flag = true 
;

结果如下所示:
在这里插入图片描述
上述案例中,careerjob 有 6 个枚举,isnew 有 2 个枚举,组合方案有 12 组,每组抽取10人,一共抽取 120 人。

ntile

将数据顺序切分成N等份,返回数据所在等份的编号(从1到N)。

  • ntile(bigint N) over ([partition_clause] [orderby_clause])
select id
from (select 
             id
            ,ntile(9) over (order by rand()) as seed
        from values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12) as t(id)
       )
where seed=1;

标签:采样,数仓,抽样,pt,TABLESAMPLE,count,yyyymmdd,where,select
From: https://blog.csdn.net/weixin_43145550/article/details/141567415

相关文章

  • Vulkan入门系列17 - 多重采样( Multisampling)
    一:概述        我们的程序现在可以加载多个级别的纹理,从而解决了在渲染远离观察者的物体时出现的伪影问题。现在图像变得平滑多了,但仔细观察,你会发现绘制的几何图形边缘呈现锯齿状。这在我们早期渲染一个四边形的程序中尤为明显:        这种不希望有的效果......
  • 【精选】基于大数据的___银行信用卡用户的数仓系统的设计与实现(全网独一无二,最新定制)
    目录: 关键技术介绍 PYTHON语言简介大数据介绍 MySql数据库DJANGO框架 Hadoop介绍Scrapy介绍B/S架构 系统的设计系统总功能模块设计系统测试系统测试的目的软件测试过程6.3测试用例参考代码:为什么选择我:博主介绍:✌我是阿龙,一名专注于Java技术领域的......
  • 机器学习-过采样(全网最详解)
    相关介绍在逻辑回归中,处理不平衡数据集是一个重要的步骤,因为不平衡的数据集可能导致模型偏向于多数类,而忽略少数类。过采样(Over-sampling)是处理不平衡数据集的一种常用方法,它通过增加少数类样本的数量来平衡数据集。1.过采样的基本概念过采样是指对训练集中的少数类样本......
  • 如何实现下采样(教科书级别教你拿捏)
    在数据处理、信号处理、图像处理以及机器学习等多个领域中,下采样(Downsampling)是一项至关重要的技术。下采样旨在减少数据集中的样本数量,同时尽量保留原始数据的关键信息,以便在降低计算成本、提高处理速度或适应特定分析需求时仍然保持数据的代表性。本文将详细介绍下采样的基本......
  • 机器学习-下采样(全网最详解)
    文章目录相关介绍逻辑回归不平衡数据集下采样逻辑回归与下采样结合下采样运用1.导入相关包2.数据预处理3.数据下采样与合并4.绘制下采样图像5.划分数据集6.模型训练与评估7.阈值调整与性能评估总结1.下采样的优点2.下采样的缺点相关介绍逻辑回归逻辑回归是一......
  • 机器学习:逻辑回归--下采样
    目录前言一、为什么使用下采样1.例如:2.导致:3.办法:4.结果:二、代码实现1.完整代码2.导入库3.可视化混淆矩阵4.导入数据5数据预处理6.下采样7.取出训练集和测试集8.建立模型9.进行测试总结前言        下采样(Downsampling)是一种在数据处理中减少样本......
  • Stable Diffusion绘画 | ControlNet应用-Tile(分块)—tile_resample(分块-重采样)
    前言PS:StableDiffusion安装与在线平台推荐,请在公众号内的消息对话框中,发送关键词**「xiaoyaoSD」**要想使用SD生成高品质图片,放大增加分辨率是必不可少的环节。tile_resample(分块-重采样)主要是将图片切分成很多个分块,并识别每个分块的信息,最终通过特定算法把分......
  • PyTorch中的随机采样秘籍:SubsetRandomSampler全解析
    标题:PyTorch中的随机采样秘籍:SubsetRandomSampler全解析在深度学习的世界里,数据是模型训练的基石。而如何高效、合理地采样数据,直接影响到模型训练的效果和效率。PyTorch作为当前流行的深度学习框架,提供了一个强大的工具torch.utils.data.SubsetRandomSampler,它允许开发者......
  • 实时数仓方案五花八门,实际落地如何选型和构建!(转载)
    原文链接:https://cloud.tencent.com/developer/article/2167527【作者介绍】王磊,阿里云MVP,华院计算技术总监。著有:《图解Spark大数据快速分析实战》;《offer来了:Java面试核心知识点精讲(原理篇)》;《offer来了:Java面试核心知识点精讲(架构篇)》。编辑|韩楠约4,989字|10......
  • 视频采样方式实现
    视频采样方式实现属于数据增强组件的一部分,源码位于mmaction.datasets.pipelines.loading.py中。支持的采样方式包括SampleFrames与DenseSampleFrames两种。SampleFrames主要参数包括:clip_len,frame_interval,num_clips两种基本采样方式:TSN形式:将视频分为x个部......