对分组表行应用递归 CTE (SQL Server 2005) [英] Apply a recursive CTE on grouped table rows (SQL server 2005)
问题描述
我有一张表 (ROOMUSAGE),其中包含人们按 PERSONKEY 和 ROOMKEY 分组的房间入住和退房时间.它看起来像这样:
I have a table (ROOMUSAGE) containing the times people check in and out of rooms grouped by PERSONKEY and ROOMKEY. It looks like this:
PERSONKEY | ROOMKEY | CHECKIN | CHECKOUT | ROW
----------------------------------------------------------------
1 | 8 | 13-4-2010 10:00 | 13-4-2010 11:00 | 1
1 | 8 | 13-4-2010 08:00 | 13-4-2010 09:00 | 2
1 | 1 | 13-4-2010 15:00 | 13-4-2010 16:00 | 1
1 | 1 | 13-4-2010 14:00 | 13-4-2010 15:00 | 2
1 | 1 | 13-4-2010 13:00 | 13-4-2010 14:00 | 3
13 | 2 | 13-4-2010 15:00 | 13-4-2010 16:00 | 1
13 | 2 | 13-4-2010 15:00 | 13-4-2010 16:00 | 2
我只想为每个 PERSONKEY、ROOMKEY 分组选择连续的行.所以所需的结果表是:
I want to select just the consecutive rows for each PERSONKEY, ROOMKEY grouping. So the desired resulting table is:
PERSONKEY | ROOMKEY | CHECKIN | CHECKOUT | ROW
----------------------------------------------------------------
1 | 8 | 13-4-2010 10:00 | 13-4-2010 11:00 | 1
1 | 1 | 13-4-2010 15:00 | 13-4-2010 16:00 | 1
1 | 1 | 13-4-2010 14:00 | 13-4-2010 15:00 | 2
1 | 1 | 13-4-2010 13:00 | 13-4-2010 14:00 | 3
13 | 2 | 13-4-2010 15:00 | 13-4-2010 16:00 | 1
我想避免使用游标,所以我想我会使用递归 CTE.这是我想出的:
I want to avoid using cursors so I thought I would use a recursive CTE. Here is what I came up with:
;with CTE (PERSONKEY, ROOMKEY, CHECKIN, CHECKOUT, ROW)
as (select RU.PERSONKEY,
RU.ROOMKEY,
RU.CHECKIN,
RU.CHECKOUT,
RU.ROW
from ROOMUSAGE RU
where RU.ROW = 1
union all
select RU.PERSONKEY,
RU.ROOMKEY,
RU.CHECKIN,
RU.CHECKOUT,
RU.ROW
from ROOMUSAGE RU inner join CTE on RU.ROW = CTE.ROW + 1
where CTE.CHECKIN = RU.CHECKOUT
and CTE.PERSONKEY = RU.PERSONKEY
and CTE.ROOMKEY = RU.ROOMKEY)
这适用于非常小的数据集(少于 100 条记录),但不适用于大型数据集.
This worked OK for very small datasets (under 100 records) but it's unusable on large datasets.
我在想我应该以某种方式在我的 ROOMUSAGE 表上的每个 PERSONKEY、ROOMKEY 分组上递归地应用 cte,但我不知道该怎么做.
I'm thinking that I should somehow apply the cte recursevely on each PERSONKEY, ROOMKEY grouping on my ROOMUSAGE table but I am not sure how to do that.
任何帮助将不胜感激,
干杯!
推荐答案
一些尝试
- 与其连接每一行并在 where 子句中过滤结果,您是否可以尝试直接在连接中减少记录数量来加快速度?
- 在 PersonKey、RoomKey、CheckOut & 上添加覆盖索引排成一行,看看它是否能提高性能.
- Instead of joining with every row and filtering the results in your where clause, could you try if reducing the amount of records directly in the join speeds things up?
- Add a covering index on PersonKey, RoomKey, CheckOut & Row and see if it improves performance.
SQL 语句
;with CTE (PERSONKEY, ROOMKEY, CHECKIN, CHECKOUT, ROW)
as (select RU.PERSONKEY,
RU.ROOMKEY,
RU.CHECKIN,
RU.CHECKOUT,
RU.ROW
from ROOMUSAGE RU
where RU.ROW = 1
union all
select RU.PERSONKEY,
RU.ROOMKEY,
RU.CHECKIN,
RU.CHECKOUT,
RU.ROW
from ROOMUSAGE RU
inner join CTE on CTE.ROW + 1 = RU.ROW
and CTE.CHECKIN = RU.CHECKOUT
and CTE.PERSONKEY = RU.PERSONKEY
and CTE.ROOMKEY = RU.ROOMKEY
)
这篇关于对分组表行应用递归 CTE (SQL Server 2005)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!