Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Необычная группировка с использованием коэфициента  [new]
Deniski4
Member

Откуда:
Сообщений: 19
Привет!

Ниже пример сгруппированного результата по таблице Info. Seconds (int) Sum (int).
Таким образом мы видим, что запись с значением 10 в столбце Seconds встречается 12 раз в данной таблице и т.д.

select Seconds, Count(*) frequency from [Info]
group by Seconds

Seconds Sum
---------------------------------
10 12
29 8
11 7
9 5
40 3
28 2
30 2

Вопрос: Как сгруппировать результат данного запроса используя коэфициент +/- % секунд по полю Seconds

Например: Если коэфициент +/- 10% то в поле seconds с строки с значением 9, 10 и 11 сгруппировались бы. (Пояснение: 10% от 10 = 1. 10 - 1 = 9. 10 + 1 = 11) В результате получилось бы что-то вроде этого:


Seconds Sum
---------------------------------
29 8
10 24
40 3
28 2
30 2

Пояснение: 10% от 10 = 1. 10 - 1 = 9. 10 + 1 = 11

Как добится такого результата?
8 июн 17, 13:36    [20549988]     Ответить | Цитировать Сообщить модератору
 Re: Необычная группировка с использованием коэфициента  [new]
Rankatan
Member

Откуда:
Сообщений: 250
А что должно быть если несколько значений идут подряд:
10, 11, 12, 13, 14, 15
8 июн 17, 13:40    [20550002]     Ответить | Цитировать Сообщить модератору
 Re: Необычная группировка с использованием коэфициента  [new]
iap
Member

Откуда: Москва
Сообщений: 46954
Как добиться, чтобы строка, уже попавшая в диапазон относительно какой-то строки,
не попадала в диапазон относительно следующей или предыдущей?

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

И проследить, чтобы одна и та же строка не могла попасть в две разные группы.
То есть каждой строке должен соответствовать только один номер группы.
8 июн 17, 13:48    [20550030]     Ответить | Цитировать Сообщить модератору
 Re: Необычная группировка с использованием коэфициента  [new]
iap
Member

Откуда: Москва
Сообщений: 46954
iap
Как добиться, чтобы строка, уже попавшая в диапазон относительно какой-то строки,
не попадала в диапазон относительно следующей или предыдущей?

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

И проследить, чтобы одна и та же строка не могла попасть в две разные группы.
То есть каждой строке должен соответствовать только один номер группы.
А может, вам как раз и надо, чтобы строка входила в несколько групп?
8 июн 17, 13:50    [20550036]     Ответить | Цитировать Сообщить модератору
 Re: Необычная группировка с использованием коэфициента  [new]
Deniski4
Member

Откуда:
Сообщений: 19
Rankatan
А что должно быть если несколько значений идут подряд:
10, 11, 12, 13, 14, 15


принцип такой же. просто например значение 8 и 17 в Seconds уже не попадет под условие и значения поля frequency суммировать для соответствующих полей Seconds не надо.

10, 11, 12, 13, 14, 15. Например

для самого минимального здесь -10% будет 9. 9 в диапозоне нету.
для самого максимального здесь +10% будет 16. 16 в диапозоне нету.
8 июн 17, 14:06    [20550108]     Ответить | Цитировать Сообщить модератору
 Re: Необычная группировка с использованием коэфициента  [new]
Deniski4
Member

Откуда:
Сообщений: 19
iap
Как добиться, чтобы строка, уже попавшая в диапазон относительно какой-то строки,
не попадала в диапазон относительно следующей или предыдущей?

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

И проследить, чтобы одна и та же строка не могла попасть в две разные группы.
То есть каждой строке должен соответствовать только один номер группы.


Спасибо!

Попробую осмыслить :)
8 июн 17, 14:08    [20550116]     Ответить | Цитировать Сообщить модератору
 Re: Необычная группировка с использованием коэфициента  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7403
iap,

полагаю, что автор имеет в виду 10 процентов отклонения от ID максимального значения суммы.
8 июн 17, 14:08    [20550118]     Ответить | Цитировать Сообщить модератору
 Re: Необычная группировка с использованием коэфициента  [new]
iap
Member

Откуда: Москва
Сообщений: 46954
Deniski4
Rankatan
А что должно быть если несколько значений идут подряд:
10, 11, 12, 13, 14, 15


принцип такой же. просто например значение 8 и 17 в Seconds уже не попадет под условие и значения поля frequency суммировать для соответствующих полей Seconds не надо.

10, 11, 12, 13, 14, 15. Например

для самого минимального здесь -10% будет 9. 9 в диапозоне нету.
для самого максимального здесь +10% будет 16. 16 в диапозоне нету.
Может ли теоретически какая-нибудь строка попасть в группу +10% от одной строки и -10% от другой?
Если подумать, то такое может случиться с окрестностями более, чем двух строк.
8 июн 17, 14:11    [20550129]     Ответить | Цитировать Сообщить модератору
 Re: Необычная группировка с использованием коэфициента  [new]
Deniski4
Member

Откуда:
Сообщений: 19
iap
Deniski4
пропущено...


