Rotating Column Data Using SQL Analytics

Jim_KnicelyJim_Knicely - Select Field - Administrator

Vertica analytics are SQL functions based on the ANSI 99 standard. These functions handle complex analysis, reporting tasks and allow for some cool data manipulation.


I have the following table of names where each name has a unique ID. I want to rotate the values in column NAME up by one row. That is, I want the new value of NAME to become the next value of NAME so that “Dave” becomes “Kristen”, “Kristen” becomes “Andrius”, “Andrius becomes “Curtis”, etc.

dbadmin=> SELECT * FROM test ORDER BY id;
id |  name
  1 | Dave
  2 | Kristen
  3 | Andrius
  4 | Curtis
  5 | Lenoy
  6 | Scott
(6 rows)

I can easily accomplish this task using the Vertica analytic SQL functions LEAD and FIRST_VALUE!

dbadmin=> UPDATE test
dbadmin->    SET name = (SELECT new_name
dbadmin(>                  FROM (SELECT id, NVL(LEAD (name, 1) OVER (ORDER BY id), FIRST_VALUE(name) OVER (ORDER BY id)) new_name
dbadmin(>                        FROM test) foo
dbadmin(>  WHERE foo.id = test.id);
(1 row)

dbadmin=> SELECT * FROM test ORDER BY id;
id |  name
  1 | Kristen
  2 | Andrius
  3 | Curtis
  4 | Lenoy
  5 | Scott
  6 | Dave
(6 rows)

Helpful links:

Have fun!

Sign In or Register to comment.