sql-server – SQL Server：跨组(而不是组内)的超前/滞后分析功

我有下表

Dt          customer_id  buy_time     money_spent
 -------------------------------------------------
 2000-01-04  100          11:00:00.00  2
 2000-01-05  100          16:00:00.00  1
 2000-01-10  100          13:00:00.00  4
 2000-01-10  100          14:00:00.00  3
 2000-01-04  200          09:00:00.00  10
 2000-01-06  200          10:00:00.00  11
 2000-01-06  200          11:00:00.00  5
 2000-01-10  200          08:00:00.00  20

并希望查询获取此结果集

Dt          Dt_next     customer_id  buy_time     money_spent
 -------------------------------------------------------------
 2000-01-04  2000-01-05  100          11:00:00.00  2
 2000-01-05  2000-01-10  100          16:00:00.00  1
 2000-01-10  NULL        100          13:00:00.00  4
 2000-01-10  NULL        100          14:00:00.00  3
 2000-01-04  2000-01-06  200          09:00:00.00  10
 2000-01-06  2000-01-10  200          10:00:00.00  11
 2000-01-06  2000-01-10  200          11:00:00.00  5
 2000-01-10  NULL        200          08:00:00.00  20

那就是：我希望每个客户(customer_id)和每天(Dt)在同一个客户访问过的第二天(Dt_next).

我已经有一个查询给出后一个结果集(数据和查询包含在水平规则下面).但是,它涉及左外连接和两个dense_rank聚合函数.这种方法对我来说似乎有些笨拙,我认为应该有更好的解决方案.对替代解决方案的任何指示都非常感谢！谢谢！

顺便说一句：我使用的是SQL Server 11,该表有>> 1m条目.

我的查询：

select
   customer_table.Dt,customer_table_lead.Dt as Dt_next,customer_table.customer_id,customer_table.buy_time,customer_table.money_spent
 from
 (
   select 
     #customer_data.*,dense_rank() over (partition by customer_id order by customer_id asc,Dt asc) as Dt_int
   from #customer_data
 ) as customer_table
 left outer join
 (
   select distinct
     #customer_data.Dt,#customer_data.customer_id,Dt asc)-1 as Dt_int
   from #customer_data
 ) as customer_table_lead
 on
 (
   customer_table.Dt_int=customer_table_lead.Dt_int
   and customer_table.customer_id=customer_table_lead.customer_id
 )

样本数据：

create table #customer_data (
   Dt date not null,customer_id int not null,buy_time time(2) not null,money_spent float not null
 );

 insert into #customer_data values ('2000-01-04',100,'11:00:00',2);
 insert into #customer_data values ('2000-01-05','16:00:00',1);
 insert into #customer_data values ('2000-01-10','13:00:00',4);
 insert into #customer_data values ('2000-01-10','14:00:00',3);

 insert into #customer_data values ('2000-01-04',200,'09:00:00',10);
 insert into #customer_data values ('2000-01-06','10:00:00',11);
 insert into #customer_data values ('2000-01-06',5);
 insert into #customer_data values ('2000-01-10','08:00:00',20);

解决方法

select cd.Dt,t.Dt_next,cd.customer_id,cd.buy_time,cd.money_spent
from (
    select Dt,LEAD(Dt) OVER (PARTITION BY customer_id ORDER BY Dt) AS Dt_next,customer_id
    from (
        select distinct Dt,customer_id
        from #customer_data
    ) t
) t
inner join #customer_data cd on t.customer_id = cd.customer_id and t.Dt = cd.Dt

为什么field money_spent有浮动类型？您可能遇到计算问题.将其转换为十进制类型.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!