I have a table which has a numeric(23,2) field that I need to divide to a constant. My baseline is this aggregation select site, sum(sales) / 1.07 as sales from sales group by site; But when I add …
Tag: amazon-redshift
How do I convert Timestamp (having milliseconds) to EPOCH in Redshift
How do I convert from timestamp (having milliseconds) to epoch For E.g., Using EPOCH gives the same results for both the timestamps, even though they are different timestamps (different milliseconds) Query- I want different results as they have separate timestamps Answer Just don’t cast to bigint if you…
Amazon Redshift – lateral column alias reference
Based on Amazon Redshift announces support for lateral column alias reference: The support for lateral column alias reference enables you to write queries without repeating the same expressions in the SELECT list. For example, you can define the alias ‘probability’ and use it within the same selec…
Redshift: Find max level of previous mode
I have a user table that store level of each category completion. User can unlock the second and the third category after a few levels of the first category completion. My goal is to find which level …
Is there any better way to avoid data skew by join in Redshift?
The query SQL as below cause one node of Redshift cluster disk full And query plan is From the above image we know node-39 hold more data than other nodes. Because data is skewed by join. To solve this issue, we try to use update instead of join Query plan The data is distributed evenly on all nodes according…
RedShift Error when using COUNT (Distinct XXX) ERROR: XX000: This type of correlated subquery pattern is not supported due to internal error
I have a small query running in RedShift through Aginity that is getting the following error: ERROR: XX000: This type of correlated subquery pattern is not supported due to internal error Current …
Process several billion records from Redshift using custom logic
I want to apply custom logic over dataset placed in Redshift. Example of input data: userid, event, fileid, timestamp, …. 100000, start, 120, 2018-09-17 19:11:40 100000, done, 120, 2018-…
Partitioned by gives me error column duplicated when creating external table
I’m creating an external table using ‘partitioned by’ but I get the following error: ‘Invalid operation: column “#id” duplicated’ I first thought it could have something to do with the character #, …
Convert HH:MM:S to seconds int
I have two columns with the format YYYY-MM-DD HH:MM:SS When I do the difference between these two columns, my result has a format of the form HH:MM:SS I would like to convert this result into seconds….
How to do like compare on case sensitive string
select http_host,user_agent,date,path, count(*) FROM “public”.”bus_request” where app_name = ‘yyyy’ and event_type ‘browser_js’ and date = GETDATE()-14 and …