SQL进阶技巧：数据预处理如何对数据进行分桶【分箱】？

SQL进阶技巧：数据预处理如何对数据进行分桶【分箱】？

引用

CSDN

1.

https://blog.csdn.net/godlovedaniel/article/details/140923513

0 引言

数据分桶（Bucket）作为数据预处理的一部分，是进行数据模型建设的前提，是构建特征工程的重要手段。也被称为离散分箱或数据分段，本质上就是把数据按特定的规则进行分组，是实现数据的离散化、增强数据稳定性、减少过拟合风险的一种重要方法。本文针对数据分析领域中常见数据分桶问题进行总结，采用SQL的方式对分桶问题进行求解。

1 数据准备

构建10个随机数

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 * from bucket

2 数据分桶SQL实现

2.1 基于规则的分桶

基于规则的分桶，它根据预先定义的规则或标准将数据点分配到不同的桶中（或区间）中。这种方法通常涉及到对数据的业务理解和特定领域的知识。以下是基于规则分桶的一些关键特点和步骤：

特点：

1. 自定义规则：分桶的规则是根据业务需求、数据分析目标或领域专家的建议来定义的。这些规则可以是简单的阈值，也可以是复杂的逻辑表达式。
2. 灵活性：基于规则的分桶允许用户根据实际情况灵活地设计分桶逻辑，这在处理具有特定业务含义的数据时非常有用。
3. 可解释性：由于分桶规则是明确的，因此结果具有较高的可解释性，便于向非技术利益相关者解释。

我们假设规则：[00.2)为1组，[0.20.5)为2组，[0.50.8)为3组，[0.81)为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. 均匀分割：数据的范围（最大值和最小值之间的差）被均匀地分割成不同的桶里，每个桶里的宽度是相同的。桶的大小一致，桶个数不确定。
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进阶技巧：动态分桶算法应用之用户观看时长问题分析
• SQL进阶技巧：如何按照固定尺寸（固定区间）对数据进行打分类标签？
• SQL进阶技巧：如何按照某个字段对数据进行动态分桶？
• SQL进阶技巧-动态分桶算法应用之数据前后百分比问题