LogoLogo
latest
latest
  • Introduction
  • Basics
    • Concepts
      • Pinot storage model
      • Architecture
      • Components
        • Cluster
          • Tenant
          • Server
          • Controller
          • Broker
          • Minion
        • Table
          • Segment
            • Deep Store
            • Segment threshold
            • Segment retention
          • Schema
          • Time boundary
        • Pinot Data Explorer
    • Getting Started
      • Running Pinot locally
      • Running Pinot in Docker
      • Quick Start Examples
      • Running in Kubernetes
      • Running on public clouds
        • Running on Azure
        • Running on GCP
        • Running on AWS
      • Create and update a table configuration
      • Batch import example
      • Stream ingestion example
      • HDFS as Deep Storage
      • Troubleshooting Pinot
      • Frequently Asked Questions (FAQs)
        • General
        • Pinot On Kubernetes FAQ
        • Ingestion FAQ
        • Query FAQ
        • Operations FAQ
    • Indexing
      • Bloom filter
      • Dictionary index
      • Forward index
      • FST index
      • Geospatial
      • Inverted index
      • JSON index
      • Native text index
      • Range index
      • Star-tree index
      • Text search support
      • Timestamp index
      • Vector index
    • Release notes
      • 1.3.0
      • 1.2.0
      • 1.1.0
      • 1.0.0
      • 0.12.1
      • 0.12.0
      • 0.11.0
      • 0.10.0
      • 0.9.3
      • 0.9.2
      • 0.9.1
      • 0.9.0
      • 0.8.0
      • 0.7.1
      • 0.6.0
      • 0.5.0
      • 0.4.0
      • 0.3.0
      • 0.2.0
      • 0.1.0
    • Recipes
      • Connect to Streamlit
      • Connect to Dash
      • Visualize data with Redash
      • GitHub Events Stream
  • For Users
    • Query
      • Querying Pinot
      • Query Syntax
        • Explain Plan (Single-Stage)
        • Filtering with IdSet
        • GapFill Function For Time-Series Dataset
        • Grouping Algorithm
        • JOINs
        • Lookup UDF Join
      • Query Options
      • Query Quotas
      • Query using Cursors
      • Multi-stage query
        • Understanding Stages
        • Stats
        • Optimizing joins
        • Join strategies
          • Random + broadcast join strategy
          • Query time partition join strategy
          • Colocated join strategy
          • Lookup join strategy
        • Hints
        • Operator Types
          • Aggregate
          • Filter
          • Join
          • Intersect
          • Leaf
          • Literal
          • Mailbox receive
          • Mailbox send
          • Minus
          • Sort or limit
          • Transform
          • Union
          • Window
        • Stage-Level Spooling
      • Explain plan
    • APIs
      • Broker Query API
        • Query Response Format
      • Broker GRPC API
      • Controller Admin API
      • Controller API Reference
    • External Clients
      • JDBC
      • Java
      • Python
      • Golang
    • Tutorials
      • Use OSS as Deep Storage for Pinot
      • Ingest Parquet Files from S3 Using Spark
      • Creating Pinot Segments
      • Use S3 as Deep Storage for Pinot
      • Use S3 and Pinot in Docker
      • Batch Data Ingestion In Practice
      • Schema Evolution
  • For Developers
    • Basics
      • Extending Pinot
        • Writing Custom Aggregation Function
        • Segment Fetchers
      • Contribution Guidelines
      • Code Setup
      • Code Modules and Organization
      • Dependency Management
      • Update documentation
    • Advanced
      • Data Ingestion Overview
      • Ingestion Aggregations
      • Ingestion Transformations
      • Null value support
      • Use the multi-stage query engine (v2)
      • Advanced Pinot Setup
    • Plugins
      • Write Custom Plugins
        • Input Format Plugin
        • Filesystem Plugin
        • Batch Segment Fetcher Plugin
        • Stream Ingestion Plugin
    • Design Documents
      • Segment Writer API
  • For Operators
    • Deployment and Monitoring
      • Set up cluster
      • Server Startup Status Checkers
      • Set up table
      • Set up ingestion
      • Decoupling Controller from the Data Path
      • Segment Assignment
      • Instance Assignment
      • Rebalance
        • Rebalance Servers
          • Examples and Scenarios
        • Rebalance Brokers
        • Rebalance Tenant
      • Separating data storage by age
        • Using multiple tenants
        • Using multiple directories
      • Pinot managed Offline flows
      • Minion merge rollup task
      • Consistent Push and Rollback
      • Access Control
      • Monitoring
      • Tuning
        • Tuning Default MMAP Advice
        • Real-time
        • Routing
        • Query Routing using Adaptive Server Selection
        • Query Scheduling
      • Upgrading Pinot with confidence
      • Managing Logs
      • OOM Protection Using Automatic Query Killing
      • Pause ingestion based on resource utilization
    • Command-Line Interface (CLI)
    • Configuration Recommendation Engine
    • Tutorials
      • Authentication
        • Basic auth access control
        • ZkBasicAuthAccessControl
      • Configuring TLS/SSL
      • Build Docker Images
      • Running Pinot in Production
      • Kubernetes Deployment
      • Amazon EKS (Kafka)
      • Amazon MSK (Kafka)
      • Monitor Pinot using Prometheus and Grafana
      • Performance Optimization Configurations
      • Segment Operations Throttling
      • Reload a table segment
  • Configuration Reference
    • Cluster
    • Controller
    • Broker
    • Server
    • Table
    • Ingestion
    • Schema
    • Database
    • Ingestion Job Spec
    • Monitoring Metrics
    • Plugin Reference
      • Stream Ingestion Connectors
      • VAR_POP
      • VAR_SAMP
      • STDDEV_POP
      • STDDEV_SAMP
    • Dynamic Environment
  • Manage Data
    • Import Data
      • SQL Insert Into From Files
      • Upload Pinot segment Using CommandLine
      • Batch Ingestion
        • Spark
        • Flink
        • Hadoop
        • Backfill Data
        • Dimension table
      • Stream Ingestion
        • Ingest streaming data from Apache Kafka
        • Ingest streaming data from Amazon Kinesis
        • Ingest streaming data from Apache Pulsar
        • Configure indexes
        • Stream ingestion with CLP
      • Upsert and Dedup
        • Stream ingestion with Upsert
        • Segment compaction on upserts
        • Stream ingestion with Dedup
      • Supported Data Formats
      • File Systems
        • Amazon S3
        • Azure Data Lake Storage
        • HDFS
        • Google Cloud Storage
      • Complex Type (Array, Map) Handling
        • Complex Type Examples (Unnest)
      • Ingest records with dynamic schemas
  • Functions
    • Aggregation Functions
    • Transformation Functions
    • Array Functions
    • Funnel Analysis Functions
    • Hash Functions
    • JSON Functions
    • User-Defined Functions (UDFs)
    • URL Functions
    • Unique Count and cardinality Estimation Functions
  • Window Functions
  • (Deprecating) Function List
    • ABS
    • ADD
    • ago
    • EXPR_MIN / EXPR_MAX
    • ARRAY_AGG
    • arrayConcatDouble
    • arrayConcatFloat
    • arrayConcatInt
    • arrayConcatLong
    • arrayConcatString
    • arrayContainsInt
    • arrayContainsString
    • arrayDistinctInt
    • arrayDistinctString
    • arrayIndexOfInt
    • arrayIndexOfString
    • ARRAYLENGTH
    • arrayRemoveInt
    • arrayRemoveString
    • arrayReverseInt
    • arrayReverseString
    • arraySliceInt
    • arraySliceString
    • arraySortInt
    • arraySortString
    • arrayUnionInt
    • arrayUnionString
    • AVGMV
    • Base64
    • caseWhen
    • ceil
    • CHR
    • codepoint
    • concat
    • count
    • COUNTMV
    • COVAR_POP
    • COVAR_SAMP
    • day
    • dayOfWeek
    • dayOfYear
    • DISTINCT
    • DISTINCTCOUNT
    • DISTINCTCOUNTMV
    • DISTINCT_COUNT_OFF_HEAP
    • SEGMENTPARTITIONEDDISTINCTCOUNT
    • DISTINCTCOUNTBITMAP
    • DISTINCTCOUNTBITMAPMV
    • DISTINCTCOUNTHLL
    • DISTINCTCOUNTHLLMV
    • DISTINCTCOUNTRAWHLL
    • DISTINCTCOUNTRAWHLLMV
    • DISTINCTCOUNTSMARTHLL
    • DISTINCTCOUNTHLLPLUS
    • DISTINCTCOUNTULL
    • DISTINCTCOUNTTHETASKETCH
    • DISTINCTCOUNTRAWTHETASKETCH
    • DISTINCTSUM
    • DISTINCTSUMMV
    • DISTINCTAVG
    • DISTINCTAVGMV
    • DIV
    • DATETIMECONVERT
    • DATETRUNC
    • exp
    • FIRSTWITHTIME
    • FLOOR
    • FrequentLongsSketch
    • FrequentStringsSketch
    • FromDateTime
    • FromEpoch
    • FromEpochBucket
    • FUNNELCOUNT
    • FunnelCompleteCount
    • FunnelMaxStep
    • FunnelMatchStep
    • GridDistance
    • Histogram
    • hour
    • isSubnetOf
    • JSONFORMAT
    • JSONPATH
    • JSONPATHARRAY
    • JSONPATHARRAYDEFAULTEMPTY
    • JSONPATHDOUBLE
    • JSONPATHLONG
    • JSONPATHSTRING
    • jsonextractkey
    • jsonextractscalar
    • LAG
    • LASTWITHTIME
    • LEAD
    • length
    • ln
    • lower
    • lpad
    • ltrim
    • max
    • MAXMV
    • MD5
    • millisecond
    • min
    • minmaxrange
    • MINMAXRANGEMV
    • MINMV
    • minute
    • MOD
    • mode
    • month
    • mult
    • now
    • percentile
    • percentileest
    • percentileestmv
    • percentilemv
    • percentiletdigest
    • percentiletdigestmv
    • percentilekll
    • percentilerawkll
    • percentilekllmv
    • percentilerawkllmv
    • quarter
    • regexpExtract
    • regexpReplace
    • remove
    • replace
    • reverse
    • round
    • roundDecimal
    • ROW_NUMBER
    • rpad
    • rtrim
    • second
    • sha
    • sha256
    • sha512
    • sqrt
    • startswith
    • ST_AsBinary
    • ST_AsText
    • ST_Contains
    • ST_Distance
    • ST_GeogFromText
    • ST_GeogFromWKB
    • ST_GeometryType
    • ST_GeomFromText
    • ST_GeomFromWKB
    • STPOINT
    • ST_Polygon
    • strpos
    • ST_Union
    • SUB
    • substr
    • sum
    • summv
    • TIMECONVERT
    • timezoneHour
    • timezoneMinute
    • ToDateTime
    • ToEpoch
    • ToEpochBucket
    • ToEpochRounded
    • TOJSONMAPSTR
    • toGeometry
    • toSphericalGeography
    • trim
    • upper
    • Url
    • UTF8
    • VALUEIN
    • week
    • year
    • Extract
    • yearOfWeek
    • FIRST_VALUE
    • LAST_VALUE
    • ST_GeomFromGeoJSON
    • ST_GeogFromGeoJSON
    • ST_AsGeoJSON
  • Reference
    • Single-stage query engine (v1)
    • Multi-stage query engine (v2)
    • Troubleshooting
      • Troubleshoot issues with the multi-stage query engine (v2)
      • Troubleshoot issues with ZooKeeper znodes
      • Realtime Ingestion Stopped
  • RESOURCES
    • Community
    • Team
    • Blogs
    • Presentations
    • Videos
  • Integrations
    • Tableau
    • Trino
    • ThirdEye
    • Superset
    • Presto
    • Spark-Pinot Connector
  • Contributing
    • Contribute Pinot documentation
    • Style guide
