Как отслеживать, сколько раз столбец менял свое значение?
У меня есть таблица под названием crewWork следующим образом:
CREATE TABLE crewWork( FloorNumber int, AptNumber int, WorkType int, simTime int ) После того, как таблица была заполнена, мне нужно знать, сколько раз происходило изменение apt и сколько раз происходило изменение пола. Обычно я ожидаю найти 10 рядов на каждой метке и 40-50 на каждом этаже. Я мог бы просто написать скалярную функцию для этого, но мне было интересно, есть ли способ сделать это в t-SQL без необходимости писать скалярные функции.
благодаря
Данные будут выглядеть так:
FloorNumber AptNumber WorkType simTime 1 1 12 10 1 1 12 25 1 1 13 35 1 1 13 47 1 2 12 52 1 2 12 59 1 2 13 68 1 1 14 75 1 4 12 79 1 4 12 89 1 4 13 92 1 4 14 105 1 3 12 115 1 3 13 129 1 3 14 138 2 1 12 142 2 1 12 150 2 1 14 168 2 1 14 171 2 3 12 180 2 3 13 190 2 3 13 200 2 3 14 205 3 3 14 216 3 4 12 228 3 4 12 231 3 4 14 249 3 4 13 260 3 1 12 280 3 1 13 295 2 1 14 315 2 2 12 328 2 2 14 346
Мне нужна информация для отчета, мне не нужно ее хранить в любом месте.
Если я ничего не пропущу, вы можете использовать следующий метод, чтобы найти количество изменений:
-
определить группы последовательных строк с одинаковыми значениями;
-
считать эти группы;
-
вычесть 1.
Применяйте метод отдельно для AptNumber и для FloorNumber .
Группы могут быть определены как в этом ответе , только в вашем случае нет столбца Seq . Вместо этого можно использовать другое выражение ROW_NUMBER() . Вот приблизительное решение:
; WITH marked AS ( SELECT FloorGroup = ROW_NUMBER() OVER ( ORDER BY simTime) - ROW_NUMBER() OVER (PARTITION BY FloorNumber ORDER BY simTime), AptGroup = ROW_NUMBER() OVER ( ORDER BY simTime) - ROW_NUMBER() OVER (PARTITION BY AptNumber ORDER BY simTime) FROM crewWork ) SELECT FloorChanges = COUNT(DISTINCT FloorGroup) - 1, AptChanges = COUNT(DISTINCT AprGroup) - 1 FROM marked
(Я предполагаю, что столбец simTime определяет временную шкалу изменений.)
ОБНОВИТЬ
Ниже приведена таблица, показывающая, как различные группы получаются для AptNumber .
AptNumber RN RN_Apt Apt_Group (= RN - RN_Apt ) --------- -- ------ --------- 1 1 1 0 1 2 2 0 1 3 3 0 1 4 4 0 2 5 1 4 2 6 2 4 2 7 3 4 1 8 5 => 3 4 9 1 8 4 10 2 8 4 11 3 8 4 12 4 8 3 13 1 12 3 14 2 12 3 15 3 12 1 16 6 10 … … … …
Здесь RN – псевдо-столбец, который обозначает ROW_NUMBER() OVER (ORDER BY simTime) . Вы можете видеть, что это всего лишь последовательность ранжирования, начиная с 1.
Другой псевдо-столбец RN_Apt содержит значения, вырабатываемые другим ROW_NUMBER , а именно ROW_NUMBER() OVER (PARTITION BY AptNumber ORDER BY simTime) . Он содержит ранжирование в пределах отдельных групп с идентичными значениями AptNumber . Вы можете видеть, что для вновь обнаруженного значения последовательность начинается, а для повторяющейся – она продолжается там, где она была остановлена в последний раз.
Вы также можете видеть из таблицы, что если мы вычитаем RN из RN_Apt (может быть наоборот, не имеет значения в этой ситуации), мы получаем значение, которое однозначно идентифицирует каждую отдельную группу из тех же значений AptNumber . Вы можете также назвать это значение идентификатором группы.
Итак, теперь, когда у нас есть эти идентификаторы, нам остается только посчитать их (конечно, считать разные значения). Это будет число групп, а число изменений будет меньше (если первая группа не будет считаться изменением).
добавьте дополнительную таблицу изменения столбца
CREATE TABLE crewWork( FloorNumber int, AptNumber int, WorkType int, simTime int ,changecount int)
значение changecount для каждого обновления
если хотите узнать счет для каждого поля, тогда добавьте соответствующие ему столбцы для changecount
Предполагая, что каждая запись представляет собой другое изменение, вы можете найти изменения на один этаж:
select FloorNumber, count(*) from crewWork group by FloorNumber
И изменения за квартиру (при условии, что AptNumber однозначно идентифицирует квартиру):
select AptNumber, count(*) from crewWork group by AptNumber
Или (при условии, что AptNumber и FloorNumber вместе однозначно идентифицируют квартиру):
select FloorNumber, AptNumber, count(*) from crewWork group by FloorNumber, AptNumber