I have a table(users) with these sample data user location name 111 usa aaa 222 canada bbb 333 usa ccc 444 mexico ddd 555 japan eee …
Tag: amazon-athena
How to assign a field name to an SQL Count in AWS Athena SQL
I’m still new to Athena. I think I got my database defined correctly, as shown in Example 1 below. However, when I run a count query, I get results unlike what I would expect. Example 1: Works Fine except count is called “_col3” Result: Example 2: syntax error This query shows a syntax error when I click “Run Query”: Answer
AWS Athena: Unsupported correlated subquery type but it works on Mysql and other flavors
For each order in orders table, I’m trying to return the latest order update value based on time_sent, grouped into a single row. The above select query works fine in MySql but not in AWS Athena. Working MySQL example below: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=66f17cf9f3c8f19f758c7cb1381d8207 Is there a way to accomplish the above using joins? Answer It’s possible to achieve this with Window Functions
Need an SQL query that will left join with another table, which will in turn return the latest values based on time, grouped into a single row
I need help with a SELECT SQL query that will left join with another table on id column, which will in turn return the latest values based on time, grouped into a single row? Basically, join the two tables in such a way that for each record in users table that exists in time_series table, return the latest values based
SQL query to get Column A with all Column B
I have a table that has 2 columns customer and city . there are 4 unique cities (w,x,y,z) I want customers that is associated with all cities. So for the table +———-+——+ | Customer | City | +———-+——+ | A | W | | A | X | | A | Y | | A | Z | | B
SQL Data cleaning
I have a data set where I am trying to clean data. I want to remove the ** from email-address and phone_number and have just numbers in the phone_number column. how can i do it. Answer Here is one option using string functions: This removes ‘**’ from email, and all non-digit characters from phone_number.
Complex SQL query aggregation and grouping on athena
I have a table like this: I would like to retrieve the number of chat performed by users for each database (db) and the last part where I fail, retrieve also a list of all mentors by users. The final output should be like this for example (notice there is only one time max for greg in the admin column)
Mapping two columns into one column in Athena
I have data in the Athena something like this: However, the table owner changed the provider to provider_new. Thus, after 2020-08-01 the provider returns to NULL. Here is my query: How can I map these two columns to one? Thanks in advance. Answer Do you want coalesce()? coalesce() returns the first non-null value of the arguments it is given.
SQL find max date based on a non null other column
I have a table like so: And I would like to return an entire row containing the uniqueID, scandatetime, and latest scanfacilityname (i.e., max scandatetime where scanfacilityname is not null). I have tried the following query: But im not sure if this is correct/if I need the coalesce. Answer You can use the max_by function: See the doc. There’s no
dbGetQuery unavailable for noctua::athena
I connected RStudio to Amazon Athena, and the database connection was successful. con <- dbConnect(noctua::athena(), aws_access_key_id = "***", ...