flattened tables question

Answers

• kfruchtman Vertica Customer ✭

  April 2021

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

  0
• Jim_Knicely - Select Field - Administrator

  April 2021 edited April 2021

  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)
  
  

  0
• kfruchtman Vertica Customer ✭

  April 2021

  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?

  0
• Jim_Knicely - Select Field - Administrator

  April 2021

  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.

  0
• kfruchtman Vertica Customer ✭

  April 2021

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

  0