非常抱歉我的帖子很长,但是我已经提供了复制并粘贴的样本数据和一种可能的解决方法。问题相关部分在帖子的上半部分(横线之上)。
我有如下表格:
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
)。我已经有一个查询可以得到后面的结果集(数据和查询包含在下方的水平线下)。但是,它涉及到一个left outer join
和两个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
,dense_rank() over (partition by customer_id order by customer_id asc, 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',100,'16:00:00',1);
insert into #customer_data values ('2000-01-10',100,'13:00:00',4);
insert into #customer_data values ('2000-01-10',100,'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',200,'10:00:00',11);
insert into #customer_data values ('2000-01-06',200,'11:00:00',5);
insert into #customer_data values ('2000-01-10',200,'08:00:00',20);