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

Откуда: Москва
Сообщений: 594
Microsoft SQL Server 2017 (RTM-CU11) (KB4462262) - 14.0.3038.14 (X64) Sep 14 2018 13:53:44 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: )

DECLARE @s TABLE(id INT IDENTITY(1,1),subjectMessage INT,[Message] VARCHAR(8000),typeMessage INT,userID int,adate SMALLDATETIME)
INSERT INTO @s
(
    subjectMessage,
    Message,
    typeMessage,
	userID,
    adate
)
SELECT 25,'Добрый день коллеги готова ли виза?',1,12,'2020-02-01 11:08:00'
UNION
SELECT 25,'Добрый день виза готова',0,116,'2020-02-02 12:22:00'
UNION
SELECT 38,'Добрый день коллеги, отель забронирован?',1,12,'2020-02-03 13:02:00'
UNION
SELECT 38,'Будет ли в отеле автомобиль на прокат?',1,12,'2020-02-03 13:12:00'
UNION
SELECT 38,'Да, отель забронирован по адресу ул. Ленина 256',0,356,'2020-02-03 14:05:00'
UNION
SELECT 38,'Автомобиля к сожалению нету, придется воспользоваться сторонней компанией',0,324,'2020-02-03 15:06:00'
UNION
SELECT 38,'Я вам подыскал компанию по прокату автомобиля позвоните по номеру 77777',0,324,'2020-02-03 17:32:00'
UNION
SELECT 38,'А пока вам будет предоставлен водитель Иванов',0,324,'2020-02-03 18:16:00'
UNION
SELECT 38,'Спасибо автомобиль забронировал, водитель не нужен, закажу такси',1,12,'2020-02-04 09:02:00'
UNION
SELECT 38,'водителя отменил, приятного отдыха',0,324,'2020-02-04 12:00:00'
UNION
SELECT 18,'Какого числа обратный рейс?',1,12,'2020-02-05 12:00:00'


SELECT * FROM @s ORDER BY adate

subjectMessage - тема сообщения
Message - текст сообщения
typeMessage - тип сообщения (1- вопрос, 0-ответ)
userID - автор сообщения
adate - дата создания сообщения

Нужно сопоставить вопрос и ответ, если ответов больше чем вопросов в одной теме, то первый неиспользованный ответ в теме вопроса, если нет ответа на вопрос, то поле ответа оставить пустым.

Т.е. получить таблицу вида:

ID subjectMessageMessageQuestionMessageAnsweruserIDQuestionuserIDAnsweradateQuestionadateAnswer
125 Добрый день коллеги готова ли виза? Добрый день виза готова12116 2020-02-01 11:08:00 2020-02-02 12:22:00
238Добрый день коллеги отель забронирован? Да отель забронирован по адресу ул. Ленина 256123562020-02-03 13:02:00 2020-02-03 14:05:00
338Будет ли в отеле автомобиль на прокат?Автомобиля к сожалению нету придется воспользоваться сторонней компанией123242020-02-03 13:12:002020-02-03 15:06:00
438Спасибо автомобиль забронировал водитель не нужен закажу таксиводителя отменил приятного отдыха123242020-02-04 09:02:002020-02-04 12:00:00
518Какого числа обратный рейс?null12null2020-02-05 12:00:00null
14 фев 20, 16:40    [22080182]     Ответить | Цитировать Сообщить модератору
 Re: Как сопоставить вопрос и ответ  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2357
minya13_85,

нейронка? :)
14 фев 20, 16:41    [22080185]     Ответить | Цитировать Сообщить модератору
 Re: Как сопоставить вопрос и ответ  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4288
StarikNavy,

ТС прост как три копейки. Решил, что SQL сервер предназначен для решения задач искусственного интеллекта.

Есть нынче из SQL Server выход на Python и R и там там есть библиотеки какие-то.

А вообще надо гуглить в cторону TensorFlow и отправляться на https://www.kaggle.com/ .
14 фев 20, 17:00    [22080208]     Ответить | Цитировать Сообщить модератору
 Re: Как сопоставить вопрос и ответ  [new]
court
Member

Откуда:
Сообщений: 1930
minya13_85
если ответов больше чем вопросов в одной теме, то первый неиспользованный ответ в теме вопроса
"сложная" фраза :)

;with cte1 as
	(SELECT *, rn=row_number()over(partition by subjectMessage order by adate) FROM @s where typeMessage = 1),
cte0 as 
	(SELECT *, rn=row_number()over(partition by subjectMessage order by adate) FROM @s where typeMessage = 0)

