I need to add an attribute that indicates if that version is an original or copy. If is the first version of the site, is original, it is not, is a copy.
the table:
id_site id_version timestamp_version
1 5589 2/3/2022
1 2030 10/7/2022
1 1560 10/8/2022
2 6748 2/3/2022
2 7890 2/4/2022
3 4532 2/3/2022
The expected result:
id_site id_version timestamp_version type_version
1 5589 2/3/2022 original
1 2030 10/7/2022 copy
1 1560 10/8/2022 copy
2 6748 2/3/2022 original
2 7890 2/4/2022 copy
3 4532 2/3/2022 original
CodePudding user response:
You can use an IF
or CASE
here. They are mostly interchangeable, but my preference is CASE
since it's portable to nearly any other RDBMS where IF
is only supported in a few.
CASE WHEN ROW_NUMBER() OVER (PARTITION BY id_site ORDER BY timestamp_version ASC) = 1 THEN 'copy' ELSE 'original' END
Inside the CASE
expression we do a ROW_NUMBER() window function will "window" or partition each row in the result set by id_site
and number each record for each distinct id_site
sequentially ordered by timestamp_version
in ascending order. We test to see if that ROW_NUMBER()
is 1
and then label it with original
or copy
.
CodePudding user response:
You can use a window function in an if statement for that:
with test as (
select * from unnest([
struct(1 as id_site, 5589 as id_version, timestamp(date "2022-03-02") as timestamp_version),
(1, 2030, timestamp(date "2022-07-10")),
(1, 1560, timestamp(date "2022-08-10")),
(2, 6748, timestamp(date "2022-03-02")),
(2, 7890, timestamp(date "2022-04-02")),
(3, 4532, timestamp(date "2022-03-02"))
])
)
select
*,
IF(timestamp_version = min(timestamp_version) over (partition by id_site), "original", "copy") AS type_version
from test
CodePudding user response:
Consider below option
select *,
if(lag(id_version) over prev is null, 'original', 'copy') type_version,
from your_table
window prev as (partition by id_site order by timestamp_version)
if applied to sample data in your question - output is