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

Откуда:
Сообщений: 17
Есть таблица со списком сообщений
в ней есть поля SenderId, ReceiverId, MessageDate
нужно получить список диалогов. И желательно автора кто начал диалог.
у меня идея такая
Отсортировать таблицу сообщений по MessageDate по убыванию (сверху самое молодое сообщение)
Выбрать все сообщения оставив только те для которых пары SenderId, ReceiverId и ReceiverId,SenderId уникальны. Тогда останется список диалогов, причём SenderId - будет автором диалога.
вот что написал
select Distinct SenderId, ReceiverId from [Messages]
но это неправильно
Посоветуйте куда копать , новичёк в SQL - подозреваю что решается задачка легко , просто не знаю куда копать , При этом я не очень понимаю как использовать Distinct когда мне нужны остальные столбцы таблицы и уникальность требуется только по составному ключу. Спасибо.
22 окт 12, 17:46    [13358081]     Ответить | Цитировать Сообщить модератору
 Re: Набор диалогов из списка сообщений, помогите с запросом.  [new]
Crimean
Member

Откуда:
Сообщений: 13147
ага, а все диалоги между двумя корреспондентами - в кучу свалим? неужели там нет ид диалога?
22 окт 12, 19:03    [13358440]     Ответить | Цитировать Сообщить модератору
 Re: Набор диалогов из списка сообщений, помогите с запросом.  [new]
Shm
Member

Откуда:
Сообщений: 17
Неа нету...
Чувствую помимо Distinct нужно ещё использовать соединение само на себя какое то хитрое
Т.е.
select Distinct SenderId ,ReceiverId from [Messages]
У меня получается пока там не много > 800 записей
что то типа
LineNmbrSenderIdReceiverId
1DF8A3704-BEC2-40B2-9F8D-000B49EFD7E65EC28F23-CFBE-4A99-BD2C-BE12FEC0091A
28DCE503B-262E-4541-8324-026A190F24235EC28F23-CFBE-4A99-BD2C-BE12FEC0091A
32A651089-6E4B-4307-9DCA-0341E107B0A95EC28F23-CFBE-4A99-BD2C-BE12FEC0091A
4B128CBFC-5455-4E94-A338-036D55DB1FBC5EC28F23-CFBE-4A99-BD2C-BE12FEC0091A
504D4687D-CEBB-492E-8D8A-03B2B694D8FFB609A896-6A8B-44A7-AA3F-D17DE3312E69
6875E02EE-EABE-4CCA-94D4-03E14BBA09085EC28F23-CFBE-4A99-BD2C-BE12FEC0091A
75EC28F23-CFBE-4A99-BD2C-BE12FEC0091A2A651089-6E4B-4307-9DCA-0341E107B0A9
868F2BBCB-2FC4-4B02-B56B-06497D364BC15EC28F23-CFBE-4A99-BD2C-BE12FEC0091A
916BEF0ED-1206-46D9-963A-07570F5E223716BEF0ED-1206-46D9-963A-07570F5E2237
1016BEF0ED-1206-46D9-963A-07570F5E2237014349F1-BCCD-450D-91A4-6B5E30E031B9

но например строки если посмотреть на строки 2 и 7 - то видно что это один диалог и скорее всего 7 строка мне в результате не нужна.
22 окт 12, 19:27    [13358550]     Ответить | Цитировать Сообщить модератору
 Re: Набор диалогов из списка сообщений, помогите с запросом.  [new]
Crimean
Member

Откуда:
Сообщений: 13147
раз ид диалога нету - со временем это все (1) ляжет и (2) станет весьма неудобно, так как старые и новые диалоги будут мешаться в кучу. совет - сделайте таки понятие "ид диалога" на уровне хранения данных
ну или выборку сведите к указанной дате - так хаос хотя бы станет "управляемым" :)
22 окт 12, 19:53    [13358681]     Ответить | Цитировать Сообщить модератору
 Re: Набор диалогов из списка сообщений, помогите с запросом.  [new]
Shm
Member

Откуда:
Сообщений: 17
Ну Id диалога можно не создавать
а у каждого сообщения вычислить например ключ составной вычисленный следующим образом будет уникальным
select Distinct
case when convert(nvarchar(max),SenderId) >  convert(nvarchar(max),ReceiverId)
then SenderId
else  ReceiverId end  as Interlocutor0Id,
case when convert(nvarchar(max),SenderId) >  convert(nvarchar(max),ReceiverId)
then ReceiverId
else SenderId end as Interlocutor1Id
 from [Messages] 

