组中最大和第二大值之间的SQL差异?

Question

我需要找到组中第一大值和第二大值之间的差异。

我现在的情况:

select ID, max(activityDate) as lastactivity
from activities
group by ID

显示用户ID和该用户的最后一次活动。我想找到的是最后一个活动和前一个活动之间的时间差。我试着:

select ID, max(activityDate) as lastactivity, 
DATEDIFF(max(activityDate), (max(activityDate) where activityDate not in lastactivity)) 
from activities 
group by ID

但是它不起作用。我是一个完整的新手在SQL -我很抱歉幼稚的错误-我试着在网上查找一些解决方案，但找不到任何相关的我的问题。

Answer 1

如果我正在阅读正确的行，似乎你实际想做的是使用LAG来获得前一行，然后你可以在外部SELECT中使用TOP (1) WITH TIES来过滤数据到"最后";行:

WITH CTE AS(
    SELECT ID,
           activityDate AS LastActivity,
           LAG(activityDate) OVER (PARTITION BY ID ORDER BY activityDate) AS LastLastActivity
    FROM dbo.activities)
SELECT TOP (1) WITH TIES
       ID,
       LastActivity,
       DATEDIFF(DAY,LastLastActivity,LastActivity) AS Days
FROM CTE
ORDER BY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY LastActivity DESC);

Answer 2

让我给你简单点:)

按日期获取最新项目的代码:

SELECT MAX(Date) FROM Table;

按日期获取第二个最新项目的代码:

SELECT TOP (1) * 
FROM Table
WHERE Date < ( SELECT MAX(Date) 
               FROM Table
             ) 
ORDER BY Date DESC ;

获取差异的代码:

SELECT DATEDIFF(DAY,(SELECT MAX(Date) 
               FROM Table), (SELECT TOP (1) * 
FROM Table
WHERE Date < ( SELECT MAX(Date) 
               FROM Table
             ) 
ORDER BY Date DESC)) AS DIFFDATE;

Answer 3

使用ROW_NUMBER()如何?

select ID, max(activityDate) as lastactivity, 
       datediff(day, nullif(min(activityDate), max(activityDate)), max(activityDate))
from (select a.*,
             row_number() over (partition by id order by activityDate desc) as seqnum
      from activities a
     ) a
where seqnum <= 2;
group by ID;

当没有两行时，nullif()返回值为NULL。