Powered by GitBook
On this page
  • Window Functions overview
  • Window function syntax
  • Example window function query layout
  • Window mechanism (OVER clause)
  • Window functions
  • Window aggregate query examples
  • Sum transactions by customer ID
  • Find the minimum or maximum transaction by customer ID
  • Find the average transaction amount by customer ID
  • Rank year-to-date sales for a sales team
  • Count the number of transactions by customer ID

Was this helpful?

Edit on GitHub
Export as PDF

Window Functions

Use window functions to compute averages, sort, rank, or count items, calculate sums, and find minimum or maximum values across windows.

PreviousUnique Count and cardinality Estimation FunctionsNext(Deprecating) Function List

Last updated 6 months ago

Was this helpful?

Important: To query using Windows functions, you must enable Pinot's . See how to ).

Window Functions overview

This is an overview of the window functions feature.

Window function syntax

Pinot's window function (windowedCall) has the following syntax definition:

windowedCall:
      windowFunction
      OVER 
      window

windowFunction:
      function_name '(' value [, value ]* ')'
   |
      function_name '(' '*' ')'

window:
      '('
      [ PARTITION BY expression [, expression ]* ]
      [ ORDER BY orderItem [, orderItem ]* ]
      [
          RANGE BETWEEN frame_start AND frame_end
        |   
          ROWS BETWEEN frame_start AND frame_end
        |
          RANGE frame_start
        |
          ROWS frame_start    
      ]
      ')'
      