принцип такой же. просто например значение 8 и 17 в Seconds уже не попадет под условие и значения поля frequency суммировать для соответствующих полей Seconds не надо.

10, 11, 12, 13, 14, 15. Например

для самого минимального здесь -10% будет 9. 9 в диапозоне нету.
для самого максимального здесь +10% будет 16. 16 в диапозоне нету.
Может ли теоретически какая-нибудь строка попасть в группу +10% от одной строки и -10% от другой?
Если подумать, то такое может случиться с окрестностями более, чем двух строк.


Такое по идее может быть
8 июн 17, 14:17    [20550160]     Ответить | Цитировать Сообщить модератору
 Re: Необычная группировка с использованием коэфициента  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20218
iap
Может ли теоретически какая-нибудь строка попасть в группу +10% от одной строки и -10% от другой?
У него это есть даже в предложенном примере. Группа 9-10-11 (9 и 11 в 10% от 10, 10 в 10% от 11) и группа 28-29-30 (тут вообще любое из трёх может быть базовым).

Deniski4, Почему группа 9-10-11 свёрнута к 10, а не 9 отдельно и свёртка к 11? Почему группа 28-29-30 свёрнуто к 28, а не к 29 или 30?
8 июн 17, 14:19    [20550171]     Ответить | Цитировать Сообщить модератору
 Re: Необычная группировка с использованием коэфициента  [new]
iap
Member

Откуда: Москва
Сообщений: 46954
Deniski4
iap
пропущено...
Может ли теоретически какая-нибудь строка попасть в группу +10% от одной строки и -10% от другой?
Если подумать, то такое может случиться с окрестностями более, чем двух строк.


Такое по идее может быть
Надо точно определиться, что же надо получить?
Для каждой строки брать все остальные строки из некоторой окрестности и вычислять для них некую агрегатную функцию?
Полистайте доку про оконные функции (для версий после SQL 2008, разумеется). Может, что-то подойдёт.
Хотя, помнится, я столкнулся со столькими ограничениями их использования...
8 июн 17, 14:23    [20550185]     Ответить | Цитировать Сообщить модератору
 Re: Необычная группировка с использованием коэфициента  [new]
Rankatan
Member

Откуда:
Сообщений: 250
with T as
(
	select * from (VALUES(1,2),(2,2),(3,2),(4,2),(6,2),(7,2),(10,2)) T(Seconds,frequency)
		--select Seconds, Count(*) frequency 
		--from [Info]
		--group by Seconds

)

select Seconds,frequency,MAX(GR) OVER(order by Seconds ) GR from
(
	select 
		Seconds,frequency,
		case 
			when Seconds-1 <> ISNULL(LAG(Seconds) OVER(order by Seconds),-1)
			then Seconds 
			else 0
		end Gr
	from T
) z


В результате GR будет общий у групп. То что нужно?
8 июн 17, 14:26    [20550193]     Ответить | Цитировать Сообщить модератору
 Re: Необычная группировка с использованием коэфициента  [new]
Deniski4
Member

Откуда:
Сообщений: 19
Akina
iap
Может ли теоретически какая-нибудь строка попасть в группу +10% от одной строки и -10% от другой?
У него это есть даже в предложенном примере. Группа 9-10-11 (9 и 11 в 10% от 10, 10 в 10% от 11) и группа 28-29-30 (тут вообще любое из трёх может быть базовым).

Deniski4, Почему группа 9-10-11 свёрнута к 10, а не 9 отдельно и свёртка к 11? Почему группа 28-29-30 свёрнуто к 28, а не к 29 или 30?


Неловко вышло...

Все верно. группа 28-29-30 тоже должна быть свернута.

По поводу
Почему группа 9-10-11 свёрнута к 10 - по хорошему это должно быть среднее число от просуммированных, но не факт. Это не оговаривалось еще. Можно обыграть по разному пока - что.
8 июн 17, 14:27    [20550200]     Ответить | Цитировать Сообщить модератору
 Re: Необычная группировка с использованием коэфициента  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7403
А вообще непонятно - какая решается задача. Мы опять же обсуждаем выбранный автором способ решения. И этот способ имеет внутренние противоречия.
8 июн 17, 14:29    [20550213]     Ответить | Цитировать Сообщить модератору
 Re: Необычная группировка с использованием коэфициента  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20218
Deniski4
Почему группа 9-10-11 свёрнута к 10 - по хорошему это должно быть среднее число от просуммированных, но не факт. Это не оговаривалось еще. Можно обыграть по разному пока - что.

10-11-12-13 - к чему сворачивать? 10-11-12 к 11 и 13 отдельно, или 10 отдельно и 11-12-13 к 12?

Мало хотеть, надо ещё и понять, что собственно ты хочешь...
8 июн 17, 14:30    [20550219]     Ответить | Цитировать Сообщить модератору
 Re: Необычная группировка с использованием коэфициента  [new]
Deniski4
Member

Откуда:
Сообщений: 19
Akina
Deniski4
Почему группа 9-10-11 свёрнута к 10 - по хорошему это должно быть среднее число от просуммированных, но не факт. Это не оговаривалось еще. Можно обыграть по разному пока - что.

