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

Откуда:
Сообщений: 7
Например, есть таблица
Возраст Кол-во
1810
1815
205
2210
3114
346


1.Как в SQL сделать группировку по возрасту, например, с шагом 5?

Возраст Кол-во
15-2025
20-2515
30-3514
35-406

2. Как сделать группировку по возрасту, например, различными шагами? Т.е. до 18 лет, с 18 до 25, с 25 до 45 и т.д.
17 окт 19, 00:02    [21995940]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
Glebanski
Member

Откуда: Msk ->NL
Сообщений: 306
Marisolka,

Сделайте новую колонку в запросе с помощью Case и назовите её AgeGroup.
PS : должно по идее быть в любом курсе по SQL...
17 окт 19, 00:31    [21995954]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
982183
Member

Откуда: VL
Сообщений: 3350
Marisolka
Т.е. до 18 лет, с 18 до 25, с 25 до 45 и т.д.


1. Round(Кол-во/5,0)
2. "и т.д." в данном конкретном случае не работает. Ряд логически не продолжается. А так - Case.
17 окт 19, 04:18    [21995980]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Select cast(b.begin as varchar(3)) + isnull(' - '+cast(b.end as varchar(3)),' и старше') as [Возраст],
       count(*) as [Кол-во]
  From [Таблица] a
  Left join (values (0,18),(18,25),(25,45),(45,null)) b(begin,end) 
    on a.[Возраст] >= b.begin and (a.[Возраст] < b.End or b.End is null)
 Group by b.begin,b.end
 Order by b.begin,b.end
17 окт 19, 04:43    [21995981]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
982183
Member

Откуда: VL
Сообщений: 3350
То-же метод, но Case проще.
17 окт 19, 05:26    [21995984]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
982183
Member

Откуда: VL
Сообщений: 3350
+ вместо null я ставлю 999
17 окт 19, 05:28    [21995985]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20208
Marisolka
Как сделать группировку по возрасту, например, различными шагами? Т.е. до 18 лет, с 18 до 25, с 25 до 45 и т.д.

Самое разумное - завести таблицу с диапазонами возрастов (идНабора-возрастОт-возрастДо), и использовать ее как источник данных для группировки. В ней можно хранить неограниченное количество наборов - и равномерных. и кастомных...
17 окт 19, 08:20    [21996025]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
msLex
Member

Откуда:
Сообщений: 7733
Marisolka,
Если возраст целочисленный, то начало периода будет Возраст / 5 * 5
17 окт 19, 09:13    [21996056]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
prog882
Member

Откуда:
Сообщений: 4
Marisolka,
declare @step int = 5;

declare @a table (age int, qty int)
insert into @a (age, qty) values 
	(18,10)
,	(18,15)
,	(20,5)
,	(22,10)
,	(31,14)
,	(35,6);

;with 
	b as 
( 
	select min(age) - min(age) % @step as g_age, max(age) + max(age) % @step as m_age from @a 
	union all
	select g_age + @step, m_age from b where g_age + @step <= m_age
)
,	c as 
(
	select g_age as min_age, isnull(lead(g_age) over (order by g_age), g_age + @step) as max_age from b
)
select concat(c.min_age, '-',c.max_age - 1) as age, sum(a.qty) as qty
from c
left join @a as a on a.age between c.min_age and c.max_age - 1
group by c.min_age, c.max_age;
17 окт 19, 10:44    [21996180]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
prog882
Member

Откуда:
Сообщений: 4
Покороче,
declare @step int = 5;

declare @a table (age int, qty int)
insert into @a (age, qty) values 
	(18,10)
,	(18,15)
,	(20,5)
,	(22,10)
,	(31,14)
,	(35,6);

;with 
	b as 
( 
	select min(age) - min(age) % @step as beg_age, min(age) - min(age) % @step + @step as end_age
	,	max(age) + max(age) % @step as max_age from @a 
	union all
	select beg_age + @step,  beg_age + @step  + @step,  max_age from b where beg_age + @step <= max_age
)
select concat(b.beg_age, '-',b.end_age - 1) as age, sum(a.qty) as qty
from b
left join @a as a on a.age between b.beg_age and b.end_age - 1
group by b.beg_age, b.end_age;
17 окт 19, 10:54    [21996207]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7401
Решение с таблицей групп возрастов наилучшее, т.к. является data-driven, а не code-driven. Оптимальным для реляционных баз является решение, при котором результат запроса зависит от данных, а не от кода.
17 окт 19, 13:50    [21996517]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
Wlr-l
Member

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

посмотрите на этом форуме темы "гистограмма", там есть варианты решения вашей задачи.
17 окт 19, 22:56    [21996978]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
982183
Member

Откуда: VL
Сообщений: 3350
Владислав Колосов
Решение с таблицей групп возрастов наилучшее, т.к. является data-driven, а не code-driven. Оптимальным для реляционных баз является решение, при котором результат запроса зависит от данных, а не от кода.

Оно конечно в теории верно, но на практике зачастую подобную задачу приходится решать в некой системе отчетности,
где заведение и хранение таблиц не предусмотрено, а к рабочей базе доступ только на чтение.
18 окт 19, 13:05    [21997357]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
Владислав Колосов
Member

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

в случае изменения диапазона возрастов, например, придется переписывать и публиковать код. Такой подход популярен, но это не pro.
18 окт 19, 13:17    [21997374]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
982183
Member

