Projection with a "WITH" sql clause

Answers

• marcothesane - Select Field - Administrator

  March 2021

  Joins are not allowed in a CREATE projection. This also applies to several CTE-s selecting from the same base table.
  It looks like you want to ease the access to the very first timestamp of an order per orderid.

  Try this as a Top-K Live Aggregate projection:

  CREATE PROJECTION orders_first AS
  SELECT
    *
  FROM orders
  LIMIT 1 OVER(PARTITION BY orderid ORDER BY order_time)
  ;
  

  (I would avoid using the reserved word TIME for a column name) ...

  0
• kfruchtman Vertica Customer ✭

  March 2021

  Thanks for the response!
  But I think I need something else than what you proposed.
  Lets say I have an orders table with many columns and order_time is one of them.I need to add a column which is actually a subquery of the same table and should look for the min(order_time) for each row so I will know for each row the first order ever made.
  So lets say I have a table:orders:
  create table orders (id,order_time,currency,d,h,)
  I want to get a projection : orders (id,order_time.currency,d,h,min(order_time) )
  The min(order_time) column is from the same table and is actually just a query to search for the min(order_time) by id.
  I will explain my needs:.This table is HUGE , so partitioning it and optimizing the query is not enough.Every time I run the query that searches for the min(order_time) for each id it takes forever (25 seconds) for just one day of orders, and I will need to have bigger search criteria like a full month maybe.
  ...so I am looking for a faster solution. I hope I was clear, if not please tell me.
  many thanks!
  Keren

  0
• marcothesane - Select Field - Administrator

  March 2021

  If you add a column to the table, it would work (and, yes, it has an effect of 8 bytes per row on the audit size)

  Tried this:

  DROP TABLE IF EXISTS public.orders;
  
  CREATE TABLE public.orders (
    orderid INTEGER
  , ordertime TIME
  , amt     NUMERIC(18,4)
  )
  ;
  
  INSERT INTO public.orders
            SELECT 1,TIME '00:01:00', 12.35
  UNION ALL SELECT 1,TIME '00:02:00', 13.35
  UNION ALL SELECT 1,TIME '00:03:00', 13.35
  UNION ALL SELECT 1,TIME '00:04:00', 13.35
  UNION ALL SELECT 1,TIME '00:05:00', 13.35
  UNION ALL SELECT 2,TIME '00:01:00', 13.35
  UNION ALL SELECT 2,TIME '00:02:00', 13.35
  UNION ALL SELECT 2,TIME '00:03:00', 13.35
  UNION ALL SELECT 2,TIME '00:04:00', 13.35
  UNION ALL SELECT 2,TIME '00:05:00', 13.35
  ;
  COMMIT;
  
  ALTER TABLE public.orders
  ADD firstordertime TIME DEFAULT (
    SELECT MIN(o.ordertime) FROM orders o
    WHERE o.orderid=orders.orderid
  );
  

  0
• marcothesane - Select Field - Administrator

  March 2021

  Someone pointed out to me that you can also use SET USING instead of DEFAULT - with a different effect on the audit size.
  Let's try:

  -- drop and recreate the table ....
  DROP TABLE IF EXISTS public.orders;
  DROP TABLE
  
  CREATE TABLE public.orders (
    orderid INTEGER
  , ordertime TIME
  , amt     NUMERIC(18,4)
  )
  ;
  CREATE TABLE
  -- fill the table ...
  INSERT INTO public.orders
            SELECT 1,TIME '00:01:00', 12.35
  UNION ALL SELECT 1,TIME '00:02:00', 13.35
  UNION ALL SELECT 1,TIME '00:03:00', 13.35
  UNION ALL SELECT 1,TIME '00:04:00', 13.35
  UNION ALL SELECT 1,TIME '00:05:00', 13.35
  UNION ALL SELECT 2,TIME '00:01:00', 13.35
  UNION ALL SELECT 2,TIME '00:02:00', 13.35
  UNION ALL SELECT 2,TIME '00:03:00', 13.35
  UNION ALL SELECT 2,TIME '00:04:00', 13.35
  UNION ALL SELECT 2,TIME '00:05:00', 13.35
  ;
   OUTPUT
  --------
       10
  (1 row)
  -- need to commit to keep the data in the table
  COMMIT;
  COMMIT
  
  -- check the audit size ...
  SELECT AUDIT('public.orders');
   AUDIT
  -------
   160
  (1 row)
  -- add the derived column with "SET USING" and check the audit size again ...
  ALTER TABLE public.orders
  ADD firstordertime_using TIME SET USING (
    SELECT MIN(o.ordertime) FROM orders o
    WHERE o.orderid=orders.orderid
  );
  ALTER TABLE
  SELECT AUDIT('public.orders');
   AUDIT
  -------
   160
  (1 row)
  -- no change. The column is NULL just now.
  -- fill the column by calling "REFRESH_COLUMNS()" ...
  SELECT REFRESH_COLUMNS('public.orders','*');
        REFRESH_COLUMNS
  ---------------------------
   refresh_columns completed
  (1 row)
  COMMIT; -- need committing; it's a DML command.
  COMMIT
  SELECT AUDIT('public.orders');
   AUDIT
  -------
   160
  (1 row)
  -- no change in audit size
  
  -- add the same column once more, but with DEFAULT:
  ALTER TABLE public.orders
  ADD firstordertime_default TIME DEFAULT (
    SELECT MIN(o.ordertime) FROM orders o
    WHERE o.orderid=orders.orderid
  );
  ALTER TABLE
  SELECT AUDIT('public.orders');
   AUDIT
  -------
   240
  (1 row)
  -- And you have 80 bytes more audit size; 8 bytes for each of the 10 rows
  

  0