Amazon Athena - ANSI SQL - Use inner or nested SELECT statements as alternative to temp tables or variables

Amazon Athena - ANSI SQL - Use inner or nested SELECT statements as alternative to temp tables or variables

When creating more complex SQL queries, it can help to break the problem down into smaller parts.

With other platforms such as Microsoft SQL Server (T-SQL) it is possible to use temp tables, or variables, to stage the retrieval work.

However Amazon Athena uses the more limited ANSI flavour of SQL.

One option to at least break down the development side of the problem, is to use nested or inner SELECT statements. 

Example:

-- Inspections by Run Kind and Month

select year_and_month, run_kind, count(event_name) as inspections

from

(

SELECT

year_and_month,

event_name

FROM

(

select

event_name,

SUBSTRING( CAST(timestamp AS VARCHAR) FROM 1 FOR 7) AS year_and_month,

CAST(json_extract(event_data_json, '$.run_kind') as varchar) as run_kind

from myDatabase.analytics_events

WHERE event_name = 'INSPECTION_FINISHED'

AND application_version not in ('9.0.0.0')

AND timestamp >= date('2021-10-01')

) data

inner join

(

select

distinct(projectid) as paid_projectid

FROM "otherDatabase"."paid_projects"

) paid_projects

on data.project_id = paid_projects. project_id

)

group by year_and_month, run_kind_with_default

order by year_and_month

Here, the innermost SELECT prepares the columns, processing a timestamp into a simpler text format (year and month as a VARCHAR). Relevant property 'run_kind' is extracted from a JSON field.

Then, the outer SELECT statements do not need to deal with these extraction steps, and can focus on aggregation and ordering.

So there is a kind of separation of responsibility between the inner select statement, that extracts and prepares fields, and then out select statements that perform aggregation and final ordering.

note: this needs careful construction to avoid very expensive queries, but it is a technique that allows for sophisticated querying and aggregation, even in ANSI SQL.