Postgresql – Converting ticks into range bars

postgresql-11select

You have a tick table with 3 columns

    CREATE TABLE tick (
        symbol character(9) NOT NULL,
        price real NOT NULL,
        "time" timestamp with time zone NOT NULL
    );

INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26175.9, '2019-04-11 10:32:00.361294-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26175.6, '2019-04-11 10:32:00.429346-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.7, '2019-04-11 10:32:01.03363-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.6, '2019-04-11 10:32:01.085657-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26175.6, '2019-04-11 10:32:01.141192-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.8, '2019-04-11 10:32:01.245584-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.6, '2019-04-11 10:32:01.353293-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.8, '2019-04-11 10:32:01.611854-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.9, '2019-04-11 10:32:02.784545-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.8, '2019-04-11 10:32:02.888302-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.9, '2019-04-11 10:32:02.947919-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.8, '2019-04-11 10:32:03.055718-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.9, '2019-04-11 10:32:03.158856-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174, '2019-04-11 10:32:03.262169-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.8, '2019-04-11 10:32:03.806712-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.6, '2019-04-11 10:32:04.3873-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.8, '2019-04-11 10:32:04.444778-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.6, '2019-04-11 10:32:04.504045-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.1, '2019-04-11 10:32:04.824775-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.6, '2019-04-11 10:32:04.880204-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.1, '2019-04-11 10:32:04.98412-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.1, '2019-04-11 10:32:05.546972-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.8, '2019-04-11 10:32:06.126979-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.1, '2019-04-11 10:32:06.180949-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.8, '2019-04-11 10:32:06.233617-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.9, '2019-04-11 10:32:06.53779-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.1, '2019-04-11 10:32:06.83216-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.1, '2019-04-11 10:32:07.099359-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.1, '2019-04-11 10:32:07.159276-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.1, '2019-04-11 10:32:07.220017-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.6, '2019-04-11 10:32:07.436384-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.1, '2019-04-11 10:32:07.492972-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.6, '2019-04-11 10:32:07.599031-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.1, '2019-04-11 10:32:08.528171-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26172.9, '2019-04-11 10:32:08.803254-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.1, '2019-04-11 10:32:08.954501-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26172.9, '2019-04-11 10:32:09.056822-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.6, '2019-04-11 10:32:09.160093-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.4, '2019-04-11 10:32:09.813164-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.6, '2019-04-11 10:32:10.168529-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.4, '2019-04-11 10:32:10.223418-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.6, '2019-04-11 10:32:10.32763-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26173.4, '2019-04-11 10:32:10.383433-05');
INSERT INTO TICK (SYMBOL, PRICE, TIME) VALUES ( 'US30', 26174.6, '2019-04-11 10:32:10.444899-05');

Since Range Bars are driven by price movement, a new Range Bar is only created once the specified Range has been met. For example, if the specified Range amount is 10, it means that each Range Bar will have a range (High/Max to Low/Min) of 10. It is thus conceivable that a single Range Bar could represent several days if the movement throughout those minuets/hours/days was only within a 10 price range. Once a Range Bar is closed-out, the open of the next Range Bar will always be at exactly the same price as the Close of the prior Range Bar.

The Range amount determines the size of the Range Bars.

Suppose that within a given five minute period prices oscillate between 10 and 12, going from 10 to 12, then back to 10 and then back to 12. If the Range amount for this chart were set to 1, this would result in six Range Bars, two from 10 to 12, two going back down to 10, and two more going back up to 12, as shown in the example below:

Since all of the aforementioned oscillations occurred within a five minute period (the specified underlying interval in this example) when the Range Bar is built historically, that five minute period would be represented by only two Range Bars, as shown in the example below:

I think this gives a clear example of what I am trying to select from tick table. Would a window with sub select be the way to go about doing something like this? Or function?

Any one up for showing a simple solution to this complex select ?? Result would look like

US30, 26175.9, 2019-04-11 10:32:00
US30, 29174.9, 2019-04-11 10:32:01
US30, 29173.9, 2019-04-11 10:32:01
US30, 29174.9, 2019-04-11 10:32:03
US30, 29173.9, 2019-04-11 10:32:04
US30, 29172.9, 2019-04-11 10:32:08

Idea on what the results of select would look like if the range was 1

Best Answer

Look at this fiddle.

SELECT price, "time", 
       LAST_VALUE(price) OVER (ORDER BY "time" ASC 
                               RANGE BETWEEN CURRENT ROW 
                                     AND '5 second' FOLLOWING) last_price,
       LAST_VALUE("time") OVER (ORDER BY "time" ASC 
                                RANGE BETWEEN CURRENT ROW 
                                      AND '5 second' FOLLOWING) last_time
