PostgreSQL：如何统计用户最近购卡后的折扣订单数量

我使用PostgreSQL数据库，包含以下表结构及数据：

card_purchases表 #

purchaser_id    purchase_date   card_id
44              12/10/2021      3
3               1/27/2022       1
19              1/31/2022       2
22              2/15/2022       1
4               6/9/2022        4
17              8/20/2022       2
19              2/4/2023        2
22              3/17/2023       1
3               3/19/2023       2
747             3/24/2023       2
1193            4/14/2023       1

card_templates表 #

card_id   card_name
1         discount pass
2         exclusive pass
3         senior citizen discount
4         customer loyalty

discounts表 #

discount_id     discount_name
1001            goodwill applied
1002            discount pass applied
1003            exclusive pass applied
1004            exclusive pass applied
1005            discount pass applied
1006            exclusive pass applied
1007            exclusive pass applied
1008            discount pass applied
1009            exclusive pass applied
1010            exclusive pass applied
1011            discount pass applied
1012            exclusive pass applied
1013            exclusive pass applied
1014            exclusive pass applied
1015            exclusive pass applied
1016            discount pass applied

discount_orders表 #

order_id    discount_id         created_at  purchaser_id
1100            1002            1/28/2022   3
1101            1003            1/31/2022   19
1102            1004            2/4/2022    19
1103            1005            3/15/2022   22
1104            1006            3/17/2022   19
1105            1007            8/27/2022   17
1106            1008            8/30/2022   22
1107            1009            2/4/2023    19
1108            1010            2/19/2023   19
1109            1011            3/18/2023   22
1110            1012            3/19/2023   19
1111            1013            3/31/2023   747
1112            1014            4/5/2023    19
1113            1015            4/15/2023   747
1114            1016            4/20/2023   1193

背景说明：客户购买的通行证可享受折扣价购买产品，有效期通常为1年，到期后可再次购卡。我需要统计每位客户最近一次购卡当天及之后的订单数量，比如客户19分别在2022年1月31日和2023年2月4日购卡，我需要统计第二张卡对应的4次订单，而非总订单数7次。

现有代码能统计客户的总订单数，但无法过滤出最近购卡后的订单，修改后的代码如下：

SELECT
    dord.purchaser_id AS user_id,
    COUNT(DISTINCT dord.order_id) AS count_orders
FROM
    discounts d
JOIN
    discount_orders dord ON d.discount_id = dord.discount_id
-- 子查询获取每个客户的最近购卡日期
JOIN (
    SELECT
        purchaser_id,
        MAX(purchase_date) AS last_purchase_date
    FROM
        card_purchases cp
    JOIN
        card_templates ct ON cp.card_id = ct.card_id
    WHERE
        ct.card_id IN (1, 2)
    GROUP BY
        purchaser_id
) AS last_cards ON dord.purchaser_id = last_cards.purchaser_id
WHERE
    d.discount_name LIKE '%pass%'
    -- 过滤出最近购卡当天及之后的订单
    AND dord.created_at >= last_cards.last_purchase_date
GROUP BY
    dord.purchaser_id

代码说明： #

1. 子查询last_cards：按客户分组，筛选出购买discount pass和exclusive pass（card_id=1、2）的客户，并取出每个客户的最近购卡日期（使用MAX(purchase_date)获取最新日期）。
2. 关联订单表：将订单表与子查询结果关联，让每个订单匹配到所属客户的最近购卡日期。
3. 日期过滤：通过dord.created_at >= last_cards.last_purchase_date条件，仅保留最近购卡当天及之后产生的订单。
4. 统计订单数：按客户分组，统计符合条件的唯一订单数量。

这样就能得到每个客户最近一次购卡后的订单数量，例如客户19会返回正确的4次订单结果。

内容的提问来源于stack exchange，提问作者crimson