在行组中查找最小值

Spencer Schneidenbach 发表于 Dev

斯潘塞·施耐登巴赫

在SQL空间（特别是T-SQL，SQL Server 2008）中，给出以下值列表：

    Status  Date
    ------  -----------------------
    ACT     2012-01-07 11:51:06.060
    ACT     2012-01-07 11:51:07.920
    ACT     2012-01-08 04:13:29.140
    NOS     2012-01-09 04:29:16.873
    ACT     2012-01-21 12:39:37.607         <-- THIS
    ACT     2012-01-21 12:40:03.840
    ACT     2012-05-02 16:27:17.370
    GRAD    2012-05-19 13:30:02.503
    GRAD    2013-09-03 22:58:48.750

从此查询生成：

    SELECT Status, Date
    FROM Account_History
    WHERE AccountNumber = '1234'
    ORDER BY Date

该特定对象的状态从ACT开始，然后更改为NOS，然后又回到ACT，再到GRAD。

从Status ='ACT'的最新“组”记录中获取最小日期的最佳方法是什么？

戈登·利诺夫

这是一个查询，通过识别学生状态相同的组然后使用简单的汇总来执行此操作：

select top 1 StudentStatus, min(WhenLastChanged) as WhenLastChanged
from (SELECT StudentStatus, WhenLastChanged,
             (row_number() over (order by "date") -
              row_number() over (partition by studentstatus order by "date)
            ) as grp
      FROM Account_History
      WHERE AccountNumber = '1234'
    ) t
where StudentStatus = 'ACT'
group by StudentStatus, grp
order by WhenLastChanged desc;

该row_number()函数基于分配行组内的顺序号date。对于您的数据，两者row_numbers()及其区别是：

Status  Date
------  -----------------------
ACT     2012-01-07 11:51:06.060    1      1      0
ACT     2012-01-07 11:51:07.920    2      2      0
ACT     2012-01-08 04:13:29.140    3      3      0
NOS     2012-01-09 04:29:16.873    4      1      3
ACT     2012-01-21 12:39:37.607    5      4      1
ACT     2012-01-21 12:40:03.840    6      5      1
ACT     2012-05-02 16:27:17.370    7      6      1
GRAD    2012-05-19 13:30:02.503    8      1      7
GRAD    2013-09-03 22:58:48.750    9      2      7

注意，对于具有相同状态的行，最后一行是常量。

汇总将这些汇总在一起，并选择top 1 . . . order by date desc第一个日期（min(date)）中的最新日期（）。

编辑：

该查询易于调整多个帐号。我可能应该以这种方式开始，除非最终选择比较棘手。由此产生的结果具有每个状态和帐户的日期：

select StudentStatus, min(WhenLastChanged) as WhenLastChanged
from (SELECT StudentStatus, WhenLastChanged, AccountNumber
             (row_number() over (partition by AccountNumber order by WhenLastChanged) -
              row_number() over (partition by AccountNumber, studentstatus order by WhenLastChanged)
            ) as grp
      FROM Account_History
    ) t
where StudentStatus = 'ACT'
group by AccountNumber, StudentStatus, grp
order by WhenLastChanged desc;

但是，您无法如此轻松地获得每个帐户的最后一个帐户。子查询的另一个级别：

select AccountNumber, StudentStatus, WhenLastChanged
from (select AccountNumber, StudentStatus, min(WhenLastChanged) as WhenLastChanged,
             row_number() over (partition by AccountNumber, StudentStatus order by min(WhenLastChanged) desc
                               ) as seqnum
      from (SELECT AccountNumber, StudentStatus, WhenLastChanged,
                   (row_number() over (partition by AccountNumber order by WhenLastChanged) -
                    row_number() over (partition by AccountNumber, studentstatus order by WhenLastChanged)
                  ) as grp
            FROM Account_History
          ) t
      where StudentStatus = 'ACT'
      group by AccountNumber, StudentStatus, grp
     ) t
where seqnum = 1;

这与窗口函数一起使用聚合row_number()。这是为组分配顺序号（汇总后），每个帐户的最后日期为1（order by min(WhenLastChanged) desc）。然后，最外面的select只是为每个帐户选择该行。

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

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