c# - 如何使用前瞻性将 SQL 转换为 LINQ(前导式查询)

Question

我有一个像这样的简单表格:

[Timestamp] DATETIME,
[Value] NVARCHAR(50)

所有 [Timestamp] 值都是唯一的，但 [Value] 不是唯一的。

我想找出每个 [Value] 何时开始和停止。我有以下 T-SQL(SQL 2008)，它似乎工作正常，尽管任何改进建议都会受到赞赏，因为在 2012 年之前的 T-SQL 中实现领先运算符是一个很好的模式。

SELECT
    h1.[Value] AS 'Event ID',
    MIN(h1.[Timestamp]) AS 'Start Time',
    h2.[Timestamp] AS 'End Time',
    DATEDIFF(second, MIN(h1.[Timestamp]), h2.[Timestamp]) AS 'Duration (sec)'
FROM
    #RawHistory h1
    INNER JOIN
    #RawHistory h2
    ON h2.[Timestamp] =
    ( 
        SELECT TOP 1 
            h3.[Timestamp] 
        FROM 
            #RawHistory h3
        WHERE
            h3.[Value] <> h1.[Value]
            AND
            h3.[Timestamp] > h1.[Timestamp]
        ORDER BY 
            h3.[Timestamp] ASC
    )
GROUP BY
    h1.[Value], h2.[Timestamp]
ORDER BY 
    MIN(h1.[Timestamp])

因此，给定此输入:

Timestamp               Value
======================= ======
2013-12-18 20:26:00.000 Violet
2013-12-18 20:30:00.000 Red
2013-12-18 20:35:00.000 Red
2013-12-18 20:40:00.000 Orange
2013-12-18 20:50:00.000 Yellow
2013-12-18 21:00:00.000 Green
2013-12-18 21:05:00.000 Green
2013-12-18 21:07:00.000 Green
2013-12-18 21:10:00.000 Blue
2013-12-18 21:20:00.000 Indigo
2013-12-18 21:30:00.000 Violet
2013-12-18 21:30:05.000 Violet
2013-12-18 21:40:00.000 Red
2013-12-18 21:50:00.000 Orange
2013-12-18 22:00:00.000 Yellow
2013-12-18 22:10:00.000 Green

我期望这样的输出:

Event ID Start Time              End Time                Duration (sec)
======== ======================= ======================= =============
Violet   2013-12-18 20:26:00.000 2013-12-18 20:30:00.000 240
Red      2013-12-18 20:30:00.000 2013-12-18 20:40:00.000 600
Orange   2013-12-18 20:40:00.000 2013-12-18 20:50:00.000 600
Yellow   2013-12-18 20:50:00.000 2013-12-18 21:00:00.000 600
Green    2013-12-18 21:00:00.000 2013-12-18 21:10:00.000 600
Blue     2013-12-18 21:10:00.000 2013-12-18 21:20:00.000 600
Indigo   2013-12-18 21:20:00.000 2013-12-18 21:30:00.000 600
Violet   2013-12-18 21:30:00.000 2013-12-18 21:40:00.000 600
Red      2013-12-18 21:40:00.000 2013-12-18 21:50:00.000 600
Orange   2013-12-18 21:50:00.000 2013-12-18 22:00:00.000 600
Yellow   2013-12-18 22:00:00.000 2013-12-18 22:10:00.000 600

请注意，最后一个 Green 实例不会返回任何内容，因为还没有“结束日期”。

但是，我真正感兴趣的是能够使用 C# 中的 LINQ 针对类似的值和时间戳列表执行相同类型的查询。不是针对 SQL 数据源，而是针对具有 Value 和 Timestamp 属性的 POCO 对象列表。

所以，如果我有一个类似于

class VT
{
    string Value { get; set; }
    DateTime Timestamp { get; set; }
}

我有一个

List<VT> vts

在这些对象中，执行相同类型转换的 List 上的 linq 语句是什么？

我之前使用状态机模式实现了这一点，该模式跟踪值何时更改 - 不明显的是时间序列数据可能在连续时间戳中具有相同值的多个条目，如

T1, Red 
T2, Red
T3, Red
T4, Yellow
T5, Yellow
T6, Blue

等等。 SQL 有效，因为它查找与当前值不同的下一个值的时间戳。使用 Linq 是否可行？

可以肯定的是，在上面的连续值示例中，最终结果应该是:

Red, T1, T4
Yellow, T4, T6

所以连续的阅读应该合并成一个阅读。查看 SQL 查询如何查找与前瞻中的当前记录具有不同值的下一条记录。

我编辑了原始数据集示例以包含重复的连续值。输出不变。

Answer 1

您可以使用 ZIP 语句实现这一点，其中相同的序列被一个元素跳过作为第二个输入——这就是您的“先行”。但是，它没有考虑到可能存在多个具有相同值的连续事件。

var firstList = new List<VT> {
   new VT { Value = "Violet", Timestamp = new DateTime(2013, 12, 18, 20, 26, 0) },
   new VT { Value = "Red", Timestamp = new DateTime(2013, 12, 18, 20, 30, 0) },
   new VT { Value = "Orange", Timestamp = new DateTime(2013, 12, 18, 20, 40, 0) },
   new VT { Value = "Yellow", Timestamp = new DateTime(2013, 12, 18, 20, 50, 0) }
};

var secondList = firstList.Skip(1);

var combined = firstList.Zip(secondList, 
                               (first, second) => new {
                                      EventID = first.Value, 
                                      StartTime = first.Timestamp, 
                                      EndTime = second.Timestamp, 
                                      Duration = (second.Timestamp - first.Timestamp).TotalSeconds});
}

这导致

EventID StartTime           EndTime             Duration
Violet  18.12.2013 20:26:00 18.12.2013 20:30:00 240 
Red     18.12.2013 20:30:00 18.12.2013 20:40:00 600 
Orange  18.12.2013 20:40:00 18.12.2013 20:50:00 600 

使用 Reactive Extensions (RX) 可以轻松解决具有相同值的多个连续事件的问题。只需将 firstList 和 secondList 的类型替换为 IObservable<VT> .然后 .Subscribe()每当颜色发生变化时，结合起来就可以获得一个流完成事件。调用 DistinctUntilChanged将确保仅传递具有不同颜色的事件。

var firstList = new List<VT> {
  new VT { Value = "Violet", Timestamp = new DateTime(2013, 12, 18, 20, 26, 0) },
  new VT { Value = "Violet", Timestamp = new DateTime(2013, 12, 18, 20, 27, 0) },
  new VT { Value = "Violet", Timestamp = new DateTime(2013, 12, 18, 20, 29, 0) },
  new VT { Value = "Red", Timestamp = new DateTime(2013, 12, 18, 20, 30, 0) },
  new VT { Value = "Red", Timestamp = new DateTime(2013, 12, 18, 20, 34, 0) },
  new VT { Value = "Orange", Timestamp = new DateTime(2013, 12, 18, 20, 40, 0) },
  new VT { Value = "Yellow", Timestamp = new DateTime(2013, 12, 18, 20, 50, 0) }
}
.ToObservable().DistinctUntilChanged(vt => vt.Value);

var secondList = firstList.Skip(1);

var combined = firstList.Zip(secondList, (first, second) => new  {
                                                 EventID = first.Value, 
                                                 StartTime = first.Timestamp, 
                                                 EndTime = second.Timestamp, 
                                                 Duration = (second.Timestamp - first.Timestamp).TotalSeconds});

combined.Subscribe();

这将返回预期的结果。

DistinctUntilChanged只是根据值从流中删除相同的所有事件，因此这有效地减少了上面 LINQ 示例中输入的输入。