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

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

Есть таблица Т1:


datemessage id_tasks Whowritedmessage
2017-11-01 04:59:07.6140000 C56D5EF0-CBC4-473F-90A7-A7A10223AE3E Client
2017-11-01 05:05:17.5000000 C56D5EF0-CBC4-473F-90A7-A7A10223AE3E Client
2017-11-01 05:06:53.9090000 C56D5EF0-CBC4-473F-90A7-A7A10223AE3E Agent
2017-11-01 05:06:53.9090000 C56D5EF0-CBC4-473F-90A7-A7A10223AE3E Agent
2017-11-01 05:07:18.7020000 C56D5EF0-CBC4-473F-90A7-A7A10223AE3E Client
2017-11-01 05:07:43.1280000 C56D5EF0-CBC4-473F-90A7-A7A10223AE3E Agent
2017-11-01 05:07:59.5780000 C56D5EF0-CBC4-473F-90A7-A7A10223AE3E Client
2017-11-01 05:09:55.0630000 C56D5EF0-CBC4-473F-90A7-A7A10223AE3E Client
2017-11-01 05:14:12.5870000 C56D5EF0-CBC4-473F-90A7-A7A10223AE3E Agent
2017-11-01 05:14:56.3690000 C56D5EF0-CBC4-473F-90A7-A7A10223AE3E Client
2017-11-01 06:06:56.1080000 C56D5EF0-CBC4-473F-90A7-A7A10223AE3E Agent
2017-11-01 06:07:07.2790000 C56D5EF0-CBC4-473F-90A7-A7A10223AE3E Agent
2017-11-01 06:21:17.5480000 C56D5EF0-CBC4-473F-90A7-A7A10223AE3E Client
2017-11-01 06:21:38.4280000 C56D5EF0-CBC4-473F-90A7-A7A10223AE3E Agent


Вот исходник:

CREATE TABLE T1(

datemessage [varchar] (100) NOT NULL,

[id_tasks] [varchar] (100) NOT NULL,

[Whowritedmessage] [varchar] (30) NOT NULL

)

GO

INSERT INTO T1 (DATEMESSAGE, ID_TASKS, Whowritedmessage) values

