Thursday, February 08, 2007

Psycho SQL

Back in the old days on an AS/400 with DB2/400, we didn't have the luxury of a CASE statement or IF or DECODE and performance was at a premium when attacking a gigantic detail table. We needed a way to provide real time performance on huge tables summarizing values by quarter without the benefit of a temporary work table or sub-queries. The AS/400 did not have "if-logic" but it had MIN(value1,value2), MAX(value1,value2) and absolute value (ABS(value)) so I cheated and used those functions with some math to produce the result set.

The goal for each quarterly column was to reduce the month (m in examples below) down to a zero or a one (false and true) then multiply that times the quantity and amount being totalled (value). Zero (false) when multiplied would produce a zero and therefore not add anything to the SUM. One (true) when multipled would produce the same number and be added to the SUM.


SELECT grouping_code
, SUM(value*(1-MIN(MAX(m-3,0),1)))
, SUM(value*(1-MIN(MAX(ABS(m-5)-1,0),1)))
, SUM(value*(1-MIN(MAX(ABS(mm-8)-1,0),1)))
, SUM(value*(1-MIN(MAX(ABS(mm-11)-1,0),1)))
FROM large_freakin_detail_table
WHERE criteria = 'whattheywerelookingfor'
GROUP BY grouping_code

So you are probably wondering what the blue heck all that SUM MIN MAX ABS crap is doing. Sometimes it is best to look at results. Assume the left-most column is the month (1 through 12) and see how that number is transformed into a true (1) or false (0) through the magic of math.


SUM(value*(1-MIN(MAX(m-3,0),1)))

m-3 MAX MIN 1-r
1 -2 0 0 1
2 -1 0 0 1
3 0 0 0 1
4 1 1 1 0
5 2 2 1 0
6 3 3 1 0
7 4 4 1 0
8 5 5 1 0
9 6 6 1 0
10 7 7 1 0
11 8 8 1 0
12 9 9 1 0

The first quarter is simple: subtract 3 (March) then take the highest of that value or zero. The 0 is higher than -2, -1 and 0, but not higher than 1, 2, etc. Now we have a zero in months 1, 2 and 3 (1st quarter). By looking for the lowest value between 1 and the last result you will get a 1 for 1 or more but the zeroes will stay 0. Subtract the result from 1 (0 = 1, 1 = 0) and now you have the true/false value to multiply against an amount to either zero it or not before adding it to the SUM. Tricky, eh?


SUM(value*(1-MIN(MAX(ABS(m-5)-1,0),1)))

# m-5 ABS r-1 MAX MIN 1-r
1 -4 4 3 3 1 0
2 -3 3 2 2 1 0
3 -2 2 1 1 1 0
4 -1 1 0 0 0 1
5 0 0 -1 0 0 1
6 1 1 0 0 0 1
7 2 2 1 1 1 0
8 3 3 2 2 1 0
9 4 4 3 3 1 0
10 5 5 4 4 1 0
11 6 6 5 5 1 0
12 7 7 6 6 1 0

The remaining quarters are more difficult but by using the absolute value (the positive value) you can eventually get the number you are looking for.


SUM(amount*(1-MIN(MAX(ABS(mm-8)-1,0),1)))

# m-8 ABS r-1 MAX MIN 1-r
1 -7 7 6 6 1 0
2 -6 6 5 5 1 0
3 -5 5 4 4 1 0
4 -4 4 3 3 1 0
5 -3 3 2 2 1 0
6 -2 2 1 1 1 0
7 -1 1 0 0 0 1
8 0 0 -1 0 0 1
9 1 1 0 0 0 1
10 2 2 1 1 1 0
11 3 3 2 2 1 0
12 4 4 3 3 1 0

This query used one sweep through the gigantic table and one sort for the group by. Grouping on month would have added an additional sort so that was not an option.

Tuesday, February 06, 2007

George Carlin's new rules for 2007

  1. Stop giving me that pop-up ad for classmates.com! There's a reason you don't talk to people for 25 years. Because you don't particularly like them! Besides, I already know what the captain of the football team is doing these days -- mowing my lawn.
  2. Don't eat anything that's served to you out a window unless you're a seagull. People are acting all shocked that a human finger was found in a bowl of Wendy's chili. Hey, it cost less than a dollar. What did you expect it to contain? Lobster?
  3. Stop saying that teenage boys who have sex with their hot, blonde teachers are permanently damaged. I have a better description for these kids: "Lucky bastards."
  4. If you need to shave and you still collect baseball cards, you're a dope. If you're a kid, the cards are keepsakes of your idols. If you're a grown man, they're pictures of men.
  5. Ladies, leave your eyebrows alone. Here's how much men care about your eyebrows: Do you have two of them? Okay, we're done.
  6. There's no such thing as flavored water. There's a whole aisle of this crap at the supermarket, water, but without that watery taste. Sorry, but flavored water is called a soft drink. You want flavored water? Pour some scotch over ice and let it melt. That's your flavored water.
  7. Stop screwing with old people. Target is introducing a redesigned pill bottle that's square, with a bigger label. And the top is now the bottom. And by the time grandpa figures out how to open it, his ass will be in the morgue. Congratulations, Target, you just solved the Social Security crisis.
  8. The more complicated the Starbucks order, the bigger the asshole. If you walk into a Starbucks and order a "decaf grande, half-soy, half-low fat, iced vanilla, double-shot, gingerbread cappuccino, extra dry, light ice, with one Sweet-n'-Low, and one NutraSweet," ooh, you're a huge asshole.
  9. I'm not the cashier! By the time I look up from sliding my card, entering my PIN number, pressing "Enter," verifying the amount, deciding, no, I don't want cash back, and pressing "Enter" again, the kid who is supposed to be ringing me up is standing there eating my Almond Joy.
  10. Just because your tattoo has Chinese characters in it doesn't make you spiritual. It's right above the crack of your ass. And it translates to "beef with broccoli." The last time you did anything spiritual, you were praying to God you weren't pregnant. You're not spiritual. You're just high.
  11. Competitive eating isn't a sport. It's one of the seven deadly sins. ESPN recently televised the U.S. Open of Competitive Eating, because watching those athletes at the poker table was just too damned exciting. What's next, competitive farting? Oh wait. They're already doing that. It's called "The Howard Stern Show."
  12. I don't need a bigger mega M&Ms. If I'm extra hungry for M&Ms, I'll go nuts and eat two.
  13. If you're going to insist on making movies based on crappy, old television shows, then you have to give everyone in the Cineplex a remote so we can see what's playing on the other screens. Let's remember the reason something was a television show in the first place is that the idea wasn't good enough to be a movie.
  14. No more gift registries. You know, it used to be just for weddings. Now it's for babies and new homes and graduations from rehab. Picking out the stuff you want and having other people buyit for you isn't gift giving, it's the white people version of looting.
  15. And this one is long overdue: No more bathroom attendants. After I zip up, some guy is offering me a towel and a mint like I just had sex with George Michael. I can't even tell if he's supposed to be there, or just some freak with a fetish. I don't want to be on your web cam, dude. I just want to wash my hands.
  16. When I ask how old your toddler is, I don't need to hear "27 months." "He's two" will do just fine. He's not a cheese. And I didn't really care in the first place.
  17. If you ever hope to be a credible adult and want a job that pays better than minimum wage, then for God's sake don't pierce or tattoo every available piece of flesh. If so, then plan your future around saying, "Do you want fries with that?"