MSUM
Window functions
Syntax
MSUM( value, rows_1 [ , rows_2 ] [ TOTAL | WITHIN [ dim1, ... ] | AMONG [ dim1, ... ] ] )
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
Function depends on how data is ordered in the chart. It will work correctly only if:
- data ordering is explicitly specified in the chart;
- result is ordered by all fields that don't use window functions.
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, PostgreSQL 9.3.