So I have 2 Json arrays that need unnesting, and joining based on a key within the json structure. In theory is easy, but without having a ‘left join unnest’ functionality, it all becomes messy. I have achieved what I want, by grouping the results; but I also have concerns that it is doing 2 cross joins, effectively generating many
Tag: amazon-athena
SQL for adding another column showing Nth occurrence of unique ID within data-set
I have a table of patient visits, where each row is a visit. There is a column with the unique patient ID to signify which client the visit was for. I would like to add another column which shows the Nth occurrence of that patient ID within the data-set. So first visit would show 1, second visit shows 2 and
Create Athena table using JOOQ java
How can I create a Athena table using jooq library in java. Since Athena table uses syntax like create external table and we need to specifiy s3 bucket path also. I have a list of string from which i want to create the athena table. Can somebody suggest a way how i can do it with jooq library. Answer Amazon
sql: query to find max count with extra columns as well
Input table:events GOAL I want max(distinct user) grouped by month column. my final result need two columns one is month and another one is max_count I need output similar to this I followed some approach Approach1: if i follow this approach, it is just giving me only max_count but i need month column as well I know we can use
Presto SQL – Looking to explode Table of IDs to include predefined row numbers
Apologies if this title was a bit confusing. It’s tough to articulate this problem in a sentence. Here is my current table I’d like to explode this table to look like this: Any help would be much appreciated. Answer You want a cross join:
MYSQL in AWS Athena
Using AWS Athena (so querying with MySql) I’m trying to retrieve information how many times the same IP has been logged. I have columns: timestamp, IP, country. I would like to count unique occurrences of each IP. I’m trying with this query but results are wrong: Thank you for helping achieving me this counter query. EDIT: Sample data: Answer If
AWS Athena custom data format?
I’d like to query my app logs on S3 with AWS Athena but I’m having trouble creating the table/specifying the data format. This is how the log lines look: 2020-12-09T18:08:48.789Z {“reqid”:&…
calculating percentiles in aws athena
result of my query is being used in aws quicksight. even though quicksight offers percentileCont() which does the job for us I want to use it in the query instead of using calculated field. eventually what I want to do is create a point column where depending on a column that ranges from [a, b]. Right now I find out
Calculate Median for each group in AWS Athena table
Below is the schema for the athena table I wish to calculate median for ‘parameter_value’ group by standard_lab_parameter_name & units. For this I followed link : https://docs.aws.amazon.com/redshift/latest/dg/r_MEDIAN.html But on running the query It throws error Any help? Or if some alternative query would be great Answer Athena is based on Presto 0.172 – you can see all supported functions in
Hierarchical SQL query to Athena
I’m trying to create a query in Athena that solves this problem: I have records that look like this which create a hierarchical structure, like a tree but with indeterminate children. I have more than one root, that is, more than one element that is not children of anyone. I want to get the complete structure for one of them.