Мax(ConvertToString(SenderId)+ConvertToString(ReceiverId),ConvertToString(ReceiverId)+ConvertToString(SenderId))
(Мах - функция выбирающая большую из двух строк ) ,сформирует уникальный ключ.
Это то запрос по идее мне даст список диалогов типа
Interlocutor0IdInterlocutor1Id
DF8A3704-BEC2-40B2-9F8D-000B49EFD7E65EC28F23-CFBE-4A99-BD2C-BE12FEC0091A
8DCE503B-262E-4541-8324-026A190F24235EC28F23-CFBE-4A99-BD2C-BE12FEC0091A
D8F37BDD-EC12-40F8-BF03-02D154ED7C555EC28F23-CFBE-4A99-BD2C-BE12FEC0091A
B128CBFC-5455-4E94-A338-036D55DB1FBC5EC28F23-CFBE-4A99-BD2C-BE12FEC0091A
875E02EE-EABE-4CCA-94D4-03E14BBA09085EC28F23-CFBE-4A99-BD2C-BE12FEC0091A
D7BEA03E-E531-4863-8B4E-04F96F614490C61D66F1-8EEA-4B1B-B6E5-BCA190121B3D
A63C769E-ADDC-427E-B130-06265A3749D35EC28F23-CFBE-4A99-BD2C-BE12FEC0091A
68F2BBCB-2FC4-4B02-B56B-06497D364BC15EC28F23-CFBE-4A99-BD2C-BE12FEC0091A


Я раб процедурных языков программирования никак не могу разобраться с этими sql .. долго копался
Методом тыка родил вот что
select Distinct
M.ReceiverId ,M.SenderId,
COUNT(M.MessageId ) over (PARTITION BY M.ReceiverId ,M.SenderId )as NumberMsg,
MAX(M.Date) over (PARTITION BY M.ReceiverId ,M.SenderId )as DateOldestMsg,
MIN(M.Date) over (PARTITION BY M.ReceiverId ,M.SenderId )as DateFirstMsg
 from [Messages] m
 order by NumberMsg desc

которое даёт мне уже более приятную страничку типа
ine#ReceiverIdSenderIdNumberMsgDateOldestMsgDateFirstMsg
1014349F1-BCCD-450D-91A4-6B5E30E031B96B1B4352-C8CB-43C0-9D2E-543D3E4F3E67152012-07-31 22:46:49.7032012-04-08 17:38:55.107
26B1B4352-C8CB-43C0-9D2E-543D3E4F3E67014349F1-BCCD-450D-91A4-6B5E30E031B9112012-07-31 23:49:04.7032012-04-08 20:57:46.370
3678201C2-561F-49EA-A3C1-3F8293D7BC01C61D66F1-8EEA-4B1B-B6E5-BCA190121B3D52012-03-05 17:05:25.3972012-02-08 13:29:09.083
4014349F1-BCCD-450D-91A4-6B5E30E031B9163CA24A-B709-42BE-A45D-27ACFFA34CCA52012-10-04 11:38:44.4232012-09-30 18:08:56.017
5014349F1-BCCD-450D-91A4-6B5E30E031B9014349F1-BCCD-450D-91A4-6B5E30E031B952012-03-21 20:31:36.1372012-03-14 21:58:15.077
6EBD1ADDB-9BAE-4611-8F0F-A451322F4723F6D417DC-7096-4AA4-996F-1A4F24D46B1652012-09-10 12:28:19.2502012-09-09 22:38:38.453
7520F9852-898F-452A-8FB1-AF7AD861EF9678A59A3B-E0B9-434F-985A-48B1BA7ADF9252012-07-13 14:22:37.9732012-05-24 11:11:38.600
8C61D66F1-8EEA-4B1B-B6E5-BCA190121B3D678201C2-561F-49EA-A3C1-3F8293D7BC0152012-03-05 17:05:51.0332012-02-08 13:28:30.540
97910AE71-7461-440B-A7F6-F2B167759BA918C6BA5A-BE5D-42B5-8FDB-E28ED67D0D6A52012-07-16 13:47:14.0502012-07-10 16:49:52.110
1016BEF0ED-1206-46D9-963A-07570F5E2237018D5902-F3DC-46A8-9FC8-E8C36C4424BA42012-07-16 17:13:18.6272012-07-11 15:34:14.793
11F68AFFC0-82B2-4AD4-902E-155362B1EB2AF68AFFC0-82B2-4AD4-902E-155362B1EB2A42012-05-07 23:17:57.3602012-05-07 23:17:30.457
12163CA24A-B709-42BE-A45D-27ACFFA34CCA014349F1-BCCD-450D-91A4-6B5E30E031B942012-10-03 16:49:30.4572012-09-30 20:22:27.127