select 
	cte1.subjectMessage
	,MessageQuestion=cte1.Message
	,MessageAnswer	=case when lead(cte1.id)over(partition by cte1.subjectMessage order by cte1.adate)is null then b.Message else a.Message end
	,userIDQuestion	=cte1.id
	,userIDAnswer	=case when lead(cte1.id)over(partition by cte1.subjectMessage order by cte1.adate) is null then b.id else a.id end
	,adateQuestion	=cte1.adate
	,adateAnswer	=case when lead(cte1.id)over(partition by cte1.subjectMessage order by cte1.adate) is null then b.adate else a.adate end
from cte1
outer apply (select * from cte0 where cte1.subjectMessage = cte0.subjectMessage and cte1.rn = cte0.rn) a
outer apply (select top 1 * from cte0 where cte1.subjectMessage = cte0.subjectMessage and cte0.rn >= cte1.rn order by cte0.rn desc) b

subjectMessagetMessageQuestiontMessageAnswertuserIDQuestiontuserIDAnswertadateQuestiontadateAnswer
18tКакого числа обратный рейс?tNULLt1tNULLt2020-05-02 12:00:00tNULL
25tДобрый день коллеги готова ли виза?tДобрый день виза готоваt3t2t2020-01-02 11:08:00t2020-02-02 12:22:00
38tДобрый день коллеги, отель забронирован?tДа, отель забронирован по адресу ул. Ленина 256t9t8t2020-03-02 13:02:00t2020-03-02 14:05:00
38tБудет ли в отеле автомобиль на прокат?tАвтомобиля к сожалению нету, придется воспользоваться сторонней компаниейt6t5t2020-03-02 13:12:00t2020-03-02 15:06:00
38tСпасибо автомобиль забронировал, водитель не нужен, закажу таксиtводителя отменил, приятного отдыхаt10t7t2020-04-02 09:02:00t2020-04-02 12:00:00


Сообщение было отредактировано: 14 фев 20, 18:05
14 фев 20, 18:01    [22080267]     Ответить | Цитировать Сообщить модератору
 Re: Как сопоставить вопрос и ответ  [new]
court
Member

Откуда:
Сообщений: 1930
редактирование "ломает" таблицу ... :(

subjectMessageMessageQuestionMessageAnsweruserIDQuestionuserIDAnsweradateQuestionadateAnswer
18Какого числа обратный рейс?NULL1NULL2020-05-02 12:00:00NULL
25Добрый день коллеги готова ли виза?Добрый день виза готова322020-01-02 11:08:002020-02-02 12:22:00
38Добрый день коллеги, отель забронирован?Да, отель забронирован по адресу ул. Ленина 256982020-03-02 13:02:002020-03-02 14:05:00
38Будет ли в отеле автомобиль на прокат?Автомобиля к сожалению нету, придется воспользоваться сторонней компанией652020-03-02 13:12:002020-03-02 15:06:00
38Спасибо автомобиль забронировал, водитель не нужен, закажу таксиводителя отменил, приятного отдыха1072020-04-02 09:02:002020-04-02 12:00:00
14 фев 20, 18:08    [22080269]     Ответить | Цитировать Сообщить модератору
 Re: Как сопоставить вопрос и ответ  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 594
court
minya13_85
если ответов больше чем вопросов в одной теме, то первый неиспользованный ответ в теме вопроса
"сложная" фраза :)

;with cte1 as
	(SELECT *, rn=row_number()over(partition by subjectMessage order by adate) FROM @s where typeMessage = 1),
cte0 as 
	(SELECT *, rn=row_number()over(partition by subjectMessage order by adate) FROM @s where typeMessage = 0)

select 
	cte1.subjectMessage
	,MessageQuestion=cte1.Message
	,MessageAnswer	=case when lead(cte1.id)over(partition by cte1.subjectMessage order by cte1.adate)is null then b.Message else a.Message end
	,userIDQuestion	=cte1.id
	,userIDAnswer	=case when lead(cte1.id)over(partition by cte1.subjectMessage order by cte1.adate) is null then b.id else a.id end
	,adateQuestion	=cte1.adate
	,adateAnswer	=case when lead(cte1.id)over(partition by cte1.subjectMessage order by cte1.adate) is null then b.adate else a.adate end
from cte1
outer apply (select * from cte0 where cte1.subjectMessage = cte0.subjectMessage and cte1.rn = cte0.rn) a
outer apply (select top 1 * from cte0 where cte1.subjectMessage = cte0.subjectMessage and cte0.rn >= cte1.rn order by cte0.rn desc) b

