SQL窗口函数示例

假设我们有一个销售表：

SaleID	SalesPersonID	Amount	SaleDate
1	1	100	2023-01-01
2	1	150	2023-01-02
3	2	200	2023-01-01
4	2	300	2023-01-03
5	1	120	2023-01-03

1. ROW_NUMBER()

sql

SELECT 
    SaleID, 
    SalesPersonID, 
    Amount,
    ROW_NUMBER() OVER (PARTITION BY SalesPersonID ORDER BY Amount DESC) as SaleRank
FROM 
    Sales;

结果：

SaleID	SalesPersonID	Amount	SaleRank
2	1	150	1
5	1	120	2
1	1	100	3
4	2	300	1
3	2	200	2

2. SUM() OVER()

sql

SELECT 
    SaleID, 
    SalesPersonID, 
    Amount,
    SUM(Amount) OVER (PARTITION BY SalesPersonID) as TotalSales
FROM 
    Sales;

结果：

SaleID	SalesPersonID	Amount	TotalSales
1	1	100	370
2	1	150	370
5	1	120	370
3	2	200	500
4	2	300	500

3. LAG() And LEAD()

sql

SELECT 
    SaleID, 
    SalesPersonID, 
    Amount,
    LAG(Amount) OVER (PARTITION BY SalesPersonID ORDER BY SaleDate) as PrevSale,
    LEAD(Amount) OVER (PARTITION BY SalesPersonID ORDER BY SaleDate) as NextSale
FROM 
    Sales;

结果：

SaleID	SalesPersonID	Amount	PrevSale	NextSale
1	1	100	NULL	150
2	1	150	100	120
5	1	120	150	NULL
3	2	200	NULL	300
4	2	300	200	NULL