I have the following table in Vertica:
+----------+----------+----------+ | column_1 | column_2 | column_3 | +----------+----------+----------+ | a | 1 | 1 | | a | 2 | 1 | | a | 3 | 1 | | b | 1 | 1 | | b | 2 | 1 | | b | 3 | 1 | | c | 1 | 1 | | c | 2 | 1 | | c | 3 | 1 | | c | 1 | 2 | | c | 2 | 2 | | c | 3 | 2 | +----------+----------+----------+
The table is ordered by column_1 and column_3. I would like to add a row number, which increases every time when column_1 or column_3 change their value. It would look something like this:
+----------+----------+----------+------------+ | column_1 | column_2 | column_3 | row_number | +----------+----------+----------+------------+ | a | 1 | 1 | 1 | | a | 2 | 1 | 1 | | a | 3 | 1 | 1 | | b | 1 | 1 | 2 | | b | 2 | 1 | 2 | | b | 3 | 1 | 2 | | c | 1 | 1 | 3 | | c | 2 | 1 | 3 | | c | 3 | 1 | 3 | | c | 1 | 2 | 4 | | c | 2 | 2 | 4 | | c | 3 | 2 | 4 | +----------+----------+----------+------------+
I tried using partition over but I can’t find the right syntax.
Advertisement
Answer
Vertica has the CONDITIONAL_CHANGE_EVENT() analytic functions.
It starts at 0, and increments by 1 every time the expression that makes the first argument undergoes a change.
Like so:
WITH
indata(column_1,column_2,column_3,rn) AS (
SELECT 'a',1,1,1
UNION ALL SELECT 'a',2,1,1
UNION ALL SELECT 'a',3,1,1
UNION ALL SELECT 'b',1,1,2
UNION ALL SELECT 'b',2,1,2
UNION ALL SELECT 'b',3,1,2
UNION ALL SELECT 'c',1,1,3
UNION ALL SELECT 'c',2,1,3
UNION ALL SELECT 'c',3,1,3
UNION ALL SELECT 'c',1,2,4
UNION ALL SELECT 'c',2,2,4
UNION ALL SELECT 'c',3,2,4
)
SELECT
*
, CONDITIONAL_CHANGE_EVENT(
column_1||column_3::VARCHAR
) OVER w + 1 AS rownum
FROM indata
WINDOW w AS (ORDER BY column_1,column_3,column_2)
;
-- out column_1 | column_2 | column_3 | rn | rownum
-- out ----------+----------+----------+----+--------
-- out a | 1 | 1 | 1 | 1
-- out a | 2 | 1 | 1 | 1
-- out a | 3 | 1 | 1 | 1
-- out b | 1 | 1 | 2 | 2
-- out b | 2 | 1 | 2 | 2
-- out b | 3 | 1 | 2 | 2
-- out c | 1 | 1 | 3 | 3
-- out c | 2 | 1 | 3 | 3
-- out c | 3 | 1 | 3 | 3
-- out c | 1 | 2 | 4 | 4
-- out c | 2 | 2 | 4 | 4
-- out c | 3 | 2 | 4 | 4