SQL ИЛИ Linq: как классифицировать в разные пакеты при изменении категории

#sql #linq #group-by

#sql #linq #группировать по

Вопрос:

У меня есть следующие данные:

 Timestamp           Min     MAX     Category
2019-03-22 08:10    12.00   5.10    AAAA
2019-03-22 08:11    10.00   5.20    AAAA
2019-03-22 08:12    11.00   4.90    AAAA
2019-03-22 08:13    12.00   5.30    BBBB
2019-03-22 08:14    14.00   5.20    BBBB
2019-03-22 08:15    9.40    5.20    CCCC
2019-03-22 08:16    7.40    5.10    CCCC
2019-03-22 08:17    14.00   5.10    AAAA
2019-03-22 08:17    14.00   5.10    AAAA

Я ищу запрос SQL (или Linq) для группировки этих данных на основе Category изменений, чтобы иметь возможность видеть начало и конец каждой категории с течением времени.

Ответ №1:

Если я правильно вас понял, вы имеете дело с проблемой пробелов и островков.

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

 SELECT 
    category, 
    MIN(timestamp) start_timestap, 
    MAX(timestamp) end_timestap
FROM (
  SELECT
      t.*,
      ROW_NUMBER() OVER(ORDER BY Timestamp) rn1,
      ROW_NUMBER() OVER(PARTITION BY Category ORDER BY Timestamp) rn2
  FROM mytable t
) x
GROUP BY category, rn1 - rn2
ORDER BY start_timestap

В этой скрипте DB с вашими образцами данных это возвращает:

 | category | start_timestap      | end_timestap        |
| -------- | ------------------- | ------------------- |
| AAAA     | 2019-03-22 08:10:00 | 2019-03-22 08:12:00 |
| BBBB     | 2019-03-22 08:13:00 | 2019-03-22 08:14:00 |
| CCCC     | 2019-03-22 08:15:00 | 2019-03-22 08:16:00 |
| AAAA     | 2019-03-22 08:17:00 | 2019-03-22 08:17:00 |

ПРИМЕЧАНИЕ: это скрипта MySQL 8.0 DB, однако это стандартный ANSI SQL, который должен работать на любой платформе, поддерживающей оконные функции.