SQL Server 2012 - Скользящий счетчик со сбросом

#sql #sql-server #partition #rolling-computation

#sql #sql-сервер #раздел #скользящее вычисление

Вопрос:

Я использую SQL Server 2012 и хочу получить общее количество неудачных попыток входа в систему. В моей таблице есть

 | Login_Name | Is_Success | Login_Date |

Мой запрос

 select login_name, 
sum(case when is_success = 1 THEN 0 ELSE 1 END) over (partition by login_name, issuccess order by login_name, login_date, is_success)
from login_table

Запрос дает мне результаты, но я хочу, чтобы счетчик сбрасывался, когда is_success становится 1

 | Login_Name | Is_Success | Unsuccessful Attempts |
| admin      | 1          | 0                     |
| admin      | 0          | 1                     |
| admin      | 0          | 2                     |
| admin      | 0          | 3                     |
| admin      | 0          | 4                     |
| admin      | 0          | 5                     |
| admin      | 1          | 0                     |
| admin      | 0          | 1                     |

Примечание: я использую SQL Server 2012

Ответ №1:

Это классическая проблема с пробелами и островами. Вы можете использовать windowed COUNT для размещения данных в группы, а затем использовать ROW_NUMBER для этих групп:

 WITH YourTable AS(
    SELECT *
    FROM (VALUES('admin',1,CONVERT(datetime2(0),'2020-10-30T20:00:00')),
                ('admin',0,CONVERT(datetime2(0),'2020-10-30T20:01:00')),
                ('admin',0,CONVERT(datetime2(0),'2020-10-30T20:02:00')),
                ('admin',0,CONVERT(datetime2(0),'2020-10-30T20:03:00')),
                ('admin',0,CONVERT(datetime2(0),'2020-10-30T20:04:00')),
                ('admin',0,CONVERT(datetime2(0),'2020-10-30T20:05:00')),
                ('admin',1,CONVERT(datetime2(0),'2020-10-30T20:06:00')),
                ('admin',0,CONVERT(datetime2(0),'2020-10-30T20:07:00')))V(Login_Name,Is_Success,Login_Date)),
Grps AS(
    SELECT Login_Name,
           Is_Success,
           Login_Date,
           COUNT(CASE Is_Success WHEN 1 THEN 1 END) OVER (PARTITION BY Login_Name ORDER BY Login_Date) AS Grp
    FROM YourTable)
SELECT Login_NAme,
       Is_Success,
       ROW_NUMBER() OVER (PARTITION BY Login_NAme, Grp ORDER BY Login_Date) -1 AS UnSuccessfulAttempts
FROM Grps
ORDER BY Login_Name,
         Grp,
         Login_Date;

1. Это выглядит как правильный подход, и я поддержал его — однако будет COUNT(Is_Success) ли работать, если неудачные попытки имеют значение (например, 0 или -1)? Вы могли бы использовать COUNT (CASE …), аналогичный их. Кроме того, в вашем заказе by есть избыточное поле — может быть, вы имели в виду дату?

2. Вместо этого вы бы использовали CASE выражение @seanb . Я просто предположил, что они, вероятно, были просто 1 or 0 , и это то, что говорят ваши данные образца.

3. Я попытался запустить этот запрос, но он не возвращает мне результат. Столбец безуспешных попыток всегда увеличивается на 1

4. Примеры данных помогут мне помочь вам, @Ankit. Я только ожидал результатов, поэтому должен был сделать предположения о ваших данных.

5. Я обновил предположения о ваших данных, @Ankit . Как я уже упоминал, нет образцов данных, только ожидаемые результаты. Login_date Например, у вас есть столбец, о котором я понятия не имею, что на самом деле хранится.

Ответ №2:

Я полагаю, вы ищете сводку, а не журнал специфики? Я бы подошел к этому так:

 SELECT login_name, 
 ( SELECT count(*) FROM login_table
    WHERE login_name = lt.login_name
      AND login_date > ( SELECT MAX(login_date) FROM login_table WHERE login_name = lt.login_name AND is_success = 1 )
 ) AS failures
  FROM login_table AS lt
  ORDER BY 1

Как это работает:

Внутренний коррелированный подзапрос получает последний успешный вход для пользователя:

 SELECT MAX(login_date) FROM login_table WHERE login_name = lt.login_name AND is_success = 1

Внешний коррелированный запрос:

(ВЫБЕРИТЕ count(*) ИЗ login_table, ГДЕ login_name = lt.login_name И login_date > —последний успешный вход в систему — ) КАК сбои

подсчитывает, сколько сбоев произошло после последнего успеха. Я не тестировал это. Удачи с вашим проектом!