Разница между двумя датами с максимальным количеством дней между группами с общим идентификатором

Question

Если я возьму даты из группы B, то я должен найти максимальную разницу между любыми двумя датами из группы B и отобразить это days_difference в виде отдельного столбца. Также хочу отобразить эти две даты в двух разных столбцах, которые имели максимальное значение days_difference.

Условие: два идентификатора класса могут иметь одинаковое имя класса в данных. Как вы можете видеть, Class-ID 1238 и 1252 имеют одинаковое имя класса. Таким образом, вывод должен быть для каждого идентификатора класса

Таким образом, результат должен иметь: ClassID, ClassName, Date1, Date 2, DateDiff

ClassName | Dates     | ClassID  | Spend($)
ClassA    | 21-Jun-16 | 1238     | 1
ClassA    | 27-Jun-16 | 1238     | 2
ClassA    | 14-Apr-11 | 1252     | 3
ClassA    | 15-Apr-11 | 1252     | 4
ClassC    | 26-Oct-15 | 1261     | 5
ClassC    | 21-Oct-15 | 1261     | 6
ClassA    | 10-Dec-10 | 1252     | 21
ClassC    | 15-Dec-14 | 1261     | 23
ClassC    | 15-May-14 | 1261     | 34
ClassA    | 10-Jan-09 | 1252     | 54
ClassA    | 12-Jun-09 | 1238     | 65
ClassA    | 29-Jun-12 | 1238     | 76
ClassA    | 11-Apr-08 | 1252     | 87
ClassA    | 01-Apr-16 | 1252     | 56
ClassC    | 16-Oct-16 | 1261     | 43
ClassC    | 02-Sep-05 | 1261     | 33
ClassA    | 29-Dec-13 | 1252     | 77
ClassC    | 13-Dec-15 | 1261     | 87
ClassC    | 13-Aug-10 | 1261     | 98
ClassA    | 21-Jan-14 | 1252     | 12

Answer 1

SELECT A.ClassId,
       B.LagDates,
       B.Dates 
  FROM 
     (
         SELECT ClassId,
                MAX( DaysDiff ) AS MD
           FROM
              (       
                SELECT *,
                       LAG( Dates, 1 ) OVER ( PARTITION BY ClassID ORDER BY Dates ) AS LagDates,   
                       TO_DATE( Dates, 'YYYY-MM-DD' ) 
                       -
                       LAG( Dates, 1 ) OVER ( PARTITION BY ClassID ORDER BY    
                       Dates ) AS DaysDiff
                  FROM ( SELECT *                                    
                           FROM Table
                          ORDER BY ClassId, Dates 
                        ) 
               ) 
         GROUP BY ClassId
      ) A,
      (
          SELECT *,
                 LAG( Dates, 1 ) OVER ( PARTITION BY ClassID ORDER BY Dates ) AS LagDates, 
                 TO_DATE( Dates, 'YYYY-MM-DD' ) 
                 -
                 LAG( Dates, 1 ) OVER ( PARTITION BY ClassID ORDER BY Dates ) 
                 AS DaysDiff
            FROM (
                    SELECT * 
                      FROM Table
                     ORDER BY ClassId, Dates  
                  ) 
       ) B
WHERE A.ClassId = B.ClassId
  AND A.MD = B.DaysDiff;

Answer 2

Вы можете получить разницу в днях между последовательными датами, используя LEAD или LAG.

SELECT 
    Dates,
    LEAD(Dates) OVER (PARTITION BY ClassID ORDER BY Dates) NextDate,
    Dates - LEAD(Dates) OVER (PARTITION BY ClassID ORDER BY Dates) DaysDiff
FROM MyTable

вы можете получить желаемый результат из этого результата, взяв запись с наибольшим значением DaysDiff

для справки

ранжировать результаты и получить разность дат с самым высоким рейтингом по classID.

SELECT * FROM (
    SELECT *, RANK() OVER (PARTITION BY ClassID ORDER BY DaysDiff DESC) Rnk
    FROM (
        SELECT 
            ClassID,
            ClassName,
            Dates,
            LEAD(Dates) OVER (PARTITION BY ClassID ORDER BY Dates) NextDate,
            Dates - LEAD(Dates) OVER (PARTITION BY ClassID ORDER BY Dates) DaysDiff
        FROM MyTable) t1
    ) t2
WHERE t2.Rnk = 1

выход для вышеуказанных данных образца должен быть.

ClassName ClassID     Dates                   NextDate                DaysDiff    Rnk
--------- ----------- ----------------------- ----------------------- ----------- --------------------
ClassA    1238        2012-06-29 00:00:00.000 2016-06-21 00:00:00.000 1453        1
ClassA    1252        2011-04-15 00:00:00.000 2013-12-29 00:00:00.000 989         1
ClassC    1261        2005-09-02 00:00:00.000 2010-08-13 00:00:00.000 1806        1