Got this comment...

Got this comment the other day

How come there aren't new posts as frequently as before? Just like the free things you used, can you also tell us the Top 5 websites that you browse on a daily basis so all of us can share.

Well, the posting here comes and goes – When I have something of interest to say, I post. When I have time to say something – I post. I was curious though to see if I was posting much less then before – so of course, it is time to use the database as a big calculator.

I have been posting for about 271 days:

SQL> select sysdate-to_date('17-apr-2005','dd-mon-yyyy') dys
 2  from dual;

      DYS
----------
271.546481

In that time, I’ve made 195 posts, so there is about 1.4 days per posting on average:

SQL> select 271/195 from dual;

  271/195
----------
1.38974359

Analytics are called for now to see how this average looks over time. So I loaded up a table T with the date of each posting. Now I can compute the average days between by month, as well as the count of postings per month and even use a bit of ASCII art to visualize it all:

SQL> select m,
 2  to_char(avg(days_btwn),'99.99') days_btwn,
 3  count(*) cnt,
 4  round(100*ratio_to_report(count(*)) over (), 2) pct,
 5  lpad('*',100*ratio_to_report(count(*)) over ()/2, '*') histg
 6    from (
 7  select trunc(x,'mm') m,
 8         x-lag(x) over
 9           (partition by trunc(x,'mm') order by x) days_btwn
10    from t
11         )
12   group by m
13   order by m
14  /

M         DAYS_B        CNT        PCT HISTG
--------- ------ ---------- ---------- --------------
01-APR-05   1.00         14       7.18 ***
01-MAY-05   1.20         26      13.33 ******
01-JUN-05   1.22         24      12.31 ******
01-JUL-05   1.73         16       8.21 ****
01-AUG-05   1.36         23      11.79 *****
01-SEP-05   1.07         28      14.36 *******
01-OCT-05   1.61         19       9.74 ****
01-NOV-05   1.75         17       8.72 ****
01-DEC-05   1.33         22      11.28 *****
01-JAN-06   1.60          6       3.08 *

10 rows selected.

So, it looks like I’ve been anywhere from 1 to 1.75 days between postings – so so far for the month of January, I’m well within the “norm”. I wanted to see what this looked like without the “outliers” – April 2005 and Jan 2006 are partial months:

SQL> select m,
 2  to_char(avg(days_btwn),'99.99') days_btwn,
 3  count(*) cnt,
 4  round(100*ratio_to_report(count(*)) over (), 2) pct,
 5  lpad('*',100*ratio_to_report(count(*)) over ()/2, '*') histg
 6    from (
 7  select trunc(x,'mm') m,
 8         x-lag(x) over
 9           (partition by trunc(x,'mm') order by x) days_btwn
10    from t
11         )
12   where m not in ( to_date('01-apr-2005','dd-mon-yyyy'),
13                    to_date('01-jan-2006','dd-mon-yyyy') )
14   group by m
15   order by m
16  /

M         DAYS_B        CNT        PCT HISTG
--------- ------ ---------- ---------- --------------
01-MAY-05   1.20         26      14.86 *******
01-JUN-05   1.22         24      13.71 ******
01-JUL-05   1.73         16       9.14 ****
01-AUG-05   1.36         23      13.14 ******
01-SEP-05   1.07         28         16 ********
01-OCT-05   1.61         19      10.86 *****
01-NOV-05   1.75         17       9.71 ****
01-DEC-05   1.33         22      12.57 ******

8 rows selected.

Just goes to show that Analytics are obviously a consume oriented feature of the database – everyone could use these things! (Analytics Rock, They Roll – if you aren’t using them, you aren’t using Oracle).

So, the number of postings really hasn’t changed significantly over time – but this week was a busy week for me. I’ve been traveling around (can you guess where I am:

should be an easy one), doing on site visits with customers. Plan on heading home tomorrow morning.

As for the top five websites, well, that changes – but some that I am on every day:

• http://www.google.com/
• http://asktom.oracle.com/ (of course)
• http://news.google.com/
• http://metalink.oracle.com/
• various blogs (via RSS feeds) – see the sidebar for a list.