The Blog of Maxim Porges

Archive for October, 2009

  • Blackberry’s Days as the Fruitiest Phone Are Numbered

    As Tyler Durden once said, on a long enough timeline the survival rate for everyone drops to zero. He was talking about the Blackberry.

    If these numbers are to be believed, Apple is totally killing it in gaining smartphone market share. In a few short years they have reached 30% of the smartphone market compared to BB’s 40%. You know the Blackberry is going down over the long term.

    Having owned both phones, I must say I really liked the Blackberry when I had one, but the iPhone is simply awesome (although in fairness I haven’t touched an updated BB model – the last time I touched one was two years ago). It’s the apps, baby. One thing I can say in positive terms about the BB is that although I’ve gotten used to the iPhone’s touch-only keyboard and can blast out an email in short order, I think I was still slightly faster on the BB.

    AAPL just took a massive hit today, so it’s going to be time to get back in soon before they unleash the iPhone in China.

    2009.10.28 / no responses / Category: Uncategorized

  • Highwinds Described as “Internet Superpower”

    Some say there’s a new cold war brewing; data centers are cold, after all. Not to worry – Highwinds is already working on cyberdecks, so we’ll be ready to save the world when the time comes.

    2009.10.13 / 2 responses / Category: Uncategorized

  • Calculating the Differences Between Consecutive Rows with SQL

    Just a quickie. I’m sure this is obvious to the seasoned SQL freaks out there, but it was a new solution for me.

    We were recently trying to figure out a simple query to generate a set of differences in values between sets of consecutive rows in a database table. Basically, the table is full of timestamp snapshots with associated values, and we need to calculate the differences between the values in each snapshot for a time period.

    The simple solution I came up with is to normalize the dates, and then use them as a join criteria with a virtual table and some simple date math. You treat the original unmodified table as one side of the join, and a virtual select of the same table with some date math applied to the normalized dates as the other side of the join. So, for tables with timestamps five seconds apart, you simply pull the date back by five seconds on the virtual table and join the dates together. You end up with a single row joining each row with its previous peer, and you can then do direct math within the row to calculate the differences in values.

    Here’s a simple MySQL script that demonstrates the technique, which will probably make more sense than my explanation.

    CREATE TABLE consecutiveDates (
  id INT NOT NULL AUTO_INCREMENT,
  valueDate TIMESTAMP NOT NULL,
  value NUMERIC(10) NOT NULL,
  PRIMARY KEY (id)
)
 
INSERT INTO consecutiveDates VALUES (NULL, TIMESTAMP('2009-08-01 10:00:00'), 10);
INSERT INTO consecutiveDates VALUES (NULL, TIMESTAMP('2009-08-01 10:00:05'), 25);
INSERT INTO consecutiveDates VALUES (NULL, TIMESTAMP('2009-08-01 10:00:10'), 50);
 
SELECT    actual.*,
          virtual.*,
          IFNULL((virtual.virtualValue - actual.value), 0) AS 'difference'
FROM      consecutiveDates actual
          LEFT OUTER JOIN (
             SELECT   id AS 'virtualId',
                      valueDate AS 'actualVirtualDate',
                      value AS 'virtualValue',
                      TIMESTAMPADD(SECOND, -5, valueDate) AS 'adjustedVirtualDate'
             FROM     consecutiveDates
          ) virtual ON virtual.adjustedVirtualDate = actual.valueDate

    If you have dates that don’t line up perfectly like my example, you can use basic date math/conversion to normalize the dates in each row in to a rounded representation (i.e. nearest five seconds, nearest minute, etc.). With basic SQL grouping, you can then easily smash together rows that are close enough to each other to be combined in to a single row and achieve the same effect as shown in the script.

    This technique is obviously not limited to dates; you can use a similar approach with virtual row IDs in MySQL. By creating an integer session variable representing a row ID, and then selecting/incrementing it with each row in the query, you can apply basic math to the virtual table’s select statement to offset the session variable by -1 and use that as the join criteria.

    2009.10.01 / no responses / Category: Uncategorized