sql – 对具有日期范围的行进行分组

我正在使用sql Server 2008,需要创建一个显示日期范围内的行的查询.

我的表格如下：

ADM_ID    WH_PID     WH_IN_DATETIME    WH_OUT_DATETIME

我的规则是：

>如果WH_OUT_DATETIME在具有相同WH_P_ID的另一个ADM_ID的WH_IN_DATETIME的24小时内或之后24小时内

我想在结果中添加另一列,如果可能的话,将列表值标识为EP_ID.

例如

ADM_ID    WH_PID    WH_IN_DATETIME         WH_OUT_DATETIME
------    ------    --------------         ---------------
1         9         2014-10-12 00:00:00    2014-10-13 15:00:00
2         9         2014-10-14 14:00:00    2014-10-15 15:00:00
3         9         2014-10-16 14:00:00    2014-10-17 15:00:00
4         9         2014-11-20 00:00:00    2014-11-21 00:00:00
5         5         2014-10-17 00:00:00    2014-10-18 00:00:00

将返回行：

ADM_ID   WH_PID   EP_ID   EP_IN_DATETIME        EP_OUT_DATETIME       WH_IN_DATETIME        WH_OUT_DATETIME
------   ------   -----   -------------------   -------------------   -------------------   -------------------
1        9        1       2014-10-12 00:00:00   2014-10-17 15:00:00   2014-10-12 00:00:00   2014-10-13 15:00:00
2        9        1       2014-10-12 00:00:00   2014-10-17 15:00:00   2014-10-14 14:00:00   2014-10-15 15:00:00
3        9        1       2014-10-12 00:00:00   2014-10-17 15:00:00   2014-10-16 14:00:00   2014-10-17 15:00:00
4        9        2       2014-11-20 00:00:00   2014-11-20 00:00:00   2014-10-16 14:00:00   2014-11-21 00:00:00
5        5        1       2014-10-17 00:00:00   2014-10-18 00:00:00   2014-10-17 00:00:00   2014-10-18 00:00:00

EP_OUT_DATETIME将始终是组中的最新日期.希望这有点澄清.
通过这种方式,我可以按EP_ID进行分组,找到EP_OUT_DATETIME以及任何属于其中的ADM_ID / PID的开始时间.

每个都应该滚动到下一个,这意味着如果另一行的WH_IN_DATETIME在另一行的WH_OUT_DATETIME之后对于相同的WH_PID,则该行的WH_OUT_DATETIME变为该EP_ID内所有WH_PID的EP_OUT_DATETIME.

我希望这是有道理的.

谢谢,
先生

解决方法

由于问题没有指明解决方案是“单一”查询;-),这是另一种方法：使用dealy的“quirky update”功能,即在更新列的同时更新变量.打破这个操作的复杂性,我创建了一个临时表来保存最难计算的部分：EP_ID.完成后,它将加入一个简单的查询,并提供用于计算EP_IN_DATETIME和EP_OUT_DATETIME字段的窗口.

步骤是：

>创建临时表
>使用所有ADM_ID值对临时表进行种子处理 – 这使我们可以执行更新,因为所有行都已存在.
>更新临时表
>最后,简单的选择将临时表连接到主表

测试设置

SET ANSI_NULLS ON;
SET NOCOUNT ON;
 
CREATE TABLE #Table
(
   ADM_ID INT NOT NULL PRIMARY KEY,WH_PID INT NOT NULL,WH_IN_DATETIME DATETIME NOT NULL,WH_OUT_DATETIME DATETIME NOT NULL
);
 
INSERT INTO #Table VALUES (1,9,'2014-10-12 00:00:00','2014-10-13 15:00:00');
INSERT INTO #Table VALUES (2,'2014-10-14 14:00:00','2014-10-15 15:00:00');
INSERT INTO #Table VALUES (3,'2014-10-16 14:00:00','2014-10-17 15:00:00');
INSERT INTO #Table VALUES (4,'2014-11-20 00:00:00','2014-11-21 00:00:00');
INSERT INTO #Table VALUES (5,5,'2014-10-17 00:00:00','2014-10-18 00:00:00');

