时间戳

首页 > 解决方案 > Multiple actions in a certain time

sql - Multiple actions in a certain time

问题描述

I need to create a list of only (Client_ID) and (Client first Purchase_date).

However, there’s a Client Criteria: return only the clients who have made their second purchase within max 50 days of their first purchase_date.

Client_id | Purchase_date | purchase_type_id | Billing_ID
---------------------------------------------------------
01        | 05-Jan-19     | 1                |  010
01        | 18-Jan-19     | 2                |  018
02        | 05-Feb-19     | 1                |  021
03        | 18-Apr-19     | 3                |  029
03        | 01-Aug-19     | 3                |  030
04        | 05-Aug-19     | 2                |  049
05        | 05-Aug-19     | 2                |  059
06        | 05-Aug-19     | 1                |  060

*Purchase Type ID: (1) For (New Client), (2) for (Repeat Client), (3) for (Same Item like the previous purchase).

According to this, the query must only contain Client_id = 01

标签： sqlpostgresql

解决方案

点击：逐步演示：db<>fiddle

您可以使用lead()窗口功能：

SELECT
    "Client_id",
    MIN("Purchase_date")
FROM (
    SELECT
        *,
        lead("Purchase_date") OVER (PARTITION BY "Client_id" ORDER BY "Purchase_date") 
            - "Purchase_date" AS diff
    FROM
        mytable
) s
WHERE diff <= 50
GROUP BY "Client_id"
  1. lead("Purchase_date")为您提供条款中指定的组内的下一个购买日期PARTITION BY（在您的情况下为Client_id）
  2. 您可以用它来计算与当前购买日期的差异，从而为您提供天数
  3. 过滤具有不同<= 50天数的所有值
  4. 如果在此范围内有多个购买，您可以通过分组给出最小日期

推荐阅读