subjectMessagetMessageQuestiontMessageAnswertuserIDQuestiontuserIDAnswertadateQuestiontadateAnswer
18tКакого числа обратный рейс?tNULLt1tNULLt2020-05-02 12:00:00tNULL
25tДобрый день коллеги готова ли виза?tДобрый день виза готоваt3t2t2020-01-02 11:08:00t2020-02-02 12:22:00
38tДобрый день коллеги, отель забронирован?tДа, отель забронирован по адресу ул. Ленина 256t9t8t2020-03-02 13:02:00t2020-03-02 14:05:00
38tБудет ли в отеле автомобиль на прокат?tАвтомобиля к сожалению нету, придется воспользоваться сторонней компаниейt6t5t2020-03-02 13:12:00t2020-03-02 15:06:00
38tСпасибо автомобиль забронировал, водитель не нужен, закажу таксиtводителя отменил, приятного отдыхаt10t7t2020-04-02 09:02:00t2020-04-02 12:00:00


При добавлении еще вопросов и ответов, не совсем правильно срабатывает:

DECLARE @s TABLE(id INT IDENTITY(1,1),subjectMessage INT,[Message] VARCHAR(8000),typeMessage INT,userID int,adate SMALLDATETIME)
INSERT INTO @s
(
    subjectMessage,
    Message,
    typeMessage,
	userID,
    adate
)
SELECT 25,'Добрый день коллеги готова ли виза?',1,12,'2020-02-01 11:08:00'
UNION
SELECT 25,'Добрый день виза готова',0,116,'2020-02-02 12:22:00'
UNION
SELECT 38,'Добрый день коллеги, отель забронирован?',1,12,'2020-02-03 13:02:00'
UNION
SELECT 38,'Будет ли в отеле автомобиль на прокат?',1,12,'2020-02-03 13:12:00'
UNION
SELECT 38,'Да, отель забронирован по адресу ул. Ленина 256',0,356,'2020-02-03 14:05:00'
UNION
SELECT 38,'Автомобиля к сожалению нету, придется воспользоваться сторонней компанией',0,324,'2020-02-03 15:06:00'
UNION
SELECT 38,'Я вам подыскал компанию по прокату автомобиля позвоните по номеру 77777',0,324,'2020-02-03 17:32:00'
UNION
SELECT 38,'А пока вам будет предоставлен водитель Иванов',0,324,'2020-02-03 18:16:00'
UNION
SELECT 38,'Спасибо автомобиль забронировал, водитель не нужен, закажу такси',1,12,'2020-02-04 09:02:00'
UNION
SELECT 38,'водителя отменил, приятного отдыха',0,324,'2020-02-04 12:00:00'
UNION
SELECT 38,'К вам в отель направил подарок',0,324,'2020-02-04 12:15:00'
UNION
SELECT 38,'Спасибо не надо было',1,12,'2020-02-04 12:30:00'
UNION
SELECT 18,'Какого числа обратный рейс?',1,12,'2020-02-05 12:00:00'


тут главное вопросы, а к ним соединить ответы, согласно алгоритму, но чтобы дата ответа всегда была позже даты вопроса.

При таком добавлении , получить таблицу вида

ID subjectMessage MessageQuestion MessageAnswer userIDQuestion userIDAnswer adateQuestion adateAnswer
1 25 Добрый день коллеги готова ли виза?Добрый день виза готова121162020-02-01 11:08:002020-02-02 12:22:00
238Добрый день коллеги отель забронирован?Да отель забронирован по адресу ул. Ленина 25612356 2020-02-03 13:02:002020-02-03 14:05:00
338 Будет ли в отеле автомобиль на прокат?Автомобиля к сожалению нету придется воспользоваться сторонней компанией123242020-02-03 13:12:002020-02-03 15:06:00
438Спасибо автомобиль забронировал водитель не нужен закажу таксиводителя отменил приятного отдыха123242020-02-04 09:02:002020-02-04 12:00:00
538Спасибо не надо былоnull12null2020-02-04 12:30:00null
618Какого числа обратный рейс? null12null2020-02-05 12:00:00null
14 фев 20, 19:55    [22080352]     Ответить | Цитировать Сообщить модератору
 Re: Как сопоставить вопрос и ответ  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36633
А что отвечает на ваши вопросы архитектор этой схемы данных?
15 фев 20, 02:41    [22080432]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить