Friday, January 13, 2006

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:
Photo_011306_001
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:

POST A COMMENT

27 Comments:

Blogger Bill S. said....

Had to do a quick google to make sure I was right (but I was) -
San Francisco.

Yup, easy one. :-D

ujbtx - "You Just Bombed Transaction"?

Fri Jan 13, 01:28:00 PM EST  

Anonymous John Hurley said....

Is that Cleveland?

Fri Jan 13, 01:32:00 PM EST  

Anonymous Anonymous said....

Tom,
Welcome to San Francisco.

Didn't see either http://espn.go.com or http://finance.yahoo.com on your list !!!

Fri Jan 13, 01:36:00 PM EST  

Blogger Thomas Kyte said....

It is indeed San Francisco, right by the Moscone Center. Window is a bit dirty and I forgot my real camera so you are seeing a fuzzy shot of the bay bridge.

Fri Jan 13, 01:38:00 PM EST  

Anonymous Anonymous said....

Yep.. you probably are at 2nd street and Market in the downtown.
Are you at the Oracle Education centre across the street ?


If only you had your 'real' camera and zoomed it, you would see me working in that picture :-)

Fri Jan 13, 02:15:00 PM EST  

Blogger KD said....

Yep
THAT's SF. I was there a few months ago. Exciting place.
Hey Tom,
are you still working on the "expert one-on-one" series?

Talking to you (almost as if) for the first time. So had to throw this question in here.

Fri Jan 13, 02:51:00 PM EST  

Anonymous Anonymous said....

> It is indeed San Francisco

That is funny - I was going to guess Cleveland too, and the two cities couldn't be more different!

sPh

Fri Jan 13, 03:11:00 PM EST  

Blogger Bill S. said....

For me, the bay bridge was a big clue but I wasn't absolutely sure until I checked up on that tan building in the lower left-hand corner (Academy Of Art University) - that one confirmed it for me.

:-D

Fri Jan 13, 03:24:00 PM EST  

Blogger scribbler said....

Tom ,

Way to proove your point and put that beautiful Feature of Analytics in action to provide #'s..i just loved reading it .

On n Off , i visit ur site to pick up valuable tips and its been a good learning experience .

LBNL , Happy New Yr !

blogistu

Fri Jan 13, 04:33:00 PM EST  

Blogger scribbler said....

Tom ,

Way to proove your point and put that beautiful Feature of Analytics in action to provide #'s..i just loved reading it .

On n Off , i visit ur site to pick up valuable tips and its been a good learning experience .

LBNL , Happy New Yr !

blogistu

Fri Jan 13, 04:35:00 PM EST  

Anonymous Andrew said....

Haning out in Sam Fran's Disco. What a blast.

Fri Jan 13, 04:38:00 PM EST  

Blogger Alberto Dell'Era said....

Second version of Raptor early adopter Release has been released (11th of January) - thought it was interesting to note.

-ugment (cool!)

Fri Jan 13, 06:57:00 PM EST  

Blogger Roderick said....

I'll guess picture was taken closer to 3rd and Mission or even from the SF Marriott.
Did you have a little utility to do the data entry of the 195 rows?

Word Verification: vtwmdod

Fri Jan 13, 08:47:00 PM EST  

Blogger Roderick said....

Oh and what was the longest gap between postings?

Fri Jan 13, 08:57:00 PM EST  

Blogger Rich said....

This was a pretty funny entry (or perhaps it's just Friday and I'm exhausted and slap happy).

Thanks for showing the Analytic functions in a "real world" scenario.

Rich

Fri Jan 13, 08:59:00 PM EST  

Anonymous Anonymous said....

Enjoyed your analytics example.
-Mike

Sat Jan 14, 12:07:00 AM EST  

Anonymous Gabe said....

Since I know you’ve been consistently publishing articles here, I’m sure this won’t matter … in as far as the actual average is concerned. Your query, with the lag applied after partitioning, is essentially ignoring the time between the beginning of a month and the first article of that month, as well as the time between the last article and the end of the month. A month with 3 articles on, say 14,15 and 16, will show an average of 1 day … which seems hardly correct.

Sat Jan 14, 08:32:00 PM EST  

Anonymous Markku Uttula said....

