sql-server - 选择每个组的第一个、最大和最后一个非空值

Question

尝试为每组选择第一个和最后一个值(按时间顺序)以及最大值。我编写了一个运行良好的查询，但它不处理 NULL 值。我需要它来忽略 NULL 值。

这是一个例子:

DECLARE @T table (
    LabName VARCHAR(20)
,   CreatedOn   date
,   LabValue    int
)

INSERT INTO @T
        ( LabName,CreatedOn,LabValue )
VALUES
    ('Creatinine', '2016-01-01',    NULL)
,   ('Creatinine', '2016-02-01',    15)
,   ('Creatinine', '2016-03-01',    20)
,   ('Creatinine', '2016-04-01',    19)
,   ('SGOT (ST)', '2016-01-01', 25)
,   ('SGOT (ST)', '2016-02-01', 31)
,   ('SGOT (ST)', '2016-03-01', 25)
,   ('SGOT (ST)', '2016-04-01', NULL)

SELECT DISTINCT 
*
FROM (
    SELECT
        LabName
    ,   FIRST_VALUE(LabValue) OVER(PARTITION BY LabName ORDER BY CreatedOn ASC) AS FirstValue
    ,   MAX(LabValue) OVER(PARTITION BY LabName) AS MaxValue
    ,   LAST_VALUE(LabValue) OVER(PARTITION BY LabName ORDER BY CreatedOn ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LastValue
    FROM @T
) AS T

在我意识到有些实验室在某些日期没有运行之前，它一直运行良好。一旦我将一些 NULL 放入测试数据中，First 和 Last 的结果将包括它们。

这是我得到的结果:

+------------+------------+----------+-----------+
|  LabName   | FirstValue | MaxValue | LastValue |
+------------+------------+----------+-----------+
| Creatinine | NULL       |       20 | 19        |
| SGOT (ST)  | 25         |       31 | NULL      |
+------------+------------+----------+-----------+

这是我想要的结果:

+------------+------------+----------+-----------+
|  LabName   | FirstValue | MaxValue | LastValue |
+------------+------------+----------+-----------+
| Creatinine | 15         |       20 | 19        |
| SGOT (ST)  | 25         |       31 | 25        |
+------------+------------+----------+-----------+

Answer 1

通过 ROW_NUMBER() 使用条件聚合:

SELECT LabName,
       MAX(CASE WHEN seqnum_asc = 1 THEN LabValue END) as FirstValue,
       MAX(LabValue) as MaxValue, 
       MAX(CASE WHEN seqnum_desc = 1 THEN LabValue END) as LastValue
FROM (SELECT t.*,
             ROW_NUMBER() OVER (PARTITION BY LabName
                                ORDER BY (CASE WHEN LabValue IS NOT NULL THEN 1 ELSE 2 END),
                                         CreatedOn
                               ) as seqnum_asc,
             ROW_NUMBER() OVER (PARTITION BY LabName
                                ORDER BY (CASE WHEN LabValue IS NOT NULL THEN 1 ELSE 2 END),
                                         CreatedOn DESC
                               ) as seqnum_desc
      FROM @T t
     ) T
GROUP BY LabName;