SQL语句求教，比如我要写11月1日-11月10日每一天的新用户的次日留存率，这个SQL应该怎么处理？谢谢

[zoroskj]

SQL语句求教，比如我要写11月1日-11月10日每一天的新用户的次日留存率，这个SQL应该怎么处理？

[OwenZeng_DBA]

把表结构和示例数据发来看看先

[zoroskj]

引用 1 楼 z10843087 的回复:

把表结构和示例数据发来看看先

比如说是这样的表

[道素]

是下面这个意思吗？ 我理解的留存率是，注册的新用户，到了第二天还有几个在用

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
CREATE TABLE #t(uuid VARCHAR(100),is_new INT,[day] VARCHAR(100),platform VARCHAR(100)) 
INSERT INTO #t
SELECT '11D1CE99-61D0-4776-94FF-8185DB777BD6',1,CONVERT(VARCHAR,GETDATE(),110),'A' UNION ALL
SELECT '11D1CE99-61D0-4776-94FF-8185DB777BD6',0,CONVERT(VARCHAR,DATEADD(d,1,GETDATE()),110),'A' UNION ALL
SELECT '11D1CE99-61D0-4776-94FF-8185DB777BD6',1,CONVERT(VARCHAR,DATEADD(d,2,GETDATE()),110),'B' UNION ALL
SELECT 'EC4F916A-8322-4321-A466-81AA605C94BB',1,CONVERT(VARCHAR,GETDATE(),110),'A' UNION ALL
SELECT 'EC4F916A-8322-4321-A466-81AA605C94BB',0,CONVERT(VARCHAR,DATEADD(d,1,GETDATE()),110),'A' UNION ALL
SELECT 'CCEFE470-7342-48A7-813C-B57081A5CC61',1,CONVERT(VARCHAR,GETDATE(),110),'A' UNION ALL
SELECT '596B7AF6-6960-48E0-AEA5-F891E4217324',1,CONVERT(VARCHAR,GETDATE(),110),'B' 

SELECT t.platform,COUNT(nt.uuid) AS RemainUserNextDay,COUNT(t.uuid) AS NewUserCount ,COUNT(nt.uuid)*1.0/COUNT(t.uuid) AS RemainRateNextDay
FROM #t AS t
LEFT JOIN #t AS nt ON t.platform=nt.platform and nt.uuid=t.uuid AND DATEDIFF(d,t.[day],nt.[day])=1 AND nt.is_new=0
WHERE t.is_new=1
GROUP BY t.platform

+----------+-------------------+--------------+-------------------+
| platform | RemainUserNextDay | NewUserCount | RemainRateNextDay |
+----------+-------------------+--------------+-------------------+
| A        | 2                 | 3            | 0.666666666666    |
| B        | 0                 | 2            | 0                 |
+----------+-------------------+--------------+-------------------+

[zoroskj]

引用 3 楼 ch21st 的回复:

是下面这个意思吗？ 我理解的留存率是，注册的新用户，到了第二天还有几个在用

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
CREATE TABLE #t(uuid VARCHAR(100),is_new INT,[day] VARCHAR(100),platform VARCHAR(100)) 
INSERT INTO #t
SELECT '11D1CE99-61D0-4776-94FF-8185DB777BD6',1,CONVERT(VARCHAR,GETDATE(),110),'A' UNION ALL
SELECT '11D1CE99-61D0-4776-94FF-8185DB777BD6',0,CONVERT(VARCHAR,DATEADD(d,1,GETDATE()),110),'A' UNION ALL
SELECT '11D1CE99-61D0-4776-94FF-8185DB777BD6',1,CONVERT(VARCHAR,DATEADD(d,2,GETDATE()),110),'B' UNION ALL
SELECT 'EC4F916A-8322-4321-A466-81AA605C94BB',1,CONVERT(VARCHAR,GETDATE(),110),'A' UNION ALL
SELECT 'EC4F916A-8322-4321-A466-81AA605C94BB',0,CONVERT(VARCHAR,DATEADD(d,1,GETDATE()),110),'A' UNION ALL
SELECT 'CCEFE470-7342-48A7-813C-B57081A5CC61',1,CONVERT(VARCHAR,GETDATE(),110),'A' UNION ALL
SELECT '596B7AF6-6960-48E0-AEA5-F891E4217324',1,CONVERT(VARCHAR,GETDATE(),110),'B' 

SELECT t.platform,COUNT(nt.uuid) AS RemainUserNextDay,COUNT(t.uuid) AS NewUserCount ,COUNT(nt.uuid)*1.0/COUNT(t.uuid) AS RemainRateNextDay
FROM #t AS t
LEFT JOIN #t AS nt ON t.platform=nt.platform and nt.uuid=t.uuid AND DATEDIFF(d,t.[day],nt.[day])=1 AND nt.is_new=0
WHERE t.is_new=1
GROUP BY t.platform

+----------+-------------------+--------------+-------------------+
| platform | RemainUserNextDay | NewUserCount | RemainRateNextDay |
+----------+-------------------+--------------+-------------------+
| A        | 2                 | 3            | 0.666666666666    |
| B        | 0                 | 2            | 0                 |
+----------+-------------------+--------------+-------------------+

感谢，理解了。