Rotating Column Data Using SQL Analytics

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

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!

Sign In or Register to comment.