对分组表行应用递归 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屋！