Let’s get this out of the way right now: I am SO going to take advantage of this T-SQL Tuesday on aggregates to talk about OVER() and PARTITION BY. Aggregates and OVER go together like french fries and catsup…sure, you can have them apart, but together they’re just magical.
Note: If you’re not at all familiar with OVER (PARTITION BY) at all, take a quick look at my brief intro, Tip: Over and Partition By.
In my T-SQL Brush-up session (also known as the Forgotten SQL session), I walk my way through fairly random examples set in the AdventureWorks database, and miraculously end up somewhere sensible.
Wow…the inagural 2011 T-SQL Tuesday was so big, it lasted a week! A few quick observations, then on to the blog posts:
According to my terribly scientific observations of T-SQL Tuesday, the third most common techie resolution for 2011 is MCM prep! The number two most common techie resolution is….Learning Powershell. As well it should be.
The number one most common resolution is a near universal desire to give back more to the SQL community. Roughly 90% who participated in this event resolved to blog more, speak more, help more on Twitter and other places, volunteer more…it’s astounding. I don’t suppose I should add much to this already 2,000+ word post, but I’d just like to say how deeply pleased I am that SO many of us are improving ourselves through what is, essentially, a communal charity of knowledge. Cheers to you, Mr. and Ms. SQL Blogger. I salute you all!
In my invitation to T-SQL Tuesday #014, I picked the topic “Resolutions”, clearly to play on the new year. The only thing I have right now that could count as a New Year’s resolution is the RTFM365 project…it’s certainly going to take some stick-to-it-iveness to finish out a year of reading from BOL every day. The rest of what I have are just ongoing life goals. So why did I pick “Resolutions”, then, if I have nothing to say?
The invitation blog gave a few brief suggestions of what to write about, but the interesting thing is how many different ways you can run with the word. When I looked at the Merriam-Webster entry for resolution, a few of the definitions stuck out as a sort of common theme:
a: the act of analyzing a complex notion into simpler ones …
e: the separating of a chemical compound or mixture into its constituents
f (1): the division of a prosodic element into its component parts (2): the substitution in Greek or Latin prosody of two short syllables for a long syllable g: the analysis of a vector into two or more vectors of which it is the sum
…[and a bunch more.]
Analyzing a complex notion into simpler ones. Hmm, why does that sound familiar? Familiar in a kind of SQL Servery way….
“Hey, let’s throw together a database to track our sales conferences!” Uh, sure boss…that simple statement has the mass and temperament of 400 angry gorillas at a monster truck rally. In an earthquake.
My job is now to take this complex idea and break it down into its simpler pieces – the data we need to store, the rules that hold the data together, the procedures that we need to run on it. Once that’s done, everything should be oh so simple!
But of course, it’s not.
“Hey, can you get me a list of the people that’ve attended any of the conferences in the last three years? Not counting the webconferences. Or HR personnel. And we’ll need their company and contact info.”
Again, with the “simple”-complex requests. And again, I can’t necessarily just sit down and bang out a query. Depending on the database architecture and the request, I might need to start with two or three queries to get different sets of data, and figure out how to link them together:
I’ll get the set of all conferences in the last three years. If I can, I’ll limit that to non-webconferences here…alternately, I can get a list of webconference IDs and do a SELECT conference
WHERE ID NOT IN (SELECT ID from Webconferences)….
(That’s fairly lousy pseudocode, but I’m assuming you’ll go with me on this.)
I’ll also pull a list of individuals (with related info) and filter based on their job category.
Aaaaand then link these together.
There. Database: designed. Ability to break a request into logical chunks: achieved. Now it’s easy street!
Oh, ya think so, do ya?
“Okay, Jane.” [My name’s Jen, sir.] “Could you get us a report for Bob? He needs numbers on attendance over time, categories, that kind of thing.”
Well, first of all, it’s time to go talk to Bob. What will he be using this for? Is this something he’s going to sort and calculate on himself, or feed into something else? Well then, Excel or CSV. Something presentable? Well then, PDF or Word.
And what questions is he trying to answer with this report? That’ll really drive the data and the presentation. In the end, we might have broken this down to something like this:
Needs something in a handout format
Prefers graphics – pie and line charts – over lists of numbers.
Wants attendance by company (not individual) over time – that is, how many events attended per company per month, over the last five years.
What this all boils down to is something I’ve heard Itzik Ben-Gan (blog) say more than once: a given problem breaks down into smaller, simpler, often reusable parts. And now we’ve learned a word for that, boys and girls: Resolution!