I am working on an aws athena query like so: Which will give results like so: Right now I have maxprice and minprice as two separate columns, but I want to combine them into one column “price range” which will have minprice and maxprice separated by a ” – ” string, so it would lo…
Tag: amazon-athena
aws athena SQL query; get unique count of column2 for each unique column1
I am fairly new to SQL queries, and am working with querying an aws athena database. My first query will return every unique brand given some parameters: Returning a list like this: And so on and so on for 553 rows. If I pick any of the brands, such as ‘Seraz’ and run this second query, it will re…
How to successfully convert string to date type in AWS Athena?
I’m trying to convert a date column of string type to date type. I use the below query in AWS Athena: SELECT a, b, date_parse(date_start, ‘%m-%d-%Y’) AS date_start FROM “database”.”…
Athena UNION – do I need to define struct columns?
I have a bunch of tables in Athena that contain structs with different nested columns. I’d like to query from all of those tables as a single table (ie, a union view) and I’d like to be able to return the nested column from the struct only if it exists, otherwise return a null. Example: I’d …
How to calculate average for every month from start from year in Presto’s SQL (Athena)?
Below is an example of the table data I have I would like to generate a result set as below I tried to use window function OVER() and PARTITION() but I managed to get average on month by month rather than starting from the year. Any suggestions, please. Thanks Answer I think you want: This puts each year in a
SYNTAX ERROR:GROUP BY clause cannot contain aggregations or window functions
select json_extract(other_detail,’$.is_substitute_allowed’) as substitute, count(date(order_date)) as order_date from prescription_metrics group by 1; I am unable to group by over the …
Athena nested Struct Querying – how to query Value_counts in SQL
I have a big nested struct in AWS Athena. Here’s one column named “petowners” in the table: I’m looking for: The equivalent of the python function value_counts in the column. Meaning I’m looking for the SQL Athena command that will output for this row: [mammals:1, birds:2, Unknow…
Athena sql query to find items not containing a value
I have a table in a bucket, I am using Athena to get the required data My table looks like I need to find all the resources where A-1 is not found the result should give me i-2. How to write in sql Answer You can use aggregation to group all rows having the same resourceid together, and then filter
SYNTAX_ERROR: line 1:1: DISTINCT in window function parameters not yet supported
I have a table below : I am writing a code below to get the next timestamp using T5 and then calculate the duration. However for the last record where T5 and T6 are equal I want to fill in the average of the previous durations. However I am getting an error in doing so. What am i doing wrong?
How to get top N rows with some conditions
I have a query something like this: But now, I want to see the top 10 product_id for each site and category_id based on the clicks. When I write the LIMIT function, it only shows the top 10 products but it does not group it by category_id and shop_id. How can I do this? Answer Use window functions. You can