frame_start:
      UNBOUNDED PRECEDING
    |
      offset PRECEDING
    |
      CURRENT ROW
    |  
      offset FOLLOWING
     
frame_end:
      offset PRECEDING
    |
      CURRENT ROW
    |
      offset FOLLOWING
    |
      UNBOUNDED FOLLOWING       
  • windowedCall refers to the actual windowed operation.

Example window function query layout

The following query shows the complete components of the window function. Note that the PARTITION BY ,ORDER BY, and the FRAME clauses are all optional.

SELECT FUNC(column1) OVER (PARTITION BY column2 ORDER BY column3 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    FROM tableName
    WHERE filter_clause  

Window mechanism (OVER clause)

Partition by clause

  • If a PARTITION BY clause is specified, the intermediate results will be grouped into different partitions based on the values of the columns appearing in the PARTITION BY clause.

  • If the PARTITION BY clause isn’t specified, the whole result will be regarded as one big partition, i.e. there is only one partition in the result set.

Order by clause

  • If an ORDER BY clause is specified, all the rows within the same partition will be sorted based on the values of the columns appearing in the window ORDER BY clause. The ORDER BY clause decides the order in which the rows within a partition are to be processed.

  • If no ORDER BY clause is specified while a PARTITION BY clause is specified, the order of the rows is undefined. To order the output, use a global ORDER BY clause in the query.

Frame clause

RANGE type window frames currently cannot be used with offset PRECEDING / offset FOLLOWING

The following window frame clauses are currently supported:

  • RANGE frame_start where frame_start can be UNBOUNDED PRECEDING or CURRENT ROW (frame_end will default to CURRENT ROW)

  • ROWS frame_start where frame_start can be UNBOUNDED PRECEDING, offset PRECEDING, or CURRENT ROW (frame_end will default to CURRENT ROW)

  • RANGE BETWEEN frame_start AND frame_end; frame_start can be either UNBOUNDED PRECEDING or CURRENT ROW and frame_end can be either CURRENT ROW or UNBOUNDED FOLLOWING

  • ROWS BETWEEN frame_start AND frame_end; frame_start / frame_end can be one of:

    • UNBOUNDED PRECEDING (frame_start only)

    • offset PRECEDING where offset is an integer literal

    • CURRENT ROW

    • offset FOLLOWING where offset is an integer literal

    • UNBOUNDED FOLLOWING (frame_end only)

In RANGE mode, a frame_start of CURRENT ROW means the frame starts with the current row's first peer row (a row that the window's ORDER BY clause sorts as equivalent to the current row), while a frame_end of CURRENT ROW means the frame ends with the current row's last peer row. In ROWS mode, CURRENT ROW simply means the current row.

