求SQL查询语句：计算一周内用户跨日期重复元素的占比

数据表示例 #

需求说明 #

需计算一周内每个用户（id）重复看到的元素的占比，同一日期内的重复浏览不计入：

1. 同一用户在同一天多次浏览同一元素，仅计1次有效访问
2. 重复访问指用户在不同日期再次浏览之前看过的元素
3. 占比 = 重复访问次数 / 总有效访问次数

预期结果 #

错误尝试的SQL #

SELECT
  dt,
  id,
  COUNT(DISTINCT element) AS total,
  (
    SELECT COUNT(DISTINCT element)
    FROM table newt
    WHERE newt.dt = dt
  ) AS same_day_elements,
  COUNT(DISTINCT element) / (SELECT COUNT(DISTINCT element) FROM table tempt WHERE date = tempt.date) * 100 AS percentage
FROM
  table t
GROUP BY
  dt,
  id

正确的SQL查询 #

-- 第一步：去重，保留每个用户每天每个元素的唯一访问记录
WITH unique_visits AS (
    SELECT DISTINCT id, element, dt
    FROM your_table_name
    -- 若需限制为最近一周，添加以下条件（根据dt字段的实际格式调整日期函数）
    -- WHERE STR_TO_DATE(dt, '%d/%m/%y') >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
),
-- 第二步：统计每个用户对每个元素的跨日期访问次数
element_visit_counts AS (
    SELECT id, element, COUNT(*) AS visit_times
    FROM unique_visits
    GROUP BY id, element
),
-- 第三步：计算每个用户的总有效访问次数和重复访问次数
user_stats AS (
    SELECT
        id,
        SUM(visit_times) AS total_valid_visits,
        -- 重复访问次数 = 每个元素的访问次数-1之和（首次访问不算重复）
        SUM(visit_times - 1) AS repeat_visits
    FROM element_visit_counts
    GROUP BY id
)
-- 第四步：计算占比，处理总有效次数为0的情况
SELECT
    id,
    CASE
        WHEN total_valid_visits = 0 THEN 0.0
        -- 按示例保留1位小数，可根据需求调整ROUND的参数
        ELSE ROUND(repeat_visits / total_valid_visits, 1)
    END AS percentage
FROM user_stats
ORDER BY id;

逻辑解释 #

1. unique_visits：对(id, element, dt)去重，确保同一用户同一天对同一元素的多次访问只保留1条，满足“同一日期内重复浏览不计入”的要求。
2. element_visit_counts：统计每个用户对每个元素的跨日期访问次数，比如用户1对元素a在3个不同日期访问过，这里计数为3。
3. user_stats：计算每个用户的总有效访问次数（所有元素的跨日期访问次数之和），以及重复访问次数（每个元素的访问次数减1的总和，因为第一次访问该元素不算重复，后续的每次都算重复）。
4. 最后计算占比，用重复访问次数除以总有效访问次数，并用ROUND函数控制小数位数，同时处理总有效次数为0的边界情况。

匹配示例结果的调整 #

若需要完全匹配示例中的结果，需将总有效次数改为用户去重前的原始记录数：

-- 修改user_stats部分的total_valid_visits计算
user_stats AS (
    SELECT
        uv.id,
        -- 统计原始表中用户的总记录数
        (SELECT COUNT(*) FROM your_table_name t WHERE t.id = uv.id) AS total_valid_visits,
        SUM(uv.visit_times - 1) AS repeat_visits
    FROM element_visit_counts uv
    GROUP BY uv.id
)

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