oracle收集直方图,sql – 在Oracle中创建直方图/频率分布的最佳方法？

如果您的创建时间是日期列,那么这将是微不足道的：

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

)