Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как посчитать количество успешных месяцев подряд / 8 сообщений из 8, страница 1 из 1
05.07.2021, 10:51
    #40081738
Abejon
Abejon
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как посчитать количество успешных месяцев подряд
Есть таблица, в которой хранится история достижений участников соревнования. Примерно такая:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
CREATE TABLE [dbo].[RankHistory](
    [UserId] [int] NOT NULL,
    [PeriodNumber] [int] NOT NULL,
    [Rank] [tinyint] NOT NULL,
 CONSTRAINT [PK_RankHistory] PRIMARY KEY CLUSTERED 
(
    [UserId] ASC,
    [PeriodNumber] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


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

Т.е. если есть такой набор данных:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
INSERT INTO RankHistory VALUES(1, 100, 1);
INSERT INTO RankHistory VALUES(1, 101, 2);
INSERT INTO RankHistory VALUES(1, 102, 3);
INSERT INTO RankHistory VALUES(1, 103, 4);
 
INSERT INTO RankHistory VALUES(2, 100, 4);
INSERT INTO RankHistory VALUES(2, 101, 5);
INSERT INTO RankHistory VALUES(2, 102, 5);
INSERT INTO RankHistory VALUES(2, 103, 5);
 
INSERT INTO RankHistory VALUES(3, 100, 4);
INSERT INTO RankHistory VALUES(3, 101, 5);
INSERT INTO RankHistory VALUES(3, 102, 3);
INSERT INTO RankHistory VALUES(3, 103, 5);
 
INSERT INTO RankHistory VALUES(4, 100, 5);
INSERT INTO RankHistory VALUES(4, 101, 6);
INSERT INTO RankHistory VALUES(4, 102, 6);
INSERT INTO RankHistory VALUES(4, 103, 4);
 
INSERT INTO RankHistory VALUES(5, 100, 7);
INSERT INTO RankHistory VALUES(5, 101, 8);
INSERT INTO RankHistory VALUES(5, 102, 6);
INSERT INTO RankHistory VALUES(5, 103, 7);


То для ранга 5 и номера текущего периода 103 запрос должен вернуть вот такой результат:

UserId Count
2 3
3 1
5 4
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.07.2021, 11:44
    #40081755
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как посчитать количество успешных месяцев подряд
Abejon
То для ранга 5 и номера текущего периода 103 запрос должен вернуть вот такой результат:

UserId Count
2 3
3 1
5 4

а UserId = 4 куда делся ?
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
;with cte as (
	select 
		*
		,row_number()over(partition by UserId order by PeriodNumber) 
			-case when Rank>=5 then row_number()over(partition by UserId, case when Rank>=5 then 0 end order by PeriodNumber) end as inv
	from RankHistory
	where PeriodNumber <= 103),
cte1 as (
	select
		UserId
		,count(*) as cnt
	from cte 
	where inv is not null
	group by
		UserId
		,inv)
select 
	UserId
	,max(cnt) as cnt
from cte1 
group by
	UserId
order by 1


UserIdcnt23314354
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.07.2021, 11:48
    #40081759
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как посчитать количество успешных месяцев подряд
Abejon
Нужно написать запрос, который выберет участников, имеющих ранг не ниже заданного, и указать, сколько раз подряд они достигали этого ранга или выше, включая последний период .

т.е. ранг в посл.периоде, должен быть не ниже заданного ?
тогда, да, 4-й - вылетает )

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
;with cte as (
	select 
		*
		,row_number()over(partition by UserId order by PeriodNumber) 
			-case when Rank>=5 then row_number()over(partition by UserId, case when Rank>=5 then 0 end order by PeriodNumber) end as inv
	from RankHistory t1
	where PeriodNumber <= 103
		and exists(select 1 from RankHistory t2 where t2.PeriodNumber = 103 and t2.Rank >= 5 and t1.UserId=t2.UserId)),
cte1 as (
	select
		UserId
		,count(*) as cnt
	from cte 
	where inv is not null
	group by
		UserId
		,inv)
select 
	UserId
	,max(cnt) as cnt
from cte1 
group by
	UserId
order by 1


UserIdcnt233154
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.07.2021, 11:58
    #40081767
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как посчитать количество успешных месяцев подряд
court
т.е. ранг в посл.периоде, должен быть не ниже заданного ?

+ и считается только период непрерывности который включает посл.период (?)

тогда всё проще )
Код: sql
1.
2.
3.
4.
5.
6.
select
	UserId
	,t1.PeriodNumber - isnull(a.PeriodNumber,99) as cnt
from RankHistory t1
outer apply (select top(1) t2.PeriodNumber from RankHistory t2 where t1.UserId=t2.UserId and t2.Rank < 5 order by t2.PeriodNumber desc) a
where t1.PeriodNumber = 103 and t1.Rank >= 5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.07.2021, 14:23
    #40081802
Abejon
Abejon
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как посчитать количество успешных месяцев подряд
court,

автортогда всё проще
Во втором варианте количество получается отрицательным.
А в первом варианте на реальной базе почему-то не работает вообще - возвращает пустую выборку.
Отличие в том, что номер периода больше и минимальный ранг. При этом, я точно знаю, что есть 10 человек, удовлетворяющих этому условию.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.07.2021, 14:42
    #40081810
Abejon
Abejon
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как посчитать количество успешных месяцев подряд
court, собственно, второй вариант тоже на реальной базе возвращает пустую выборку.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.07.2021, 14:45
    #40081814
Abejon
Abejon
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как посчитать количество успешных месяцев подряд
court, а если беру не последний период, а последний период - 1, то данные возвращаются, но много лишних записей, которые не должны попадать в выборку.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.07.2021, 15:13
    #40081823
Щукина Анна
Щукина Анна
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как посчитать количество успешных месяцев подряд
Abejon,


Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
declare @cper int = 103
      , @rank int = 5
select UserId, count(1) as cnt
  from (
         select t.*
               , sum(case when [Rank] < @rank then 1 else 0 end) 
                   over(partition by UserId order by PeriodNumber desc) as flag
           from [dbo].[RankHistory] as t
          where PeriodNumber <= @cper
       ) v
 where flag = 0
 group by UserId
having max(PeriodNumber) = @cper; -- На случай, если по участнику нет данных за текущий период
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как посчитать количество успешных месяцев подряд / 8 сообщений из 8, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 427ms
Закрыть
start [/forum/topic.php?fid=46&mobile=1&tid=1684545]:
 0ms
get settings:
 9ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 53ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 44ms
get tp. blocked users:
 1ms
others: 289ms
total:  427ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]