如何统计新旧客户？探讨SQL中创建UDF实现可复用方案

完全可以通过SQL用户定义函数（UDF）实现你的需求，尤其是表值函数，能一次性返回回头客、新客户的统计数量和具体客户列表。以下是基于你提供的日志数据的具体实现方案：

1. 明确数据表结构 #

假设你的客户日志表名为ClientLoginLogs，先创建并插入示例数据：

CREATE TABLE ClientLoginLogs (
    StartDate VARCHAR(10), -- 建议改为DATE类型适配实际业务
    ClientID INT,
    cycleID INT
);

INSERT INTO ClientLoginLogs VALUES
('01-10-2022',101,100),
('01-10-2022',102,100),
('01-10-2022',103,100),
('01-10-2022',104,100),
('01-10-2022',105,100),
('01-11-2022',101,200),
('01-11-2022',102,200),
('01-11-2022',104,200),
('01-11-2022',106,200),
('01-11-2022',107,200);

2. 实现内联表值UDF（推荐，性能更优） #

内联表值函数语法简洁，执行计划与普通查询更接近，性能更好，适合用CTE实现的逻辑：

CREATE FUNCTION GetCustomerCycleAnalysis(
    @BaseCycleID INT, -- 基准CycleID
    @NewCycleID INT   -- 新CycleID
)
RETURNS TABLE
AS
RETURN (
    WITH BaseClients AS (
        -- 提取基准周期的唯一客户
        SELECT DISTINCT ClientID 
        FROM ClientLoginLogs 
        WHERE cycleID = @BaseCycleID
    ),
    NewClients AS (
        -- 提取新周期的唯一客户
        SELECT DISTINCT ClientID 
        FROM ClientLoginLogs 
        WHERE cycleID = @NewCycleID
    )
    SELECT
        CASE 
            WHEN bc.ClientID IS NOT NULL THEN '回头客' 
            ELSE '新客户' 
        END AS CustomerType,
        nc.ClientID,
        -- 计算对应类型的总数量
        COUNT(*) OVER (PARTITION BY CASE WHEN bc.ClientID IS NOT NULL THEN '回头客' ELSE '新客户' END) AS TotalCount
    FROM NewClients nc
    LEFT JOIN BaseClients bc ON nc.ClientID = bc.ClientID
);

3. 调用UDF获取结果 #

获取详细客户列表及对应统计数 #

SELECT * FROM GetCustomerCycleAnalysis(100, 200);

返回结果示例：

仅获取汇总统计 #

SELECT DISTINCT CustomerType, TotalCount 
FROM GetCustomerCycleAnalysis(100, 200);

返回结果示例：

4. 多语句表值函数（复杂场景备选） #

如果需要更复杂的分步逻辑，可以使用多语句表值函数：

CREATE FUNCTION GetCustomerCycleAnalysis_Multi(
    @BaseCycleID INT,
    @NewCycleID INT
)
RETURNS @Result TABLE (
    CustomerType VARCHAR(20),
    ClientID INT,
    TotalCount INT
)
AS
BEGIN
    -- 存储基准周期客户
    DECLARE @BaseClients TABLE (ClientID INT);
    INSERT INTO @BaseClients
    SELECT DISTINCT ClientID FROM ClientLoginLogs WHERE cycleID = @BaseCycleID;

    -- 存储新周期客户
    DECLARE @NewClients TABLE (ClientID INT);
    INSERT INTO @NewClients
    SELECT DISTINCT ClientID FROM ClientLoginLogs WHERE cycleID = @NewCycleID;

    -- 插入回头客数据
    INSERT INTO @Result
    SELECT 
        '回头客',
        nc.ClientID,
        (SELECT COUNT(*) FROM @NewClients INNER JOIN @BaseClients ON @NewClients.ClientID = @BaseClients.ClientID)
    FROM @NewClients nc
    INNER JOIN @BaseClients bc ON nc.ClientID = bc.ClientID;

    -- 插入新客户数据
    INSERT INTO @Result
    SELECT 
        '新客户',
        nc.ClientID,
        (SELECT COUNT(*) FROM @NewClients LEFT JOIN @BaseClients ON @NewClients.ClientID = @BaseClients.ClientID WHERE @BaseClients.ClientID IS NULL)
    FROM @NewClients nc
    LEFT JOIN @BaseClients bc ON nc.ClientID = bc.ClientID
    WHERE bc.ClientID IS NULL;

    RETURN;
END;

注意事项 #

• 去重处理：用DISTINCT确保每个周期内的客户只统计一次，避免同一客户多次登录导致结果错误。
• 方言适配：上述示例基于SQL Server，不同数据库（如MySQL、PostgreSQL）的UDF语法略有差异，需根据实际环境调整。
• 扩展能力：如果需要结合StartDate筛选，可以在UDF中添加日期参数，或在调用时额外过滤。

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