легко видеть что например строки 1 и 2 (как строки 4-12) - можно было бы объединить, прописав новые DateOldestMsg, NumberMsg,DateFirstMsg тк они принадлежат одному диалогу , просто для строк 1 и 2 зачинателем диалога был 6B1B4352-C8CB-43C0-9D2E-543D3E4F3E67 , его бы тоже желательно было вынести . Но как это сделать проще , я не допёр. Разгрызу лучше эти данные на сервере в С# , тем более табличка №2 в худшем случае в два раза длинее чем табличка №1 , не буду из-за этого париться. тем более запрос этот будет не частый и с учётом номера страницы.
23 окт 12, 20:31    [13365242]     Ответить | Цитировать Сообщить модератору
 Re: Набор диалогов из списка сообщений, помогите с запросом.  [new]
Shm
Member

Откуда:
Сообщений: 17
Да добавлю. Диалог может это то что происходит между 2 пользователями . Ну как в простейшем месседжере (это не ветка в форуме или конференция в скайпе где много участников). Так что пока считаю ID диалога - лишним , тк диалог полностью определяется двумя ID-шниками участников
23 окт 12, 20:43    [13365274]     Ответить | Цитировать Сообщить модератору
 Re: Набор диалогов из списка сообщений, помогите с запросом.  [new]
Shm
Member

Откуда:
Сообщений: 17
Да всё ж хотелось бы табличку
ine# ReceiverId SenderId NumberMsg DateOldestMsg DateFirstMsg

перелопатить в несколько в другую
ine# DialogBeginerId InterlocutorId NumberMsg DateOldestMsg DateFirstMsg

где
DialogBeginerId - Тот кто начал диалог -определяется как SenderId из двух(одной) записей принадлежащих одному диалогу с минимальной DateFirstMsg
InterlocutorId - Второй собеседник в диалоге -определяется как ReciverId из двух(одной) записей принадлежащих одному диалогу с минимальной DateFirstMsg
NumberMsg - всего сообщений в диалоге - определяется как Сумма записей NumberMsg принадлежащих одному диалогу
DateOldestMsg - дата последнего сообщения в диалоге - определяется как Максимум записей DateOldestMsg принадлежащих одному диалогу
DateFirstMsg - дата первого сообщения - определяется как Минимум записей DateFirstMsg принадлежащих одному диалогу (хотя этот столбец по идее мне нужен)
Кто-нибудь что-нибудь путное подскажет.
24 окт 12, 13:16    [13368162]     Ответить | Цитировать Сообщить модератору
 Re: Набор диалогов из списка сообщений, помогите с запросом.  [new]
Shm
Member

Откуда:
Сообщений: 17
Родил следующее:
declare @Result Table 
(   [Interlocutor0Id] [uniqueidentifier],
	[Interlocutor1Id] [uniqueidentifier],
	[ReceiverId] [uniqueidentifier],
	[SenderId] [uniqueidentifier],
	[NumberMsg] int,
	[DateOldestMsg] [datetime],
	[DateFirstMsg] [datetime]
	
	)

Insert into @Result
select Distinct
case when convert(nvarchar(max),SenderId) >  convert(nvarchar(max),ReceiverId)
then SenderId
else  ReceiverId end  as Interlocutor0Id,
case when convert(nvarchar(max),SenderId) >  convert(nvarchar(max),ReceiverId)
then ReceiverId
else SenderId end as Interlocutor1Id,
M.ReceiverId ,M.SenderId,
COUNT(M.MessageId ) over (PARTITION BY M.ReceiverId ,M.SenderId )as NumberMsg,
MAX(M.Date) over (PARTITION BY M.ReceiverId ,M.SenderId )as DateOldestMsg,
MIN(M.Date) over (PARTITION BY M.ReceiverId ,M.SenderId )as DateFirstMsg
 from [Messages] m
 
 declare @Result1 Table 
(   [Interlocutor0Id] [uniqueidentifier],
	[Interlocutor1Id] [uniqueidentifier],
	[SumNumberMsg] int,
	[MinDateFirstMsg] [datetime],
	[LastMsgDlgDate][datetime],
	[AthorSelector] int,
	[SenderId] [uniqueidentifier],
	[ReceiverId] [uniqueidentifier],
	[DateOldestMsg] [datetime],
	[DateFirstMsg] [datetime]
	)
