#sql-server #sql-server-2012 #sequence #partitioning #ranking
#sql-server #sql-server-2012 #последовательность #разделение #Рейтинг
Вопрос:
Я использую SQL Server 2012, и мои данные выглядят следующим образом:
ActivityID ActivityCode Name_of_User ActivityCode_PrevActivity isLastActivityByUserSame
1020 B1 ABE A2 0
1021 A2 BOB A1 0
1022 A2 BOB A2 1
1023 B1 ABE B1 1
1024 B1 ABE B1 1
1025 B2 ABE B1 0
1026 B2 CARL A3 0
1027 A1 CARL B2 0
1028 B8 BOB A2 0
1029 A1 CARL A1 1
1030 B2 ABE B2 1
1031 B4 ABE B2 0
1032 B8 BOB B8 1
1033 A3 BOB B8 0
1034 B4 CARL A1 0
Просто как краткий обзор того, что представляют поля:
- ActivityId: PK таблицы
- ActivityCode: тип активности пользователя
- Name_of_User: имя пользователя
- ActivityCode_PrevActivity: код действия последнего действия пользователя
- isLastActivityByUserSame: двоичное значение, указывающее, совпадает ли код последнего действия пользователя с кодом текущего действия
Кроме того, есть несколько других столбцов, которые, скорее всего, не помогут с поставленной задачей.
Я хотел бы сгенерировать текущий подсчет количества последовательных строк с одинаковым значением для каждой строки с помощью Name_of_User . Чтобы дать вам представление о том, как это будет выглядеть, вот предполагаемый результат (обратите внимание на новый столбец ConsecActivityCount):
ActivityID ActivityCode Name_of_User ActivityCode_PrevActivity isLastActivityByUserSame ConsecActivityCount
1020 B1 ABE A2 0 0
1023 B1 ABE B1 1 1
1024 B1 ABE B1 1 2
1025 B2 ABE B1 0 0
1030 B2 ABE B2 1 1
1031 B4 ABE B2 0 0
1021 A2 BOB A1 0 0
1022 A2 BOB A2 1 1
1028 B8 BOB A2 0 0
1032 B8 BOB B8 1 1
1033 A3 BOB B8 0 0
1026 B2 CARL A3 0 0
1027 A1 CARL B2 0 0
1029 A1 CARL A1 1 1
1034 B4 CARL A1 0 0
Я пытался использовать что-то вроде:
Select *,
DENSE_RANK() OVER (PARTITION BY Name_of_User ORDER BY ActivityID) as ConsecReports
FROM ActivityTable
Но мне не повезло.
Каков наилучший способ вычисления такого столбца?
Заранее спасибо!
Ответ №1:
Я думаю, что нашел хорошее решение. Сначала я создал идентификаторы для последовательностей, в которых они появлялись, а затем подсчитал количество последовательных положительных значений ‘isLastActivityByUserSame’.
Вот решение:
SELECT at.*,
(CASE WHEN at.isLastActivityByUserSame != 0
THEN SUM(CASE WHEN at.isLastActivityByUserSame != 0 THEN at.isLastActivityByUserSame END) OVER (PARTITION BY Name_of_User, LastSequenceID ORDER BY ActivityID)
ELSE 0 END) AS ConsecActivityCount
FROM (select at.*,
SUM(CASE WHEN isLastActivityByUserSame != 0 THEN 0 ELSE 1 END) OVER (ORDER BY ActivityID) AS LastSequenceID
FROM ActivityTable at
) at ORDER BY Name_of_User, ActivityID;
Ответ №2:
Используйте приведенный ниже запрос. Это будет работать.
SELECT *
,ROW_NUMBER()OVER(PARTITION BY Name_of_User, ActivityCode ORDER BY ActivityID)-1 AS ConsecActivityCount
FROM ActivityTable
Комментарии:
1. Аджай, этот запрос разделяет данные на Name_of_User и ActivityCode. Однако не очень хорошо вести последовательные подсчеты и сбрасывать счетчик на ‘0’ каждый раз, когда в поле isLastActivityByUserSame встречается ‘0’. Похоже, что в этом ограниченном случае это работает, как указано на вашем скриншоте, но после ручного подсчета на большей выборке это далеко