Show all the columns in a projection's ORDER by clause

Vertica_CurtisVertica_Curtis Employee
in Tips from the Team

select distinct case when p.is_Segmented then 'Segmented' else 'Replicated' end, p.projection_name, p.segment_expression, pc.num_cols, pc.order_by
from projections p
join (select projection_id
, count(1) over (partition by projection_id ) as num_cols
, nth_value(projection_column_name, 1) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following) || ', ' ||
coalesce(nth_value(projection_column_name, 2) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 3) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 4) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 5) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 6) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 7) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 8) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 9) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 10) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 11) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 12) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 13) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 14) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 15) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 16) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 17) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 18) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 19) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 20) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 21) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 22) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 23) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 24) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 25) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 26) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 27) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 28) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 29) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') || ', ' ||
coalesce(nth_value(projection_column_name, 30) over (partition by projection_id order by sort_position asc rows between unbounded preceding and unbounded following), '') as order_by
from projection_columns where sort_position is not null ) pc using (projection_id)
where lower(projection_schema) = 'public' and lower(anchor_Table_name) = 'vendor_dimension'
order by 2;

output:

case    |   projection_name   |        segment_expression         | num_cols |                                                   order_by

-----------+---------------------+-----------------------------------+----------+--------------------------------------------------------------------------------------------------------------
Segmented | vendor_dimension_b0 | hash(vendor_dimension.vendor_key) | 1 | vendor_key, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,
Segmented | vendor_dimension_b1 | hash(vendor_dimension.vendor_key) | 1 | vendor_key, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,
(2 rows)

Comments

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    You can also make use of the new LISTAGG function to show a projection's sort order!

    Example:

    dbadmin=> CREATE TABLE public.vendor_dimension (c1 INT, c2 INT, c3 INT) ORDER BY c3, c2;
CREATE TABLE

dbadmin=> SELECT table_schema,
dbadmin->        projection_name,
dbadmin->        LISTAGG(projection_column_name) order_by
dbadmin->   FROM (SELECT table_schema,
dbadmin(>                table_name,
dbadmin(>                projection_name,
dbadmin(>                projection_column_name
dbadmin(>           FROM projection_columns
dbadmin(>          WHERE sort_position IS NOT NULL
dbadmin(>          ORDER BY sort_position ) foo
dbadmin->   WHERE table_schema = 'public'
dbadmin->     AND table_name = 'vendor_dimension'
dbadmin->   GROUP BY table_schema,
dbadmin->            projection_name;
 table_schema |    projection_name     | order_by
--------------+------------------------+----------
 public       | vendor_dimension_super | c3,c2
(1 row)
Sign In or Register to comment.