代码之家 › 专栏 › 技术社区 › Black Dynamite

岛屿和缺口问题

dense-rank gaps-and-islands window-functions sql-server-2012 tsql

4

Black Dynamite · 技术社区 · 6 年前

后台:我有一个数据库,其中包含卡车司机的数据点。在卡车中,驾驶员可以具有“驾驶员状态”。我想做的是按司机、卡车对这些状态进行分组。

到目前为止,我已经尝试使用滞后/超前来帮助别人。这样做的原因是,我可以知道何时发生驱动程序状态更改,然后我可以将该行标记为具有该状态的最后一个日期时间。

这本身是不够的,因为我需要按状态和日期对状态进行分组。因为这个,我有一些东西,比如密级,但是我不能设法得到关于逐条款排序的权利。

这是我的测试数据,这是我许多人在排名上挣扎的一次尝试。

/****** Script for SelectTopNRows command from SSMS  ******/
DECLARE @SomeTable TABLE
(
    loginId VARCHAR(255),
    tractorId VARCHAR(255),
    messageTime DATETIME,
    driverStatus VARCHAR(2)
);

INSERT INTO @SomeTable (loginId, tractorId, messageTime, driverStatus)
VALUES('driver35','23533','2018-08-10 8:33 AM','2'),
('driver35','23533','2018-08-10 8:37 AM','2'),
('driver35','23533','2018-08-10 8:56 AM','2'),
('driver35','23533','2018-08-10 8:57 AM','1'),
('driver35','23533','2018-08-10 8:57 AM','1'),
('driver35','23533','2018-08-10 8:57 AM','1'),
('driver35','23533','2018-08-10 9:07 AM','1'),
('driver35','23533','2018-08-10 9:04 AM','1'),
('driver35','23533','2018-08-12 8:07 AM','3'),
('driver35','23533','2018-08-12 8:37 AM','3'),
('driver35','23533','2018-08-12 9:07 AM','3'),
('driver35','23533','2018-06-12 8:07 AM','2'),
('driver35','23533','2018-06-12 8:37 AM','2'),
('driver35','23533','2018-06-12 9:07 AM','2')
;
SELECT *, DENSE_RANK() OVER(PARTITION BY 
  loginId, tractorId, driverStatus 
ORDER BY messageTime ) FROM @SomeTable
;

理想情况下,我的最终结果是这样的:

loginId tractorId   startTime           endTime            driverStatus
driver35    23533   2018-08-10 8:33 AM  2018-08-10 8:56 AM      2
driver35    23533   2018-08-10 8:57 AM  2018-08-10 9:07 AM      1
driver35    23533   2018-08-12 8:07 AM  2018-08-12 9:07 AM      3

对此,任何帮助都非常感谢。

3 回复 | 直到 6 年前

1

JohnyL 6 年前

WITH drivers_data AS
(
    SELECT *,
           row_num =     ROW_NUMBER()
                         OVER (PARTITION BY loginId,
                                            tractorId,
                                            CAST(messageTime AS date),
                                            driverStatus
                               ORDER BY messageTime),

           row_num_all = ROW_NUMBER()
                         OVER (PARTITION BY loginId,
                                            tractorId
                               ORDER BY messageTime),

           first_date =  FIRST_VALUE (messageTime)
                         OVER (PARTITION BY loginId,
                                            tractorId,
                                            CAST(messageTime AS date),
                                            driverStatus
                               ORDER BY messageTime),

           last_date =   LAST_VALUE (messageTime)
                         OVER (PARTITION BY loginId,
                                            tractorId,
                                            CAST(messageTime AS date),
                                            driverStatus
                               ORDER BY messageTime
                               ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM @t
)
SELECT loginId, tractorId, first_date, last_date, driverStatus
FROM drivers_data
WHERE row_num = 1
ORDER BY row_num_all;

输出:

+==========+===========+=====================+=====================+==============+
| loginId  | tractorId | first_date          | last_date           | driverStatus |
|==========|===========|=====================|=====================|==============|
| driver35 | 23533     | 2018-10-08 08:33:00 | 2018-10-08 08:56:00 | 2            |
|----------|-----------|---------------------|---------------------|--------------|
| driver35 | 23533     | 2018-10-08 08:57:00 | 2018-10-08 09:07:00 | 1            |
|----------|-----------|---------------------|---------------------|--------------|
| driver35 | 23533     | 2018-12-06 08:07:00 | 2018-12-06 09:07:00 | 2            |
|----------|-----------|---------------------|---------------------|--------------|
| driver35 | 23533     | 2018-12-08 08:07:00 | 2018-12-08 09:07:00 | 3            |
+----------+-----------+---------------------+---------------------+--------------+

我会尽力解释这里发生了什么:

行编号 这用于对受驱动程序日期和状态限制的行进行编号。我们需要演员阵容,因为我们需要没有时间的约会。
行编号全部 这是键属性,因为它允许我们在最后按出现次数对行进行排序。此窗口不受状态限制,因为我们需要对整个驾驶员数据进行编号。
第一个日期 这个 FIRST_VALUE 对我们来说是方便的功能。它只检索第一个日期时间事件。
最后一个日期 假设我们最后需要的日期是正确的 LAST_VALUE 窗口功能。但是使用它很棘手,需要更多的解释。如你所见,我明确使用特殊框架 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING . 但为什么呢?让我解释一下。让我们来做一部分数据输出 10/8/2018 和状态 2 使用默认框架 . 我们得到以下结果:

+==========+===========+=====================+=====================+==============+
| loginId  | tractorId | first_date          | last_date           | driverStatus |
|==========|===========|=====================|=====================|==============|
| driver35 | 23533     | 2018-10-08 08:33:00 | 2018-10-08 08:33:00 | 2            |
|----------|-----------|---------------------|---------------------|--------------|
| driver35 | 23533     | 2018-10-08 08:33:00 | 2018-10-08 08:37:00 | 2            |
|----------|-----------|---------------------|---------------------|--------------|
| driver35 | 23533     | 2018-10-08 08:33:00 | 2018-10-08 08:56:00 | 2            | 
+----------+-----------+---------------------+---------------------+--------------+

如你所见,最后一次约会是 不正确的 !这是因为 最后一个值 使用默认框架 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -这意味着最后一排总是 当前行 在窗户里。下面是引擎盖下面发生的事情。创建了三个窗口。每一行都有自己的窗口。然后从窗口检索最后一行:

第一排窗口

+==========+===========+=====================+=====================+==============+
| loginId  | tractorId | first_date          | last_date           | driverStatus |
|==========|===========|=====================|=====================|==============|
| driver35 | 23533     | 2018-10-08 08:33:00 | 2018-10-08 08:33:00 | 2            |
+----------+-----------+---------------------+---------------------+--------------+

第二排窗口

+==========+===========+=====================+=====================+==============+
| loginId  | tractorId | first_date          | last_date           | driverStatus |
|==========|===========|=====================|=====================|==============|
| driver35 | 23533     | 2018-10-08 08:33:00 | 2018-10-08 08:33:00 | 2            |
|----------|-----------|---------------------|---------------------|--------------|
| driver35 | 23533     | 2018-10-08 08:33:00 | 2018-10-08 08:37:00 | 2            |
+----------+-----------+---------------------+---------------------+--------------+

第三排窗口

+======+=======+=======+=======+=======+======+============……+
| loginid tractorid first date last date driverstatus|
|==========_==_==_|
|驾驶员35 23533 2018-10-08 08:33:00 2018-10-08 08:33:00 2|
|——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————|
|驾驶员35 23533 2018-10-08 08:33:00 2018-10-08 08:37:00 2|
|——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————|
|驾驶员35 23533 2018-10-08 08:33:00 2018-10-08 08:56:00 2|
+——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————+

所以,解决这个问题的方法是改变框架:我们不需要从开始到当前行,而是从当前行到结束。所以, UNBOUNDED FOLLOWING 就是这个意思-当前窗口的最后一行。

下一个是 WHERE row_num = 1 . 这很简单:因为所有行都有关于第一个日期和最后一个日期的相同信息,所以我们只需要第一行。
最后一部分是 ORDER BY row_num_all . 这是您获得正确订购的地方。

附笔。

您所需的输出不正确。日期 8/10/18 8:57 AM 和状态 1 最后一个日期必须是 10/8/2018 9:07 AM -不 10/8/2018 9:04 AM 如你所说。
此外,还缺少日期的输出 12/6/2018 和状态 二 .

更新:

以下是如何 第一个值 和 最后一个值 工作。

所有三个数字都有以下部分:

查询数据 这是查询的结果。
原始查询 原始源数据。
窗户这些是计算的中间步骤。
框架提到使用哪一帧。
绿色单元格 窗户规格。

下面是引擎盖下面发生的事情:

首先,SQL Server为所有提到的字段创建分区。就数字而言 partition 列。
每个分区可以有一个框架:默认或自定义。默认帧为 前一行和当前行之间无边界的范围 . 这意味着该行在分区开始和当前行之间获取窗口。如果您没有提到帧,默认帧就开始发挥作用。
每一帧为每一行创建窗口。在图上,这些窗户是成列的。 row 1 到 row 2 并标有颜色。行号对应于 row_num_all 字段。
行只在其窗口的边界内操作。

1。第一个值

IMG_FIRST_VALUE

为了第一次约会,我们可以用Handy 第一个值 窗口功能。如您所见,我们在这里使用默认框架。这意味着对于每一行,窗口将位于窗口开始和当前行之间。为了第一次约会,这正是我们需要的。每行将从第一行获取值。第一个日期在“第一个日期”字段中。

2。最后一个值-帧不正确

IMG_LAST_VALUE

现在我们需要计算最后一个日期。最后一个日期在分区的最后一行,因此我们可以使用 最后一个值 窗口功能。正如我前面提到的,如果我们不提到帧,则使用默认帧。如图所示,框架总是以当前行结束-这是 不正确的 因为我们需要最后一行窗口的日期。这个 last_date 字段显示的结果不正确-它反映了当前行的日期。

三。最后一个值-正确的帧

IMG_LAST_VALUE_correct_frame

为了解决获取最后一个日期的情况,我们需要更改在其上 最后一个值 将在以下条件下操作: 当前行和无边界后续行之间的行 . 如您所见,现在每行的窗口位于当前行和分区末尾之间。在这种情况下 最后一个值 将从窗口的最后一行正确提取日期。现在的结果是 最后一个日期 字段正确。

2

1

tarheel 6 年前

下面的解决方案确定每次岛启动时(当 driverStatus 变化)在每个 loginID / tractorID 组合,然后为该岛分配一个“ID”号。

在那之后,它是一个简单的 min / max 找出那个岛何时开始和结束。

回答:

select b.loginId
, b.tractorId
, min(b.messageTime) as startTime
, max(b.messageTime) as endTime
, b.driverStatus
from (
    select a.loginId
    , a.tractorId
    , a.messageTime
    , a.driverStatus
    , a.is_island_start_flg
    , sum(a.is_island_start_flg) over (partition by a.loginID, a.tractorID order by a.messageTime asc) as island_nbr --assigning the "id" number to the island
    from (
        select st.loginId
        , st.tractorId
        , st.messageTime
        , st.driverStatus
        , iif(lag(st.driverStatus, 1, st.driverStatus) over (partition by st.loginID, st.tractorId order by st.messageTime asc) = st.driverStatus, 0, 1) as is_island_start_flg --identifying start of island
        from @SomeTable as st
        ) as a
    ) as b
group by b.loginId
, b.tractorId
, b.driverStatus
, b.island_nbr --purposefully in the group by, to make sure each occurrence of a status is in final results
order by b.loginId asc
, b.tractorId asc
, min(b.messageTime) asc

当您去掉样本数据的最后三个记录(因为这不在问题的预期输出中,就像johnyl所说的那样),这个查询将产生问题的确切输出。

3

0

Alan Burstein 6 年前

SELECT 
  t.loginId, 
  t.tractorId, 
  startTime = MIN(messageTime), 
  endTime   = MAX(messageTime),
  driverStatus 
FROM @someTable t
GROUP BY loginId, tractorId, driverStatus
ORDER BY MIN(messageTime);

结果:

loginId        tractorId  startTime               endTime                 driverStatus
-------------- ---------- ----------------------- ----------------------- ------------
driver35       23533      2018-10-08 08:33:00.000 2018-10-08 08:56:00.000 2
driver35       23533      2018-10-08 08:57:00.000 2018-10-08 09:07:00.000 1
driver35       23533      2018-12-08 08:07:00.000 2018-12-08 09:07:00.000 3