Keboola WIKI‎ > ‎Transformations‎ > ‎MySQL‎ > ‎

Calculating Difference between events (rows)

If you have a table of events or daily stats, and you want to calculate the difference between the events.

Use Case: 
  • Subscription Events. If on the same row of data, you want to know the "previous value" or the "next value", and somehow you need it on the same line. For a subscription events table, I wanted to know what was the "previous subscription $ amount" along with the "subscription $ amount". I could then have two facts on the same line, made it easy for calculations in GD where I can say "upgrade $ amount" or "downgrade $ amount"

Method:

1. Re-order the table to correct ascending date(time) order while adding two columns (`one` and `two`), `one` with auto_increment.
CREATE TABLE `whatever` (`one` int not null auto_increment primary key, `two` int not null default 1, rest of your columns)
SELECT blah blah FROM blah ORDER BY `username`,`creation_time`;

2. Update your table and set the second added column (`two`) like so:
UPDATE `whatever` SET `two` = `one` - 1

3. Join the table on itself and get the diff
CREATE TABLE `whatever2` 
SELECT
w2.`fact` - w1.`fact` AS `factDiff`
FROM `whatever` w2
LEFT JOIN `whatever` w1 ON w2.`two` = w1.`one`;



Comments