Откуда: VL
Сообщений: 3350
А в альтернативе надо создавать интерфейс ввода таблиц, их контроля права доступа к ним.
Зачастую оно того не стоит.
А современные пользователи откорректировать правильно составленный код вполне в состоянии.
И многие "конструкторы отчетов" это замечательно позволяют делать.
Хотя конечно способ с таблицей более "прогрессивен" и правилен.
18 окт 19, 13:46    [21997409]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30807
982183
А в альтернативе надо создавать интерфейс ввода таблиц, их контроля права доступа к ним.
Не обязательно, если коррекция справочника предполагается раз в несколько месяцев/лет.
Но разработчикам будет проще править такой справочник, чем искать место, где, например, "Если возраст целочисленный, то начало периода будет Возраст / 5 * 5", а потом, если возникнет потребность изменить диапазон, впендюривать туда сложную формулу в CASE, по инерции.
Притом, что очень плохо, таких мест будет несколько (разные отчёты, интерфейсы), и, конечно, никто не булдет понимать ,что из этого используется, а что нет, поэтому придётся править и те 90% запросов, которые не используются.
В общем, что бы не делать справочник, нужно очень веское обоснование.
18 окт 19, 14:41    [21997472]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7401
Обычно такого рода таблицы находятся в зоне ответственности группы эксплуатации. По если ПО пишется для мелкоофисной работы, то создается интерфейс. Существуют еще и аналитические системы, но там другой случай, т.к. активно используется парадигма функционального программирования.
18 окт 19, 14:56    [21997492]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
982183
Member

Откуда: VL
Сообщений: 3350
"Группа эксплуатации" - это одно, а рисовальщики отчетов - совсем другое.
И не всегда у них есть соответствующие права и полномочия.
18 окт 19, 16:22    [21997575]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
msLex
Member

Откуда:
Сообщений: 7733
alexeyvg
982183
А в альтернативе надо создавать интерфейс ввода таблиц, их контроля права доступа к ним.
Не обязательно, если коррекция справочника предполагается раз в несколько месяцев/лет.
Но разработчикам будет проще править такой справочник, чем искать место, где, например, "Если возраст целочисленный, то начало периода будет Возраст / * 5", а потом, если возникнет потребность изменить диапазон, впендюривать туда сложную формулу в CASE, по инерции.
Притом, что очень плохо, таких мест будет несколько (разные отчёты, интерфейсы), и, конечно, никто не булдет понимать ,что из этого используется, а что нет, поэтому придётся править и те 90% запросов, которые не используются.
В общем, что бы не делать справочник, нужно очень веское обоснование.


Вы же понимаете, что вот эту "5", так же можно вынести в соответствующий справочник при необходимости и рулить им?

Сообщение было отредактировано: 18 окт 19, 17:16
18 окт 19, 17:16    [21997613]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7401
Кодд со товарищи писал, что все данные должны находиться в таблицах. И вся теория, на основе которой написана математика сервера БД, строится вокруг этого. Поэтому любые отступления снижают производительность запросов.

Другое дело, что в народе популярно использовать "против лома нет приёма", т.е. наращивать аппаратную мощь. А поэтому можно халтурить или не знать основные принципы, ишак же везёт.
18 окт 19, 17:52    [21997654]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
msLex
Member

Откуда:
Сообщений: 7733
Владислав Колосов
Поэтому любые отступления снижают производительность запросов.


И вы можете это доказать сравнительными тестами варианта "Возраст / 5 * 5" и варианта "джойн на таблицу диапазонов"?
18 окт 19, 18:00    [21997663]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4385
Marisolka
2. Как сделать группировку по возрасту, например, различными шагами? Т.е. до 18 лет, с 18 до 25, с 25 до 45 и т.д.


Используйте OLAP кубы
18 окт 19, 18:38    [21997697]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30807
msLex
alexeyvg
В общем, что бы не делать справочник, нужно очень веское обоснование.

Вы же понимаете, что вот эту "5", так же можно вынести в соответствующий справочник при необходимости и рулить им?
Формулу в справочник, и потом динамически строить запросы?
Понимаю, но мне этот вариант кажется ещё ужаснее, я его не рассматривал всерьёз.
18 окт 19, 19:04    [21997718]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
msLex
Member

Откуда:
Сообщений: 7733
alexeyvg
msLex
пропущено...

Вы же понимаете, что вот эту "5", так же можно вынести в соответствующий справочник при необходимости и рулить им?
Формулу в справочник, и потом динамически строить запросы?
Понимаю, но мне этот вариант кажется ещё ужаснее, я его не рассматривал всерьёз.


Зачем формулу, просто "шаг" группировки
18 окт 19, 19:15    [21997727]     Ответить | Цитировать Сообщить модератору
 Re: Как в SQL сгруппировать значения с определенным шагом?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30807
msLex
alexeyvg
пропущено...
Формулу в справочник, и потом динамически строить запросы?
Понимаю, но мне этот вариант кажется ещё ужаснее, я его не рассматривал всерьёз.


Зачем формулу, просто "шаг" группировки
Шаг не имеет смысла, по тем причинам, которые я описал в посте, на который вы ответили.

Я же писал не про "куда засунуть цифру 5", а про то, что плохо раскидывать по приложению множество одинаковых формул, для одного и того же расчёта, их будет трудно читать, потом программисты будут их неоптимально менять (иногда слишком усложняя), да ещё и во многих местах.
Намного проще сделать справочник групп возрастов.
Или хотя бы справочник в коде, как 21996207 - это придётся менять во многих местах, но хотя бы логика будет прозрачная, и в сравнении исходников тоже будет всё прозрачно - что было, и что стало.
18 окт 19, 19:59    [21997748]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить