如何在SQL Server中计算序列中组的最小值和最大值？

Zapper 发表于 Dev

拉链

我在SQL Server的数据库表中包含以下数据：

Id  Date        Val_A   Val_B   Val_C   Avg     Vector  MINMAXPOINTS
329 2016-01-15  78.09   68.40   70.29   76.50   BELOW   68.40
328 2016-01-14  79.79   75.40   76.65   76.67   BELOW   75.40
327 2016-01-13  81.15   74.59   79.00   76.44   ABOVE   81.15
326 2016-01-12  81.95   77.04   78.95   76.04   ABOVE   81.95
325 2016-01-11  82.40   73.65   81.34   75.47   ABOVE   82.40
324 2016-01-08  78.75   73.40   77.20   74.47   ABOVE   78.75
323 2016-01-07  76.40   72.29   72.95   73.74   BELOW   72.29
322 2016-01-06  81.25   77.70   78.34   73.12   ABOVE   81.25
321 2016-01-05  81.75   76.34   80.54   72.08   ABOVE   81.75
320 2016-01-04  80.95   75.15   76.29   70.86   ABOVE   80.95

列MIMMAXPOINTS应实际上包含最低的Val_B，直到Vector是'BELOW'与最高的Val_A，直到Vector为'ABOVE'。因此，我们将在其中具有以下值MINMAXPOINTS：

MINMAXPOINTS
68.40
68.40
82.40
82.40
82.40
82.40
72.29
81.75
81.75
81.75

没有光标可能吗？

任何帮助将不胜感激！。

弗拉基米尔·巴拉诺夫（Vladimir Baranov）

在第一应用经典gaps-and-islands以确定组（空白/岛/上方/下方），然后计算MIN和MAX为每个组。

我认为ID列定义了行的顺序。

在SQL Server 2008上进行了测试。这是SQL Fiddle。

样本数据

DECLARE @T TABLE
([Id] int, [dt] date, [Val_A] float, [Val_B] float, [Val_C] float, [Avg] float, 
[Vector] varchar(5));

INSERT INTO @T ([Id], [dt], [Val_A], [Val_B], [Val_C], [Avg], [Vector]) VALUES
(329, '2016-01-15', 78.09, 68.40, 70.29, 76.50, 'BELOW'),
(328, '2016-01-14', 79.79, 75.40, 76.65, 76.67, 'BELOW'),
(327, '2016-01-13', 81.15, 74.59, 79.00, 76.44, 'ABOVE'),
(326, '2016-01-12', 81.95, 77.04, 78.95, 76.04, 'ABOVE'),
(325, '2016-01-11', 82.40, 73.65, 81.34, 75.47, 'ABOVE'),
(324, '2016-01-08', 78.75, 73.40, 77.20, 74.47, 'ABOVE'),
(323, '2016-01-07', 76.40, 72.29, 72.95, 73.74, 'BELOW'),
(322, '2016-01-06', 81.25, 77.70, 78.34, 73.12, 'ABOVE'),
(321, '2016-01-05', 81.75, 76.34, 80.54, 72.08, 'ABOVE'),
(320, '2016-01-04', 80.95, 75.15, 76.29, 70.86, 'ABOVE');

询问

为了更好地了解其工作原理，请检查每个CTE的结果。

CTE_RowNumbers 计算两个行号序列。

CTE_Groups 为每个组分配一个数字（上方/下方）。

CTE_MinMaxMIN/MAX为每个组计算。

最终SELECT选择MIN还是MAX要返回。

WITH
CTE_RowNumbers
AS
(
    SELECT [Id], [dt], [Val_A], [Val_B], [Val_C], [Avg], [Vector]
        ,ROW_NUMBER() OVER (ORDER BY ID DESC) AS rn1
        ,ROW_NUMBER() OVER (PARTITION BY Vector ORDER BY ID DESC) AS rn2
    FROM @T
)
,CTE_Groups
AS
(
    SELECT [Id], [dt], [Val_A], [Val_B], [Val_C], [Avg], [Vector]
        ,rn1-rn2 AS Groups
    FROM CTE_RowNumbers
)
,CTE_MinMax
AS
(
    SELECT [Id], [dt], [Val_A], [Val_B], [Val_C], [Avg], [Vector]
        ,MAX(Val_A) OVER(PARTITION BY Groups) AS MaxA
        ,MIN(Val_B) OVER(PARTITION BY Groups) AS MinB
    FROM CTE_Groups
)
SELECT [Id], [dt], [Val_A], [Val_B], [Val_C], [Avg], [Vector]
    ,CASE 
        WHEN [Vector] = 'BELOW' THEN MinB
        WHEN [Vector] = 'ABOVE' THEN MaxA
    END AS MINMAXPOINTS
FROM CTE_MinMax
ORDER BY ID DESC;

结果

+-----+------------+-------+-------+-------+-------+--------+--------------+
| Id  |     dt     | Val_A | Val_B | Val_C |  Avg  | Vector | MINMAXPOINTS |
+-----+------------+-------+-------+-------+-------+--------+--------------+
| 329 | 2016-01-15 | 78.09 | 68.4  | 70.29 | 76.5  | BELOW  | 68.4         |
| 328 | 2016-01-14 | 79.79 | 75.4  | 76.65 | 76.67 | BELOW  | 68.4         |
| 327 | 2016-01-13 | 81.15 | 74.59 | 79    | 76.44 | ABOVE  | 82.4         |
| 326 | 2016-01-12 | 81.95 | 77.04 | 78.95 | 76.04 | ABOVE  | 82.4         |
| 325 | 2016-01-11 | 82.4  | 73.65 | 81.34 | 75.47 | ABOVE  | 82.4         |
| 324 | 2016-01-08 | 78.75 | 73.4  | 77.2  | 74.47 | ABOVE  | 82.4         |
| 323 | 2016-01-07 | 76.4  | 72.29 | 72.95 | 73.74 | BELOW  | 72.29        |
| 322 | 2016-01-06 | 81.25 | 77.7  | 78.34 | 73.12 | ABOVE  | 81.75        |
| 321 | 2016-01-05 | 81.75 | 76.34 | 80.54 | 72.08 | ABOVE  | 81.75        |
| 320 | 2016-01-04 | 80.95 | 75.15 | 76.29 | 70.86 | ABOVE  | 81.75        |
+-----+------------+-------+-------+-------+-------+--------+--------------+

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。