insert into @Result1
select 
Interlocutor0Id,
Interlocutor1Id,
Sum(NumberMsg)over (PARTITION BY Interlocutor0Id ,Interlocutor1Id )as SumNumberMsg,
Min(DateFirstMsg)over (PARTITION BY Interlocutor0Id ,Interlocutor1Id )as MinDateFirstMsg,
Max(DateOldestMsg)over (PARTITION BY Interlocutor0Id ,Interlocutor1Id )as LastMsgDlgDate,
Row_Number() OVER (PARTITION BY Interlocutor0Id ,Interlocutor1Id order by DateFirstMsg) As AthorSelector,
SenderId,
ReceiverId,
DateOldestMsg,
DateFirstMsg
from @Result 
order by SumNumberMsg desc

Select SenderId as DialogBeginer,
ReceiverId as InterlocutorId,
DateOldestMsg,
SumNumberMsg as NumberMsg
from @Result1 where AthorSelector=1

Вроде работает :
DialogBeginerInterlocutorIdDateOldestMsgNumberMsg
6B1B4352-C8CB-43C0-9D2E-543D3E4F3E67014349F1-BCCD-450D-91A4-6B5E30E031B92012-07-31 22:46:49.70326
678201C2-561F-49EA-A3C1-3F8293D7BC01C61D66F1-8EEA-4B1B-B6E5-BCA190121B3D2012-03-05 17:05:51.03310
163CA24A-B709-42BE-A45D-27ACFFA34CCA014349F1-BCCD-450D-91A4-6B5E30E031B92012-10-04 11:38:44.4239
9CA7FCB5-0307-49EB-BA05-AA8F8328913E014349F1-BCCD-450D-91A4-6B5E30E031B92012-06-13 10:48:28.5038
8237E747-8680-4218-A25B-9ECFC02B9307014349F1-BCCD-450D-91A4-6B5E30E031B92012-10-03 10:09:49.4707
78A59A3B-E0B9-434F-985A-48B1BA7ADF92520F9852-898F-452A-8FB1-AF7AD861EF962012-07-13 14:22:37.9737
52E42DE9-6E76-4617-8F18-73D1466749659505B5B5-DBBB-4296-B025-74F7780F2B692011-11-16 19:45:00.9636
D8CFF935-DB87-478C-ABB4-42539BBBA0AA014349F1-BCCD-450D-91A4-6B5E30E031B92012-09-30 17:02:17.0676
014349F1-BCCD-450D-91A4-6B5E30E031B997C741D9-29CA-4E49-A98F-3D812E5C73912012-07-20 10:52:27.0336
16BEF0ED-1206-46D9-963A-07570F5E2237018D5902-F3DC-46A8-9FC8-E8C36C4424BA2012-07-16 17:03:37.7706
F6D417DC-7096-4AA4-996F-1A4F24D46B16EBD1ADDB-9BAE-4611-8F0F-A451322F47232012-09-10 12:28:19.2505
5EC28F23-CFBE-4A99-BD2C-BE12FEC0091AD6190A71-E6F3-4501-BF70-1E8A2823E0782012-07-30 14:58:08.0035
014349F1-BCCD-450D-91A4-6B5E30E031B9014349F1-BCCD-450D-91A4-6B5E30E031B92012-03-21 20:31:36.1375
5EC28F23-CFBE-4A99-BD2C-BE12FEC0091A87F79527-8F40-41D8-9781-65C860064DD52012-08-01 13:17:50.9635
18C6BA5A-BE5D-42B5-8FDB-E28ED67D0D6A7910AE71-7461-440B-A7F6-F2B167759BA92012-07-16 13:47:14.0505
5EC28F23-CFBE-4A99-BD2C-BE12FEC0091AC27215A4-37AF-48E7-895F-FC19E0A144C82012-07-19 15:11:24.0974
5EC28F23-CFBE-4A99-BD2C-BE12FEC0091A7A43B63A-BCF0-4AE3-A791-E4815FAF40452012-07-20 15:16:51.3004
9A30759B-30E7-4103-B918-EC35542072D3014349F1-BCCD-450D-91A4-6B5E30E031B92012-03-16 18:00:49.5374

Может и не оптимально, может и сломается когда будет много сообщений , но к этому моменту я надеюсь изучу sql :)
24 окт 12, 16:14    [13369652]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить