title: Optimal way to create a histogram/frequency distribution in Oracle?
body: <p>I have an <code>events</code> table with two columns <code>eventkey</code> (unique, primary-key) and <code>createtime</code>, which stores the creation time of the event as the number of milliseconds since Jan 1 1970 in a <code>NUMBER</code> column.</p>
  
  <p>I would like to create a "histogram" or frequency distribution that shows me how many events were created in each hour of the past week. </p>
  
  <p>Is this the best way to write such a query in Oracle, using the <code>width_bucket()</code> function? Is it possible to derive the number of rows that fall into each bucket using one of the other Oracle analytic functions rather than using <code>width_bucket</code> to determine what bucket number each row belongs to and doing a <code>count(*)</code> over that?</p>
  
  <pre><code>-- 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
  </code></pre>