获取行的连续差异，包括第一行和最后一行，按一个或多个列分组。

Question

我正在尝试获取SQL中数据行的连续差异，包括第一行和最后一行与0之间的差异，其中行按多列分组。

我有两张桌子看起来像这样

Date                       Value

+------------+-------+     +------------+-------+------+------+
| Date       | Name  |     | Date       | Value | Name | Type |
+------------+-------+     +------------+-------+------+------+
| 2019-10-10 | A     |     | 2019-10-11 | 10    | A    | X    |
| 2019-10-11 | A     |     | 2019-10-12 | 11    | A    | X    |
| 2019-10-12 | A     |     | 2019-10-14 | 20    | A    | X    |
| 2019-10-13 | A     |     | 2019-10-11 | 10    | A    | Y    |
| 2019-10-14 | A     |     | 2019-10-12 | 22    | A    | Y    |
| 2019-10-15 | A     |     | 2019-10-14 | 30    | A    | Y    |
| 2019-10-10 | B     |     | 2019-10-11 | 10    | B    | X    |
| 2019-10-11 | B     |     | 2019-10-12 | 33    | B    | X    |
| 2019-10-12 | B     |     | 2019-10-14 | 40    | B    | X    |
| 2019-10-13 | B     |     | 2019-10-11 | 10    | B    | Y    |
| 2019-10-14 | B     |     | 2019-10-12 | 44    | B    | Y    |
| 2019-10-15 | B     |     | 2019-10-15 | 50    | B    | Y    |
+------------+-------+     +------------+-------+------+------+

Date表保存不同名称的日期。Value表对每个名称具有不同类型的值。我想为每个值获得一组连续的差异，按Name和Type分组。

我要找的最终结果是

+------------+-------+------+-------+---------------+------------+
| Date       | Name  | Type | Value | PreviousValue | Difference |
+------------+-------+------+-------+---------------+------------+
| 2019-10-11 | A     | X    | 10    | 0             | 10         |
| 2019-10-12 | A     | X    | 11    | 10            | 1          |
| 2019-10-14 | A     | X    | 20    | 11            | 9          |
| 2019-10-15 | A     | X    | 0     | 20            | -20        |
| 2019-10-11 | A     | Y    | 10    | 0             | 10         |
| 2019-10-12 | A     | Y    | 22    | 10            | 12         |
| 2019-10-14 | A     | Y    | 30    | 22            | 8          |
| 2019-10-15 | A     | Y    | 0     | 30            | -30        |
| 2019-10-11 | B     | X    | 10    | 0             | 10         |
| 2019-10-12 | B     | X    | 33    | 10            | 23         |
| 2019-10-14 | B     | X    | 40    | 33            | 7          |
| 2019-10-15 | B     | X    | 0     | 40            | -40        |
| 2019-10-11 | B     | Y    | 10    | 0             | 10         |
| 2019-10-12 | B     | Y    | 44    | 10            | 34         |
| 2019-10-15 | B     | Y    | 50    | 44            | 10         |
+------------+-------+------+-------+---------------+------------+

请注意，B-Y行集说明了一个重要点--我们可能有最后一个日期的值，在这种情况下，该集合不需要“额外”行。

我现在能得到的最接近的是

SELECT
    d.[Date],
    d.[Name],
    v.[Type],
    v.[Value],
    [PreviousValue] = COALESCE(LAG(v.[Value]) OVER (PARTITION BY d.[Name], v.[Type] ORDER BY d.[Date]), 0),
    [Difference] = v.[Value] - COALESCE(LAG(v.[Value]) OVER (PARTITION BY d.[Name], v.[Type] ORDER BY v.[Date]), 0)
FROM
    [Dates] d
LEFT JOIN
    [Values] v
ON
    d.[Date] = v.[Date]
    AND d.[Name] = v.[Name]

但这不会产生最后一行的差异。

Answer 1

只需在默认值参数中使用lag()：

[PreviousValue] = COALESCE(LAG(v.Value, 1, 0) OVER (PARTITION BY d.[Name], v.[Type] ORDER BY d.[Date]), 0)
[Difference] = v.[Value] - COALESCE(LAG(v.Value, 1, 0) OVER (PARTITION BY d.[Name], v.[Type] ORDER BY v.[Date]), 0)