我有一个events
表,它有两列eventkey
(唯一的,主键)和createtime
,它将事件的创建时间存储在NUMBER
列中,作为自1970年1月1日以来的毫秒数。
我想创建一个"直方图"或频率分布,向我显示过去一周中每小时创建了多少个事件。
这是在Oracle中使用width_bucket()
函数编写此类查询的最佳方式吗?是否可以使用其他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
如果您的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
或者,如果你正在寻找fencepost值(例如,我从第一个十分位数(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
)
我不熟悉Oracle的日期函数,但我很确定有一种等效的方式来编写Postgres语句:
select date_trunc('hour', stamp), count(*)
from your_data
group by date_trunc('hour', stamp)
order by date_trunc('hour', stamp)
与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
使用oracle提供的函数"WIDTH_BUCKET"来累积连续或精细的离散数据。以下示例显示了一种创建具有5个桶的直方图并收集510到520的"COLUMN_VALUE"的方法(因此每个桶的值范围为2)。WIDTH_BUCKET将为低于最小值和高于最大值的值创建额外的id=0和num_buckets+1个BUCKET。
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的bucket没有显示。另一方面,有低于min(510)的值,所以有一个id=0的bucket,将-inf收集到510个值。