有没有办法在通信源中对回复进行建模?

Question

>我有一个包含以下列的表格：

communication_id|communication_date|company_id|customer_id|salesperson_id|medium|is_incoming|is_outgoing|communication_text

其中communication_id是表的粒度，medium可以是phone、sms等值。 从销售人员的角度来看，is_incoming和is_outgoing是沟通方向的指标列。如果销售人员发送通信，我们将有一个 0 表示is_incoming，一个 1 表示is_outgoing。

因此，给定communication_date订购的customer_id，我有该客户和他们的销售人员之间的通信源。如果我看到销售人员有 2 个传入通信，然后有 1 个传出通信给该客户，那么我们的规则/推断是将该传出通信标记为回复，依此类推。按照这个逻辑，只有is_outgoing通信可以是回复。所以，我认为我需要做的是建立一个is_salesperson_reply列，指示通信是否是回复。我认为同样有帮助的是，将他们回复的通信communication_id显示为salesperson_replied_to_communication_id，并将时间戳显示为salesperson_replied_to_at。

我正在尝试编写的是一个选择查询，它输出销售人员沟通的摘要;我已经能够创建一个表，其中包含每个销售人员以及通信总数、发送总数和接收总数。我现在想模拟他们做出的回复数量，也许是他们回复时所需的平均时间。

我有一种感觉，我可能需要使用array_agg函数或一些花哨的case语句，但我不确定如何。任何帮助不胜感激！

Answer 1

基于 @Deadzone 提供的小提琴，这可以通过以下方法实现：

USE "DEMO_DB"."PUBLIC";
INSERT INTO Communications VALUES (101, '5/1/2019 13:00:00', 12345, 0, 'First Incoming Communication'),
(102, '5/1/2019 13:00:10', 12345, 1, 'First Outgoing Communication'),
(103, '5/1/2019 13:00:14', 12345, 0, 'Second Incoming Communication'),
(111, '5/1/2019 13:01:00', 12345, 1, 'Second Outgoing Communication'),
(121, '5/1/2019 13:02:00', 12345, 1, 'Third Outgoing Communication'),
(122, '5/1/2019 13:02:08', 12345, 0, 'Third Incoming Communication')
SELECT *, 
DATEDIFF(second, 
LAG(communication_date, 1, communication_date) OVER (ORDER BY communication_date),
communication_date) AS ResponseTimeInSeconds
FROM Communications C
WHERE Customer_id = 12345
ORDER BY communication_date;

## COMMUNICATION_ID COMMUNICATION_DATE  CUSTOMER_ID IS_OUTGOING COMMUNICATION_TEXT  RESPONSETIMEINSECONDS
## 101  2019-05-01 13:00:00.000 12345   0   First Incoming Communication    0
## 102  2019-05-01 13:00:10.000 12345   1   First Outgoing Communication    10
## 103  2019-05-01 13:00:14.000 12345   0   Second Incoming Communication   4
## 111  2019-05-01 13:01:00.000 12345   1   Second Outgoing Communication   46
## 121  2019-05-01 13:02:00.000 12345   1   Third Outgoing Communication    60
## 122  2019-05-01 13:02:08.000 12345   0   Third Incoming Communication    8