第1步：创建并填充临时表

CREATE TABLE #Scratch
(
   ADM_ID INT NOT NULL PRIMARY KEY,EP_ID INT NOT NULL
   -- Might need WH_PID and WH_IN_DATETIME fields to guarantee proper UPDATE ordering
);
 
INSERT INTO #Scratch (ADM_ID,EP_ID)
   SELECT ADM_ID,0
   FROM   #Table;

备用临时表结构以确保正确的更新顺序(因为“古怪更新”使用聚集索引的顺序,如本答案底部所述)：

CREATE TABLE #Scratch
(
   WH_PID INT NOT NULL,ADM_ID INT NOT NULL,EP_ID INT NOT NULL
);
 
INSERT INTO #Scratch (WH_PID,WH_IN_DATETIME,ADM_ID,EP_ID)
   SELECT WH_PID,0
   FROM   #Table;
 
CREATE UNIQUE CLUSTERED INDEX [CIX_Scratch]
   ON #Scratch (WH_PID,ADM_ID);

步骤2：使用局部变量更新Scratch Table以跟踪先前值

DECLARE @EP_ID INT; -- this is used in the UPDATE
 
;WITH cte AS
(
  SELECT TOP (100) PERCENT
         t1.*,t2.WH_OUT_DATETIME AS [PriorOut],t2.ADM_ID AS [PriorID],ROW_NUMBER() OVER (PARTITION BY t1.WH_PID ORDER BY t1.WH_IN_DATETIME)
                AS [RowNum]
  FROM   #Table t1
  LEFT JOIN #Table t2
         ON t2.WH_PID = t1.WH_PID
        AND t2.ADM_ID <> t1.ADM_ID
        AND t2.WH_OUT_DATETIME >= (t1.WH_IN_DATETIME - 1)
        AND t2.WH_OUT_DATETIME < t1.WH_IN_DATETIME
  ORDER BY t1.WH_PID,t1.WH_IN_DATETIME
)
UPDATE sc
SET    @EP_ID = sc.EP_ID = CASE
                               WHEN cte.RowNum = 1 THEN 1
                               WHEN cte.[PriorOut] IS NULL THEN (@EP_ID + 1)
                               ELSE @EP_ID
                        END
FROM   #Scratch sc
INNER JOIN cte
        ON cte.ADM_ID = sc.ADM_ID

第3步：选择加入划痕表

SELECT tab.ADM_ID,tab.WH_PID,sc.EP_ID,MIN(tab.WH_IN_DATETIME) OVER (PARTITION BY tab.WH_PID,sc.EP_ID)
           AS [EP_IN_DATETIME],MAX(tab.WH_OUT_DATETIME) OVER (PARTITION BY tab.WH_PID,sc.EP_ID)
           AS [EP_OUT_DATETIME],tab.WH_IN_DATETIME,tab.WH_OUT_DATETIME
FROM   #Table tab
INNER JOIN #Scratch sc
    ON sc.ADM_ID = tab.ADM_ID
ORDER BY tab.ADM_ID;

资源

> UPDATE的MSDN页面

寻找“@variable = column = expression”
> Performance Analysis of doing Running Totals(与此不完全相同,但不太远)

这篇博文确实提到：

> PRO：这种方法通常很快> CON：“UPDATE的顺序由聚集索引的顺序控制”.此行为可能会根据具体情况排除使用此方法.但在这种特殊情况下,如果WH_PID值至少不是通过聚簇索引的排序自然地组合在一起并由WH_IN_DATETIME排序,那么这两个字段只会被添加到临时表和PK(带有隐含的聚簇索引)上临时表变为(WH_PID,ADM_ID).

sql – 对具有日期范围的行进行分组

解决方法

猜你在找的MsSQL相关文章