Increment a column when using a UNION clause-CodePudding

I have the following query :

select row_number() over (order by 1) as id,"-1" AS StatusCode,"Not informed" AS StatusLibelle
union
select row_number() over (order by 1) as id,"0" AS StatusCode,"Off" AS StatusLibelle
union
select row_number() over (order by 1) as id,"1" AS StatusCode,"On" AS StatusLibelle

When I try to query it on hive, I get the following result

id StatusCode StatusLibelle
1  -1         Not informed
1   0         Off
1   1         On

This is my expected output, the id column should be incremented:

id StatusCosqlde StatusLibelle
1  -1         Not informed
2   0         Off
3   1         On

CodePudding user response：

Try the following :

select row_number() over (order by 1) as id,"-1" AS StatusCode,"Not informed" AS StatusLibelle
union
select row_number() over (order by 1)   1 as id,"0" AS StatusCode,"Off" AS StatusLibelle
union
select row_number() over (order by 1)   2 as id,"1" AS StatusCode,"On" AS StatusLibelle

CodePudding user response：

Why are you using row_number()?

select 1 as id, -1 AS StatusCode, 'Not informed' AS StatusLibelle
union all
select 2 as id, 0 AS StatusCode, 'Off' AS StatusLibelle
union all
select 3 as id, 1 AS StatusCode, 'On' AS StatusLibelle

In addition, there is no reason to use UNION, which incurs overhead for removing duplicates. And, StatusCode looks like a number, so return it as a number, not a string.