洗车行车牌频次统计：新老客户占比SQL实现需求

1. 修正后统计占比的SQL语句 #

原查询逻辑存在偏差，以下是直接实现新/回头客占比统计的SQL：

WITH customer_visit AS (
    -- 统计本月各车牌到店次数，同时判断是否为回头客（过去12个月非本月有记录）
    SELECT 
        pl.plate,
        COUNT(*) AS frequency,
        CASE 
            WHEN EXISTS (
                SELECT 1 
                FROM POS.dbo.plate AS p2 
                WHERE p2.plate = pl.plate 
                  AND p2.datetime >= DATEADD(MONTH, -12, DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), MONTH(CURRENT_TIMESTAMP), 1))
                  AND p2.datetime < DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), MONTH(CURRENT_TIMESTAMP), 1)
            ) THEN '回头客'
            ELSE '新客户'
        END AS customer_type
    FROM POS.dbo.plate AS pl
    WHERE pl.datetime >= DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), MONTH(CURRENT_TIMESTAMP), 1)
    GROUP BY pl.plate
),
total_customers AS (
    SELECT COUNT(*) AS total FROM customer_visit
)
SELECT 
    customer_type,
    COUNT(*) AS customer_count,
    CONCAT(ROUND((COUNT(*) * 100.0 / (SELECT total FROM total_customers)), 0), '%') AS percentage
FROM customer_visit
GROUP BY customer_type;

2. 逻辑说明 #

• customer_visit：按车牌分组统计本月到店次数，通过子查询校验该车牌在过去12个月（不含本月）的到店记录，标记客户类型。
• total_customers：统计本月到店的总客户数（按车牌去重）。
• 最终查询：按客户类型分组，计算对应数量及占比，百分比保留整数。

3. 示例输出（基于提供的测试数据） #

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