Rotating Column Data Using SQL Analytics

Jim_Knicely · October 2018

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.

Example:

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);
OUTPUT
--------
      6
(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:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AnalyzingData/SQLAnalytics/UsingSQLAnalytics.htm
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Analytic/LEADAnalytic.htm
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Analytic/FIRST_VALUEAnalytic.htm

Have fun!