Projection with a "WITH" sql clause

kfruchtman

Hi Experts!
Is is possible to create a projection with a WITH clause of sql?
something like this:
CREATE PROJECTION Orders_with_regtime AS
(WITH
registered as (
select min(time) as first_order_time,OrderId from orders group by OrderId),
orders as
(select OrderId,time,C,D,E,F,G,H,I,J from orders)
select orders.OrderId,time,C,D,E,F,G,H,I,J,registered.first_order_time
from registered,orders where
registered.OrderId=orders.OrderId order by registered.first_order_time desc

?

marcothesane

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) ...

kfruchtman

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

marcothesane

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
);

marcothesane

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