使用sql计算每天新增用户的ltv1、ltv2以及次留

最新推荐文章于 2025-04-28 09:24:42 发布

tiang_chc

最新推荐文章于 2025-04-28 09:24:42 发布

阅读量1.1k

点赞数 24

分类专栏： mysql 文章标签： java 开发语言

本文链接：https://blog.csdn.net/weixin_42698221/article/details/143181735

版权

mysql 专栏收录该内容

13 篇文章

订阅专栏

1.题目：使用sql计算每天新增用户的ltv1、ltv2以及次留

表名：login;

字段

usercreateymd string #新增日期

accountid string #账号ID

day_pay string #账号在dt当天的充值

logymd string #日期

次留定义：新增第二天有登录，比如20180623新增，在20180624有登录则认为该账号有次留

LTV定义：sun(新增前N天的充值)/sum(账号数)，比如20180623的新增用户，计算LTV1就是sum(20180623的新增用户在当日总充值)/sum(20180623新增用户数)，LTV2是sum(20180623的新增用户在20180623、20180624两日总充值)/sum(20180623新增用户数)

结果输出：

20180623 当日新增账号数次日还有登录的账号数 LTV1 LTV2

20180622 当日新增账号数次日还有登录的账号数 LTV1 LTV2

2.以下使用mysql实现

首先，代码使用了CTE（WITH语句）来创建了四个临时表。

1.new_users表计算了每天的新增用户数。它从login表中统计了每个用户创建账号的日期，并计算每天新增用户的数量。

2.retention_users表计算了次留用户数（第二天仍然有登录的用户数）。它使用了自连接查询，将用户在前一天登录的记录和后一天登录的记录进行比较，统计了每天的次留用户数

3.ltv1表计算了LTV1指标，即首日付费总额除以新增用户数。它从login表中筛选出当天的付费记录，并计算了每天的首日付费总额。

4.ltv2表计算了LTV2指标，即首日和次日付费总额除以新增用户数。它从login表中筛选出当天和次日的付费记录，并计算了每天的首日和次日付费总额。

最后，查询语句将以上四个表按照日期进行了连接，并选择了需要显示的字段。结果按照日期降序排列。

总结：

new_users表计算了每天的新增用户数。
retention_users表计算了每天的次留用户数。
ltv1表计算了每天的LTV1指标。
ltv2表计算了每天的LTV2指标。

3.完整代码

-- 计算每天新增用户的次留和LTV指标
WITH 
-- 计算每天的新增用户数
new_users AS (
    SELECT 
        usercreateymd,
        COUNT(DISTINCT accountid) as new_user_count
    FROM login 
    GROUP BY usercreateymd
),
-- 计算次留用户数（第二天还有登录的用户数）
retention_users AS (
    SELECT 
        l1.usercreateymd,
        COUNT(DISTINCT l2.accountid) as retention_count
    FROM login l1
    LEFT JOIN login l2 ON l1.accountid = l2.accountid 
        AND l1.usercreateymd = l2.usercreateymd
        AND l2.logymd = DATE_ADD(l1.usercreateymd, INTERVAL 1 DAY)
    GROUP BY l1.usercreateymd
),
-- 计算LTV1（首日付费总额/新增用户数）
ltv1 AS (
    SELECT 
        usercreateymd,
        SUM(day_pay)/COUNT(DISTINCT accountid) as ltv1_value
    FROM login 
    WHERE logymd = usercreateymd
    GROUP BY usercreateymd
),
-- 计算LTV2（首日+次日付费总额/新增用户数）
ltv2 AS (
    SELECT 
        usercreateymd,
        SUM(day_pay)/COUNT(DISTINCT accountid) as ltv2_value
    FROM login 
    WHERE logymd <= DATE_ADD(usercreateymd, INTERVAL 1 DAY)
    GROUP BY usercreateymd
)

-- 最终结果
SELECT 
    n.usercreateymd,
    n.new_user_count as '当日新增账号数',
    r.retention_count as '次日还有登录的账号数',
    ROUND(l1.ltv1_value, 2) as 'LTV1',
    ROUND(l2.ltv2_value, 2) as 'LTV2'
FROM new_users n
LEFT JOIN retention_users r ON n.usercreateymd = r.usercreateymd
LEFT JOIN ltv1 l1 ON n.usercreateymd = l1.usercreateymd
LEFT JOIN ltv2 l2 ON n.usercreateymd = l2.usercreateymd
ORDER BY n.usercreateymd DESC;

建表及测试数据

-- 创建登录表
CREATE TABLE login (
    usercreateymd DATE,
    accountid VARCHAR(50),
    day_pay DECIMAL(10,2),
    logymd DATE
);

-- 插入一些示例数据
INSERT INTO login (usercreateymd, accountid, day_pay, logymd) VALUES
-- 2018-06-22的新增用户
('2018-06-22', 'user1', 100.00, '2018-06-22'),  -- 首日登录+充值
('2018-06-22', 'user1', 50.00, '2018-06-23'),   -- 次日登录+充值
('2018-06-22', 'user2', 200.00, '2018-06-22'),  -- 首日登录+充值
('2018-06-22', 'user2', 0.00, '2018-06-23'),    -- 次日登录无充值
('2018-06-22', 'user3', 0.00, '2018-06-22'),    -- 首日登录无充值
-- 2018-06-23的新增用户
('2018-06-23', 'user4', 150.00, '2018-06-23'),  -- 首日登录+充值
('2018-06-23', 'user4', 75.00, '2018-06-24'),   -- 次日登录+充值
('2018-06-23', 'user5', 300.00, '2018-06-23'),  -- 首日登录+充值
('2018-06-23', 'user5', 0.00, '2018-06-24');    -- 次日登录无充值

4.日期为YYYYMMDD格式

-- 创建登录表
CREATE TABLE login (
    usercreateymd VARCHAR(8),    -- YYYYMMDD格式
    accountid VARCHAR(50),
    day_pay DECIMAL(10,2),
    logymd VARCHAR(8)           -- YYYYMMDD格式
);

-- 插入一些示例数据
INSERT INTO login (usercreateymd, accountid, day_pay, logymd) VALUES
-- 20180622的新增用户
('20180622', 'user1', 100.00, '20180622'), 
('20180622', 'user1', 50.00, '20180623'), 
('20180622', 'user2', 200.00, '20180622'),  
('20180622', 'user2', 0.00, '20180623'),   
('20180622', 'user3', 0.00, '20180622'),    
-- 20180623的新增用户
('20180623', 'user4', 150.00, '20180623'), 
('20180623', 'user4', 75.00, '20180624'),   
('20180623', 'user5', 300.00, '20180623'), 
('20180623', 'user5', 0.00, '20180624');   

-- SQL查询
WITH NewUserStats AS (
    -- 计算每日新增用户数
    SELECT 
        usercreateymd,
        COUNT(DISTINCT accountid) as new_users
    FROM login
    GROUP BY usercreateymd
),
RetentionStats AS (
    -- 计算次日留存用户数
    SELECT 
        l1.usercreateymd,
        COUNT(DISTINCT l2.accountid) as retained_users
    FROM login l1
    LEFT JOIN login l2 ON l1.accountid = l2.accountid 
        AND l1.usercreateymd = l2.usercreateymd 
        AND l2.logymd = DATE_FORMAT(DATE_ADD(STR_TO_DATE(l1.usercreateymd, '%Y%m%d'), INTERVAL 1 DAY), '%Y%m%d')
    WHERE l1.usercreateymd = l1.logymd
    GROUP BY l1.usercreateymd
),
LTVStats AS (
    -- 计算LTV1和LTV2
    SELECT 
        usercreateymd,
        -- LTV1: 新增当天的总充值/新增用户数
        SUM(CASE 
            WHEN logymd = usercreateymd 
            THEN day_pay ELSE 0 
        END) as ltv1_total,
        -- LTV2: 新增当天和次日的总充值/新增用户数
        SUM(CASE 
            WHEN logymd <= DATE_FORMAT(DATE_ADD(STR_TO_DATE(usercreateymd, '%Y%m%d'), INTERVAL 1 DAY), '%Y%m%d')
            THEN day_pay ELSE 0 
        END) as ltv2_total
    FROM login
    GROUP BY usercreateymd
)
SELECT 
    n.usercreateymd as create_date,
    n.new_users as new_user_count,
    IFNULL(r.retained_users, 0) as next_day_active_users,
    ROUND(IFNULL(l.ltv1_total / n.new_users, 0), 2) as ltv1,
    ROUND(IFNULL(l.ltv2_total / n.new_users, 0), 2) as ltv2
FROM NewUserStats n
LEFT JOIN RetentionStats r ON n.usercreateymd = r.usercreateymd
LEFT JOIN LTVStats l ON n.usercreateymd = l.usercreateymd
ORDER BY n.usercreateymd DESC;