基于MariaDB实现含子查询的客户筛选查询扩展需求

环境与表结构说明 #

• clients表：存储客户基础信息
• orders表：存储订单明细信息
• weekly_analyses表：每周末生成的客户周度聚合数据（周起始为周一），核心字段包括client_id、Brand、Store、orders（订单数）、discount（折扣额）、amount（订单总额）

现有查询框架 #

select {} from clients 
left join {...} 
where 
condition1 = 1
and (
   field1 = 1 OR field2 = 2
)
and (
   field3 = 3 OR field4 = 4
)
...

新增筛选需求 #

1. 筛选指定时间段（示例：2023-12-01 至 2024-01-15）内，特定品牌+门店下总订单数≥指定值（示例：10）的客户；
2. 筛选特定品牌+门店下，最近4个活跃周（该客户对应品牌门店有记录的最近4周）中discount≥X的客户；
3. 筛选特定品牌+门店下，最近4个活跃周中amount≥X的客户。

完整SQL语句 #

SELECT c.*  -- 替换为你实际需要查询的字段
FROM clients c
-- 关联预处理后的周度聚合筛选结果
JOIN (
    SELECT 
        wa.client_id,
        wa.Brand,
        wa.Store,
        -- 统计指定时间段内的总订单数
        SUM(CASE WHEN wa.week_start_date BETWEEN '2023-12-01' AND '2024-01-15' THEN wa.orders ELSE 0 END) AS total_orders_in_period,
        -- 判断最近4个活跃周的discount是否全部≥X（按需调整为至少N周满足）
        MIN(CASE WHEN wa.rn <= 4 THEN wa.discount ELSE NULL END) >= X AS recent_4w_discount_ok,
        -- 判断最近4个活跃周的amount是否全部≥X（按需调整为至少N周满足）
        MIN(CASE WHEN wa.rn <= 4 THEN wa.amount ELSE NULL END) >= X AS recent_4w_amount_ok
    FROM (
        SELECT 
            *,
            -- 按客户+品牌+门店分组，周起始日期倒序排名，取最近4个活跃周
            ROW_NUMBER() OVER (
                PARTITION BY client_id, Brand, Store 
                ORDER BY week_start_date DESC
            ) AS rn
        FROM weekly_analyses
        -- 先过滤目标品牌和门店
        WHERE Brand = '目标品牌' AND Store = '目标门店'
    ) wa
    GROUP BY wa.client_id, wa.Brand, wa.Store
    -- 组合筛选条件
    HAVING total_orders_in_period >= 10
       AND recent_4w_discount_ok = 1
       AND recent_4w_amount_ok = 1
) wa_filtered ON c.client_id = wa_filtered.client_id
-- 保留原有查询的WHERE条件
WHERE 
    condition1 = 1
    AND (field1 = 1 OR field2 = 2)
    AND (field3 = 3 OR field4 = 4)
    ...;

关键逻辑说明 #

1. 最近4个活跃周的实现：
   用ROW_NUMBER()窗口函数，按client_id+Brand+Store分组，以周起始日期倒序排序，排名rn<=4的记录就是该客户对应品牌门店的最近4个活跃周。
2. 时间段订单数统计：
   通过CASE WHEN配合SUM()，仅累加指定时间范围内的订单数，精准统计目标周期的订单总量。
3. 条件灵活调整：
   • 若需求是「最近4个活跃周中至少1周满足discount≥X」，把MIN(...)>=X改成MAX(...)>=X即可；
   • 若要求「至少N周满足条件」，可替换为SUM(CASE WHEN wa.rn<=4 AND wa.discount>=X THEN 1 ELSE 0 END) >= N。

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