Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Хитрое ранжирование. Нужна помощь. Небольшой пример с описанием прилагается.  [new]
Ранжировщик Боря
Guest
Здравствуйте, гуру и не очень! Вот в такую задачку я упёрся. Поможите, кто чем сможет.
Есть следующая табличка с данными:
create table #T
(
  ID int,
  A  int,
  B  int
)

insert into #T (ID, A, B)
  select  1, 20, 0 union all
  select  2, 20, 0 union all
  select  3, 20, 0 union all
  select  4, 20, 0 union all
  select  5, 20, 0 union all
  select  6,  7, 0 union all
  select  7,  7, 0 union all
  select  8,  7, 0 union all
  select  9,  8, 1 union all
  select 10,  8, 1 union all
  select 11,  8, 1 union all
  select 12,  4, 1 union all
  select 13,  4, 1 union all
  select 14,  4, 1 union all
  select 15,  5, 0 union all
  select 16,  5, 0 union all
  select 17,  5, 0

select * from #T order by ID
drop table #T


То, что имеем
ID A B
1 20 0
2 20 0
3 20 0
4 20 0
5 20 0
6 7 0
7 7 0
8 7 0
9 8 1
10 8 1
11 8 1
12 4 1
13 4 1
14 4 1
15 5 0
16 5 0
17 5 0

Суть задачки такова, что нужно запросом сформировать выборку так, чтоб в новом столбце ID_NEW проставились значения с поля ID, но по одному условию. Если, идя по записям с сортировкой по ID мы встречаем в поле B единицу, то, начиная со следующей группы записей (группы составлены по полю А), значения поля ID должны поменять сортировку на обратную (в пределах своей группы) до конца данных.

То, что должно получиться
ID A B ID_NEW
1 20 0 1
2 20 0 2
3 20 0 3
4 20 0 4
5 20 0 5
6 7 0 6
7 7 0 7
8 7 0 8
9 8 1 9
10 8 1 10
11 8 1 11
12 4 1 14
13 4 1 13
14 4 1 12
15 5 0 15
16 5 0 16
17 5 0 17

Т.е. тут мы на ID = 9 встретили единицу в поле B, значит для всех записей после группы A = 8, а это записи с ID >= 12 мы меняем сортировку в пределах своих групп.
+

То, что временно получаем при нахождении B = 1 на записи с ID = 9
ID A B ID_NEW
1 20 0 1
2 20 0 2
3 20 0 3
4 20 0 4
5 20 0 5
6 7 0 6
7 7 0 7
8 7 0 8
9 8 1 9
10 8 1 10
11 8 1 11
12 4 1 14
13 4 1 13
14 4 1 12
15 5 0 17
16 5 0 16
17 5 0 15


Переходя далее на новую группу (A = 4), мы вновь встречаем единицу в поле B (ID = 12). Это говорит о том, что для групп записей, начиная с ID >= 15 опять нужно сменить сортировку. Это приводит к тому, что записи 5-й группы (и всех последующих, если б они были) повторно меняют сортировку по ID и восстанавливают прежний порядок.
9 дек 14, 15:51    [16972854]     Ответить | Цитировать Сообщить модератору
 Re: Хитрое ранжирование. Нужна помощь. Небольшой пример с описанием прилагается.  [new]
Ранжировщик Боря
Guest
Забыл уточнить: значение поля B в пределах группы А не меняется.
Версия SQL сервера: 2005
9 дек 14, 16:08    [16972963]     Ответить | Цитировать Сообщить модератору
 Re: Хитрое ранжирование. Нужна помощь. Небольшой пример с описанием прилагается.  [new]
хмхмхм
Guest
Ранжировщик Боря,

Ну, судя по тому как вы объяснили должно быть так:

create table #T (ID int, A int, B int)

insert	 into #T
		 (
		  ID
		 ,A
		 ,B
		 )
		 select
			1
		   ,20
		   ,0
		 union all
		 select
			2
		   ,20
		   ,0
		 union all
		 select
			3
		   ,20
		   ,0
		 union all
		 select
			4
		   ,20
		   ,0
		 union all
		 select
			5
		   ,20
		   ,0
		 union all
		 select
			6
		   ,7
		   ,0
		 union all
		 select
			7
		   ,7
		   ,0
		 union all
		 select
			8
		   ,7
		   ,0
		 union all
		 select
			9
		   ,8
		   ,1
		 union all
		 select
			10
		   ,8
		   ,1
		 union all
		 select
			11
		   ,8
		   ,1
		 union all
		 select
			12
		   ,4
		   ,1
		 union all
		 select
			13
		   ,4
		   ,1
		 union all
		 select
			14
		   ,4
		   ,1
		 union all
		 select
			15
		   ,5
		   ,0
		 union all
		 select
			16
		   ,5
		   ,0
		 union all
		 select
			17
		   ,5
		   ,0


