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

Откуда:
Сообщений: 132
Каюсь, не силенн в аналитических функциях.
Вот попросили посчитать, а я что-то торможу...

Есть простая (но большая) табличка со численными значениями, скажем Val_Int.

Val_Int
1
2
2
3
7
5
......

Нужно получить что-то вроде таблички из 10 строчек:

Percentile Max_Val_Int Count
10% 1 17
20% 3 25
30% 9 19
....................
100% 259 2


Я пытался поиграться с функциями PERCENTILE_CONT но что-то ничего вразумительного не получилось...
Намекните на путь истинный...
8 май 16, 11:52    [19147581]     Ответить | Цитировать Сообщить модератору
 Re: Распределение значений колонки по процентилям  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Daba,

автор
100% 259 2

А 2 это что? а так SUM COUNT GROUP BY всё, что нужно
8 май 16, 12:16    [19147639]     Ответить | Цитировать Сообщить модератору
 Re: Распределение значений колонки по процентилям  [new]
Daba
Member

Откуда:
Сообщений: 132
Первая колонка - процентиль
Вторая - максимальное значение внутри процентиля
Третяя - кол-во строчек попавшие в процентиль.
8 май 16, 13:51    [19147811]     Ответить | Цитировать Сообщить модератору
 Re: Распределение значений колонки по процентилям  [new]
LoopN
Guest
PERCENTILE_CONT считает медиану

1й ранжирующей функцией эту задачу не решить.

в таблице только один столбец Val_Int? А по каком столбцу сортировка идет?

Пришлите создание таблицы + заполните ее 30 значениями и точный результат который должен быть. Тогда будет вероятность, что вам помогут.
8 май 16, 14:17    [19147845]     Ответить | Цитировать Сообщить модератору
 Re: Распределение значений колонки по процентилям  [new]
iap
Member

Откуда: Москва
Сообщений: 47052
LoopN
PERCENTILE_CONT считает медиану
Да что вы говорите?! Это если аргумент 0.5

8 май 16, 16:23    [19148032]     Ответить | Цитировать Сообщить модератору
 Re: Распределение значений колонки по процентилям  [new]
LoopN
Guest
iap
LoopN
PERCENTILE_CONT считает медиану
Да что вы говорите?! Это если аргумент 0.5

Ну да, если аргумент 0.5

Daba, то что нужно?
WITH tbl as
(
	SELECT 1 Val_Int UNION all
	SELECT 2 UNION all
	SELECT 2 UNION all
	SELECT 3 UNION all
	SELECT 7 UNION all
	SELECT 5
)
SELECT Pers,ISNULL(MAX(Val_Int),0) Mx,COUNT(Val_Int) Cnt FROM
(
	SELECT LAG(Val,1) OVER(ORDER BY Pers) PrevVal,Val,Pers FROM
	(
		SELECT Val,CAST(Pers AS INT) Pers
		FROM 
		(
			SELECT TOP 1 
				   PERCENTILE_CONT(0.1) WITHIN GROUP (ORDER BY Val_Int) OVER (PARTITION BY 1/0) [10], 
				   PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY Val_Int) OVER (PARTITION BY 1/0) [20], 
				   PERCENTILE_CONT(0.3) WITHIN GROUP (ORDER BY Val_Int) OVER (PARTITION BY 1/0) [30], 
				   PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY Val_Int) OVER (PARTITION BY 1/0) [40], 
				   PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Val_Int) OVER (PARTITION BY 1/0) [50], 
				   PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY Val_Int) OVER (PARTITION BY 1/0) [60], 
				   PERCENTILE_CONT(0.7) WITHIN GROUP (ORDER BY Val_Int) OVER (PARTITION BY 1/0) [70], 
				   PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY Val_Int) OVER (PARTITION BY 1/0) [80], 
				   PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY Val_Int) OVER (PARTITION BY 1/0) [90], 
				   PERCENTILE_CONT(1) WITHIN GROUP (ORDER BY Val_Int) OVER (PARTITION BY 1/0) [100]
			FROM  tbl
		) upvt
		UNPIVOT(Val FOR Pers IN ([10],[20],[30],[40],[50],[60],[70],[80],[90],[100])) upvt
	) Z
) Z
LEFT JOIN tbl
	ON (tbl.Val_Int>PrevVal OR PrevVal IS NULL)
	AND  tbl.Val_Int <= Val
