Categories: Window

NTILE

Equally splits the rows in each partition into ranked parts specified by the integer value and starting from 1. This function requires the ORDER BY clause.

Syntax

NTILE(buckets) OVER (PARTITION BY partition_expression ORDER BY order_expression) → int

• buckets: A positive integer literal.
• partition_expression: An expression that groups rows into partitions.
• order_expression: An expression that specifies the order of the rows within each partition.

Examples

SELECT "Category", 
  "Descript", 
  "DayOfWeek",
  NTILE(1) 
    OVER (
      PARTITION BY "Category" 
      ORDER BY "DayOfWeek")
FROM Samples."samples.dremio.com"."SF_incidents2016.json"

-- Category, Descript, DayOfWeek, EXPR$3
-- ARSON, ARSON, Friday, 1 
-- EMBEZZLEMENT, EMBEZZLED VEHICLE, Friday, 1

Usage Notes

The example uses Dremio’s Samples Data Lake, which includes sample datasets that you can practice with. You must manually add this data lake. To add in Dremio Cloud, see Add Dremio’s Samples Data Lake to Your Project. Guidance to add in Dremio Software is in development.