I’m trying to create a view that contains a column with a list of codes. This is the SQL code that I currently have: This creates a view with this column: ?column? code1 code2 code3 As you can see the column name is “?column?” and I would like to change it to a name of my choice. I’ve …
Tag: amazon-redshift
Want to run a query multiple times using a for loop and add each result into a dictionary. This code only execute once even as it loops through
I have a query that take a random sample of records. I want to do this multiple times and add each result into a dictionary, which I will concat into a pandas DataFrame later. This code only execute once even as it loops through. Answer cursor.fetchall() doesn’t execute the query, it just fetches the re…
Customer/Material pair to count the occurences in the past 3 years and set the status flag. – aws redshift
I have a table with the following values date customer name material 9/1/2020 A A1 8/1/2019 A A2 8/1/2018 A A2 9/2/2020 B A1 9/2/2019 B A2 9/2/2017 B A2 So the output should be something like, if I could see a customer with a material, who has an entry in the past 3 years, then the status flag should
Redshift : Coalesce Can’t Work As Expected
We’re complementing null value to all zero value like ’00’ on Redshift. Sometimes, I found coalesce function can’t work as we expected. If we use case and len, it can work fine as follows; So far, I have no idea why coalesce can’t work. Any advice would be appreciated. Thank you.…
Redshift SQL — transposing rows by id into columns
I am using Redshift SQL. I would like to transpose the distinct rows of “Id1” in “Table” into columns. Thus the output should be like the bottom most table, providing weights as columns for each “Id2”.Or Alternatively, concatenate weights into one column. Any guidance on fu…
Redshift SQL sumup amount from two groups only when group 1 is found
I have a requirement where I have to add amounts from two groups only if first group exists. So far, i have selected distinct groups and the corresponding ids. But i am not sure how to proceed further….
Where column “c” is not null for each group in column “a” – give group “a” a value of 1
Column “C” is a flag indicator. I want to run a query where I can sum column “c” where it is not null. I would like 1 value for each group of column A. For instance group “Test”, would have a total of 1. Group chance has a total of 1. Group “Play” has a total of…
when using a case statement to aggregate fields in redshift, is it more performant to replace binary fields with 1s and 0s?
For example, which of the following calculations should perform faster? or For the sake of this example, assume that when fieldA is not null, fieldB will always equal 1. fieldB can also equal 1 if fieldA is null, which is why I use the case statement. Answer The two queries do not do the same thing, unless fi…
remove the first two part of delimited string
I have a string with delimiter ‘_’, example below ‘AAA_BBB_CCC_DDD_EEE’ I want to trim the first two elements of delimited string. Expected output: ‘CCC_DDD_EEE’ Answer You should be able to use regexp_substr(): Or regexp_replace():
Redshift Postgres 8
I’m trying to write a query to solve a logical problem using Redshift Postgres 8. Input column is a bunch of Order IDs and Step Group IDs and desired output is basically a sequence of the IDs as you can see in the screenshot. If you could help me answer this question, that would be great, thanks! This i…