- raw data
no | group | date | value | flag |
---|---|---|---|---|
1 | a | 2022-10-13 | old | y |
2 | a | 2022-10-15 | new | y |
3 | b | 2022-01-01 | old | n |
4 | b | 2022-01-03 | new | n |
- step1. insert no1 raw
- step2. modify date value using by no2 raw
and I want to update latest date no1 raw using by no2 raw
and the condition is where `flag` = "y"
- final sql table
no | group | date | value | flag |
---|---|---|---|---|
1 | a | 2022-10-15 | old | y |
3 | b | 2022-01-01 | old | n |
is it possible?
) I insert/update raw data line by line.
CodePudding user response:
Not entirely clear but I hope below answer gives you a hint if not the solution.
select no,
`group`,
case when flag='Y' then mx_dt else `date` end as new_date,
value,
flag
from ( select no,
`group`,
value,
`date`,
flag ,
row_number() over(partition by `group` order by `date` asc ) as rn,
max(`date`) over (partition by `group`,(case when flag <> 'Y' then `date` end) ) mx_dt
from raw_data
) as tbl
where rn=1;
Above code will select the max(date) per group if the flag=Y otherwise it will take the date per row.
CodePudding user response:
The solution is to self join the source table and select the right field, prioritizing the latest date.
Here you have a working query:
WITH source_data AS (
SELECT 1 AS no_, 'a' AS group_, CAST('2022-10-13' AS DATE) AS date, 'old' AS value, 'y' AS flag
UNION ALL
SELECT 2, 'a', CAST('2022-10-15' AS DATE), 'new', 'y'
UNION ALL
SELECT 3, 'b', CAST('2022-01-01' AS DATE), 'old', 'n'
UNION ALL
SELECT 4, 'b', CAST('2022-01-03' AS DATE), 'new', 'n')
SELECT no_, group_, COALESCE(new_date, date), value, flag
FROM
(SELECT * FROM source_data WHERE value = 'old') old_values
LEFT JOIN (SELECT group_ AS new_group, date AS new_date FROM source_data WHERE value = 'new' AND flag='y') new_values
ON old_values.group_ = new_values.new_group
The result is what you expected:
no_ group_ f0_ value flag
1 a 2022-10-15 old y
3 b 2022-01-01 old n