Flatten table failure when combining current quantities with most recent prices (Top1 Projection)

BHIFPBHIFP Vertica Customer

In the context of showing someone's portfolio with most up-to-date prices, I have a table of each position's quantities and a prices table that keeps updating.

This creates latest price and works fine:

CREATE PROJECTION prices_sync.ticks_cs /*+basename(ticks),createtype(L)*/
    (
     distribution_id ENCODING RLE,
     base_asset_id ENCODING RLE,
     counter_asset_id ENCODING RLE,
     business_time ENCODING DELTAVAL,
     price ENCODING AUTO
        )
    AS
        SELECT ticks.distribution_id,
               ticks.base_asset_id,
               ticks.counter_asset_id,
               ticks.business_time,
               ticks.price
        FROM prices_sync.ticks
        LIMIT 1
    OVER (PARTITION BY distribution_id, base_asset_id, counter_asset_id
    ORDER BY business_time DESC);
SELECT START_REFRESH();
SELECT REFRESH('prices_sync.ticks');

When creating the flatten table, this failed due to ROLLBACK 7347: Default queries must refer to tables:

ALTER TABLE public.std_trades
    ADD COLUMN latest_price numeric(35, 20)
        DEFAULT (
            SELECT price
            FROM prices_sync.ticks_cs b
            WHERE base_asset_id = b.base_asset_id
              and counter_asset_id = b.counter_asset_id
              and b.distribution_id = 1000
        );

If I put topK inside though, got a different error:

ROLLBACK 4840: Subquery used as an expression returned more than one row
DETAIL: Error occurred in Join [(UNKNOWN x prices_sync.ticks_cs) using subquery and subquery (PATH ID: 1)] with localplan_id=[1]

ALTER TABLE public.std_trades
    ADD COLUMN latest_price numeric(35, 20)
        DEFAULT (
            SELECT price
            FROM (
                     SELECT ticks.distribution_id,
                            ticks.base_asset_id,
                            ticks.counter_asset_id,
                            ticks.price
                     FROM prices_sync.ticks
                     LIMIT 1 OVER (PARTITION BY distribution_id, base_asset_id, counter_asset_id ORDER BY business_time DESC)
                 ) b
            WHERE base_asset_id = b.base_asset_id
              and counter_asset_id = b.counter_asset_id
              and b.distribution_id = 1000
        );

Please advise on how to do it correctly. Maybe the best solution is not the use flatten table but a simple join whenever I need the data?

Answers

  • moshegmosheg Vertica Employee Administrator
    edited September 2021

    Vertica automatically populates the DEFAULT column latest_price in the following code example.
    Instead of DEFAULT, if we would use the "SET USING" syntax the latest_price would remain NULL, and would be populated by calling the function REFRESH_COLUMNS on flattened table prices_sync.std_trades.

    CREATE SCHEMA prices_sync;
    
    CREATE TABLE prices_sync.std_trades
        (distribution_id INT,
         base_asset_id  INT,
         counter_asset_id  INT,
         business_time TIMESTAMP,
         price numeric(35, 20)) 
    ORDER BY business_time;
    
    CREATE TABLE prices_sync.ticks_cs
        (distribution_id INT,
         base_asset_id  INT,
         counter_asset_id  INT,
         business_time TIMESTAMP,
         price numeric(35, 20))
    ORDER BY business_time;
    
    ALTER TABLE prices_sync.std_trades
        ADD COLUMN latest_price numeric(35, 20)
            DEFAULT (
                SELECT max(price)
                FROM prices_sync.ticks_cs b
                WHERE base_asset_id = b.base_asset_id
                  and counter_asset_id = b.counter_asset_id
                  and b.distribution_id = 1000
            );
    
    
  • BHIFPBHIFP Vertica Customer

    SELECT max(price) is not what I am looking for. I am looking for the price associated with SELECT max(business_time), which was why TopK projection is needed.

  • moshegmosheg Vertica Employee Administrator

    Since in your case the subquery to find the max business_time may return more than one value, a join is more suitable.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file