Skip to content
Advertisement

Tag: amazon-redshift

Distinct performance in Redshift

I am trying to populate a multiple dimension tables from single Base table. Sample Base Table: Required Dimension tables : emp_name_dim with values – AAA,BBB,CCC emp_surname_dim with values – ZZZ,XXX country_dim with values – USA,IND,CAN dept_dim with values – CE,IT,MECH university_dim with values – U_01,U_02 Now to populate above dimension tables from base table, I am thinking of 2 approaches

Multiple conditions in CASE WHEN statement

I’m working in SQL (Amazon Redshift) with Recruitment data where each applicant has multiple sources, which I’ve split into different columns called source_1, source_2, and source_3, along with a number_of_sources column to record how many sources that applicant has. We want each applicant to only have one source, and have certain rules to follow in how to select which source,

Combining Data Across Multiple Data Fields

I am trying to create a common data field based on multiple columns below. When I try to use nvl i am getting blank values in the common field and its not getting picked up..The input and output tables are below: Input table model-manufacture model -CAM MODEL -schedule CX190 NM890 NM890 JK1231 LO121 LO121 VB1231 OPE12 ope12 jk7812 Output table

partition by customer for distinct items

select customer_id, row_number()over(partition by customer id order by date) as rn from table How to get same rn when Item Id is the same? Below did not work: #1 select customer_id, row_number()over(partition by customer id, Item Id order by date) as rn from table Answer We can try to use DENSE_RANK instead of row_number window function If the optional PARTITION

DATEPART and DATEFIRST alternative for redshift

I’m trying to run in Redshift with the start day being Saturday. I tried using different syntax for SET DATEFIRST but it seems like it’s not supported (or I just can’t find the correct syntax for redshift). Are there any alternatives available? The documentation page only shows information for extracting a specific dow (day of week): https://docs.aws.amazon.com/redshift/latest/dg/r_DATE_PART_function.html Answer The date_trunc()

Advertisement