在Oracle中创建直方图/频率分布的最佳方法是什么？

Question

我有一个包含两列eventkey（唯一主键）和createtime的events表，它将事件的创建时间存储为自1970年1月1日以来以毫秒为单位的数字，存储在一个NUMBER列中。

我想创建一个“直方图”或频率分布，显示过去一周内每个小时创建了多少个事件。

这是在Oracle中编写此类查询的最佳方法吗？是否可以使用其他Oracle分析函数之一推导出落入每个桶中的行数，而不是使用width_bucket()函数确定每行属于哪个桶号并对其进行count(*)?

-- 1305504000000 = 5/16/2011 12:00am GMT
-- 1306108800000 = 5/23/2011 12:00am GMT
select 
timestamp '1970-01-01 00:00:00' + numtodsinterval((1305504000000/1000 + (bucket * 60 * 60)), 'second') period_start,
numevents
from (
  select bucket, count(*) as events from (
    select eventkey, createtime, 
    width_bucket(createtime, 1305504000000, 1306108800000, 24 * 7) bucket
    from events 
    where createtime between 1305504000000 and 1306108800000
  ) group by bucket
) 
order by period_start

- matt b

Answer 1

如果您的createtime是日期列，那么这将是微不足道的：

SELECT TO_CHAR(CREATE_TIME, 'DAY:HH24'), COUNT(*) 
  FROM EVENTS
 GROUP BY TO_CHAR(CREATE_TIME, 'DAY:HH24');

就目前而言，将createtime列进行转换并不太难：

select TO_CHAR( 
         TO_DATE('19700101', 'YYYYMMDD') + createtime / 86400000), 
         'DAY:HH24') AS BUCKET, COUNT(*)
   FROM EVENTS
  WHERE createtime between 1305504000000 and 1306108800000
 group by TO_CHAR( 
         TO_DATE('19700101', 'YYYYMMDD') + createtime / 86400000), 
         'DAY:HH24') 
 order by 1

如果你想寻找栏杆值（例如，从第一个十分位数（0-10％）到下一个十分位数（11-20％）的跳转点），则可以尝试以下方式：

select min(createtime) over (partition by decile) as decile_start,
       max(createtime) over (partition by decile) as decile_end,
       decile
  from (select createtime, 
               ntile (10) over (order by createtime asc) as decile
          from events
         where createtime between 1305504000000 and 1306108800000
       )

- Adam Musch

---

这个很好用，谢谢。不确定为什么我一开始没有想到简单地截取日期，我想我太专注于解析和转换这种奇怪的“日期”格式了。 - matt b

有没有办法保留创建时间为零的行？ - Brett VanderVeen

Answer 2

我对Oracle的日期函数不熟悉，但我相信有一种等效的方法可以编写这个Postgres语句：

select date_trunc('hour', stamp), count(*)
from your_data
group by date_trunc('hour', stamp)
order by date_trunc('hour', stamp)

- Denis de Bernardy

---

在PG中完美运行！速度也非常快。 - George L

Answer 3

基本上与Adam的回答相同，但是我更喜欢将period_start保留为时间字段，以便在需要进一步过滤时更容易。

with
events as
(
    select rownum eventkey, round(dbms_random.value(1305504000000, 1306108800000)) createtime
    from dual
    connect by level <= 1000 
)
select
    trunc(timestamp '1970-01-01 00:00:00' + numtodsinterval(createtime/1000, 'second'), 'HH') period_start,
    count(*) numevents
from
    events
where
    createtime between 1305504000000 and 1306108800000
group by
    trunc(timestamp '1970-01-01 00:00:00' + numtodsinterval(createtime/1000, 'second'), 'HH')
order by
    period_start

- Craig

---

你能解释一下 with events as () 的作用，以及为什么要选择随机值吗？我对 Oracle 语法不是很熟悉。 - matt b

抱歉...由于我没有您的数据表来运行查询，因此我正在生成随机数据来模拟可能在您的表中的内容。 "with events"语句只是允许我将该查询别名为"events"，以便查询的其余部分将与您可以直接针对事件表使用而不进行任何更改的内容相匹配。 对于您的目的，只需删除“select trunc（....”上面的所有内容。 - Craig

啊，谢谢，我明白了，这在这种类型的答案中会很有用 :) - matt b

Answer 4

使用Oracle提供的函数“WIDTH_BUCKET”来累积连续或细分散数据。以下示例展示了创建具有5个桶的直方图的方法，并从510到520收集“COLUMN_VALUE”（因此每个桶都获得范围为2的值）。WIDTH_BUCKET将为最小值以下和最大值以上的值创建额外的id = 0和num_buckets + 1个桶。

SELECT "BUCKET_ID", count(*), 
CASE
    WHEN "BUCKET_ID"=0 THEN -1/0F
    ELSE 510+(520-510)/5*("BUCKET_ID"-1)
END "BUCKET_MIN",
CASE
    WHEN "BUCKET_ID"=5+1 THEN 1/0F
    ELSE 510+(520-510)/5*("BUCKET_ID")
END "BUCKET_MAX"
FROM 
(
    SELECT  "COLUMN_VALUE", 
            WIDTH_BUCKET("COLUMN_VALUE", 510, 520, 5) "BUCKET_ID"
    FROM "MY_TABLE"
)
group by "BUCKET_ID"
ORDER BY "BUCKET_ID";

示例输出

 BUCKET_ID   COUNT(*) BUCKET_MIN BUCKET_MAX
---------- ---------- ---------- ----------
         0         45       -Inf   5.1E+002
         1        220   5.1E+002  5.12E+002
         2        189  5.12E+002  5.14E+002
         3         43  5.14E+002  5.16E+002
         4          3  5.16E+002  5.18E+002

在我的表中，没有518-520这个范围，因此ID为5的桶不会显示。另一方面，存在小于最小值（510）的值，因此存在一个ID为0的桶，收集-inf到510的值。

- hychou