select
   t.*
  ,case	when t3.A is null then t.ID
		else t3.last_ID - row_number() over (partition by t.A order by t.ID) + 1
   end
from
   #T t
   left join (
			  select
			   T1.A
			  ,T1.first_ID
			  ,T1.last_ID
			  from
			   (
				select
				  T1.A
				 ,min(T1.ID) as first_ID
				 ,max(T1.ID) as last_ID
				from
				  #T T1
				group by
				  T1.A
			   ) T1
			   inner join (
						   select
							  max(ID) + 1 as ID
						   from
							  #T
						   where
							  B = 1
						   group by
							  A
						  ) T2
				  on T2.ID = T1.first_ID
			 ) t3
	  on --t3.A = t.A and
t.ID >= t3.first_ID
		 and t.ID <= t3.last_ID
order by
   T.ID




drop table #T
9 дек 14, 16:18    [16973011]     Ответить | Цитировать Сообщить модератору
 Re: Хитрое ранжирование. Нужна помощь. Небольшой пример с описанием прилагается.  [new]
хмхмхм
Guest
ПЫСЫ
На супер оптимальное решение не претендую, может быть можно упростить запрос
9 дек 14, 16:19    [16973023]     Ответить | Цитировать Сообщить модератору
 Re: Хитрое ранжирование. Нужна помощь. Небольшой пример с описанием прилагается.  [new]
Ранжировщик Боря
Guest
хмхмхм, К сожалению, в последних 3-х записях результат неверный. Т.е. итоговая выборка не удовлетворяет описанному условию.
9 дек 14, 16:23    [16973051]     Ответить | Цитировать Сообщить модератору
 Re: Хитрое ранжирование. Нужна помощь. Небольшой пример с описанием прилагается.  [new]
хмхмхм
Guest
Ранжировщик Боря
хмхмхм, К сожалению, в последних 3-х записях результат неверный. Т.е. итоговая выборка не удовлетворяет описанному условию.


Т.е. на тех данных, которые вы опубликовали в сообщении все работает, а на реальных данных нет?
9 дек 14, 16:26    [16973071]     Ответить | Цитировать Сообщить модератору
 Re: Хитрое ранжирование. Нужна помощь. Небольшой пример с описанием прилагается.  [new]
PaNik
Member

Откуда: Москва
Сообщений: 1143
Ранжировщик Боря
хмхмхм, К сожалению, в последних 3-х записях результат неверный. Т.е. итоговая выборка не удовлетворяет описанному условию.


напротив, результат верный в соответствии с условием

Ранжировщик Боря
Если, идя по записям с сортировкой по ID мы встречаем в поле B единицу, то, начиная со следующей группы записей (группы составлены по полю А), значения поля ID должны поменять сортировку на обратную (в пределах своей группы) до конца данных.


В группе А=4, В = 1 -> в следующей группе (А=5) порядок ID_NEW так же должен быть обратным (как и возвращает пример хмхмхм).

Озвученные Вами условия не соответствуют Вашей таблице "То, что должно получиться".
9 дек 14, 16:43    [16973185]     Ответить | Цитировать Сообщить модератору
 Re: Хитрое ранжирование. Нужна помощь. Небольшой пример с описанием прилагается.  [new]
Ранжировщик Боря
Guest
хмхмхм, вот это важный момент:
автор
Переходя далее на новую группу (A = 4), мы вновь встречаем единицу в поле B (ID = 12). Это говорит о том, что для групп записей, начиная с ID >= 15 опять нужно сменить сортировку. Это приводит к тому, что записи 5-й группы (и всех последующих, если б они были) повторно меняют сортировку по ID и восстанавливают прежний порядок.


Разве тут не очевидно, что последняя группа останется прежней, т.к. на неё будет наложена двойная инверсия? Т. е. на группе 8 она первый раз поменяла порядок, а на группе 4 она во второй раз поменяла порядок, который стал инверсионным от 8-й группы.
9 дек 14, 17:01    [16973309]     Ответить | Цитировать Сообщить модератору
 Re: Хитрое ранжирование. Нужна помощь. Небольшой пример с описанием прилагается.  [new]
хмхмхм
Guest
Ранжировщик Боря,

по мне так у вас описание в начале поста:
автор
Суть задачки такова, что нужно запросом сформировать выборку так, чтоб в новом столбце ID_NEW проставились значения с поля ID, но по одному условию. Если, идя по записям с сортировкой по ID мы встречаем в поле B единицу, то, начиная со следующей группы записей (группы составлены по полю А), значения поля ID должны поменять сортировку на обратную (в пределах своей группы) до конца данных.