FROM tick
ORDER BY "time"

It calculates last price (and time) value within 5-second interval for each record in the table. You may:

build range bar(s) for each separate record from price to last_price with the step you need using recursive CTE;
select only those records which are "first" in some (5 second) interval;
do both.

Now I cannot say more because you have no provided the result which you want to obtain on shown sample data.

Get qualifying rows only

One way ..

WITH x AS (
   SELECT *
         ,row_number() OVER (PARTITION BY reference ORDER BY date_out DESC) AS rn
   FROM   tbl
   )
, y AS (
   SELECT *
   FROM   x
   WHERE  x.rn = 1
   AND    date_out >= '2012-10-01'
   AND    date_out <  '2012-11-01'
   )
, z AS (
   SELECT x.*
   FROM   x
   JOIN   y USING (reference)
   WHERE  x.rn = 2
   AND    x.centreid = 1
   )
SELECT y.taskid, y.reference, y.centreid, y.date_out
FROM   y
JOIN   z USING (reference)

UNION  ALL
SELECT taskid, reference, centreid, date_out
FROM   z
ORDER  BY reference, date_out;

Another way:

WITH x AS (
   SELECT *
         ,row_number() OVER (PARTITION BY reference ORDER BY date_out DESC) AS rn
   FROM   tbl
   )
,y AS (
    SELECT x.*, y.taskid AS taskid2, y.centreid AS centreid2, y.date_out AS date_out2
    FROM   x
    JOIN   x y USING (reference)
    WHERE  x.rn = 1
    AND    x.date_out >= '2012-10-01'
    AND    x.date_out <  '2012-11-01'
    AND    y.rn = 2
    AND    y.centreid = 1
    )
SELECT y.taskid, y.reference, y.centreid, y.date_out
FROM   y

UNION  ALL
SELECT y.taskid2, y.reference, y.centreid2, y.date_out2
FROM   y
ORDER  BY reference, date_out;

I'd expect the second one to be faster. Depends on your data distribution. Test with EXPLAIN ANALYZE.

Get all rows for qualifying references

WITH x AS (
   SELECT *
         ,row_number() OVER (PARTITION BY reference ORDER BY date_out DESC) AS rn
   FROM   tbl
   )
,y AS (
    SELECT reference
    FROM   x
    JOIN   x y USING (reference)
    WHERE  x.rn = 1
    AND    x.date_out >= '2012-10-01'
    AND    x.date_out <  '2012-11-01'
    AND    y.rn = 2
    AND    y.centreid = 1
    )
SELECT *
FROM   tbl
JOIN   y USING (reference)
ORDER  BY reference, date_out;

-> sqlfiddle

Answer to follow-up in comment

Separate groups if more than 30 days between entries.

WITH a AS (
   SELECT *
         ,lag(date_out) OVER (PARTITION BY reference ORDER BY date_out DESC) AS last_date
         ,CASE WHEN date_out > 
                   (lag(date_out) OVER (PARTITION BY reference ORDER BY date_out DESC) - 30)
               THEN 0 ELSE 1
          END AS step
   FROM   tbl
   )
,b AS (
   SELECT *
         ,sum(step) OVER (PARTITION BY reference ORDER BY date_out DESC) AS grp
   FROM a
   )
 ,c AS (
   SELECT *
         ,row_number() OVER (PARTITION BY reference, grp ORDER BY date_out DESC) AS rn
   FROM b
   )
,d AS (
   SELECT reference, grp
   FROM   c
   JOIN   c d USING (reference, grp)
   WHERE  c.rn = 1
   AND    c.date_out >= '2012-10-01'
   AND    c.date_out <  '2012-11-01'
   AND    d.rn = 2
   AND    d.centreid = 1
   )
SELECT b.taskid, b.reference, b.centreid, b.date_out
FROM   b
JOIN   d USING (reference, grp)
ORDER  BY reference, date_out

-> sqlfiddle

But while pure SQL is a beaut .. I would solve this procedurally in a plpgsql function. Very similar to this recent answer on SO. Would be faster, because it can be done in a single table scan.

Best Answer

Related Solutions

MySQL Insert into two tables using new IDs

Postgresql – Tricky selection of grouped rows, selecting based on values of two distinct but related rows

Get qualifying rows only

Get all rows for qualifying references

Answer to follow-up in comment

Related Question