Waldar's SQLing and Datawarehousing Place

I’m not speaking of Google Analytics – even if it’s quite awesome – I’m speaking of SQL analytic functions, or window functions.

I first met them back in 2002, when the firm I was working in upgraded their database from Oracle 7.3.4 to Oracle 8i. At first, I just didn’t understand the syntax. It felt so different from the regular SQL I was knowing about. At this time I wasn’t running after the cool SQL tricks, I was still a beginner and most of my job was to adjust some sheets within Business Objects.

I’m not going to write a tutorial or even explain the rules of those function. I’ll translate an easy problem in both classic-SQL and analytic SQL, that I did answer too on the french-speaking Développez.com forum. Continue reading ‘You Should Like Analytics’

Some weeks ago, one client of mine told me something I found really weird.
He told me that he updated the (weekly) day number of a couple of days to have a good count of working days in the month.
In fact, it was a quick and dirty way to take care of something that went in production with a flaw : the need for working days has been detected in a last minute reporting, and it was a faster solution that writing an additional interface. Continue reading ‘How A Monday Became A Sunday’

During my researches about SQL Challenge, I had an idea which I think is a fairly good one.

I had to cross join a lot of time the same value, cross join being very expensive. So I thought about quite of “faking” it, using inner join with a not so hard joining predicate. In the problematic, I had to generate the distribution of faces, using a cross join and then filtering it, join by join.

In the yesterday’s SQL, with k being the number of faces and N the number of throws, cross joins gives N^k rows, where i needed “only” (N+k-1)!/(N!*(k-1)!), which is way lower (for N=20 and k=6, it is 64.000.000 versus 53.130 rows).

To fake it, I began by identifying that my first column of data is the master. Continue reading ‘Fake Cross Join : Is Oracle Really Four Times Better Than MS SQL Server ?’

I was recently randomly browsing Laurent Schneider’s blog, and saw a link about a riddle to be solved in plain SQL. The challenge is clear, and very shiny solutions were already found by Laurent Schneider, Craig Martin, Rob van Wijk and Vadim Tropashko. You can find links to these solutions on the comments of Chen Shapira related topic.

I had the idea not to find what is the results of N*val and proba^N when properly self cross joined, but first to see how the faces are distributed when N throws have been done.

This allows me to cross join each face with the number of throw, and finding the final self value is becoming a basic multiplication. But i had problem with probabilities. Continue reading ‘NoCOUG’s First SQL Challenge !’

After chatting a while with Ozh, I crossed the line and registered today a domain name with a serious webhoster.

In 2009 this is extremly common, but it’s like I’m being proud of myself, to see my pseudo looking over the Internet and claiming : “Now, I can live” (isn’t that sound a bit geek ?).

As the past news, this one is more about the site on which I’m getting familiar with… I can also publish one more news with this nice RGB theme.