描述

題目：現(xiàn)在運營想要查看用戶在某天刷題后第二天還會再來刷題的留存率。請你取出相應(yīng)數(shù)據(jù)。

示例：question_practice_detail

根據(jù)示例，你的查詢應(yīng)返回以下結(jié)果：

題目分析

需求：計算用戶在某天刷題后，第二天還會再來刷題的留存率。

什么是次日留存率？次日留存率 = (第一天刷題的用戶中，第二天也來刷題的用戶數(shù)) / (第一天刷題的總用戶數(shù))

示例解讀：根據(jù)示例數(shù)據(jù)，最終的平均留存率是 0.3000，即 30%。這意味著，在所有某天刷過題的用戶中，平均有 30% 的用戶會在第二天繼續(xù)刷題。

關(guān)鍵點：

1. 用戶標識：我們通過 device_id 來唯一標識一個用戶。
2. 行為日期：我們關(guān)心的是用戶刷題的日期 (date)。
3. 去重：一個用戶在同一天可能刷了多道題，但我們只關(guān)心他 “是否來過”，所以需要對 (device_id, date) 進行去重。
4. 關(guān)聯(lián)：我們需要將用戶第一天的行為和第二天的行為關(guān)聯(lián)起來，以判斷他是否留存。

解題思路

要計算留存率，我們需要明確兩個集合：A. 某日活躍用戶集合：在某天 date1 刷過題的用戶。B. 次日留存用戶集合：在集合 A 中的用戶，并且在 date1 的第二天（date1 + 1天）也刷過題。

留存率就是 |B| / |A|。

直接計算比較復(fù)雜，我們可以換個角度，為每一條 “某日刷題記錄” 匹配一條 “次日刷題記錄”，然后通過計數(shù)來求比率。

1. 構(gòu)建用戶每日刷題的唯一記錄：首先，我們需要一個只包含 (device_id, date) 唯一組合的數(shù)據(jù)集。
2. 自連接匹配次日記錄：將這個唯一記錄數(shù)據(jù)集與自身進行左連接（LEFT JOIN）。連接條件是：
   • A.device_id = B.device_id (同一個用戶)
   • B.date = A.date + INTERVAL 1 DAY (B 表的日期是 A 表日期的第二天)
3. 計算留存率：
   • 左連接的好處是，如果一個用戶在第二天沒有來，B.date 字段會是 NULL。
   • 因此，COUNT(B.date) 就等于 “第二天也來的用戶數(shù)”。
   • COUNT(A.date) 就等于 “第一天來的總用戶數(shù)”。
   • 兩者相除，就得到了我們想要的平均次日留存率。

分步解析最終代碼

-- 外層查詢：計算平均次日留存率
select count(date2)/count(date1) as avg_ret
from(
    -- 子查詢 A: 為每個用戶的每日刷題記錄匹配次日是否也刷題
    select distinct 
        qpd.device_id,
        qpd.date as date1,  -- 用戶當(dāng)天刷題的日期
        uniq_id_date.date as date2  -- 用戶次日是否刷題的日期（可能為NULL）
    from 
        question_practice_detail as qpd
    left join(
        -- 子查詢 B: 獲取所有用戶所有刷題日期的唯一記錄
        select distinct device_id, date
        from question_practice_detail
    )as uniq_id_date
    -- 左連接條件
    on qpd.device_id = uniq_id_date.device_id  -- 同一個用戶
    and date_add(qpd.date, interval 1 day) = uniq_id_date.date -- 次日
)as id_last_next_code;

1. 子查詢 B (uniq_id_date)

select distinct device_id, date
from question_practice_detail

• 目標：創(chuàng)建一個 “用戶 - 日期” 的唯一映射表。
• 作用：這個子查詢是整個邏輯的基石。它確保了我們處理的每一條記錄都代表一個用戶在某一天的一次獨立訪問行為，無論他當(dāng)天刷了多少道題。
• 結(jié)果示例（基于題目數(shù)據(jù)）：

  device_id	date
  2138	2021-05-03
  3214	2021-05-09
  3214	2021-06-15
  6543	2021-08-13
  2315	2021-08-13
  2315	2021-08-14
  ...	...

2. 子查詢 A (id_last_next_code)

select distinct 
    qpd.device_id,
    qpd.date as date1,
    uniq_id_date.date as date2
from 
    question_practice_detail as qpd
left join
    uniq_id_date
on 
    qpd.device_id = uniq_id_date.device_id
    and date_add(qpd.date, interval 1 day) = uniq_id_date.date

• 目標：這是核心的匹配步驟。對于原始表中的每一條刷題記錄（這里用 qpd 別名），我們都嘗試在 uniq_id_date 表中找到該用戶次日的刷題記錄。
• FROM question_practice_detail as qpd：我們從原始表開始，而不是從去重后的表開始。這是為了確保我們考慮到所有的 “刷題日”，即使一個用戶一天刷了多道題，distinct 會保證最終每 (device_id, date1) 只留下一條記錄。
• LEFT JOIN uniq_id_date：使用左連接至關(guān)重要。它會保留 qpd 表中的所有記錄。如果在 uniq_id_date 中找到了匹配的次日記錄，date2 就會有值；如果沒找到，date2 就會是 NULL。
• date_add(qpd.date, interval 1 day)：這是一個日期函數(shù)，用于計算 qpd.date 的第二天。
• select distinct ...：再次使用 distinct 是為了確保對于同一個用戶在同一天的多次刷題行為，我們在最終的派生表中只保留一條 (device_id, date1, date2) 記錄。
• 結(jié)果示例：

  device_id	date1	date2
  2138	2021-05-03	NULL	-- 2138 在 5 月 4 日沒有來
  3214	2021-05-09	NULL	-- 3214 在 5 月 10 日沒有來
  2315	2021-08-13	2021-08-14	-- 2315 在 8 月 14 日來了
  2315	2021-08-14	2021-08-15	-- 2315 在 8 月 15 日來了
  2315	2021-08-15	NULL	-- 2315 在 8 月 16 日沒有來
  ...	...	...

3. 外層查詢

select count(date2)/count(date1) as avg_ret
from id_last_next_code;

• 目標：計算最終的平均留存率。
• count(date2)：計算 id_last_next_code 表中 date2 字段不為 NULL 的行數(shù)。這正好等于 “次日留存的用戶行為次數(shù)”。
• count(date1)：計算 id_last_next_code 表中 date1 字段不為 NULL 的行數(shù)。這正好等于 “第一天的總用戶行為次數(shù)”。
• count(date2)/count(date1)：兩者相除，得到的就是平均次日留存率。
• 在我們的示例中，如果 count(date2) 是 3，count(date1) 是 10，那么結(jié)果就是 3/10 = 0.3。

總結(jié)

這道題的解法非常巧妙地運用了“自連接”和“左連接”的技巧。

1. 自連接：通過將一個去重后的 “用戶 - 日期” 表與自身連接，我們能夠在一條記錄里同時看到一個用戶在兩天的行為。
2. 左連接：通過左連接，我們確保了不會丟失任何一個 “第一天” 的用戶行為記錄，同時用 NULL 值清晰地標記出了哪些用戶沒有在 “第二天” 回來。
3. 計數(shù)與除法：最后，利用 COUNT() 函數(shù)對非空值的計數(shù)特性，輕松地計算出了分子（留存數(shù)）和分母（總活躍數(shù)），從而得到了最終的留存率。

這種模式在用戶行為分析中非常常見，例如計算 7 日留存、月留存等，核心思想都是類似的。

到此這篇關(guān)于SQL題目分析之計算用戶的平均次日留存率的文章就介紹到這了,更多相關(guān)SQL計算用戶平均次日留存率內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論