How to calculate the percentage between the last value and the value from X time ago?

Question

Today I tried to play a bit with currencies and gave PostgreSQL a chance to help me a bit.

I have a table in a PostgreSQL database, which has three fields:

CREATE TABLE IF NOT EXISTS binance (
     date TIMESTAMP,
     symbol VARCHAR(20),
     price REAL
)

This table is updated from 10 to 10 seconds with ~250rows. The symbols are always the same between intervals. E.g data:

+----------------------------+--------+-------+
|            date            | symbol | price |
+----------------------------+--------+-------+
| 2018-01-18 00:00:00.000000 | x      |    12 |
| 2018-01-18 00:00:00.000120 | y      |    15 |
| 2018-01-18 00:00:00.000200 | z      |    19 |
| 2018-01-18 00:00:10.080000 | x      |    14 |
| 2018-01-18 00:00:10.123000 | y      |    16 |
| 2018-01-18 00:00:10.130000 | z      |    20 |
+----------------------------+--------+-------+

Now, what I'd like to do is get for each symbol how much did it grow (percentage) in the last 5 minutes.

Let's take a symbol as an example (ETHBTC). Data for this symbol in the last 5 minutes looks like this:

+----------------------------+--------+----------+
|            date            | symbol |  price   |
+----------------------------+--------+----------+
| 2018-01-19 22:59:10.000000 | ETHBTC |  0.09082 |
| 2018-01-19 22:58:59.000000 | ETHBTC |   0.0907 |
| 2018-01-19 22:58:47.000000 | ETHBTC | 0.090693 |
| 2018-01-19 22:58:35.000000 | ETHBTC | 0.090697 |
| 2018-01-19 22:58:24.000000 | ETHBTC | 0.090712 |
| 2018-01-19 22:58:11.000000 | ETHBTC | 0.090682 |
| 2018-01-19 22:57:59.000000 | ETHBTC | 0.090774 |
| 2018-01-19 22:57:48.000000 | ETHBTC | 0.090672 |
| 2018-01-19 22:57:35.000000 | ETHBTC |  0.09075 |
| 2018-01-19 22:57:24.000000 | ETHBTC | 0.090727 |
| 2018-01-19 22:57:12.000000 | ETHBTC | 0.090705 |
| 2018-01-19 22:57:00.000000 | ETHBTC | 0.090707 |
| 2018-01-19 22:56:49.000000 | ETHBTC | 0.090646 |
| 2018-01-19 22:56:37.000000 | ETHBTC | 0.090645 |
| 2018-01-19 22:56:25.000000 | ETHBTC | 0.090636 |
| 2018-01-19 22:56:13.000000 | ETHBTC | 0.090696 |
| 2018-01-19 22:56:00.000000 | ETHBTC | 0.090698 |
| 2018-01-19 22:55:48.000000 | ETHBTC | 0.090693 |
| 2018-01-19 22:55:37.000000 | ETHBTC | 0.090698 |
| 2018-01-19 22:55:25.000000 | ETHBTC | 0.090601 |
| 2018-01-19 22:55:13.000000 | ETHBTC | 0.090644 |
| 2018-01-19 22:55:01.000000 | ETHBTC |   0.0906 |
| 2018-01-19 22:54:49.000000 | ETHBTC |   0.0906 |
| 2018-01-19 22:54:37.000000 | ETHBTC |  0.09062 |
| 2018-01-19 22:54:25.000000 | ETHBTC | 0.090693 |
+----------------------------+--------+----------+

To select this data I'm using the following query:

SELECT *
FROM binance
WHERE date >= NOW() AT TIME ZONE 'EET' - INTERVAL '5 minutes'
      AND symbol = 'ETHBTC'
ORDER BY date DESC;

What I'd like to do is to find out for every symbol:

• the percentage between the last value and the value from 10s ago
• the percentage between the last value and the value from 1min ago
• the percentage between the last value and the value from 5mins ago

