时间戳

首页 > 解决方案 > 根据 SQL Server 中的条件分组重复

sql - 根据 SQL Server 中的条件分组重复

问题描述

我有一个包含重复值的表。我想要做的是对列进行求和。我唯一的问题是我不想根据NewTransaction表中的列对我想要复制的所有重复值求和。

RecoveryKey   DateTime      Duration NewTransaction Variable8   CallTypeID
--------------------------------------------------------------------------
7994113912466 12/4/18 16:26 19       Y              152643-5657     -1    
7994113912470 12/4/18 16:26 1168     Y              152643-5657   6390
7994113912751 12/4/18 16:51 2686     N              152643-5657   6390

因此，根据表格，我要做的是选择记录Max(DateTime)for Variable8is duplicate having NewTransaction == Y。

和做类似的记录NewTransaction == N

如果你这样做，你得到的价值低于 -

RecoveryKey   DateTime      Duration NewTransaction Variable8   CallTypeID
--------------------------------------------------------------------------
7994113912470 12/4/18 16:26 1168     Y              152643-5657    6390
7994113912751 12/4/18 16:51 2686     N              152643-5657    6390

我需要的是max(RecoveryKey), max(DateTime), Sum(Duration). 我的最终结果将如下所示

RecoveryKey   DateTime      Duration NewTransaction Variable8   CallTypeID
---------------------------------------------------------------------------
7994113912470 12/4/18 16:26 3854     Y              152643-5657    6390

标签： sqlsql-server

解决方案

基于这个问题，我相信你正在尝试做以下两件事：

  1. 过滤到一条记录，即max([DateTime]), for eachVariable8和NewTransaction组合。
  2. 让列显示NewTransaction值为的记录中包含的数据Y，而不是显示Duration通过步骤 1 中的过滤器的记录的总数。

回答

with initial_filter as
    (
        select *
        , row_number() over (partition by Variable8, NewTransaction order by [DateTime] desc, RecoveryKey desc) as rnk_a
        , row_number() over (partition by Variable8 order by NewTransaction desc, [DateTime] desc, RecoveryKey desc) as rnk_b
        from MyTable as t
    )
select max(b.RecoveryKey) as RecoveryKey
, max(b.[DateTime]) as [DateTime]
, sum(a.Duration) as Duration
, max(b.NewTransaction) as NewTransaction
, max(b.Variable8) as Variable8
, max(b.CallTypeID) as CallTypeID
from initial_filter as a
left join initial_filter as b on a.RecoveryKey = b.RecoveryKey and b.rnk_b = 1
where a.rnk_a = 1

结果：

+---------------+-------------------------+----------+----------------+-------------+------------+
| RecoveryKey   | DateTime                | Duration | NewTransaction | Variable8   | CallTypeID |
+---------------+-------------------------+----------+----------------+-------------+------------+
| 7994113912470 | 2018-12-04 16:26:00.000 | 3854     | Y              | 152643-5657 | 6390       |
+---------------+-------------------------+----------+----------------+-------------+------------+

推荐阅读