如何有效获取子组中的最大数据和大组中的最小数据？

一尘不染

如何有效获取子组中的最大数据和大组中的最小数据？

sql

首先，我希望在每个子组中获得最大日期。

A 组 = 动作 1 和 2

B 组 = 行动 3 和 4

actionName	action	actionBy	actiontime
999	1	Tom	2022-07-15 09:18:00
999	1	Tom	2022-07-15 15:21:00
999	2	Peter	2022-07-15 14:06:00
999	2	Peter	2022-07-15 14:08:00
999	3	Sally	2022-07-15 14:20:00
999	3	Mary	2022-07-15 14:22:00
999	4	Mary	2022-07-15 14:25:00

在这个例子中：

A组最大时间为“1 | Tom | 2022-07-15 15:21:00”

B组最大时间为“4 | Mary | 2022-07-15 14:25:00”

最终答案是“1 | Tom | 2022-07-15 14:25:00”，这是组间的最小数据。

我有一种方法如何获取每个组中的最大日期，如下面的代码。

with cte1
as (select actionName,
           actiontime,
           actionBy,
           row_number() over (partition by actionName order by actiontime desc) as rn
    from actionDetails
    where action in ( '1', '2' )
    UNION
    select actionName,
           actiontime,
           actionBy,
           row_number() over (partition by actionName order by actiontime desc) as rn
    from actionDetails
    where action in ( '3', '4' )
   )
select *
from cte1
where rn = 1

ActionName 不是 PK。它将获得每组中的最大数据。

然后，我不知道如何使用有效的方法来获取A组和B组之间的最小数据。您能给我一些想法吗？

我知道其中一种方法是再次自我加入。但是，我认为这不是最好的解决方案。

阅读 89

2022-07-21

共1个答案

一尘不染

首先，您可以通过将操作组放入分区子句来简化查询。使用 case 表达式为操作 1 和 2 获取一组，为操作 3 和 4 获取另一组。

然后在获得每个动作名称和动作组的最大日期之后，您希望获得每个动作名称的最小日期。这意味着您希望在第一个 CTE 的基础上建立第二个 CTE：

with max_per_group as
(
  select top(1) with ties
    actionname,
    actiontime,
    actionby
  from actiondetails
  where action in (1, 2, 3, 4)
  order by row_number() 
             over (partition by actionname, case when action <= 2 then 1 else 2 end
                   order by actiontime desc)
)
, min_of_max as
(
  select top(1) with ties
    actionname,
    actiontime,
    actionby
  from max_per_group
  order by row_number() over (partition by actionname order by actiontime)
)
select actionname, actiontime, actionby
from min_of_max
order by actionname;

如您所见，我不是计算行号然后必须在下一个查询中基于该行限制行，而是通过将行编号放入ORDER BY子句并申请TOP(1) WITH TIES将所有行编号为 1 来立即限制行。我喜欢这个稍微好一点，因为 CTE 已经生成了我想要使用的行，而不是只在更大的数据集中标记它们。但我猜这是个人喜好。

免责声明：

在我的查询中，我假设列操作是数字的。如果该列是一个字符串，因为它可以保存不是数字的值，那么使用字符串：

where action in ('1', '2', '3', '4')

partition by actionname, case when action in ('1', '2') then 1 else 2 end

另一方面，如果该列是一个字符串，但该列中只有数字，请改为修复您的表格。

2022-07-21