Now, I'm kind of stuck about how'd a query like this should look like. More, IDK if this is important or not but the queries are run from within Python so I might not have the possibility to take advantage of the full PostgreSQL functionality.

Answer 1

Demo

Rextester online demo: http://rextester.com/QNVGU31219

SQL

Below is the SQL for comparing the latest price with the price 1 minute ago:

WITH cte AS
(SELECT price,
        ABS(EXTRACT(EPOCH FROM (
                    SELECT date - (SELECT MAX(date) - INTERVAL '1 minute' FROM binance))))
        AS secs_from_prev_timestamp
 FROM binance
 WHERE symbol = 'ETHBTC')
SELECT price /
       (SELECT price FROM binance 
        WHERE symbol = 'ETHBTC' AND date = (SELECT MAX(date) FROM binance))
       * 100.0 AS percentage_difference
FROM cte
WHERE secs_from_prev_timestamp = (SELECT MIN(secs_from_prev_timestamp) FROM cte);

The above can be simply changed to compare with the price from a different interval ago, e.g. by changing to INTERVAL '5 minutes' instead of INTERVAL '1 minute', or to give results for a different symbol by changing the two references to 'ETHBTC' to a different symbol.

Explanation

The tricky bit is getting the previous price. This is done by using a common table expression (CTE), which lists all the prices and the number of seconds away from the desired timestamp. The absolute value function is used (ABS) so the nearest one will be found, regardless of whether it is more or less than the target timestamp.

Results

In the one example above, the query gives a result of 99.848...%. This is formulated from 0.090682 / 0.09082 * 100.0, where 0.09082 is the latest price and 0.090682 is the price one minute ago.

The above was based on an assumption of what was meant by "percentage difference" but there are alternative percentages that could be calculated - e.g. 0.09082 is 0.152% higher than 0.090682. (Please reply in the comments if my interpretation of percentage difference wasn't what you are after and I'll update the answer accordingly.)

UPDATE - "do it all" query

After reading your comments to Dan's answer that you would like to get all these results using a single query, I've posted one below that should do what is required. Rextester demo here: http://rextester.com/QDUN45907

WITH cte2 AS
(WITH cte1 AS
 (SELECT symbol,
         price,
         ABS(EXTRACT(EPOCH FROM (
                   SELECT date - (SELECT MAX(date) - INTERVAL '10 seconds' FROM binance))))
         AS secs_from_latest_minus_10,
         ABS(EXTRACT(EPOCH FROM (
                   SELECT date - (SELECT MAX(date) - INTERVAL '1 minute' FROM binance))))
         AS secs_from_latest_minus_60,
         ABS(EXTRACT(EPOCH FROM (
                   SELECT date - (SELECT MAX(date) - INTERVAL '5 minutes' FROM binance))))
         AS secs_from_latest_minus_300
  FROM binance)
 SELECT symbol,
        (SELECT price AS latest_price
         FROM binance b2
         WHERE b2.symbol = b.symbol AND date = (SELECT MAX(date) FROM binance)),
        (SELECT price AS price_latest_minus_10
         FROM cte1
         WHERE cte1.symbol = b.symbol AND secs_from_latest_minus_10 =
               (SELECT MIN(secs_from_latest_minus_10) FROM cte1)),
        (SELECT price AS price_latest_minus_60
         FROM cte1
         WHERE cte1.symbol = b.symbol AND secs_from_latest_minus_60 = 
               (SELECT MIN(secs_from_latest_minus_60) FROM cte1)),
        (SELECT price AS price_latest_minus_500
         FROM cte1
         WHERE cte1.symbol = b.symbol AND secs_from_latest_minus_60 = 
               (SELECT MIN(secs_from_latest_minus_60) FROM cte1))
 FROM binance b
 GROUP BY symbol)
