
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/latest/HTML/index.htm#Authoring/AnalyzingData/SQLAnalytics/UsingSQLAnalytics.htm
https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Analytic/LEADAnalytic.htm
https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Analytic/FIRST_VALUEAnalytic.htm
Have fun!