MSUM (window)
Syntax
MSUM( value, rows_1 [ , rows_2 ] [ TOTAL | WITHIN ... | AMONG ... ] [ ORDER BY ... ] [ BEFORE FILTER BY ... ] )
Description
Returns the moving sum of values in a fixed-size window defined by the sort order and arguments:
rows_1 |
rows_2 |
Window |
|---|---|---|
| positive | - | The current row and rows_1 preceding rows. |
| negative | - | The current row and -rows_1 following rows. |
| any sign | any sign | rows_1 preceding rows, the current row and rows_2 following rows. |
Window functions with a similar behavior: MCOUNT, MMIN, MMAX, MAVG.
Argument types:
value—Numberrows_1—Number (whole)rows_2—Number (whole)
Return type: Same type as (value)
Note
Only constant values are accepted for arguments (rows_1, rows_2).
Warning
The sorting order is based on the fields listed in the chart's sorting section and in the function's ORDER BY clause. First, ORDER BY fields are used, and then they are complemented by the fields from the chart.
Examples
MSUM([Profit], -2)
MSUM([Profit], 3)
MSUM([Profit] 5, 5 TOTAL)
MSUM([Profit], -5 WITHIN [Date])
MSUM([Profit], -5 AMONG [Date])
Data source support
Materialized Dataset, ClickHouse 1.1, Microsoft SQL Server 2017 (14.0), MySQL 5.6, Oracle Database 12c (12.1), PostgreSQL 9.3.