SELECT symbol,
       price_latest_minus_10 / latest_price * 100.0 AS percentage_diff_10_secs_ago,
       price_latest_minus_60 / latest_price * 100.0 AS percentage_diff_1_minute_ago,
       price_latest_minus_500 / latest_price * 100.0 AS percentage_diff_5_minutes_ago
FROM cte2;

Answer 2

To get a Relative Percentage for three different times in a row you have to join each case for every time, in this case 10s / 1min / 5 mins.

Here is the query, NOTE that the JOIN is ON id. You need a primary key or a unique value for this JOIN to work properly:

-- Overall SELECT, '*' includes 5min
SELECT a.*,b."1min",c."10sec"
FROM
-- First we select the group with most rows, that are <=5min
    (SELECT *,
    -- Formula for the percentage
    100*price/last_value(price)
        OVER (PARTITION BY symbol
             ORDER BY date DESC rows between unbounded preceding and
             unbounded following) AS "5min"
    FROM test
    WHERE date >= NOW() AT TIME ZONE 'EET' - INTERVAL '5 minutes'
    ORDER BY symbol,date DESC)a
LEFT JOIN
-- Join with 1 minute query
    (SELECT *,
    -- Formula for the percentage
    100*price/last_value(price)
        OVER (PARTITION BY symbol
             ORDER BY date DESC rows between unbounded preceding and
             unbounded following) AS "1min"
    FROM test
    WHERE date >= NOW() AT TIME ZONE 'EET' - INTERVAL '1 minutes'
    ORDER BY symbol,date DESC)b
-- join with id (primary or unique)
ON a.id = b.id
-- Join with 30 seconds query
LEFT JOIN
    (SELECT *,
    -- Formula for the percentage
    100*price/last_value(price)
        OVER (PARTITION BY symbol
             ORDER BY date DESC rows between unbounded preceding and
             unbounded following) AS "10sec"
    FROM test
    WHERE date >= NOW() AT TIME ZONE 'EET' - INTERVAL '30 seconds'
    ORDER BY symbol,date DESC)c
-- join with id (primary or unique)
ON a.id=c.id

In this query you can alter the formula for the percentage and the time, according to your needs. If you like the percentage to be relative to another value like a master price, it will have to be included in each query and added to the formula instead of last_value(price) OVER.... Keep in mind that the actual formula gets the percentage relative to the oldest row in the query.

Answer 3

Percent Rank:

This query gives the percentage from 0 to 1 of the rows in a query, being 0 the first row, and 1 the last one.

For example:

date       |symbol  |price | percentage
-----------+--------+------+-------------
2017-01-05 | 1      | 0.5  | 1
2017-01-04 | 1      | 1.5  | 0.5
2017-01-03 | 1      | 1    | 0
2017-01-05 | 2      | 1    | 1
2017-01-04 | 2      | 3    | 0.5
2017-01-03 | 2      | 2    | 0

This is the query:

SELECT *,
-- this makes a column with the percentage per row
percent_rank() OVER (PARTITION BY symbol ORDER BY date) AS percent
FROM binance
WHERE date >= NOW() AT TIME ZONE 'EET' - INTERVAL '5 minutes'
ORDER BY symbol,date DESC;

Relative Percentage:

This query shows the percentage regarding the oldest value of the price of the data set.

For example:

date       | symbol |price | percentage
-----------+--------+------------
2017-01-05 | 1      | 0.5  | 50
2017-01-04 | 1      | 1.5  | 150
2017-01-03 | 1      | 1    | 100
2017-01-05 | 2      | 1    | 50
2017-01-04 | 2      | 3    | 150
2017-01-03 | 2      | 2    | 100

The query is:

SELECT *,
-- Formula to get the percentage taking the price from the oldest date:
100*price/last_value(price) OVER (PARTITION BY symbol ORDER BY date DESC rows between unbounded preceding and unbounded following) AS percentage
FROM binance
WHERE date >= NOW() AT TIME ZONE 'EET' - INTERVAL '5 minutes'
ORDER BY symbol,date DESC;