противоречит:

автор
Переходя далее на новую группу (A = 4), мы вновь встречаем единицу в поле B (ID = 12). Это говорит о том, что для групп записей, начиная с ID >= 15 опять нужно сменить сортировку. Это приводит к тому, что записи 5-й группы (и всех последующих, если б они были) повторно меняют сортировку по ID и восстанавливают прежний порядок.



А что будет для таких тестовых данных:
ID A B
1 20 0
2 20 0
3 20 0
4 20 0
5 20 0
6 7 0
7 7 0
8 7 0
9 8 1
10 8 1
11 8 1
12 4 0
13 4 0
14 4 0
15 5 0
16 5 0
17 5 0
9 дек 14, 17:14    [16973371]     Ответить | Цитировать Сообщить модератору
 Re: Хитрое ранжирование. Нужна помощь. Небольшой пример с описанием прилагается.  [new]
хмхмхм
Guest
Возможно должно быть так:

select
   t.*
  ,case	when t3.A is null then t.ID
		else t3.last_ID - row_number() over (partition by t.A order by t.ID) + 1
   end
from
   #T t
   left join (
			  select
			   T1.A
			  ,T1.first_ID
			  ,T1.last_ID
			  ,num
			  from
			   (
				select
				  T1.A
				 ,min(T1.ID) as first_ID
				 ,max(T1.ID) as last_ID
				 ,row_number() over(order by T1.A)%2 as Num
				from
				  #T T1
				group by
				  T1.A
			   ) T1
			   inner join (
						   select
							  max(ID) + 1 as ID
						   from
							  #T
						   where
							  B = 1
						   group by
							  A
						  ) T2
				  on T2.ID = T1.first_ID
				where num = 1
			 ) t3
	  on --t3.A = t.A and
t.ID >= t3.first_ID
		 and t.ID <= t3.last_ID
order by
   T.ID
9 дек 14, 17:25    [16973435]     Ответить | Цитировать Сообщить модератору
 Re: Хитрое ранжирование. Нужна помощь. Небольшой пример с описанием прилагается.  [new]
Ранжировщик Боря
Guest
хмхмхм
Ранжировщик Боря,

А что будет для таких тестовых данных:
ID A B
1 20 0
2 20 0
3 20 0
4 20 0
5 20 0
6 7 0
7 7 0
8 7 0
9 8 1
10 8 1
11 8 1
12 4 0
13 4 0
14 4 0
15 5 0
16 5 0
17 5 0


Для этих данных группы 4 и 5 поменяют сортировку 1 раз и для ID_NEW в конце получим: 14,13,12,17,16,15.
Ваш последний запрос выдал корректные данные. Буду его ещё тестировать и разбирать.
9 дек 14, 17:45    [16973580]     Ответить | Цитировать Сообщить модератору
 Re: Хитрое ранжирование. Нужна помощь. Небольшой пример с описанием прилагается.  [new]
invm
Member

Откуда: Москва
Сообщений: 9402
declare @t table
(
  ID int,
  A  int,
  B  int
)

insert into @t (ID, A, B)
  select  1, 20, 0 union all
  select  2, 20, 0 union all
  select  3, 20, 0 union all
  select  4, 20, 0 union all
  select  5, 20, 0 union all
  select  6,  7, 0 union all
  select  7,  7, 0 union all
  select  8,  7, 0 union all
  select  9,  8, 1 union all
  select 10,  8, 1 union all
  select 11,  8, 1 union all
  select 12,  4, 1 union all
  select 13,  4, 1 union all
  select 14,  4, 1 union all
  select 15,  5, 0 union all
  select 16,  5, 0 union all
  select 17,  5, 0;

with x as
(
 select
  *,
  row_number() over (partition by A order by ID) as rnA,
  row_number() over (partition by A order by ID desc) as rnD
 from
  @t
)
select
 t1.ID, t1.A, t1.B,
 case when a.c % 2 = 0 then t1.ID else t2.ID end as ID_New
from
 x t1 outer apply
 (select count(distinct case when B = 1 then A end) from @t where A <> t1.A and ID < t1.ID) a(c) join
 x t2 on t2.A = t1.A and t2.rnD = t1.rnA
order by
 t1.ID;
9 дек 14, 18:38    [16973837]     Ответить | Цитировать Сообщить модератору
 Re: Хитрое ранжирование. Нужна помощь. Небольшой пример с описанием прилагается.  [new]
Ранжировщик Боря
Guest
хмхмхм, invm, Спасибо, товарищи! Голова у вас работает хорошо, ибо задачка по-моему была не из легких.
10 дек 14, 03:22    [16975085]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить