Note: This is a revised version of another question – i realized i didnt have complete requirements (sorry about that!) I have a Redshift table called metadata with the following columns: id key value 1001 code1 my value 1001 code2 another 1001 code3 yet another 1002 code1 new one 1002 code2 here 1002 c…
Tag: amazon-redshift
How best can I to pivot my redshift table?
I have a Redshift table called metadata with the following columns: id key value 1001 code1 my value 1001 code2 another 1001 code3 yet another 1002 code1 new one 1002 code2 here 1002 code3 last 1003 code1 hello 1003 code2 goodbye 1004 code2 now 1004 code3 then I’d like to have a query that return it as:…
SQL extracting date substring using regexp_subtr
I’ve got a field named signal_notes containing strings like the follow (this would be a single value of signal_notes): Sometimes, the ^expiry_date^ line will have a date between the ^’s in the format ‘YYYY-MM-DD’. My new field expiry_date will ideally be in the format ‘YYYY-MM-DD…
Find missing entries by date
I have two redshift tables: alert_type: where i keep types of alerts in my system alert: where i keep alerts Every day I generate a new alert for each alert type. If something has failed in …
How can I create a column which computes only the change of other column on redshift?
I have this dataset: The dataset has the product the company sells and the customers who saw the product. A customer can see more than one product, but the combination product + customer doesn’t have any repetition. I want to get how many people bought the product before the customer sees it. This would…
Redshift regex for before and after certain char
Data :- 1,2,3=20,4,5 select regexp_substr(data,’,3=[^,]*’) output = ,3=20 Desired output = 20 How do I get rid of,3=? I can add split_part(regexp_substr(Data,’,713=[^,]*’),’=’,2) but using the same expression would make sense. Thanks Answer You can extract a pattern using a…
How to sum few colums values for each type and calculate percentage?
I have below query which gives me data with three columns – type, amount and total for current week using week_number column. Below is the output I get back as of now with above query: Problem Statement Now I need to calculate the percentage for each type by using below formula: For each type (sum(total…
How to copy CSV into table with missing column?
I want to import data from a csv file into redshift. CSV format: Here is my import command: The problem I have is that sometimes, my file has only col1, col2 and col3. Is it possible to execute the COPY and add null for the missing values? Answer Yes, but must be explicit about it: The missing col2 will be
How to get data for past X weeks in redshift?
I have a below query which I run gives me the single count for previous week which is Week 44. Current week is 45 as of now. with data_holder as ( with tree_post as (Select contractid as conid, max(…
How to pivot rows into a single comma separated string
Important update: When I try to use the suggested string_agg method I get this error – Specified types or functions (one per INFO message) not supported on Redshift tables. Original question I have a query but I’m struggling to “pivot” multiple rows into a single column of strings. I h…