Блог

У меня есть набор данных, аналогичный приведенному ниже образцу:

 RecordId  RecordDateTime      AttributeA  AttributeB  Amount Value
--------- ------------------- ----------- ----------- ------ -------
1         2020-08-01 00:00:00 AttA01      AttB01      10.00   6510
2         2020-08-01 00:00:00 AttA01      AttB01      11.00   3620
3         2020-08-01 00:07:00 AttA01      AttB01      17.00  87430
4         2020-08-01 00:15:00 AttA01      AttB01      15.00   4140
5         2020-08-01 00:20:00 AttA02      AttB01      30.00   2350
6         2020-08-01 00:30:00 AttA01      AttB01      20.00  14560
7         2020-08-01 00:46:00 AttA01      AttB01      15.00   1370
8         2020-08-01 02:15:00 AttA01      AttB01      30.00   1380
9         2020-08-01 02:30:00 AttA01      AttB01       5.00   4490
10        2020-08-01 02:30:00 AttA01      AttB01       6.00  13100
  

Мне нужно сгруппировать все записи, где attributeA и attributeB совпадают и где время записи следующей записи находится в пределах 15 минут от времени записи текущей записи.

Результаты должны показывать самую раннюю дату группы, attributeA и attributeB, сумму Сумм и самую раннюю запись в значении группы в качестве начального значения и значение последней записи в качестве конечного значения.

Примерные результаты:

 RecordDateTime      AttributeA  AttributeB  Amount StartValue   EndValue
------------------- ----------- ----------- ------ ---------- ----------
2020-08-01 00:00:00 AttA01      AttB01      73.00        3620      14560
2020-08-01 00:20:00 AttA02      AttB01      30.00        2350       2350
2020-08-01 00:46:00 AttA01      AttB01      15.00        1370       1370
2020-08-01 02:15:00 AttA01      AttB01      41.00        1380      13100
  

Я борюсь с рекурсивной группировкой, основанной на разнице во времени в 15 минут. Я попробовал несколько вещей, включая рекурсивный CTE, но на самом деле не добился никакого прогресса, который стоит показывать. Есть ли простое решение для группировки?

Предоставление более подробной информации по запросу.
Редактировать: Для приложения всегда будут начальная и конечная даты, которые пользователь может динамически выбирать, которые могут охватывать несколько месяцев, начиная с любого дня или времени.

Предоставленные данные являются изготовленными данными. В реальной жизни атрибутом a будет имя пользователя, а атрибутом B — местоположение, а записи — транзакции.

Любая серия транзакций, выполняемых одним и тем же пользователем в одном и том же местоположении с задержкой от 0 до 15 минут (>= 0 и <= 15) между каждой транзакцией, должна быть сгруппирована, суммирована и рассматриваться как одна транзакция. Другими словами, если транзакции находятся в пределах 15 минут от предыдущей транзакции, они должны быть сгруппированы с предыдущими транзакциями и любой другой транзакцией до этого, с которой сгруппирована предыдущая транзакция.

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

Это своего рода поворот в решении проблемы островов.

Учитывая этот пример данных:

 CREATE TABLE #x
(
  RecordId int, 
  RecordDateTime datetime, 
  AttributeA char(6), 
  AttributeB char(6), 
  Amount DECIMAL(12,2), 
  [Value] int
);

INSERT #x(RecordId, RecordDateTime, AttributeA, AttributeB, Amount, [Value])
VALUES
  (1,  '2020-08-01 00:00:00', 'AttA01', 'AttB01', 10.00,   6510),
  (2,  '2020-08-01 00:00:00', 'AttA01', 'AttB01', 11.00,   3620),
  (3,  '2020-08-01 00:07:00', 'AttA01', 'AttB01', 17.00,  87430),
  (4,  '2020-08-01 00:15:00', 'AttA01', 'AttB01', 15.00,   4140),
  (5,  '2020-08-01 00:20:00', 'AttA02', 'AttB01', 30.00,   2350),
  (6,  '2020-08-01 00:30:00', 'AttA01', 'AttB01', 20.00,  14560),
  (7,  '2020-08-01 00:46:00', 'AttA01', 'AttB01', 15.00,   1370),
  (8,  '2020-08-01 02:15:00', 'AttA01', 'AttB01', 30.00,   1380),
  (9,  '2020-08-01 02:30:00', 'AttA01', 'AttB01',  5.00,   4490),
  (10, '2020-08-01 02:30:00', 'AttA01', 'AttB01',  6.00,  13100);
  

Я разделил это на три CTE:

• x определяет, какие строки являются «привязанными» строками, путем нахождения любой строки с отметкой времени, с которой прошло более 15 минут, чем с предыдущей строки для той же комбинации A / B (или с начала 2000 года в случае первого экземпляра любой комбинации A / B). Он присваивает столбец с именем is_anchor , чтобы легко идентифицировать эти строки.
• y просто применяет группу путем суммирования всех is_anchor значений из всех предыдущих строк для заданной комбинации A / B, упорядоченной по времени. Эффект здесь заключается в том, что всем строкам, следующим за привязкой, которые не являются привязками, присваивается одна и та же группа, потому что все они добавляют 0 к сумме.
• z затем для каждой группы суммируются Amount значения и находят первое и последнее Value значения. Из z мы выбираем только строки, где is_anchor = 1 .

Результирующий запрос:

 ;WITH x(a,b,d,am,v,is_anchor) AS 
(
  SELECT AttributeA, AttributeB, RecordDateTime, Amount, [Value],
      CASE WHEN DATEDIFF(MINUTE, COALESCE(LAG(RecordDateTime, 1) 
      OVER 
      (
        PARTITION BY AttributeA, AttributeB ORDER BY RecordDateTime
      ), '20000101'), RecordDateTime) > 15 THEN 1 ELSE 0 END
  FROM #x
),
y AS 
(
  SELECT *, 
    grp = SUM(is_anchor) OVER 
          (PARTITION BY a,b ORDER BY d ROWS UNBOUNDED PRECEDING)
  FROM x
),
z AS 
(
    SELECT d, a, b, is_anchor,
      Amount = SUM(am) OVER (PARTITION BY a,b,grp), 
      StartValue = FIRST_VALUE(v) OVER 
                   (PARTITION BY a,b,grp ORDER BY d, v),
      EndValue = FIRST_VALUE(v) OVER 
                   (PARTITION BY a,b,grp ORDER BY d DESC, v DESC)
    FROM y
)
SELECT RecordDateTime = d, AttributeA = a, AttributeB = b,
       Amount, StartValue, EndValue
FROM z
WHERE is_anchor = 1
ORDER BY d, a, b;
  

Результаты:

 RecordDateTime           AttributeA  AttributeB  Amount  StartValue  EndValue
=======================  ==========  ==========  ======  ==========  ========
2020-08-01 00:00:00.000  AttA01      AttB01      73.00         3620     14560
2020-08-01 00:20:00.000  AttA02      AttB01      30.00         2350      2350
2020-08-01 00:46:00.000  AttA01      AttB01      15.00         1370      1370
2020-08-01 02:15:00.000  AttA01      AttB01      41.00         1380     13100
  

dbfiddle