If no ORDER BY clause is specified, the window frame will always be RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING and cannot be modified. When an ORDER BY clause is present, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW if no explicit window frame is defined in the query.

If there is no FRAME, no PARTITION BY, and no ORDER BY clause specified in the OVER clause (empty OVER), the whole result set is regarded as one partition, and there's one frame in the window.

Inside the over clause, there are three optional components: PARTITION BY clause, ORDER BY clause, and FRAME clause.

Window functions

Window functions are commonly used to do the following:

Supported window functions are listed in the following table.

Function
Description
Example
Default Value When No Record Selected

Returns the average of the values for a numeric column in the defined window.

AVG(playerScore)

Double.NEGATIVE_INFINITY

BOOL_AND

Returns false if even a single value in the window is false, null if a single value in the window is null, and true if all the values in the window are true.

null

BOOL_OR

Returns true if even a single value in the window is true , null if a single value in the window is null, and false if all the values in the window are false.

null

Returns the number of values in the window

COUNT(*)

0

Returns the minimum value of a numeric column as Double

MIN(playerScore)

null

Returns the maximum value of a numeric column as Double

MAX(playerScore)

null

Returns the sum of the values for a numeric column as Double

SUM(playerScore)

null

The LEAD function provides access to a subsequent row within the same result set, without the need for a self-join.

LEAD(column_name, offset, default_value)

The LAG function provides access to a previous row within the same result set, without the need for a self-join.

LAG(column_name, offset, default_value)

The FIRST_VALUE function returns the value from the first row in the window.

FIRST_VALUE(salary)

The LAST_VALUE function returns the value from the last row in the window

LAST_VALUE(salary)

Returns the number of the current row within its partition, counting from 1.

ROW_NUMBER()

RANK

Returns the rank of the current row, with gaps - i.e., the row_number of the first row in its peer group.

RANK()

DENSE_RANK

Returns the rank of the current row, without gaps.

DENSE_RANK()

Note that no window frame clause can be specified for ROW_NUMBER, RANK, and DENSE_RANK window functions since they're applied on the entire partition by definition. Similarly, no window frame clause can be specified for LAG and LEAD since the row offset is an input to those functions themselves.

Window aggregate query examples

Sum transactions by customer ID

