Does vertica support derivative column concept in case statement.

hdk0310hdk0310 Registered User

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 Administrator, Moderator, Employee, Registered User, VerticaExpert admin
    edited January 11

    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 Administrator, Moderator, Employee, Registered User, VerticaExpert admin

    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)
    
  • hdk0310hdk0310 Registered User
    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