目录
0 引言
数据分桶(Bucket)作为数据预处理的一部分,是进行数据模型建设的前提,是构建特征工程的重要手段。也被称为离散分箱或数据分段,本质上就是把数据按特定的规则进行分组,是实现数据的离散化、增强数据稳定性、减少过拟合风险的一种重要方法。本文针对数据分析领域中常见数据分桶问题进行总结,采用SQL的方式对分桶问题进行求解。
1 数据准备
构建10个随机数 |
with bucket as |
2 数据分桶SQL实现
2 1.基于规则的分桶
基于规则的分桶,它根据预先定义的规则或标准将数据点分配到不同的桶中(或区间)中。这种方法通常涉及到对数据的业务理解和特定领域的知识。以下是基于规则分桶的一些关键特点和步骤:
特点:
1.自定义规则:分桶的规则是根据业务需求、数据分析目标或领域专家的建议来定义的。这些规则可以是简单的阈值,也可以是复杂的逻辑表达式。
2.灵活性:基于规则的分桶允许用户根据实际情况灵活地设计分桶逻辑,这在处理具有特定业务含义的数据时非常有用。
3.可解释性:由于分桶规则是明确的,因此结果具有较高的可解释性,便于向非技术利益相关者解释。
我们假设规则: [0~0.2) 为1组,[0.2~0.5)为2组,[0.5~0.8)为3组,[0.8~1)为4组。
select
id,
val,
case when val >=0 and val < 0.2 then 1
when val >=0.2 and val <0.5 then 2
when val >=0.5 and val <0.8 then 3
when val >=0.8 and val <1 then 4
end as bucket_flag
from bucket; |
2.2 等距分桶
等距分桶是一种将连续数值数据分布到等间隔区间的方法。在等距分桶中,数据的整个范围被均匀地分割成若干个桶里,每个桶的宽度(即间隔)是相同的,但桶的个数是不确定的。
等距分桶的关键特点:
1.均匀分割:数据的范围(最大值和最小值之间的差)被均匀地分割成不同的桶里,每个桶里的宽度是相同的。桶的大小一致,桶个数不确定。例如年龄分桶:0-5岁,5-10岁,10-15岁,…
2.边界确定:桶的边界是基于数据的实际值计算出来的,通常是通过取最大值和最小值的平均值来确定中间点,然后根据这个中间点向两边扩展。
3.数据分配:每个数据点根据其值被分配到相应的桶中。如果数据点的值正好等于某个桶的边界值,它通常会被分配到该边界值所在的箱子。
等距分桶将数据从最小值到最大值之间,平均分为 N 等份。比如年龄数据,最大值为75,最小值为3,如果试图将数据分为4份,这时候每个区间的长度就是:(75-3)/4 = 18。切分后的边界就是:21,39,47,75,(amin + W, amin + 2W, …, amin +N*W)
我们假设规则:数据平均分为5组
(1)尺寸确定
bucket_size :(max_val-min_val)/N
(2)边界确定
min_val + N* bucket_size。其中N为分桶编号
bucket_1:min_val + 1*bucket_size
bucket_2:min_val + 2*bucket_size
bucket_3:min_val + 3*bucket_size
bucket_4:min_val + 4*bucket_size
bucket_5:min_val + 5*bucket_size
(3)桶编号确定(当前数据值进入哪个桶中)
根据当前值确定当前值在哪个桶中。由公式:
min_val + N* bucket_size = val得到
(val – min_val) / bucket_size –-分组标签确定(关键点)
由于编号一般都为整数,因此我们对上述公式取整处理
int((val – min_val) / bucket_size) –-编号从0开始
或floor((val – min_val) / bucket_size) –-编号从0开始
具体示例SQL如下:
with bucket as
(select pos + 1 as id, rand() as val
from (select split(space(10), '') as a) t
lateral view posexplode(a) tt as pos, val)
select id
, val
, min_val
, max_val
, bucket_size
--- 边界确定
, min_val + 1 * bucket_size bucket_1
, min_val + 2 * bucket_size bucket_2
, min_val + 3 * bucket_size bucket_3
, min_val + 4 * bucket_size bucket_4
, min_val + 5 * bucket_size bucket_5
--编号确定
, floor((val - min_val) / bucket_size) bucket_fag -- 编号从0开始
from
(select id
, val
, min_val
, max_val
, bucket_size
--- 边界确定
, min_val + 1 * bucket_size bucket_1
, min_val + 2 * bucket_size bucket_2
, min_val + 3 * bucket_size bucket_3
, min_val + 4 * bucket_size bucket_4
, min_val + 5 * bucket_size bucket_5
from (select id
, val
, min_val
, max_val
, (max_val - min_val) / 5 as bucket_size
from (select id,
val,
min(val) over () as min_val,
max(val) over () as max_val
from bucket) t) t) t |
2.3等频分桶
等频分桶是将数据集划分为具有相同数量的区间或桶。这种方法的目标是确保每个桶中包含的数据点数量大致相同,而不是像等距分桶那样将数据范围均匀分割。等频分桶在处理具有不同密度区域的数据集时特别有用,因为它可以更好地反映数据的实际分布。
等频分桶的关键特点:
1.数据点均匀分布:每个桶中的数据点数量相同或非常接近,这有助于在数据可视化和分析中保持一致性。【桶的个数一致】
2.边界动态确定:桶的边界不是预先定义的,而是根据数据点的实际分布动态计算的【桶的大小不确定】,每个桶尺寸大小不一样。
3.适用于偏斜分布:等频分桶可以很好地处理偏斜分布的数据,因为它不受数据范围的影响,而是关注数据点的分布。
等频分桶是将数据的值由大到小排列后,将数据分成N等份,保证每份中数据的个数是一样的。比如我们有12个数据值,按照从小到大排序,我们希望把数据分为4个桶,这时候等频分桶中每个桶就有3个数据。
我们假设规则:数据平均分为5组。
桶个数: 5
每个桶中数据点数:均等
桶尺寸:动态计算
桶编号:nitile(n),n为桶个数
具体SQL示例如下:
with bucket as
(select pos + 1 as id, rand() as val
from (select split(space(10), '') as a) t
lateral view posexplode(a) tt as pos, val)
select id,
val,
ntile(5) over (order by val ) as bucket_flag
from bucket |
3 小结
本文详细介绍了数据分析中常见的几种分桶方式:基于业务规则的分桶、等距分桶及等频分桶等,针对每种分桶方式给出了SQL实现。具体实际中SQL应用如下:
SQL进阶技巧:动态分桶算法应用之用户观看时长问题分析_not yet supported place for udaf 'sum-CSDN博客
SQL进阶技巧:如何按照固定尺寸(固定区间)对数据进行打分类标签?_sql给数据打标签-CSDN博客
SQL进阶技巧:如何按照某个字段对数据进行动态分桶?_按照某个字段进行动态分桶-CSDN博客
SQL进阶技巧-动态分桶算法应用之数据前后百分比问题_underlying error: org.apache.hadoop.hive.ql.parse.-CSDN博客