Заявление об ограничении ответственности: Показанная проблема гораздо более общая, чем я ожидал вначале. Приведенный ниже пример взят из решения другого вопроса. Но теперь я взял этот образец для решения многих других проблем - в основном связанных с временными рядами (посмотрите на раздел «Связанные» на правой панели).
Итак, сначала я пытаюсь объяснить проблему в более общем плане:
Я использую PostgreSQL, но уверен, что эта проблема существует и в других оконных функциях, поддерживающих СУБД (MS SQL Server, Oracle, ...).
Оконные функции можно использовать для группировки определенных значений по общему атрибуту или значению. Например, вы можете группировать строки по дате. Затем вы можете рассчитать максимальное значение для каждой даты или среднее значение, или подсчитать строки, или что-то еще.
Это может быть достигнуто путем определения PARTITION
. Группировка по датам работает с PARTITION BY date_column
. Теперь вы хотите выполнить операцию, которая требует особого порядка в ваших группах (вычисление номеров строк или суммирование столбца). Это можно сделать с помощью PARTITON BY date_column ORDER BY an_attribute_column
.
Теперь подумайте о более точном разрешении временных рядов. Что делать, если у вас есть не даты, а временные метки. Тогда вы больше не сможете группировать по столбцу времени. Но, тем не менее, может быть важно проанализировать данные в том порядке, в котором они были добавлены (возможно, метка времени - это время создания вашего набора данных). Затем вы понимаете, что некоторые последовательные строки имеют одинаковое значение, и вы хотите сгруппировать данные по этому общему значению. Но подсказка в том, что строки имеют разные отметки времени.
Проблема здесь в том, что вы не можете сделать PARTITION BY value_column
. Потому что PARTITION BY
заставляет заказывать первым. Таким образом, ваша таблица будет упорядочена value_column
перед группировкой и больше не будет упорядочена по метке времени. Это дает результаты, которых вы не ожидаете.
В общем: Проблема в том, чтобы обеспечить особый порядок, даже если упорядоченный столбец не является частью созданного раздела..
Пример:
У меня есть следующая таблица:
ts val
100000 50
130100 30050
160100 60050
190200 100
220200 30100
250200 30100
300000 300
500000 100
550000 1000
600000 1000
650000 2000
700000 2000
720000 2000
750000 300
У меня была проблема, что мне пришлось сгруппировать все связанные значения столбца val
. Но хотел держать заказ по ts
. Для этого я хотел добавить столбец с уникальным идентификатором для каждой группы val
.
Ожидаемый результат:
ts val group
100000 50 1
130100 30050 2
160100 60050 3
190200 100 4
220200 30100 5 \ same group
250200 30100 5 /
300000 300 6
500000 100 7
550000 1000 8 \ same group
600000 1000 8 /
650000 2000 9 \
700000 2000 9 | same group
720000 2000 9 /
750000 300 10
Первая попытка - это использование оконной функции rank
, которая обычно выполняет эту работу:
SELECT
*,
rank() OVER (PARTITION BY val ORDER BY ts)
FROM
test
Но в этом случае это не работает, потому что предложение PARTITION BY
упорядочивает таблицу сначала по столбцам раздела (в данном случае val
), а затем по столбцам ORDER BY
. Таким образом, заказ осуществляется по val, ts
вместо ожидаемого заказа по ts
. Так что результат, конечно же, не был ожидаемым.
ts val rank
100000 50 1
190200 100 1
500000 100 2
300000 300 1
750000 300 2
550000 1000 1
600000 1000 2
650000 2000 1
700000 2000 2
720000 2000 3
130100 30050 1
220200 30100 1
250200 30100 2
160100 60050 1
Вопрос: Как получить идентификаторы групп относительно порядка по ts
?
Редактировать: Я добавил ниже собственное решение, но мне оно очень неудобно. Это кажется слишком сложным. Мне было интересно, есть ли способ лучше для достижения этого результата.
Я сам придумал это решение (надеясь, что кто-то другой получит лучший):
демонстрация: db <> рабочий пример
ts
val
с помощью оконной функции lag
(https://www.postgresql.org/docs/current/static/tutorial-window.html)0
или 1
SUM
. Это создает группы, которые я ищу. Они группируют столбец val
, но обеспечивают упорядочение по столбцу ts
.Запрос:
SELECT
*,
SUM(is_diff) OVER (ORDER BY ts)
FROM (
SELECT
*,
CASE WHEN val = lag(val) over (order by ts) THEN 0 ELSE 1 END as is_diff
FROM test
)s
Результат:
ts val is_diff sum
100000 50 1 1
130100 30050 1 2
160100 60050 1 3
190200 100 1 4
220200 30100 1 5 \ group
250200 30100 0 5 /
300000 300 1 6
500000 100 1 7
550000 1000 1 8 \ group
600000 1000 0 8 /
650000 2000 1 9 \
700000 2000 0 9 | group
720000 2000 0 9 /
750000 300 1 10
Да ладно, я стар.