wanted to copy down values in impala or sql using partition or any other functions.

Input table

resultant table

CodePudding user response：

you can try like below

with cte as
(select *,row_number()over(partition by col3 order by col2) as rn1,
row_number()over( order by col2) as rn2 ,
lag(col3) over(order by col2) as v from a
) select col1,col2,
 max(coalesce(col3,v)) over(partition by rn2-rn1) as col3  from cte order by col2

CodePudding user response：

In Oracle, use the LAST_VALUE analytic function:

SELECT col1,
       col2,
       LAST_VALUE(col3) IGNORE NULLS OVER (PARTITION BY col1 ORDER BY col2)
         AS col3
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name(col1, col2, col3) AS
SELECT 'AA', DATE '2021-01-26', 'BB' FROM DUAL UNION ALL
SELECT 'AA', DATE '2021-01-27', NULL FROM DUAL UNION ALL
SELECT 'AA', DATE '2021-01-28', NULL FROM DUAL UNION ALL
SELECT 'AA', DATE '2021-01-29', 'CC' FROM DUAL UNION ALL
SELECT 'AA', DATE '2021-01-30', NULL FROM DUAL UNION ALL
SELECT 'AA', DATE '2021-01-31', NULL FROM DUAL UNION ALL
SELECT 'AA', DATE '2021-02-01', 'BA' FROM DUAL UNION ALL
SELECT 'AA', DATE '2021-02-02', NULL FROM DUAL UNION ALL
SELECT 'AA', DATE '2021-02-03', NULL FROM DUAL;

Outputs:

COL1	COL2	COL3
AA	26-JAN-21	BB
AA	27-JAN-21	BB
AA	28-JAN-21	BB
AA	29-JAN-21	CC
AA	30-JAN-21	CC
AA	31-JAN-21	CC
AA	01-FEB-21	BA
AA	02-FEB-21	BA
AA	03-FEB-21	BA

If you want to update the table then you can use a MERGE statement:

MERGE INTO table_name dst
USING (
  SELECT ROWID AS rid,
         LAST_VALUE(col3) IGNORE NULLS OVER (PARTITION BY col1 ORDER BY col2)
           AS col3
  FROM   table_name
) src
ON (src.rid = dst.ROWID)
WHEN MATCHED THEN
  UPDATE
  SET col3 = src.col3;

db<>fiddle here