flattened tables question

kfruchtman

Hi!
I have a flattened tables question:
Can I create live aggregate projections on flattened tables ? If I want to groupby or create aggregations on the columns? is it possible somehow?

Thanks!
Keren

kfruchtman

And another issue regarding the flattened tables: Can I use the default or SET USING constraint to populate a column that is aggregate function?
something like -
CREATE TABLE public.spins_flattened
(
...
cid int REFERENCES public.spins(id),
PlayerId_f int DEFAULT (SELECT PlayarId FROM public.spins
WHERE public.spins(id) = spins_flattened.cid);
,
registered_time timestamp SET USING (
SELECT min(time) FROM public.spins WHERE (spins.playerId = spins_flattened.PlayerId_f) group by 1 ),
...

Jim_Knicely

Can you cretae a LAP on a Flattened Table? Sure!

verticademos=> CREATE TABLE dim(c INT);
CREATE TABLE

verticademos=> CREATE TABLE FACT(c INT SET USING (SELECT c FROM dim));
CREATE TABLE

verticademos=> CREATE PROJECTION fact_pr AS SELECT c FROM fact;
CREATE PROJECTION

verticademos=> CREATE PROJECTION fact_lap AS SELECT c, SUM(c) c FROM fact GROUP BY c;
CREATE PROJECTION

Can I use the default or SET USING / DEFAULT constraint to populate a column that is aggregate function? Sure!

verticademos=> CREATE TABLE dim (a INT, z INT);
CREATE TABLE

verticademos=> CREATE TABLE FACT(a INT, c INT DEFAULT (SELECT SUM(z) FROM dim WHERE a = fact.a));
CREATE TABLE

verticademos=> INSERT INTO dim SELECT 1, 1;
 OUTPUT
--------
      1
(1 row)

verticademos=> INSERT INTO dim SELECT 1, 3;
 OUTPUT
--------
      1
(1 row)

verticademos=> INSERT INTO fact (a) SELECT 1;
 OUTPUT
--------
      1
(1 row)

verticademos=> SELECT * FROM fact;l
 a | c
---+---
 1 | 4
(1 row)

kfruchtman

Wow SUPERB!
Thank!!!!
And if I want to refresh_columns every 24 hours can I do it automatically somehow through the vertica or do I need a third party app?

Jim_Knicely

Vertica doesn't have a built-in scheduler like that. But you could easily add a SQL command to refresh every 24 hours via crontab.

kfruchtman

That sounds like a plan
Many thanks for all your help!
awesome support !