窗口函数 ROW_NUMBER

ROW_NUMBER:在窗口函数的分区、排序列 会为每一数据行分配唯一的序列值

 

最近在工作中经常会碰到，计算当前订单时该会员的第多少单以及会员的累积消费金额等情况

 

1：计算会员当前订单是该会员第几单(根据订单创建时间计算)

SELECT  OrderCode ,
        CustCode ,
        OrderMoney ,
        CreateTime ,
        ROW_NUMBER() OVER ( ORDER BY CreateTime ) AS OrderSeq
FROM    dbo.Sales_Order
WHERE   CustCode = '6702791';

 

2：除了计算每笔订单时该会员的第几单，同时还会关注，截止当前订单，会员累计消费金额

SELECT  OrderCode ,
        CustCode ,
        OrderMoney ,
        CreateTime ,
        ROW_NUMBER() OVER ( ORDER BY CreateTime ) AS OrderSeq ,
        SUM(OrderMoney) OVER ( ORDER BY CreateTime RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS CurTotalMoney
FROM    dbo.Sales_Order
WHERE   CustCode = '6702791';

 

 

窗口函数也会涉及到一些性能的相关问题，有关窗口函数的索引优化，后续会补充进来。