ROW_NUMBER() OVER()函数用法详解 (分组排序 例子多)
喝咖啡~:
[code=sql]
-- 大佬看看我这个思路,是否也可以
-- 通过row_number()为每个用户的所有的订单,按创建时间生成序号,表示客户是第几次购买
-- 保留字段:ranker, customerID, totalPrice, DID
with tabs as
(
select
ROW_NUMBER() over(partition by customerID order by insDT) as ranker,
customerID,
totalPrice,
DID
from
OP_Order
);
-- 在上面的查询基础上再进行一次row_numer()计算,这次计算根据客户的订单金额顺序排序给出编号,通过where条件,找出每个客户所有订单当中,最小金额的那条记录
select * from
(
select tabs.ranker, tabs.customerID, tabs.totalPrice, tabs.DID, ROW_NUMBER() over (partition customerID order by tabs.totalPrice asc) as ranker2
) t2
where t2.ranker2 < 2
[/code]
|