巧用 row_number 处理复杂问题

最近遇到一个需求,处听起来很复杂,要求如下:

1 统计用户的订单时间,并根据这个订单时间和用户注册时间去获得用户隔了几天才下单.

2 用户存在多次消费行为,因此,需要计算第一次消费的时间间隔,第二次的间隔.

基于上次面的需求分析,如果按照普通的做法,第一次的时间可以这样得到:

select userid,datadiff(dd,od.ordertime,od.usertime)

from order od join (select userid,min(ordertime) as ordertime from order group by userid) as morder on

od.userid=morder.userid and od.ordertime=morder.ordertime

上面语句,可以得出第一次下单离用户注册时间的天数.

但是,当需要拿第二次,第三次,甚至第四次的消费间隔天数时,怎么办呢?如果按照这样的写法,肯定语句很,运行速度也会很慢.

根据这种情况,分析,由于用户多次下单,是有时间性,可以排序,因此,可以用row_number这个函数,对userid,和ordertime进行排序,这样,就可以省下很多功夫了.

下面是row_number的用法介绍(返回结果集分区内行的序列号，每个分区的第一行从 1 开始。)

ROW_NUMBER ( )     OVER ( [ <partition_by_clause> ] <order_by_clause> )

参数

<partition_by_clause>

将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。若要了解 PARTITION BY 语法，请参阅 OVER 子句 (Transact-SQL)。

<order_by_clause>

确定将 ROW_NUMBER 值分配给分区中的行的顺序。有关详细信息，请参阅 ORDER BY 子句 (Transact-SQL)。当在排名函数中使用 <order_by_clause> 时，不能用整数表示列。

返回类型

bigint

基本语句:

select top 10000 userid,order_sn,ordertime,ROW_NUMBER() over (order by userid,ordertime)
from dim_order

 

这样,我们可以通过这语句,生成一张临时表,可以很快的得到userid的ordertime,确定它的第一次,第二次,或第三次消费的时间点.
扩展,上面的出来的结果会是1,2,3这样子的递增,我们还可以用"partition"去扩展,