Overview
TheCOUNT()
window function allows you to retrieve the number of records that meet a specific criteria. When using it with he RANGE
clause, it allows you to perform counts within a defined range based on the values of the current row.
This function can be used with all data types supported by Oxla.
Syntax
There are two available variants of that function:COUNT(*)
: counts all rows in the target table, regardless of whether they contain NULL values or notCOUNT(expression)
: counts the number of non-NULL values in a specific column or expression
COUNT RANGE
COUNT()
window function always return BIGINT
as an output, which represents the total number of rows in a table irrespective of the input types.
Parameters
expression
: input’s column or expression that the function operates onPARTITION BY
: optional clause, which divides the result set into partitions to which the function is appliedROWS | RANGE BETWEEN
: range-based window frame relative to the current row
Examples
For the needs of this section, we will create awinsales
table that stores the details of some sales transactions:
COUNT(*)
In this example, we will focus on executing the variant of this function that counts all rows in the target table:COUNT(expression)
In this example, we will focus on executing the variant of this function that counts the number of non-NULL values in a specific expression:Time Series: COUNT(*) with RANGE for Last 90 Days
In this example, we will demonstrate counting the number of sales within a 90-day window prior to each sale, based ondateid
:
dateid
,
including the current sale: