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

Question

4

我的表格：

期望结果：

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

这意味着，在包含/不包含范围内，

- gberger

你使用的是哪个数据库？ - Gordon Linoff

@GordonLinoff 通用 :) - gberger

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- gberger · Accepted Answer

在这种情况下，您的箱子大小以对数形式为2。

如果您希望使用其他箱子大小，请在下面的脚本中替换2。

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

解释

首先，我们以2为底对您的值取对数。在某些关系型数据库管理系统中，log(val, 2)可能有效，但如果无效，则请记住对数属性：log(val, 2) = ln(val) / ln(2)。

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的幂将这些向下取整的值转换为对数二进制值。

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

其余步骤只需按对数区间分组并计算。

如果您的关��型数据库不支持pow(x, y)函数，您可以使用exp(y * ln(x))。表达式变为：

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

log(0)是未定义的。在我测试过的关系型数据库管理系统中，它返回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)