First of all (out of shere interest); does blogger.com r do you yourself put your postings on an Oracle DB or does blogger.com offer you an access to a database (or do you have an...) where you can do these checks of yours?

On the picture; that sure looks lot like the San Francisco/Oakland Bay bridge (aka. "The Golden Gate") viewed from the east. I must point out that I've never actually visited SF (though it is on my personal "to-do"-list) and because the bridge looks the same when viewed from southeast as it does when viewed from the northwest ... well; I'd hazard a guess that this picture is from the southeast (in proportion with the bridge)... hope I'm not way off line here :)

Sat Jan 14, 10:26:00 PM EST  

Anonymous Markku Uttula said....

does blogger.com r do you yourself put your postings

Hmm... looks a lot like I failed to check my spelling and/or ideas here... i *was* supposed to say "does blogger.com offer you access to their (Oracle-)database or do you put your postings"... on a database yourself.

Sorry for my posting; it's saturday and I (sure as "hell") have been "celebrating" the time off from work :) It still is (or at least appears to be) a long way until monday morning :D

Sat Jan 14, 10:35:00 PM EST  

Blogger Stephen Booth said....

Markku,

It shouldn't be too hard, especially not for a genius of Tom's calibre, to come up with some code to parse the Atom feed from Blogger (or an RSS feed via a service like feedburner, such as Tom uses). Looking at the RSS feed of Tom's blog the <pubDate> and <link> items look like good candidates for primary key with <lastBuildDate> being useful for versioning.

The site LiveJournal.com (a blogging site) takes RSS and ATOM feeds and caches them in a MySQL database for rendering as parts of members' "Friends" pages.

Sun Jan 15, 09:08:00 AM EST  

Blogger Thomas Kyte said....

Since I know you’ve been consistently publishing articles here

that is why I added the count as well with the histogram - to help aid in deciding whether that was relevant.

I was faced with chosing between

a) no partitioning - but then what month does such a large lag go into - that if, if I didn't do anything between 15-sep and 15-oct - which month would get that large lage

b) ignoring those and hoping the by month counts would be consistent (which they showed they have pretty much been consistent - 10-15 a month)

I thought about that for a bit. I guess the hardest part with analyzing data is getting the question you want to ask right :)

Sun Jan 15, 10:07:00 AM EST  

Blogger Thomas Kyte said....

or does blogger.com offer you an access to a database

Blogger simply gives me a user interface in which to input information and they store it "somewhere, somehow". I'm sure they use a database of some sort - which one, how they use it - no idea.

As to the picture, that is the bay bridge - very different from the golden gate though! The bay bridge is a double decker - whereas the golden gate is a single layer bridge.

If you enter "san francisco" into maps.google.com, the bay bridge will be to the right and the golden gate to the top of the map (route 1)

Sun Jan 15, 10:14:00 AM EST  

Anonymous Markku Uttula said....

As to the picture, that is the bay bridge - very different from the golden gate though!

Oops, my bad... As I said, I've never been to San Francisco, and I've always been under the impression they're one and the same. :(

I took a look at some pictures, and I must say that only way I could tell them apart was by the supports that appear to be horizontal on the Golden Gate, and X-diagonal in the Bay Bridge. However, I must say that I'm very poor with identifying sights; it wouldn't take a leap of imagination to say that I'd be able to get the Golden Gate mixed with the Tower Bridge if they weren't on different continents :)

Sun Jan 15, 03:49:00 PM EST  

Anonymous Anonymous said....

can u post what alot of the main sayings r

Mon Feb 05, 04:36:00 PM EST  

Blogger Thomas Kyte said....

the main saying is

"Using text speak - one letter abbreviations for real words - is a horrible habit that must be stopped"

Mon Feb 05, 04:44:00 PM EST  

Anonymous Anonymous said....

This was a very funny :)

This is the Analytic functions. Realy LOL.

Mon Mar 05, 01:08:00 PM EST  

Blogger zxcvd said....

Cheap WOW GOld, Please look here
Buy wow GoLd,
World of Warcraft WOw golD and woW gOld money,
wOw Gold.Acquista WOw gold oro,
wow goldSoldi, WOw GOld to each loyal and
woW goLD siamo uno dei miglioriWoW gold which is very cheap.WOw gold

Sun Aug 03, 11:15:00 PM EDT  

POST A COMMENT

<< Home