ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Результат - чат клиента с агентом в хронологической последовательсности
12 сообщений из 12, страница 1 из 1
Результат - чат клиента с агентом в хронологической последовательсности
    #39870897
jounymod
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
jounymod
Гость
Всем привет. Подскажите как решить эту задачу одним сложным запросом.

Есть таблица Т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

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
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-A7A10223AE3EClient 2017-11-01 05:07:18.7020000 2017-11-01 05:07:43.1280000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3EClient 2017-11-01 05:07:59.5780000 2017-11-01 05:14:12.5870000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3EClient 2017-11-01 05:14:56.3690000 2017-11-01 06:06:56.1080000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3EClient 2017-11-01 06:21:17.5480000 2017-11-01 06:21:38.4280000 Agent C56D5EF0-CBC4-473F-90A7-A7A10223AE3E
Решил эту задачу так:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
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



Но мое решение неверное, хоть и дает желаемый результат.
Нужно решить задачу в один запрос (количество подзапросов не ограничено).
...
Рейтинг: 0 / 0
02.10.2019, 23:56
| Ответить | Цитировать | Написать  
Результат - чат клиента с агентом в хронологической последовательсности
    #39870917
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
jounymodНужно решить задачу в один запрос
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
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
...
Рейтинг: 0 / 0
03.10.2019, 02:51
| Ответить | Цитировать | Написать  
Результат - чат клиента с агентом в хронологической последовательсности
    #39870927
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
alexeyvgjounymodНужно решить задачу в один запрос
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
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



Садись. Неуд.
...
Рейтинг: 0 / 0
03.10.2019, 06:07
| Ответить | Цитировать | Написать  
Результат - чат клиента с агентом в хронологической последовательсности
    #39871026
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
jounymod,

в один запрос - это ради спорта? Ничего не слышали о функциях, возвращающих таблицу?
...
Рейтинг: 0 / 0
03.10.2019, 11:19
| Ответить | Цитировать | Написать  
Результат - чат клиента с агентом в хронологической последовательсности
    #39871052
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
jounymod Нужно решить задачу в один запрос
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
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, то результат будет неверен.
...
Рейтинг: 0 / 0
03.10.2019, 12:08
| Ответить | Цитировать | Написать  
Результат - чат клиента с агентом в хронологической последовательсности
    #39871066
PavelPS
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PavelPS
Гость
jounymod,
какая версия сервера?
при нескольких сообщениях подряд выводить только время первого сообщения?
...
Рейтинг: 0 / 0
03.10.2019, 12:24
| Ответить | Цитировать | Написать  
Результат - чат клиента с агентом в хронологической последовательсности
    #39871304
Ftt330
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ftt330
Гость
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
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
...
Рейтинг: 0 / 0
03.10.2019, 18:11
| Ответить | Цитировать | Написать  
Результат - чат клиента с агентом в хронологической последовательсности
    #39928240
newchoppa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
newchoppa
Гость
Ftt330,
не совсем верная выборка получается, нужен первый ответ от агента, а не последний, поэтому не получится просто использоваться max
...
Рейтинг: 0 / 0
19.02.2020, 03:11
| Ответить | Цитировать | Написать  
Результат - чат клиента с агентом в хронологической последовательсности
    #39928242
newchoppa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
newchoppa
Гость
PavelPS,
да, можете подсказать как это реализовать?
...
Рейтинг: 0 / 0
19.02.2020, 03:20
| Ответить | Цитировать | Написать  
Результат - чат клиента с агентом в хронологической последовательсности
    #39928905
Павел Фурсов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Павел Фурсов
Гость
Здравствуйте,

jounymod

Но мое решение неверное, хоть и дает желаемый результат.
...
Рейтинг: 0 / 0
20.02.2020, 13:47
| Ответить | Цитировать | Написать  
Результат - чат клиента с агентом в хронологической последовательсности
    #39928909
Павел Фурсов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Павел Фурсов
Гость
Здравствуйте,
вот мой вариант:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
--группа = первый клиент - последний агент
; 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);


С уважением,
Павел
...
Рейтинг: 0 / 0
20.02.2020, 13:50
| Ответить | Цитировать | Написать  
Результат - чат клиента с агентом в хронологической последовательсности
    #39928966
Ftt330
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ftt330
Гость
newchoppa,

За 30 сек допиливается.
Max (datemessage) -> min(case when whowritedmessage = 'Agent' then datemessage end)
...
Рейтинг: 0 / 0
20.02.2020, 15:17
| Ответить | Цитировать | Написать  
12 сообщений из 12, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Результат - чат клиента с агентом в хронологической последовательсности
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=39871066&tid=1686457]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 45ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 41ms
get tp. blocked users:
 1ms
others: 334ms
total:  461ms
созд. / загр.: 461ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]