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: presto
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…
Presto equivalent of CONCAT_WS
I’m looking for a function in Presto to concat two columns with a separator like underline. Answer Your are looking here for the array_join function, see docs. array_join(x, delimiter, null_replacement) → varchar Concatenates the elements of the given array using the delimiter and an optional string to …
Is there a way to group by part of a datetime column in SQL?
I have a dataframe something like this. is there a way to return a table that sums the dollar values based on the time down to the minute. For example: Notice that the “time” data in the original data set spans over a few days but I am only concerned with the time of day. Thanks Answer I’m n…
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”.”…
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
What is cardinality() in presto sql?
Does anyone know the purpose of cardinality() function in sql? How does it work with unnest function? SELECT story_id, cardinality(image_tags) AS image_tags, cardinality(comment_tags) as …
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
Generate list of all dates between x & y
I am tyring to use a solution I found online to generate a table of all dates between yesterday and Jan 1st of the prior year. SELECT CAST(date_column AS DATE) AS DAY FROM ( VALUES (SEQUENCE(…
want to use sql count online people with begintime and endtime
I want to use sql count online people with begintime and endtime on presto。 my data like ： userid begintime endtime 023150000030040 2020-03-05 12:50:46 2020-03-05 12:50:49 023150004186637 2020-03-…