主页 / dba / 问题 / 275508
Accepted
igorkf
Asked: 2020-09-15 08:54:00 +0800 CST

根据列的连续值，为每个 customer_id 计算不同行中日期之间的天数差

  • 772

我有一张像这样的表格，显示每个客户在不同日期的付款：

|  stream_datetime | customer_id | order_status | rn |
|:----------------:|:-----------:|:------------:|:--:|
| 04/06/20 11:19AM |      1      |   completed  |  1 |
| 05/06/20 10:54AM |      1      |   completed  |  2 |
| 06/06/20 10:59AM |      1      |   completed  |  3 |
| 08/06/20 09:27AM |      2      |    failed    |  1 |
| 09/06/20 11:02AM |      2      |    failed    |  2 |
| 01/11/20 05:59PM |      3      |   completed  |  1 |
| 02/11/20 05:59PM |      3      |   completed  |  2 |
| 03/11/20 10:01AM |      3      |   cancelled  |  3 |
| 04/11/20 09:20AM |      3      |   completed  |  4 |
| 05/11/20 10:25AM |      3      |   completed  |  5 |
| 01/13/20 03:29PM |      4      |   completed  |  1 |
| 02/13/20 03:29PM |      4      |   completed  |  2 |
| 03/13/20 03:29PM |      4      |   cancelled  |  3 |
| 04/13/20 03:29PM |      4      |   completed  |  4 |
| 05/13/20 03:29PM |      4      |   completed  |  5 |
| 06/13/20 03:29PM |      4      |   completed  |  6 |
| 07/13/20 03:29PM |      4      |   completed  |  7 |
| 08/13/20 03:29PM |      4      |   cancelled  |  8 |
| 06/20/20 03:29PM |      5      |    failed    |  1 |
| 07/20/20 03:29PM |      5      |   completed  |  2 |
| 08/20/20 03:29PM |      5      |   completed  |  3 |
| 09/20/20 03:29PM |      5      |    failed    |  4 |
| 10/20/20 03:29PM |      5      |   completed  |  5 |

我想计算一个客户canceled他的计划的天差。

这里的挑战是客户端可以取消多次，因此客户端4必须被计为churned客户端两次，但客户端3只会被计为一次churned客户端。

我只想考虑order_status = completed后面有（不一定在下个月）的客户order_status = cancelled。

我也想创建一个名为purchase_day记录付款日期的列。

Obs.：该列rn表示特定客户端的行号。

编辑： 对不起。我犯了一些错误并写了问题。

也许order_status = cancelled先到先得order_status = completed。这是由于业务中的错误，但它可能发生。如果发生这种情况，那么我们不能将其视为流失客户。

所以这是我的预期结果（现在可以了）：

|   purchase_day   | customer_id | lifetime|
|:----------------:|:-----------:|:-------:|
| 01/11/20 05:59PM |      3      |   60    |
| 01/13/20 03:29PM |      4      |   60    |
| 04/13/20 03:29PM |      4      |   122   |

如你看到的：

  • 客户1从未取消（因此他不需要出现在结果中）
  • 客户2从未取消（因此他不需要出现在结果中）
  • 客户3取消了一次（他的生命周期等于 60 天）
  • 客户4取消了两次（一次为 60 天，另一次为 122 天）
  • 客户5从未取消（因此他不需要出现在结果中）
postgresql gaps-and-islands

1 个回答

  1. Best Answer

    好的，必须分三步完成：

    1. 标记我们要保留的记录（每个客户的第一条记录，状态更改的记录）
    2. 获取前一行stream_datetime并计算两者之间的差异
    3. 仅选择order_status被“取消”的行。
    SELECT
  customer_id
 ,purchase_day
 ,lifetime
FROM
(
  SELECT
    customer_id
   ,order_status
   ,LAG(stream_datetime) OVER (PARTITION BY customer_id ORDER BY stream_datetime) AS purchase_day
   ,CAST(stream_datetime AS DATE) - LAG(CAST(stream_datetime AS DATE)) OVER (PARTITION BY customer_id ORDER BY stream_datetime) AS lifetime
  FROM
    (
      SELECT
        customer_id
       ,stream_datetime
       ,order_status
       ,CASE
          WHEN customer_id <> LAG(customer_id,1,-1) OVER (PARTITION BY customer_id ORDER BY stream_datetime) THEN 'Y'
          WHEN order_status <> LAG(order_status,1,'') OVER (PARTITION BY customer_id ORDER BY stream_datetime) THEN 'Y'
          ELSE 'N'
        END AS change_ind
      FROM
        Payment
      WHERE
        order_status IN ('completed','cancelled')  
    ) payment_summary
  WHERE
    change_ind = 'Y'
) payment_change
WHERE
  order_status = 'cancelled'
    AND purchase_day IS NOT NULL

    结果是：

    customer_id     purchase_day            lifetime
3               2020-01-11T17:59:00Z    60
4               2020-01-13T15:29:00Z    60
4               2020-04-13T15:29:00Z    122

    您可以使用此Fiddle进行测试。

    我建议自己运行子查询以了解每个步骤的工作原理，这样您就可以对结果充满信心。

    您的查询工作得很好！但我忘了告诉一些事情：也许 order_status = 已取消比 order_status = 已完成。这是由于业务中的错误，但它可能发生。如果发生这种情况，那么我们不能将其视为流失客户。

    好的，考虑到事情的设置方式，这很容易解决。如果 cancel 输入错误，purchase_day将为 NULL（我们已经忽略了具有两个连续状态的实例），因此我们可以将 final 更改WHERE为：

    WHERE
  order_status = 'cancelled'
    AND purchase_day IS NOT NULL

    更新小提琴供您测试。

    • 1

相关问题