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

Откуда: Россия. Санкт-Петербург
Сообщений: 3200
Добрый день.

Есть данные в следующем виде
+
declare @data table (s_id int, s_name nvarchar(100), contactsCount int, statusChangeOrder int, a_id int)
insert into @data values
(13,'Х. не обработан',1,1,195943),
(1041,'Х.Перезвон БЕЗ ИМЕНИ',1,2,195943),
(1013,'Х.Почта ИМЯ ЕСТЬ',13,3,195943),
(15,'Х. отказ',1,4,195943),
(13,'Х. не обработан',1,1,195944),
(1013,'Х.Почта ИМЯ ЕСТЬ',1,2,195944),
(2079,'Курьерская доставка',1,3,195944),
(1035,'Х. отложен',1,4,195944),
(14,'Х.Почта БЕЗ ИМЕНИ',1,5,195944),
(13,'Х. не обработан',1,1,195945),
(1041,'Х.Перезвон БЕЗ ИМЕНИ',4,2,195945),
(13,'Х. не обработан',1,1,195946),
(1013,'Х.Почта ИМЯ ЕСТЬ',8,2,195946),
(2090,'Запрос реквизитов',22,3,195946),
(15,'Х. отказ',1,4,195946),
(13,'Х. не обработан',1,1,195947),
(1041,'Х.Перезвон БЕЗ ИМЕНИ',4,2,195947),
(13,'Х. не обработан',1,1,195948),
(1013,'Х.Почта ИМЯ ЕСТЬ',1,2,195948),
(2079,'Курьерская доставка',1,3,195948),
(1035,'Х. отложен',2,4,195948),
(13,'Х. не обработан',1,1,195949),
(1041,'Х.Перезвон БЕЗ ИМЕНИ',4,2,195949),
(13,'Х. не обработан',1,1,195950),
(1041,'Х.Перезвон БЕЗ ИМЕНИ',2,2,195950),
(1013,'Х.Почта ИМЯ ЕСТЬ',16,3,195950),
(13,'Х. не обработан',1,1,195951),
(1013,'Х.Почта ИМЯ ЕСТЬ',10,2,195951),
(15,'Х. отказ',1,3,195951),
(13,'Х. не обработан',1,1,195952),
(1041,'Х.Перезвон БЕЗ ИМЕНИ',2,2,195952),
(1013,'Х.Почта ИМЯ ЕСТЬ',3,3,195952),
(2090,'Запрос реквизитов',1,4,195952),
(3092,'Реквизиты получены',1,5,195952),
(2082,'Счёт выставлен',6,6,195952),
(2083,'Отказ после счёта',1,7,195952)

select * from @data

Где s_id - Код статуса,
s_name - наименование статуса, проставленного менеджером по результатам разговора с клиентом,
contactsCount - количество контактов в данном статусе без его изменения,
statusChangeOrder - порядок простановки менеджером данного статуса по данному клиенту,
a_id - код контрагента

Необходимо сгруппировать данные так, чтобы получить воронки продаж. Иными словами, цепочки изменения статусов с количеством контрагентов по данной цепочке статусов, где цепочка изменения статусов определяется количеством статусов, их наименованием и порядком присвоения. Помогите, пожалуйста, с запросом.
25 мар 18, 14:19    [21284108]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом на группировку данных  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5112
приведите пример
- исходные данные (минимально достаточные для получения пары этих ваших цепочек)
- результат какой хотите получить с объяснением как именно он получается
25 мар 18, 14:24    [21284118]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом на группировку данных  [new]
Vsevolod V
Member

Откуда: Россия. Санкт-Петербург
Сообщений: 3200
Да, прошу прощения за не точность.

Пример данных:
+
declare @data table (s_id int, s_name nvarchar(100), contactsCount int, statusChangeOrder int, a_id int)
insert into @data values
(13,'Х. не обработан',1,1,195943),
(1041,'Х.Перезвон БЕЗ ИМЕНИ',1,2,195943),
(1013,'Х.Почта ИМЯ ЕСТЬ',13,3,195943),
(15,'Х. отказ',1,4,195943),

(13,'Х. не обработан',1,1,195944),
(1041,'Х.Перезвон БЕЗ ИМЕНИ',1,2,195944),
(1013,'Х.Почта ИМЯ ЕСТЬ',13,3,195944),
(15,'Х. отказ',1,4,195944),

(13,'Х. не обработан',1,1,195945),
(1041,'Х.Перезвон БЕЗ ИМЕНИ',4,2,195945),

(13,'Х. не обработан',1,1,195946),
(1041,'Х.Перезвон БЕЗ ИМЕНИ',4,2,195946),

(13,'Х. не обработан',1,1,195947),
(1013,'Х.Почта ИМЯ ЕСТЬ',1,2,195947),
(2079,'Курьерская доставка',1,3,195947),
(1035,'Х. отложен',1,4,195947),
(14,'Х.Почта БЕЗ ИМЕНИ',1,5,195947)

select * from @data


Необходимый результат (с сортировкой по глубине цепочки):
Кол-во клиентов 12345
2 Х. не обработанХ.Перезвон БЕЗ ИМЕНИ
2Х. не обработанХ.Перезвон БЕЗ ИМЕНИХ.Почта ИМЯ ЕСТЬХ. отказ
1Х. не обработанХ.Почта ИМЯ ЕСТЬКурьерская доставкаХ. отложенХ.Почта БЕЗ ИМЕНИ
25 мар 18, 14:35    [21284125]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом на группировку данных  [new]
andrey odegov
Member

Откуда:
Сообщений: 463
PIVOT в помощь
+

declare @data table (s_id int, s_name nvarchar(100), contactsCount int, statusChangeOrder int, a_id int)
insert into @data values
(13,N'Х. не обработан',1,1,195943),
(1041,N'Х.Перезвон БЕЗ ИМЕНИ',1,2,195943),
(1013,N'Х.Почта ИМЯ ЕСТЬ',13,3,195943),
(15,N'Х. отказ',1,4,195943),

(13,N'Х. не обработан',1,1,195944),
(1041,N'Х.Перезвон БЕЗ ИМЕНИ',1,2,195944),
(1013,N'Х.Почта ИМЯ ЕСТЬ',13,3,195944),
(15,N'Х. отказ',1,4,195944),

(13,N'Х. не обработан',1,1,195945),
(1041,N'Х.Перезвон БЕЗ ИМЕНИ',4,2,195945),

(13,N'Х. не обработан',1,1,195946),
(1041,N'Х.Перезвон БЕЗ ИМЕНИ',4,2,195946),

(13,N'Х. не обработан',1,1,195947),
(1013,N'Х.Почта ИМЯ ЕСТЬ',1,2,195947),
(2079,N'Курьерская доставка',1,3,195947),
(1035,N'Х. отложен',1,4,195947),
(14,N'Х.Почта БЕЗ ИМЕНИ',1,5,195947)


select count(*),[1],[2],[3],[4],[5]
from (select s_name,statuschangeorder,a_id from @data) s
pivot(max(s_name) for statuschangeorder in([1],[2],[3],[4],[5])) p
group by [1],[2],[3],[4],[5];
25 мар 18, 22:27    [21284708]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом на группировку данных  [new]
Vsevolod V
Member

Откуда: Россия. Санкт-Петербург
Сообщений: 3200
andrey odegov,

Спасибо! Уточните еще, пожалуйста, по сортировке. На тестовом примере сортировка верная, но она явно не определена в запросе. А на реальных данных сортировка слетает, например, как указано ниже. Четвертая строка должна идти 2-ой.

Кол-во клиентов 1 2 3 4 5
2 Х. не обработан Х.Перезвон БЕЗ ИМЕНИ
2 Х. не обработан Х.Перезвон БЕЗ ИМЕНИ Х.Почта ИМЯ ЕСТЬ Х. отказ
1 Х. не обработан Х.Почта ИМЯ ЕСТЬ Курьерская доставка Х. отложен Х.Почта БЕЗ ИМЕНИ
1 Х. не обработан Х.Перезвон БЕЗ ИМЕНИ Х.Почта ИМЯ ЕСТЬ
26 мар 18, 10:52    [21285483]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом на группировку данных  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Vsevolod V,

Order by 
case When [1] is null Then 0 else 1 End,
case When [2] is null Then 0 else 1 End,
case When [3] is null Then 0 else 1 End,
case When [4] is null Then 0 else 1 End,
case When [5] is null Then 0 else 1 End,
[1],[2],[3],[4],[5]
26 мар 18, 11:13    [21285597]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом на группировку данных  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Vsevolod V,

Хотя пустые и так выйдут вверху так что
ORder by [1],[2],[3],[4],[5]
26 мар 18, 11:17    [21285610]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом на группировку данных  [new]
Vsevolod V
Member

Откуда: Россия. Санкт-Петербург
Сообщений: 3200
Kopelly,

Это фантастика! Спасибо за помощь!)
26 мар 18, 11:29    [21285666]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом на группировку данных  [new]
Vsevolod V
Member