('2017-11-01 04:59:07.6140000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),

('2017-11-01 05:05:17.5000000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),

('2017-11-01 05:06:53.9090000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),

('2017-11-01 05:06:53.9090000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),

('2017-11-01 05:07:18.7020000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),

('2017-11-01 05:07:43.1280000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),

('2017-11-01 05:07:59.5780000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),

('2017-11-01 05:09:55.0630000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),

('2017-11-01 05:14:12.5870000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),

('2017-11-01 05:14:56.3690000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),

('2017-11-01 06:06:56.1080000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),

('2017-11-01 06:07:07.2790000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent'),

('2017-11-01 06:21:17.5480000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Client'),

('2017-11-01 06:21:38.4280000','C56D5EF0-CBC4-473F-90A7-A7A10223AE3E','Agent')

GO


Нужно написать запрос, который из исходных данных (выше) сделает выборку следующего вида :
//Результат - чат клиента с агентом в хронологической последовательсности


Client 2017-11-01 04:59:07.6140000 2017-11-01 05:06:53.9090000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client 2017-11-01 05:07:18.7020000 2017-11-01 05:07:43.1280000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client 2017-11-01 05:07:59.5780000 2017-11-01 05:14:12.5870000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client 2017-11-01 05:14:56.3690000 2017-11-01 06:06:56.1080000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Client 2017-11-01 06:21:17.5480000 2017-11-01 06:21:38.4280000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E

Решил эту задачу так:

create table #temp_Client (
id int not null identity,
Whowritedmessage nvarchar(100),
datemessage nvarchar(100)
)
insert into #temp_Client
select Whowritedmessage, datemessage from T1
where Whowritedmessage like '%Client%'
order by datemessage

create table #temp_Agent (
id int not null identity,
datemessage nvarchar(100),
Whowritedmessage nvarchar(100),
id_tasks nvarchar(100)
)
insert into #temp_Agent
select datemessage, Whowritedmessage, id_tasks from T1
where Whowritedmessage like '%Agent%'
order by datemessage
--
select c.Whowritedmessage as Client, c.datemessage as DateBefore, a.datemessage as DateAfter, a.Whowritedmessage as Agent, a.id_tasks from #temp_Client c
join #temp_Agent a on a.id=c.id

drop table #temp_Client, #temp_Agent


Но мое решение неверное, хоть и дает желаемый результат.
Нужно решить задачу в один запрос (количество подзапросов не ограничено).
2 окт 19, 23:56    [21985467]     Ответить | Цитировать Сообщить модератору
 Re: Результат - чат клиента с агентом в хронологической последовательсности  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
jounymod
Нужно решить задачу в один запрос
select c.Whowritedmessage as Client, c.datemessage as DateBefore, a.datemessage as DateAfter, a.Whowritedmessage as Agent, a.id_tasks 
from (
	select Whowritedmessage, datemessage, id_tasks, row_number() over(order by datemessage) as id
	from T1
	where Whowritedmessage like '%Client%'
) c
join (
	select Whowritedmessage, datemessage, id_tasks, row_number() over(order by datemessage) as id
	from T1
	where Whowritedmessage like '%Agent%'
) a on a.id=c.id
order by c.datemessage
3 окт 19, 02:51    [21985492]     Ответить | Цитировать Сообщить модератору
 Re: Результат - чат клиента с агентом в хронологической последовательсности  [new]
aleks222
Member

Откуда:
Сообщений: 952
alexeyvg
jounymod
Нужно решить задачу в один запрос
select c.Whowritedmessage as Client, c.datemessage as DateBefore, a.datemessage as DateAfter, a.Whowritedmessage as Agent, a.id_tasks 
from (
	select Whowritedmessage, datemessage, id_tasks, row_number() over(order by datemessage) as id
	from T1
	where Whowritedmessage like '%Client%'
) c
join (
	select Whowritedmessage, datemessage, id_tasks, row_number() over(order by datemessage) as id
	from T1
	where Whowritedmessage like '%Agent%'
) a on a.id=c.id
order by c.datemessage


Садись. Неуд.
3 окт 19, 06:07    [21985511]     Ответить | Цитировать Сообщить модератору
 Re: Результат - чат клиента с агентом в хронологической последовательсности  [new]
Владислав Колосов
Member

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

в один запрос - это ради спорта? Ничего не слышали о функциях, возвращающих таблицу?
3 окт 19, 11:19    [21985648]     Ответить | Цитировать Сообщить модератору
 Re: Результат - чат клиента с агентом в хронологической последовательсности  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
jounymod
Нужно решить задачу в один запрос
with a as
(
 select
  id_tasks, Whowritedmessage, datemessage,
  row_number() over (partition by id_tasks order by datemessage) as rn
 from
  T1
 where
  Whowritedmessage like '%Client%'
),
b as
(
 select
  id_tasks, Whowritedmessage, datemessage,
  row_number() over (partition by id_tasks order by datemessage) as rn
 from
  T1
 where
  Whowritedmessage like '%Agent%'
)
select
 a.Whowritedmessage, a.datemessage, b.datemessage, b.Whowritedmessage, a.id_tasks
from
 a join
 b on b.id_tasks = a.id_tasks and b.rn = a.rn
order by
 a.id_tasks, a.datemessage;

Если допускается нарушение чередования client - agent - client - agent, то результат будет неверен.
3 окт 19, 12:08    [21985700]     Ответить | Цитировать Сообщить модератору
 Re: Результат - чат клиента с агентом в хронологической последовательсности  [new]
PavelPS
Member

Откуда: петербург
Сообщений: 84
jounymod,
какая версия сервера?
при нескольких сообщениях подряд выводить только время первого сообщения?
3 окт 19, 12:24    [21985726]     Ответить | Цитировать Сообщить модератору
 Re: Результат - чат клиента с агентом в хронологической последовательсности  [new]
Ftt330
Member

Откуда:
Сообщений: 7
with tt as (
		select t1.*, 
			   case when whowritedmessage = 'Client' and lag(whowritedmessage, 1, 'Agent') over (partition by id_tasks order by datemessage) = 'Agent' 
					then 1 
					else 0 
				end as w
		from  T1
),
tt1 as (
		select tt.*,
			   sum(w) over (partition by id_tasks order by datemessage) as grp 
		  from tt)
select 'Client',
        min(datemessage),
	max(datemessage),
	'Agent',
	id_tasks
from tt1
group by grp, id_tasks
3 окт 19, 18:11    [21986144]     Ответить | Цитировать Сообщить модератору
 Re: Результат - чат клиента с агентом в хронологической последовательсности  [new]
newchoppa
Member

Откуда:
Сообщений: 3
Ftt330,
не совсем верная выборка получается, нужен первый ответ от агента, а не последний, поэтому не получится просто использоваться max
19 фев 20, 03:11    [22082667]     Ответить | Цитировать Сообщить модератору
 Re: Результат - чат клиента с агентом в хронологической последовательсности  [new]
newchoppa
Member

Откуда:
Сообщений: 3
PavelPS,
да, можете подсказать как это реализовать?
19 фев 20, 03:20    [22082669]     Ответить | Цитировать Сообщить модератору
 Re: Результат - чат клиента с агентом в хронологической последовательсности  [new]
Павел Фурсов
Member

Откуда: Украина
Сообщений: 111
Здравствуйте,

jounymod

Но мое решение неверное, хоть и дает желаемый результат.
20 фев 20, 13:47    [22083765]     Ответить | Цитировать Сообщить модератору
 Re: Результат - чат клиента с агентом в хронологической последовательсности  [new]
Павел Фурсов
Member

Откуда: Украина
Сообщений: 111
Здравствуйте,
вот мой вариант:
--группа = первый клиент - последний агент
; with
a as (
	--для перебора записей по порядку добавить номер строки в партициях id_tasks
	select
		*,
		rn = row_number() over (partition by id_tasks order by datemessage)
	from	T1
),
b as (
	select
		*,
		is_client = cast(case when Whowritedmessage = 'Client' then 1 else 0 end as bit),
		grp = 1
	from	a
	where
		rn = 1

	union all

	select
		a.*,
		is_client = cast(case when a.Whowritedmessage = 'Client' then 1 else 0 end as bit),
		grp = case when a.Whowritedmessage = 'Client'
			and b.is_client = 0 then -- .. Agent -> Client
				b.grp + 1 --начать новую группу
			else 
				b.grp
		end
	from	a --следующий
	join	b on a.rn = b.rn + 1 and a.id_tasks = b.id_tasks --предыдущий
),
c as (
	select
		rn1 = min(rn),
		rn2 = min(case when is_client = 0 then rn else null end),
		id_tasks
	from	b
	group by
		id_tasks,
		grp
)
select
	Client = b1.Whowritedmessage,
	DateBefore = b1.datemessage,
	DateAfter = b2.datemessage,
	Agent = b2.Whowritedmessage,
	id_tasks = isnull(b1.id_tasks, b2.id_tasks)
from	c
left join	b b1 on c.rn1 = b1.rn and c.id_tasks = b1.id_tasks and b1.is_client = 1
left join	b b2 on c.rn2 = b2.rn and c.id_tasks = b2.id_tasks and b2.is_client = 0
order by
	isnull(b1.datemessage, b2.datemessage);

С уважением,
Павел
20 фев 20, 13:50    [22083771]     Ответить | Цитировать Сообщить модератору
 Re: Результат - чат клиента с агентом в хронологической последовательсности  [new]
Ftt330
Member

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

За 30 сек допиливается.
Max (datemessage) -> min(case when whowritedmessage = 'Agent' then datemessage end)
20 фев 20, 15:17    [22083895]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить