Skip to main content

Posts

Showing posts from April, 2022

Migrate Green plum distinct on to Redshift row_number()

Green plum distinct on To Redshift row_number: Green Plum: select distinct on (col_1, col_2, col_3) from table_t1 where condition_1=some_condition order by col_1, col_2, col_3 Rdshift: select col_1, col_2, col_3 as <alias> from      (      select row_number() over     (      partition by col_1 order by col_1      ) as rn, col_1, col_2, col_3      from table_1      where col_4=<condition>      order by col_1, col_2, col_3      ) as t1 where t1.rn=1 Note: If you include more columns for partition by clause, check the no. of row count, as it should be different.

Migration Green Plum date_part to Redshift datediff function

date_part to datediff: Green Plum: select ( date_part ('second' :: text, t2.transaction_date - t1.transaction_date) ) / (60 * 24 * 60) :: double precision as GP_time from Table_T1 where t1.tra_id=t2.tran_id Redshift: select DATEDIFF ( 'second', t1.transaction_date::timestamp, t2.transaction_date::timestamp )/ (60 * 24 * 60) :: double precision as RS_time from Table_T1 where t1.tra_id=t2.tran_id   Attention: Pay attention to the removal of minus sign while using DATADIFF function for Redshift, order of date column such as 2nd column will be at 1st position and vice versa, number of argument is 3 - second / minute / hour / day, date1, date2. Green Plum date_part function has 2 argument compared to Redshift DATEDIFF function.