ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как посчитать количество успешных месяцев подряд
8 сообщений из 8, страница 1 из 1
Как посчитать количество успешных месяцев подряд
    #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, 10:51
| Ответить | Цитировать | Написать  
Как посчитать количество успешных месяцев подряд
    #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:44
| Ответить | Цитировать | Написать  
Как посчитать количество успешных месяцев подряд
    #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:48
| Ответить | Цитировать | Написать  
Как посчитать количество успешных месяцев подряд
    #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, 11:58
| Ответить | Цитировать | Написать  
Как посчитать количество успешных месяцев подряд
    #40081802
Abejon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Abejon
Гость
court,

автортогда всё проще
Во втором варианте количество получается отрицательным.
А в первом варианте на реальной базе почему-то не работает вообще - возвращает пустую выборку.
Отличие в том, что номер периода больше и минимальный ранг. При этом, я точно знаю, что есть 10 человек, удовлетворяющих этому условию.
...
Рейтинг: 0 / 0
05.07.2021, 14:23
| Ответить | Цитировать | Написать  
Как посчитать количество успешных месяцев подряд
    #40081810
Abejon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Abejon
Гость
court, собственно, второй вариант тоже на реальной базе возвращает пустую выборку.
...
Рейтинг: 0 / 0
05.07.2021, 14:42
| Ответить | Цитировать | Написать  
Как посчитать количество успешных месяцев подряд
    #40081814
Abejon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Abejon
Гость
court, а если беру не последний период, а последний период - 1, то данные возвращаются, но много лишних записей, которые не должны попадать в выборку.
...
Рейтинг: 0 / 0
05.07.2021, 14:45
| Ответить | Цитировать | Написать  
Как посчитать количество успешных месяцев подряд
    #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
05.07.2021, 15:13
| Ответить | Цитировать | Написать  
8 сообщений из 8, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Как посчитать количество успешных месяцев подряд
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&fpage=20&tid=1684545]:
 0ms
get settings:
 7ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 31ms
get topic data:
 12ms
get forum data:
 2ms
get page messages:
 48ms
get tp. blocked users:
 1ms
others: 271ms
total:  395ms
созд. / загр.: 395ms
x
x
Закрыть


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