对不起,很长的帖子,但我提供了副本&粘贴样本数据和下面可能的解决方案方法.问题的相关部分位于帖子的上半部分(水平规则之上).
我有下表
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有浮动类型？您可能遇到计算问题.将其转换为十进制类型.
