如何获得过去 X 天每天的最高金额？

Question

这可能是一件容易的事，但对于我来说，我似乎无法弄清楚。

这是我的表格：

Date          User   Amount
----------    -----  ------
01/01/2010    User1  2
01/01/2010    User2  2
01/01/2010    User1  4
01/01/2010    User2  1
01/02/2010    User2  2
01/02/2010    User1  2
01/02/2010    User2  4
01/02/2010    User2  1

过去几个月都是如此。我需要得到以下结果：

Date          User   Amount
----------    -----  ------
01/01/2010    User1  6
01/02/2010    User2  7

基本上，用户每天都有 Max(SUM(Amount)) 。

我将不胜感激你们能提供的任何提示。

谢谢。

Answer 1

SELECT MAX(amt),`Date`,`User` FROM
    (SELECT SUM(`Amount`),`Date`,`User` as amt .... GROUP BY `Date`,`User`)
GROUP BY `Date`

Answer 2

select t.*
from (
    select Date, Max(Amount) as MaxAmount
    from MyTable
    group by Date           
) tm
inner join MyTable t on tm.Date = t.Date and tm.MaxAmount = t.Amount

注意：如果在某一天有两个用户的最大金额相同，这将为您提供两个用户记录。

Answer 3

我实际上最终选择了以下内容：

WITH ranked AS
(
  SELECT ROW_NUMBER() OVER (ORDER BY SUM(Amount), Date, User) as 'rank', SUM(Amount) AS Amount, User, Date FROM MyTable GROUP BY Date, User
)
SELECT Date, User, Amount
FROM ranked
WHERE rank IN ( select MAX(rank) from ranked group by Date)
ORDER BY Date DESC

Answer 4

使用 RANK ... OVER 可以不那么冗长，但以下是直接的解决方案：

WITH summary_user_date 
AS (SELECT      Date, User, SUM(Amount) AS SumAmount
    FROM        MyTable
    GROUP BY    Date, User
)
,   summary_date
AS (SELECT      Date, MAX(SumAmount) AS SumAmount
    FROM        summary_user_date 
    GROUP BY    Date
)
SELECT      summary_user_date.*
FROM        summary_user_date 
INNER JOIN  summary_date
        ON  summary_date.Date = summary_user_date.Date
        AND summary_date.SumAmount = summary_user_date.SumAmount

应该提到的是，如果多个用户具有相同的最大金额，则将显示所有用户。如果不希望出现这种情况，则应使用基于 RANK 的解决方案。

Answer 5

使用 CTE，您可以执行以下操作：

With DailyTotals As
    (
    Select [Date], [User], Sum(Amount) As Total
    From #Test
    Group By [Date], [User]
    )
Select [Date],[User],Total
From DailyTotals As DT
Where Total = (
                Select Max(Total)
                From DailyTotals As DT1
                Where DT1.[Date] = DT.[Date]
                )
Order By DT.[Date]

非 CTE 解决方案是：

Select [Date],[User],Total
From    (
        Select [Date], [User], Sum(Amount) As Total
        From #Test
        Group By [Date], [User]
        ) As DT
Where DT.Total =    (
                    Select Max(DT1.Total)
                    From    (       
                            Select [Date], [User], Sum(Amount) As Total
                            From #Test
                            Group By [Date], [User]
                            ) As DT1
                    Where DT1.[Date] = DT.[Date]
                    )
Order By DT.[Date]