10-11-12-13 - к чему сворачивать? 10-11-12 к 11 и 13 отдельно, или 10 отдельно и 11-12-13 к 12?

Мало хотеть, надо ещё и понять, что собственно ты хочешь...


Я очень извиняюсь за путаницу. Просто в первую очередь для меня стоял вопрос с коэфициентом. уперся вообщем.

Смысл такой:

Это результвт селекта.

select Seconds, Count(*) frequency from [Info]
group by Seconds

отсортированный на самом деле по desc frequency

10 12
11 7
9 5
40 3
28 2
45 1

Формула подсчета Avarage Seconds для группы

((10*12) + (11*7) + (9*5)) / 24 = 10.08 (округлил до 2 знаков после запятой)

Что говорит, что:

число 10 попадается в селекте 12 раз
число 11 попадается в селекте 7 раз
число 9 попадается в селекте 5 раз

24 - сумма от (12+7+5)

Результат:

10.08 24
40 3
28 2
45 1
8 июн 17, 14:49    [20550302]     Ответить | Цитировать Сообщить модератору
 Re: Необычная группировка с использованием коэфициента  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20218
Вы не ответили на вопрос.
Почему результат именно такой, а не
val count
95
1119
403
282
451

?
8 июн 17, 14:54    [20550328]     Ответить | Цитировать Сообщить модератору
 Re: Необычная группировка с использованием коэфициента  [new]
Rankatan
Member

Откуда:
Сообщений: 250
with T as
(
	select * from (VALUES(10,12),(29,8),(11,7),(9,5),(40,3),(28,2),(30,2)) T(Seconds,frequency)
)

select cast(SUM(Seconds*frequency)/SUM(frequency) as decimal(10,2)),SUM(frequency)   from
(
	select Seconds*1.0 Seconds,
		   frequency*1.0 frequency,
		   MAX(GR) OVER(order by Seconds ) GR 
	from
	(
		select 
			Seconds,frequency,
			case 
				when LAG(Seconds,1,-100) OVER(order by Seconds)*1.0 < Seconds*0.9
				then Seconds 
				else 0
			end Gr
		from T
	) z
) Z2
group by GR
8 июн 17, 15:00    [20550353]     Ответить | Цитировать Сообщить модератору
 Re: Необычная группировка с использованием коэфициента  [new]
Deniski4
Member

Откуда:
Сообщений: 19
Akina
Вы не ответили на вопрос.
Почему результат именно такой, а не
val count
95
1119
403
282
451

?


Потому - что изночально задача состоит в том, чтобы выделить из выборки, только те данные которые не имеют больших рассхождений.

Seconds: 9, 10, 11 - похоже на правду.
Seconds: 45, 90 и т д. скорее всего погрешность, были какие о задержки.

При дальнейшей обработки данных мн нужно будет учитывать только

29 8 --- отметается
10 24 --- это
40 3 --- отметается
28 2 --- отметается
30 2 --- отметается

Поэтому мне нужно найти самые часто встречаемые записи их и сгруппировать.
В данном примере я беру коэфициент 10%, но могу взять и 80% тогда диапозон совсем измениться.

Это задача на прямую связана с Mode (статистика)

Надеюсь, стало понятнее
8 июн 17, 15:03    [20550369]     Ответить | Цитировать Сообщить модератору
 Re: Необычная группировка с использованием коэфициента  [new]
Deniski4
Member

Откуда:
Сообщений: 19
Rankatan
with T as
(
	select * from (VALUES(10,12),(29,8),(11,7),(9,5),(40,3),(28,2),(30,2)) T(Seconds,frequency)
)

select cast(SUM(Seconds*frequency)/SUM(frequency) as decimal(10,2)),SUM(frequency)   from
(
	select Seconds*1.0 Seconds,
		   frequency*1.0 frequency,
		   MAX(GR) OVER(order by Seconds ) GR 
	from
	(
		select 
			Seconds,frequency,
			case 
				when LAG(Seconds,1,-100) OVER(order by Seconds)*1.0 < Seconds*0.9
				then Seconds 
				else 0
			end Gr
		from T
	) z
) Z2
group by GR


По-моему то что надо!

Спасибо огромное.
буду разбираться в самом запросе.
8 июн 17, 15:06    [20550384]     Ответить | Цитировать Сообщить модератору
 Re: Необычная группировка с использованием коэфициента  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7403
Deniski4,

я думаю, что Вам надо рассчитывать отключение по медианному значению. 10-ку Вы выбрали из-за "красоты", фактическое же значение - это 11.

declare @t table (seconds numeric(5,2), amount numeric(5,2))

insert @t values
	(10.0,12.0),(29.0,8.0),(9.0,7.0),(11.0,5.0),(40.0,3.0),(28.0,2.0),(30.0,2.0);

select distinct t1.seconds,
	PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY t1.seconds) OVER() trueSeconds
from master.dbo.spt_values 
cross join @t t1
where type = 'P'
	and t1.amount > number
order by seconds;


11 как раз разделяет массив пополам.
8 июн 17, 17:23    [20550977]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить