tsql - 离岛与缺口问题

Question

背景故事：我有一个数据库，其中包含卡车司机的数据点，也包含。在卡车上时，驾驶员可以具有“ driverstatus”状态。我想做的是按驾驶员，卡车将这些状态分组。

到目前为止，我已经尝试使用LAG / LEAD来提供帮助。这样做的原因是，我可以告诉驾驶员何时发生驱动程序状态更改，然后可以将该行标记为具有该状态的最后日期时间。

这本身是不够的，因为我需要按状态和日期对状态进行分组。为此，我拥有诸如DENSE_RANK之类的东西，但是我无法设法获得有关ORDER BY子句的正确信息。

这是我的测试数据，这是我许多人在排名中挣扎的一种尝试。

/****** 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

在此方面的任何帮助将不胜感激。

Answer 1

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 |
+ ---------- + ----------- + --------------------- + ---- ----------------- + -------------- +


我将尝试解释这里发生的情况：


row_num用于为行编号，这些行受驱动程序的日期和状态限制。我们需要转换，因为我们需要没有时间的日期部分。
row_num_all这是关键属性，因为它最终允许我们按出现的顺序对行进行排序。此窗口不受状态限制，因为我们需要对整个驾驶员数据进行编号。
first_date FIRST_VALUE是我们方便的函数。它只是检索第一个日期时间出现。
last_date假设最后一个日期我们需要 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 |
+ ---------- + ----------- + --------------------- + ---- ----------------- + -------------- +


如您所见，最后日期不正确！发生这种情况是因为 LAST_VALUE使用默认框架 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 |
| ========= | ======================================== ================ || ============= |
| 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 |
+ ---------- + ----------- + --------------------- + ---- ----------------- + -------------- +


因此，解决此问题的方法是更改​​框架：我们不需要从开头移至当前行，而是从当前行移至结尾。因此， 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和状态 2缺少输出。


更新：

这是 FIRST_VALUE和 LAST_VALUE的工作方式的说明。

这三个数字均包含以下部分：


查询数据这是查询的结果。
原始查询原始源数据。
Windows这些是计算的中间步骤。
框架提及使用哪个框架。
绿色单元格窗口规范。


这是幕后故事：


首先，SQL Server为所有提到的字段创建分区。在图上是 partition列。
每个分区可以有一个框架：默认或自定义。默认框架为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。这意味着该行在分区的起始位置和当前行之间获得窗口。如果您不提及框架，则默认框架会起作用。
每个框架为每一行创建一个窗口。在图中，这些窗口在 row 1至 row 2列中，并用颜色标记。行号对应于 row_num_all字段。
行仅在其窗口范围内运行。


1. FIRST_VALUE



要获得初次约会，我们可以使用方便的 FIRST_VALUE窗口函数。
如您所见，我们在这里使用默认框架。这意味着对于每一行，窗口将在窗口的开始与当前行之间。对于第一次约会，这正是我们需要的。每行将从第一行获取值。第一个日期在“ first_date”字段中。

2. LAST_VALUE-帧不正确



现在我们需要计算最后日期。最后日期在分区的最后一行中，因此我们可以使用 LAST_VALUE窗口函数。
如前所述，如果不提及框架，则使用默认框架。正如您在图上看到的那样，框架始终在当前行结束-这是不正确的，因为我们需要从最后一个窗口行开始的日期。 last_date字段向我们显示了不正确的结果-它反映了当前行的日期。

3. LAST_VALUE-正确的帧



为了解决获取最后日期的问题，我们需要更改 LAST_VALUE将在其上运行的框架： ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING。如您所见，每行的窗口现在位于当前行和分区末尾之间。在这种情况下， LAST_VALUE将正确地从窗口的最后一行获取日期。现在， last_date字段中的结果正确。