AWS

How to assign rows to ranked groups in AWS Athena

How to use the NTILE function in Athena

Bartosz Mikulski 13 Dec 2020 – 1 min read

A ranked group is a method of grouping rows in the following way:

First, we have to order the rows by a column.
After that, we determine how many rows should be in every group. All of the groups (except the last one) must have the same number of rows.
In the end, we traverse the ordered rows and assign them to groups one by one.

For example, let’s assume that I have numbers 3, 5, 1, 8, 2, 4, 7, 9, 0, and I want to assign them to three groups.

In the first step, I have to order them 0, 1, 2, 3, 4, 5, 7, 8, 9. I calculate the size of the group. I have nine numbers, and I want three groups, so every group contains three numbers. Finally, I assign the numbers to groups:

Group I: 0, 1, 2
Group II, 3, 4, 5
Group III: 7, 8, 9

How do we do that in Athena? Fortunately, there is a built-in function NTILE:

SELECT t.*,,
       NTILE(3) OVER (ORDER BY col)
FROM table t;

Older post

How to define an AWS Athena view using Airflow

How to use the AWSAthenaOperator

Newer post

How to generate a sequence of dates in Redshift

How to use the generate_series function to generate a sequence of dates

MLOps AWS

How to deploy a Transformer-based model with custom preprocessing code to Sagemaker Endpoints using BentoML

Deploy a machine learning model with custom inference code to a Sagemaker Endpoint using BentoML
Published on: 01 Oct 2021

AWS MLOps

Multimodel deployment in Sagemaker Endpoints

How to deploy multiple models in a single Sagemaker Endpoint?
Published on: 28 May 2021

AWS

How to deploy a REST API AWS Lambda using Chalice and AWS Code Pipeline

How to create a REST API Endpoint using AWS Lambda, Chalice, and AWS Code Pipeline
Published on: 16 Apr 2021