Откуда: Россия. Санкт-Петербург
Сообщений: 3200
Добрый день.

Помогите, пожалуйста, скорректировать запрос.
Мне необходимо добавить фильтр по статусу, чтобы при заданном фильтре отображались цепочки изменения статусов, начиная с заданного статуса. Простите за путанницу)

Пример данных:

+
declare @data table (s_id int, s_name nvarchar(100), contactsCount int, statusChangeOrder int, a_id int)
insert into @data values
(13,'Х. не обработан',1,1,195943),
(1041,'Х.Перезвон БЕЗ ИМЕНИ',1,2,195943),
(1013,'Х.Почта ИМЯ ЕСТЬ',13,3,195943),
(15,'Х. отказ',1,4,195943),

(13,'Х. не обработан',1,1,195944),
(1041,'Х.Перезвон БЕЗ ИМЕНИ',1,2,195944),
(1013,'Х.Почта ИМЯ ЕСТЬ',13,3,195944),
(15,'Х. отказ',1,4,195944),

(13,'Х. не обработан',1,1,195945),
(1041,'Х.Перезвон БЕЗ ИМЕНИ',4,2,195945),

(13,'Х. не обработан',1,1,195946),
(1041,'Х.Перезвон БЕЗ ИМЕНИ',4,2,195946),

(13,'Х. не обработан',1,1,195947),
(1013,'Х.Почта ИМЯ ЕСТЬ',1,2,195947),
(2079,'Курьерская доставка',1,3,195947),
(1035,'Х. отложен',1,4,195947),
(14,'Х.Почта БЕЗ ИМЕНИ',1,5,195947),

(13,'Х. не обработан',1,1,195948),
(1013,'Х.Перезвон БЕЗ ИМЕНИ',1,2,195948),
(2079,'Х.Почта ИМЯ ЕСТЬ',1,3,195948),

(13,'Х. не обработан',1,1,195949),
(1035,'Х. отложен',1,2,195949),
(1013,'Х.Перезвон БЕЗ ИМЕНИ',1,3,195949),
(2079,'Х.Почта ИМЯ ЕСТЬ',1,4,195949)

select * from @data


Запрос, в который надо добавить фильтр
select count(*),[1],[2],[3],[4],[5]
from (select s_name,statuschangeorder,a_id from @data) s
pivot(max(s_name) for statuschangeorder in([1],[2],[3],[4],[5])) p
group by [1],[2],[3],[4],[5]
order by [1],[2],[3],[4],[5]


Результат без фильтра по статусу
Кол-во клиентов 1 2 3 4 5
1 Х. не обработан Х. отложен Х.Перезвон БЕЗ ИМЕНИ Х.Почта ИМЯ ЕСТЬ
2 Х. не обработан Х.Перезвон БЕЗ ИМЕНИ
1 Х. не обработан Х.Перезвон БЕЗ ИМЕНИ Х.Почта ИМЯ ЕСТЬ
2 Х. не обработан Х.Перезвон БЕЗ ИМЕНИ Х.Почта ИМЯ ЕСТЬ Х. отказ
1 Х. не обработан Х.Почта ИМЯ ЕСТЬ Курьерская доставка Х. отложен Х.Почта БЕЗ ИМЕНИ


Необходимый результат с фильтром по статусу "Х.Перезвон БЕЗ ИМЕНИ"
Кол-во клиентов 1 2 3
2 Х.Перезвон БЕЗ ИМЕНИ
2 Х.Перезвон БЕЗ ИМЕНИ Х.Почта ИМЯ ЕСТЬ
2 Х.Перезвон БЕЗ ИМЕНИ Х.Почта ИМЯ ЕСТЬ Х. отказ
7 апр 18, 12:02    [21320516]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом на группировку данных  [new]
aleks222
Member

Откуда:
Сообщений: 855
declare @f nvarchar(128) = N'Х.Перезвон БЕЗ ИМЕНИ';

with data as (select * 
                      from @data as t
                     where exists( select * from @data as x where x.s_name = @f and x.a_id = t.a_id and x.statusChangeOrder <= t.statusChangeOrder )
                   )
select count(*),[1],[2],[3],[4],[5]
from (select s_name,statuschangeorder,a_id from data) s
pivot(max(s_name) for statuschangeorder in([1],[2],[3],[4],[5])) p
group by [1],[2],[3],[4],[5]
order by [1],[2],[3],[4],[5]


Не в коня корм.
7 апр 18, 12:21    [21320532]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить