Does vertica support derivative column concept in case statement.

HI,
Please help me out as I am stuck with a Sql Case statement in vertica.

I have a a data set where
ID | rank | Columnname
1 | 1 | A
1 | 2 | B
1 | 3 | C
1 | 4 | D
My Desired Output :
ID | rank | Columnname | Aggregate
1 | 1 | A | A
1 | 2 | B | A,B
1 | 3 | C | A,B,C
1 | 4 | D | A,B,C,D

The aggregate column should concatenate the columnname values based on the rank.
For example For rank =2 Aggregate value should be Columnname in rank 1+ Columnname in Seq 2.
For rank = 3 Aggregate value should be Columnname in rank 1+ Columnname in rank 2 (Or derivative obtain from last statement )+ Columnname in rank 2. and so on

I have written a CASE statement to handle this . But This is not giving the result as expected since for every rank it is re-evaluating the case instead of taking result from first iteration.

SELECT
ID,rank,columnname
(CASE WHEN rank = 1 THEN ColumnName END) ||
(CASE WHEN rank = 2 THEN ', ' || ColumnName ELSE '' END) ||
(CASE WHEN rank = 3 THEN ', ' || ColumnName ELSE '' END) AS Aggregate
FROM Table;

The problem with this is from rank =2 I GET NULL VALUES FOR AGGREGATE.
NOTE= Rank can go upto 25.

Please let me know if anyone has a solution to this.

Thanks

Comments

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited January 2019

    What version of Vertica are you running?

    It's relatively easy to accomplish your goal using the LISTAGG function which is available in Vertica 9.1.1-4 and above.

    Example:

    dbadmin=> SELECT * FROM tab1;
     id | rank | columnname
    ----+------+------------
      1 |    1 | A
      1 |    2 | B
      1 |    3 | C
      1 |    4 | D
    (4 rows)
    
    dbadmin=> SELECT a.id, a.rank, a.columnname, listagg(b.columnname)
    dbadmin->  FROM tab1 a
    dbadmin->  JOIN tab1 b
    dbadmin->    ON a.id = b.id
    dbadmin->   AND b.rank <= a.rank
    dbadmin->  GROUP BY a.id, a.rank, a.columnname;
     id | rank | columnname | listagg
    ----+------+------------+---------
      1 |    1 | A          | A
      1 |    2 | B          | A,B
      1 |    3 | C          | A,B,C
      1 |    4 | D          | A,B,C,D
    (4 rows)
    

    See:
    https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/LISTAGG.htm

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    Another option is to use the LAG analytic function:

    dbadmin=> SELECT * FROM tab1;
     id | rank | columnname
    ----+------+------------
      1 |    1 | A
      1 |    2 | B
      1 |    3 | C
      1 |    4 | D
      2 |    1 | X
      2 |    2 | Y
      2 |    3 | Z
    (7 rows)
    
    dbadmin=> SELECT id, rank, columnname,
    dbadmin->        CASE
    dbadmin->        WHEN rank = 1
    dbadmin->          THEN columnName
    dbadmin->        WHEN rank = 2
    dbadmin->          THEN LAG(columnName) OVER (PARTITION BY id ORDER BY rank) || ',' || columnName
    dbadmin->        WHEN rank = 3
    dbadmin->          THEN LAG(columnName, 2) OVER (PARTITION BY id ORDER BY rank) || ',' ||
    dbadmin->            LAG(columnName) OVER (PARTITION BY id ORDER BY rank) || ',' || columnName
    dbadmin->        WHEN rank = 4
    dbadmin->          THEN LAG(columnName, 3) OVER (PARTITION BY id ORDER BY rank) || ',' ||
    dbadmin->            LAG(columnName, 2) OVER (PARTITION BY id ORDER BY rank) || ',' || LAG(columnName) OVER (PARTITION BY id ORDER BY rank) || ',' || columnName
    dbadmin->        END as aggregate
    dbadmin->   FROM tab1
    dbadmin->   ORDER BY id, rank;
     id | rank | columnname | aggregate
    ----+------+------------+-----------
      1 |    1 | A          | A
      1 |    2 | B          | A,B
      1 |    3 | C          | A,B,C
      1 |    4 | D          | A,B,C,D
      2 |    1 | X          | X
      2 |    2 | Y          | X,Y
      2 |    3 | Z          | X,Y,Z
    (7 rows)
    
  • Thanks Jim
    I will try both the approaches .

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file