postgresql:合并行保留一些信息，没有循环

Question

我有一个每个用户的通话列表，有时以分钟为单位。用户可以在这些通话中购买或不购买。当用户在上次通话后的 45 分钟内调用电话时，我需要考虑这是与第一次通话相同的通话。

我需要获得最终的通话次数(汇总相隔不到 45 分钟的通话)以及每位用户购买商品的通话次数。

例如，我有一个这样的列表:

buyer       timestamp              bougth_flag        
tom         20150201 9:15                 1
anna        20150201 9:25                 0
tom         20150201 10:15                0
tom         20150201 10:45                1
tom         20150201 10:48                1
anna        20150201 11:50                0
tom         20150201 11:52                0
anna        20150201 11:54                0

决赛 table 将是:

buyer       time_started        calls              articles_bought        
tom         20150201 9:15          1                        1
anna        20150201 9:25          1                        0
tom         20150201 10:15         3                        2
anna        20150201 10:50         2                        0
tom         20150201 11:52         1                        0

因此，我需要合并相隔不到 45 分钟的行，并且仍然按用户分开。使用循环很容易做到这一点，但我在使用的 postgresql 中没有循环或函数/过程。关于如何做到这一点有什么想法吗？

谢谢

Answer 1

由于您事先不知道“电话”将持续多长时间(您可能一整天每 30 分钟接到某个买家的电话 - 请参阅问题评论)，您只能通过递归解决此问题CTE。 (请注意，我将您的列“timestamp”更改为“ts”。切勿使用关键字作为表名或列名。)

WITH conversations AS (
  WITH RECURSIVE calls AS (
    SELECT buyer, ts, bought_flag, row_number() OVER (ORDER BY ts) AS conversation, 1::int AS calls
    FROM (
      SELECT buyer, ts, lag(ts) OVER (PARTITION BY buyer ORDER BY ts) AS lag, bought_flag
      FROM list) sub
    WHERE lag IS NULL OR ts - lag > interval '45 minutes'
    UNION ALL
    SELECT l.buyer, l.ts, l.bought_flag, c.conversation, c.calls + 1
    FROM list l
    JOIN calls c ON c.buyer = l.buyer AND l.ts > c.ts
    WHERE l.ts - c.ts < interval '45 minutes'
  )
  SELECT buyer, ts, bought_flag, conversation, max(calls) AS calls
  FROM calls
  GROUP BY buyer, ts, bought_flag, conversation
  order by conversation, ts
)
SELECT buyer, min(ts) AS time_started, max(calls) AS calls, sum(bought_flag) AS articles_bought
FROM conversations
GROUP BY buyer, conversation
ORDER BY time_started

几句解释:

• 内部递归 CTE 的起始项有一个子查询，它为每次调用从表中获取基本数据，以及上一次调用的时间。内部 CTE 起始项中的主查询仅保留没有先前调用的行(lag IS NULL)或先前调用距离超过 45 分钟的行。因此，这些是我在这里称之为“对话”的最初调用。对话获得一列和一个 id，它只是查询中的行号，另一列用于跟踪对话“调用”中的调用次数。
• 在递归术语中，添加同一对话中的连续调用，并增加“调用”计数器。
• 当通话非常接近时(例如 10:45 和 10:15 之后的 10:48)，则后面的通话可能会被多次包含，这些重复的通话 (10:48) 会通过选择每次对话顺序中最早的调用。
• 在主查询中，最后，“bought_flag”列针对每个买家的每次对话进行汇总。