Contact UsGet started

MAVG (window)

Written by
Improved by
Updated at November 6, 2024

Syntax

MAVG( value, rows_1 [ , rows_2 ] )

MAVG( value, rows_1 [ , rows_2 ]
      [ TOTAL | WITHIN ... | AMONG ... ]
      [ ORDER BY ... ]
      [ BEFORE FILTER BY ... ]
    )

More info:

Description

Warning

The sorting order is based on the fields listed in the sorting section of the chart and in the ORDER BY clause. First, ORDER BY fields are used, and then they are complemented by the fields from the chart.

Returns the moving average 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: MSUM, MCOUNT, MMIN, MMAX.

See also AVG, RAVG.

Argument types:

  • value — Fractional number | Integer
  • rows_1 — Integer
  • rows_2 — Integer

Return type: Same type as (value)

Note

Only constant values are accepted for the arguments (rows_1, rows_2).

Examples

Example with two and three arguments

Source data

Date City Category Orders Profit
'2019-03-01' 'London' 'Office Supplies' 8 120.80
'2019-03-04' 'London' 'Office Supplies' 2 100.00
'2019-03-05' 'London' 'Furniture' 1 750.00
'2019-03-02' 'Moscow' 'Furniture' 2 1250.50
'2019-03-03' 'Moscow' 'Office Supplies' 4 85.00
'2019-03-01' 'San Francisco' 'Office Supplies' 23 723.00
'2019-03-01' 'San Francisco' 'Furniture' 1 1000.00
'2019-03-03' 'San Francisco' 'Furniture' 4 4000.00
'2019-03-02' 'Detroit' 'Furniture' 5 3700.00
'2019-03-04' 'Detroit' 'Office Supplies' 25 1200.00
'2019-03-04' 'Detroit' 'Furniture' 2 3500.00

Grouped by [City].

Sorted by [City].

Formulas:

  • City: [City] ;
  • Order Sum: SUM([Orders]) ;
  • MAVG 1: MAVG(SUM([Orders]), 1) ;
  • MAVG 2: MAVG(SUM([Orders]), -2) ;
  • MAVG 3: MAVG(SUM([Orders]) 1, 1) .

Result

City Order Sum MAVG 1 MAVG 2 MAVG 3
'Detroit' 32 32.00 16.33 21.50
'London' 11 21.50 15.00 16.33
'Moscow' 6 8.50 17.00 15.00
'San Francisco' 28 17.00 28.00 17.00
Example with ORDER BY

Source data

Date City Category Orders Profit
'2019-03-01' 'London' 'Office Supplies' 8 120.80
'2019-03-04' 'London' 'Office Supplies' 2 100.00
'2019-03-05' 'London' 'Furniture' 1 750.00
'2019-03-02' 'Moscow' 'Furniture' 2 1250.50
'2019-03-03' 'Moscow' 'Office Supplies' 4 85.00
'2019-03-01' 'San Francisco' 'Office Supplies' 23 723.00
'2019-03-01' 'San Francisco' 'Furniture' 1 1000.00
'2019-03-03' 'San Francisco' 'Furniture' 4 4000.00
'2019-03-02' 'Detroit' 'Furniture' 5 3700.00
'2019-03-04' 'Detroit' 'Office Supplies' 25 1200.00
'2019-03-04' 'Detroit' 'Furniture' 2 3500.00

Grouped by [City].

Sorted by [City].

Formulas:

  • City: [City] ;
  • Order Sum: SUM([Orders]) ;
  • MAVG 1: MAVG(SUM([Orders]), 1 ORDER BY [City] DESC) ;
  • MAVG 2: MAVG(SUM([Orders]), 1 ORDER BY [Order Sum]) .

Result

City Order Sum MAVG 1 MAVG 2
'Detroit' 32 21.50 30.00
'London' 11 8.50 8.50
'Moscow' 6 17.00 6.00
'San Francisco' 28 28.00 19.50
Example with grouping

Source data

Date City Category Orders Profit
'2019-03-01' 'London' 'Office Supplies' 8 120.80
'2019-03-04' 'London' 'Office Supplies' 2 100.00
'2019-03-05' 'London' 'Furniture' 1 750.00
'2019-03-02' 'Moscow' 'Furniture' 2 1250.50
'2019-03-03' 'Moscow' 'Office Supplies' 4 85.00
'2019-03-01' 'San Francisco' 'Office Supplies' 23 723.00
'2019-03-01' 'San Francisco' 'Furniture' 1 1000.00
'2019-03-03' 'San Francisco' 'Furniture' 4 4000.00
'2019-03-02' 'Detroit' 'Furniture' 5 3700.00
'2019-03-04' 'Detroit' 'Office Supplies' 25 1200.00
'2019-03-04' 'Detroit' 'Furniture' 2 3500.00

Grouped by [City], [Category].

Sorted by [City], [Category].

Formulas:

  • City: [City] ;
  • Category: [Category] ;
  • Order Sum: SUM([Orders]) ;
  • MAVG 1: MAVG(SUM([Orders]), 1 TOTAL ORDER BY [City], [Category]) ;
  • MAVG 2: MAVG(SUM([Orders]), 1 WITHIN [City] ORDER BY [Category]) ;
  • MAVG 3: MAVG(SUM([Orders]), 1 AMONG [City] ORDER BY [City]) .

Result

City Category Order Sum MAVG 1 MAVG 2 MAVG 3
'Detroit' 'Furniture' 7 7.00 7.00 7.00
'Detroit' 'Office Supplies' 25 16.00 16.00 25.00
'London' 'Furniture' 1 13.00 1.00 4.00
'London' 'Office Supplies' 10 5.50 5.50 17.50
'Moscow' 'Furniture' 2 6.00 2.00 1.50
'Moscow' 'Office Supplies' 4 3.00 3.00 7.00
'San Francisco' 'Furniture' 5 4.50 5.00 3.50
'San Francisco' 'Office Supplies' 23 14.00 14.00 13.50

Data source support

ClickHouse 21.8, Files, Google Sheets, Microsoft SQL Server 2017 (14.0), MySQL 5.7, Oracle Database 12c (12.1), PostgreSQL 9.3, Yandex Documents.

Previous
LAST
Next
MAX