Archive February 2010

Important News! On the American Sci-fi channel on Saturday, there are three movies in a row that must, based on their names, be fantastic. "Trans-morphers: Fall of Man", "The Terminators" and "I Am Omega".

I picked up a leaflet in a bank the other day, while waiting far too long for service, entitled "Make Peace With Your Money". I expected it to be retarded, but the degree to which it was retarded was still a surprise.

"Because unemployment lasts longer than in years past, instead of a 3 month emergency fund you should now have 6 months, or even 12 months if you have dependents. If the budgeting strategies on page 3 won't get you there quickly enough, you may have to take more drastic action, like temporarily redirecting 401(k) contributions that you're making over your company's match." Seriously? What tiny proportion of the population is the target audience of this advice?

"The percentage of your portfolio that should be in stocks is now 110 minus your age, rather than the 100 minus your age that it used to be." Take note, newborns, 110% of your portfolio should be in stocks. Ten year olds, 100% of your portfolio in stocks. 100 year olds, 10% of your portfolio in stocks! Gotta save for the future!

There was something in there about avoiding stressing your relationship; that you shouldn't be arguing over "you bought $150 golf shoes!" but rather "how can we save $150 more a month." I think you'll find that you should, in fact, be arguing over your partner buying $150 golf shoes if you're needing to save an extra $150 a month because stopping that is the obvious way to do it. And again, $150 golf shoes? Target audience?

To figure out how big a nest egg you'll need for retirement, insert your ideal annual income into this formula: ____ * 30. What the hell is going on here? My ideal annual income is a trillion dollars, so apparently I need a 30 trillion dollar nest egg. Or maybe you mean my actual annual income, in which case how am I supposed to save up 30 times my annual income? Do I spend none of my income for 30 years, or maybe 'only' half of it while working for 60 years? Since neither of those are plausible, perhaps the bank is suggesting that we each need to rob a bank before we retire?

Oh hey, the leaflet is online! [14:14] [2 comments]

As of a couple of weeks ago, Jessica and I are engaged.

In other news that someone else might care about and that I might want to refer back to at some point, I found a reasonably quick way of putting an approximate percentile into a big SQL table (in mysql at least). The solution is:

SET @count=(SELECT COUNT(*) FROM table);
SET @row=0;
UPDATE table SET percentile=(@row:=@row+1)/@count ORDER BY whatever;

That should be *100 for an actual percentile, but I prefer to work with the real number. If you want the top one of 5 values to be 80th percentile rather than 100th then row should start at -1 rather than at 0, and similarly if you want the percentiles to never be 0 or 100 then @row starts at 0 and @count should be count(*)+1.

Main flaw in this model is that tied values don't get the same percentile, but rather a distribution of percentiles through the number of ties, ordered by some indeterminate secondary criterion (or a determinate one if you supply a second value for ORDER BY). For my purposes that's fine. It could be resolved by adding one or two more procedural variables and using an IF function. Certainly beats the solutions I've seen most online.

Actually, since this is apparently a pretty common google search and if someone finds my solution there'll surely be someone who'd ask "how would you do it for same-level with IFs?" I made a quick test for proof of concept. Here we go.

SET @count=(SELECT COUNT(*) FROM test);
SET @row=-1;
SET @last=0;
SET @lastperc=0;
UPDATE test SET percentile=IF(number=@last, -- if the number is the same as last time
IF((@row:=@row+1)!='x',@lastperc,0), -- increase row, set percentile to @lastperc
IF((@last:=number)!='x',(@lastperc:=((@row:=@row+1)/@count)),0) -- else set @last to this number, set @lastperc to new percentile, increase row
) ORDER BY number;

This operates on a table named 'test' with columns 'number' and 'percentile', and gives results where the percentile is "percentage of values that are lower", eg.

+--------+------------+
| number | percentile |
+--------+------------+
|      1 |          0 |
|      4 |        0.2 |
|      4 |        0.2 |
|     13 |        0.6 |
|     17 |        0.8 |
+--------+------------+

Also of note, if you're doing this in PHP or Perl then the 'SET' queries must each be done as a separate query before the main one. Probably best to remove the comments too for putting it in through a program.

I assume there's a better way of doing "update this procedural variable and don't incorporate it into the output in any way just now" than my horrible use of setting the variable and then comparing it against 'x' in an IF that thus goes on to be ignored, but I don't know what that way would be. Regardless of that horribleness, it remains that this is a bucketload faster than what seems to be the common standard method, "UPDATE test SET percentile=(SELECT COUNT(*) FROM test AS b WHERE b.number<test.number)/$count" (I'm allowing $count from a PHP or something because without that it's even more horrendous.) [17:35] [2 comments]