GROUP BY Pers
8 май 16, 17:06    [19148104]     Ответить | Цитировать Сообщить модератору
 Re: Распределение значений колонки по процентилям  [new]
Daba
Member

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

пардон, сегодня не было шанса.
Завтра буду рыть. Отпишусь.

Полюбому, спасибо.
8 май 16, 19:14    [19148270]     Ответить | Цитировать Сообщить модератору
 Re: Распределение значений колонки по процентилям  [new]
Daba
Member

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

Да, практически то что нужно.
Большое спасибо!

Интересно а что это за трюк с "PARTITION BY 1/0"?
Я понимаю что это обход обязательного синтаксиса, но почему 1/0?
9 май 16, 13:23    [19149557]     Ответить | Цитировать Сообщить модератору
 Re: Распределение значений колонки по процентилям  [new]
LoopN
Guest
Daba
LoopN,

Да, практически то что нужно.
Большое спасибо!

Интересно а что это за трюк с "PARTITION BY 1/0"?
Я понимаю что это обход обязательного синтаксиса, но почему 1/0?

Заглушка, когда нужно что-то указать в оконных функциях, а указывать ничего не хочется. Некоторые пишут еще (select 1).

Но в этом случае, наверно, лучше указать PARTITION BY null.

Вообще PERCENTILE_CONT какая-то не до конца доделанная оконная функция. Например у PERCENTILE_CONT(0.4) параметр обязательно должен быть константой. Обязательно указывать OVER(PARTITION BY...). Не понимаю причин для этих ограничений.
9 май 16, 13:53    [19149621]     Ответить | Цитировать Сообщить модератору
 Re: Распределение значений колонки по процентилям  [new]
iap
Member

Откуда: Москва
Сообщений: 47052
LoopN
Но в этом случае, наверно, лучше указать PARTITION BY null.
А так нельзя?
WITH tbl as
(
	SELECT 1 Val_Int UNION all
	SELECT 2 UNION all
	SELECT 2 UNION all
	SELECT 3 UNION all
	SELECT 7 UNION all
	SELECT 5
)
SELECT Pers,ISNULL(MAX(Val_Int),0) Mx,COUNT(Val_Int) Cnt FROM
(
	SELECT LAG(Val,1) OVER(ORDER BY Pers) PrevVal,Val,Pers FROM
	(
		SELECT Val,CAST(Pers AS INT) Pers
		FROM 
		(
			SELECT TOP 1 
				   PERCENTILE_CONT(0.1) WITHIN GROUP (ORDER BY Val_Int) OVER () [10], 
				   PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY Val_Int) OVER () [20], 
				   PERCENTILE_CONT(0.3) WITHIN GROUP (ORDER BY Val_Int) OVER () [30], 
				   PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY Val_Int) OVER () [40], 
				   PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Val_Int) OVER () [50], 
				   PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY Val_Int) OVER () [60], 
				   PERCENTILE_CONT(0.7) WITHIN GROUP (ORDER BY Val_Int) OVER () [70], 
				   PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY Val_Int) OVER () [80], 
				   PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY Val_Int) OVER () [90], 
				   PERCENTILE_CONT(1) WITHIN GROUP (ORDER BY Val_Int) OVER () [100]
			FROM  tbl
		) upvt
		UNPIVOT(Val FOR Pers IN ([10],[20],[30],[40],[50],[60],[70],[80],[90],[100])) upvt
	) Z
) Z
LEFT JOIN tbl
	ON (tbl.Val_Int>PrevVal OR PrevVal IS NULL)
	AND  tbl.Val_Int <= Val
GROUP BY Pers
9 май 16, 14:02    [19149639]     Ответить | Цитировать Сообщить модератору
 Re: Распределение значений колонки по процентилям  [new]
LoopN
Guest
iap
LoopN
Но в этом случае, наверно, лучше указать PARTITION BY null.
А так нельзя?
OVER ()

Можно, спасибо =)
9 май 16, 14:06    [19149648]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить