选择按日期和客户划分的最高收入

Question

以下是我表中的示例数据：

Rev       Date          Client
1890      2015-11-20    xyz
1536.28   2017-10-27    AAA
934.84    2017-10-27    AAA
919.592   2017-03-22    AAA 
760.985   2014-11-25    xyz

我需要为每个客户选择收入最高的日期。

我有以下查询，但它按客户选择每天的最大收入，而不仅仅是每个客户转速最高的一天

SELECT TOP 1 max(rev)/1000 AS Rev, date, client FROM table1 GROUP BY date, client

Answer 1

常见问题。我假设 Rev 已经每天总计，我们只是挑选出最高的行。如果有领带，你可以改用dense_rank()。

select * from (
    select *, row_number() over (partition by Client order by Rev desc) as rn
    from table1
) t
where rn = 1;

Answer 2

一种选择是将 WITH TIES 子句与 Row_Number(( 一起使用

例

Select top 1 with ties *
 From  YourTable
 Order By Row_Number() over (Partition By Client Order By Rev Desc)

Answer 3

设置：

DECLARE @data TABLE
(
    Rev money
    , [Date] date
    , Client nvarchar(10)
)
INSERT INTO @data
VALUES
    (1890, '2015-11-20', 'xyz')
    ,(1536.28, '2017-10-27', 'AAA')
    ,(934.84, '2017-10-27', 'AAA')
    ,(919.592, '2017-03-22', 'AAA') 
    ,(760.985, '2014-11-25', 'xyz')

查询：

SELECT Client
    , t.[Date]
FROM
(
    SELECT Client
        , [Date]
        , SUM(Rev) AS [TotalRev]
        , ROW_NUMBER() OVER (PARTITION BY Client ORDER BY SUM(Rev) DESC) AS RN
    FROM @data
    GROUP BY Client, [Date]
) AS t
WHERE t.RN = 1

结果：

Client    Date
====================
AAA       2017-10-27
xyz       2015-11-20

Answer 4

我的理解是michaelg的问题是每天收入最高的交易，所以：

 select * from (
    select *, row_number() over (partition by [date] order by Rev desc) as rn
    from ##table1
) t
where rn = 1;

如果你想要你的客户端的总量，你可以将所有版本分组到子查询中。

请在我的 T-SQL 代码操场 https://sqleditor.net/q/B19r72H_f 中试用你的脚本

Answer 5

我会为此使用CROSS APPLY。

SELECT DISTINCT ca.*
FROM table1 t
CROSS APPLY (SELECT TOP 1 *
             FROM table1
             WHERE Client = t.Client
             ORDER BY Rev DESC) ca

如果您已经有一个具有唯一 ID 的Client表，则更干净。

SELECT ca.Rev, ca.Date, c.Client
FROM Client c
CROSS APPLY (SELECT TOP 1 Rev, Date
             FROM table1
             WHERE Client = c.Client
             ORDER BY Rev DESC) ca