Calculate the rolling sum transaction amount ordered by the payment date for each customer ID (note, the default frame here is UNBOUNDED PRECEDING and CURRENT ROW).

SELECT customer_id, payment_date, amount, SUM(amount) OVER(PARTITION BY customer_id ORDER BY payment_date) from payment;
customer_id
payment_date
amount
sum

1

2023-02-14 23:22:38.996577

5.99

5.99

1

2023-02-15 16:31:19.996577

0.99

6.98

1

2023-02-15 19:37:12.996577

9.99

16.97

1

2023-02-16 13:47:23.996577

4.99

21.96

2

2023-02-17 19:23:24.996577

2.99

2.99

2

2023-02-17 19:23:24.996577

0.99

3.98

3

2023-02-16 00:02:31.996577

8.99

8.99

3

2023-02-16 13:47:36.996577

6.99

15.98

3

2023-02-17 03:43:41.996577

6.99

22.97

4

2023-02-15 07:59:54.996577

4.99

4.99

4

2023-02-16 06:37:06.996577

0.99

5.98

Find the minimum or maximum transaction by customer ID

Calculate the least (use MIN()) or most expensive (use MAX()) transaction made by each customer comparing all transactions made by the customer (default frame here is UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING). The following query shows how to find the least expensive transaction.

SELECT customer_id, payment_date, amount, MIN(amount) OVER(PARTITION BY customer_id) from payment;
customer_id
payment_date
amount
min

1

2023-02-14 23:22:38.996577

5.99

0.99

1

2023-02-15 16:31:19.996577

0.99

0.99

1

2023-02-15 19:37:12.996577

9.99

0.99

2

2023-04-30 04:34:36.996577

4.99

4.99

2

2023-04-30 12:16:09.996577

10.99

4.99

3

2023-03-23 05:38:40.996577

2.99

2.99

3

2023-04-07 08:51:51.996577

3.99

2.99

3

3 | 2023-04-08 11:15:37.996577

4.99

2.99

Find the average transaction amount by customer ID

Calculate a customer’s average transaction amount for all transactions they’ve made (default frame here is UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING).

SELECT customer_id, payment_date, amount, AVG(amount) OVER(PARTITION BY customer_id) from payment;
customer_id
payment_date
amount
avg

1

2023-02-14 23:22:38.996577

5.99

5.66

1

2023-02-15 16:31:19.996577

0.99

5.66

1

2023-02-15 19:37:12.996577

9.99

5.66

2

2023-04-30 04:34:36.996577

4.99

7.99

2

2023-04-30 12:16:09.996577

10.99

7.99

3

2023-03-23 05:38:40.996577

2.99

3.99

3

2023-04-07 08:51:51.996577

3.99

3.99

3

2023-04-08 11:15:37.996577

4.99

3.99

Rank year-to-date sales for a sales team

Use ROW_NUMBER() to rank team members by their year-to-date sales (default frame here is UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING).

SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row,   
    FirstName, LastName AS "Total sales YTD"   
FROM Sales.vSalesPerson;  
Row
FirstName
LastName
Total sales YTD

1

Joe

Smith

2251368.34

2

Alice

Davis

2151341.64

3

James

Jones

1551363.54

4

Dane

Scott

1251358.72

Count the number of transactions by customer ID

Count the number of transactions made by each customer (default frame here is UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING).

SELECT customer_id, payment_date, amount, count(amount) OVER(PARTITION BY customer_id) from payment;
customer_id
payment_date
amount
count

1

2023-02-14 23:22:38.99657

10.99

2

1

2023-02-15 16:31:19.996577

8.99

2

2

2023-04-30 04:34:36.996577

23.50

3

2

2023-04-07 08:51:51.996577

12.35

3

2

2023-04-08 11:15:37.996577

8.29

3

windowFunction refers to the window function used, see supported .

window is the window definition / windowing mechanism, see supported .

You can jump to the section to see more concrete use cases of window functions in Pinot.

The OVER clause applies a specified supported to compute values over a group of rows and return a single result for each row. The OVER clause specifies how the rows are arranged and how the aggregation is done on those rows.

multi-stage query engine (v2)
enable and use the multi-stage query engine (v2
window functions
window mechanism
examples
windows function
Compute averages
Rank items
Count items
Calculate sums
Find minimum or maximum values
Sum transactions by customer ID
Find the minimum or maximum transaction by customer ID
Find the average transaction amount by customer ID
Rank year-to-date sales for a sales team
Count the number of transactions by customer ID
AVG
COUNT
MIN
MAX
SUM
LEAD
LAG
FIRST_VALUE
LAST_VALUE
ROW_NUMBER