LAG (window)
Syntax
LAG( value [ , offset [ , default ] ] )
LAG( value [ , offset [ , default ] ]
[ 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 value
re-evaluated against the row that is offset from the current row by offset
within the specified window:
- Positive
offset
seeks among preceding rows. - Negative
offset
seeks among following rows.
By default offset
is 1
.
If there is no available value (offset
reaches before the first row or after the last one), then default
is returned. If default
is not specified, then NULL
is used.
See also AGO for a non-window function alternative.
Argument types:
value
—Any
offset
—Integer
default
—Any
Return type: Same type as (value
)
Note
Only constant values are accepted for the arguments (offset
, default
).
Examples
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])
; - LAG TOTAL:
LAG(SUM([Orders]) TOTAL)
; - LAG WITHIN:
LAG(SUM([Orders]) WITHIN [City])
; - LAG AMONG:
LAG(SUM([Orders]) AMONG [City])
.
Result
City | Category | Order Sum | LAG TOTAL | LAG WITHIN | LAG AMONG |
---|---|---|---|---|---|
'Detroit' |
'Furniture' |
7 |
NULL |
NULL |
NULL |
'Detroit' |
'Office Supplies' |
25 |
7 |
7 |
NULL |
'London' |
'Furniture' |
1 |
25 |
NULL |
7 |
'London' |
'Office Supplies' |
10 |
1 |
1 |
25 |
'Moscow' |
'Furniture' |
2 |
10 |
NULL |
1 |
'Moscow' |
'Office Supplies' |
4 |
2 |
2 |
10 |
'San Francisco' |
'Furniture' |
5 |
4 |
NULL |
2 |
'San Francisco' |
'Office Supplies' |
23 |
5 |
5 |
4 |
Example with the optional argument
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])
; - LAG 1:
LAG(SUM([Orders]), 1)
; - LAG 2:
LAG(SUM([Orders]), -2)
.
Result
City | Order Sum | LAG 1 | LAG 2 |
---|---|---|---|
'Detroit' |
32 |
NULL |
6 |
'London' |
11 |
32 |
28 |
'Moscow' |
6 |
11 |
NULL |
'San Francisco' |
28 |
6 |
NULL |
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])
; - LAG ORDER BY City:
LAG(SUM([Orders]) ORDER BY [City] DESC)
; - LAG ORDER BY Order Sum:
LAG(SUM([Orders]) ORDER BY [Order Sum])
.
Result
City | Order Sum | LAG ORDER BY City | LAG ORDER BY Order Sum |
---|---|---|---|
'Detroit' |
32 |
11 |
28 |
'London' |
11 |
6 |
6 |
'Moscow' |
6 |
28 |
NULL |
'San Francisco' |
28 |
NULL |
11 |
Data source support
ClickHouse 21.8
, Microsoft SQL Server 2017 (14.0)
, MySQL 5.7
, Oracle Database 12c (12.1)
, PostgreSQL 9.3
.