时间戳

首页 > 解决方案 > 替代 CASE WHEN 从列创建 bin

sql - 替代 CASE WHEN 从列创建 bin

问题描述

我有一个表，其中列 ( COL1) 的值可以从 0 到 1（含）。在本专栏中，我想创建一个COL1使用预定义范围进行分类的新列（即创建箱）。当类别/箱的数量很少时，可以使用 轻松完成CASE WHEN，例如：

SELECT
       CASE WHEN COL1 <= 0.2 THEN 1
            WHEN COL1 > 0.2 AND COL1 <= 0.4 THEN 2
            ....
            WHEN COL1 > 0.8 AND COL1 <= 1 THEN 5
       END AS COL1_bin
FROM   Table

因为我想要 100 个 bin，所以我正在寻找一种使用函数更“自动”地执行此操作的方法，例如，我可以在其中给出最小值、最大值和 stepsize / binsize（例如，min = 0，max = 1 , 步长 = 0.01)

我在 HiveQL 中执行此操作，它应该与 MySQL 非常相似。

标签： sqlhivehiveql

解决方案

这是一种方法。如果假设您希望 bin 范围为 0.1,0.2,0.3,... 直到 1。然后您将使用块 row_gen 并将 step_size 设置为 row_number/10 生成 10 行，并且还通过将 lower_limit 和 upper_limit 设置为每个垃圾箱。

之后，您需要检查 col1 的值是否符合 bin 的相应下限和上限，如下所示。（我使用左连接来包括 col1 值为空的那些条件。如果这不是可能的情况，则可以将其更改为常规连接）

with row_gen 
  as (select top 10 
             ,row_number() over(order by 1) as bin_id
             ,row_number() over(order by 1)/10 as as lower_limit
             ,row_number() over(order by 1)/10 + 1/10 as as upper_limit
        from table /*any table that has 10 rows*/ 
      )
   select a.*
          ,b.bin_id
     from your_table a
left join row_gen b
       on a.col1 between b.lower_rnk and b.upper_rnk

推荐阅读