0

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:

enter image description here

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:

enter image description here

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

  • Add some example data and desured result for THAT data. The best way - create a loadable INSERT INTO script or a fiddle. – Akina Sep 23 '19 at 12:13
  • I added about 10 seconds of 1 symbol to give example of what data looks like – user10078199 Sep 23 '19 at 12:54
  • And what is the result which you want to obtain on THAT data? – Akina Sep 23 '19 at 13:21
  • Added results of select should look like. I looked at your fiddle and I do see what you are trying to do but this is not time based it's price based. You are doing what I did this weekend trying to solve by doing time. How I am looking currently is window with some way to store a value. I do not think sql allows values to be stored for later reference on each look at a row. I have been looking at functions to see if I could pull it off by using functions. – user10078199 Sep 23 '19 at 13:51
  • Sorry, I cannot understand how this result was obtained from this source data... Why milliseconds are truncated, why 10:32:01 is duplicated (misprint?), why 10:32:05..07 are skipped, why those price value for each output record? – Akina Sep 23 '19 at 13:57
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.