sql - 如何在 SQL 中制作对数直方图？

Question

我的表:

val
1
2
3
4
5
6
10
15

预期结果:

bin | qty 
1   | 1
2   | 2
4   | 3
8   | 3

这意味着有包含/不包含范围，

1 1-2 之间的值，

2-4 之间的 2 个值，

4-8 之间的 3 个值，

8-16 之间的 3 个值。

Answer 1

在这种情况下，您的 bin 大小(以对数形式表示)为 2。

如果您希望使用其他 bin 大小，请替换以下脚本中的 2s。

select 
    pow(2, floor(ln(val) / ln(2))) as bin,
    count(bin) as qty
from 
    mytable
group by
    bin;

解释

首先，我们将您的值记录在基数 2 中。 log(val, 2) 可能适用于某些 RDBMS，但如果不是，请记住 log(val, 2) = ln(val) / ln(2) 的 log 属性。

val | ln(val) / ln(2)
1   | 0
2   | 1
3   | 1.58496250072
4   | 2
5   | 2.32192809489

然后我们把这个:

val | floor(ln(val) / ln(2))
1   | 0
2   | 1
3   | 1
4   | 2
5   | 2

最后，我们使用 2 的幂将这些下限值转换为对数 bin 值。

val | pow(2, floor(ln(val) / ln(2)))
1   | 1
2   | 2
3   | 2
4   | 4
5   | 4

其余的只是按对数箱和计数进行分组。

注意事项

没有战俘

如果您的 RDBMS 不支持 pow(x, y) ，您可以使用 exp(y * ln(x)) 。然后表达式变为:

    exp(floor(ln(val) / ln(2)) * ln(2))

零

log(0) 未定义。在我测试的 RDBMS 中，它返回 null。

如果您的表的值为 0，您很可能希望将它们放在 0 和 1 之间。为此，您可以使用 ifnull(..., 0) 包装整个表达式，如下所示:

    ifnull(pow(2, floor(ln(val) / ln(2))), 0)

消极的

负数的对数未定义...但您可能希望将它们归类为 [0 到 -1)、[-1 到 -2)、[-2 到 -4)、[-4 到 -8) 等” .

如果您的数据库具有负值，您可以通过首先在您的值中使用 abs 来实现该分箱，然后最后通过将结果乘以 val/abs(val) 来恢复其原始信号。然后你的表达变成:

pow(2, floor(ln(abs(val)) / ln(2))) * val/abs(val)

负数和零

如果您的数据库同时具有负值和零值，则应将 ifnull 包裹在其他所有内容中。否则， val/abs(val) 部分会让你除以零，重新引入空值。

ifnull(pow(2, floor(ln(abs(val)) / ln(2))) * val/abs(val), 0)