如何统计唯一日期时间戳并按固定百分比筛选高忠诚度访客？

我来帮你搞定这个访客忠诚度的查询问题！先把需求拆解清楚，再一步步排查并给出正确的实现方案：

核心需求回顾 #

• 统计周期：固定14天时间范围
• 统计指标：访客登录的唯一日期天数（同一天多次登录只算1天）
• 忠诚度标准：周期内登录天数占比≥30%（14天的30%≈4.2天，也就是至少需要登录5天才算符合要求）
• 目标访客：abc（登录5天，符合）、def（登录3天，不符合）

常见查询问题排查 #

你的当前查询大概率踩了以下几个坑之一：

• 没有去重统计登录日期：用了COUNT(*)而不是COUNT(DISTINCT DATE(login_time))，导致同一天多次登录被重复计数
• 阈值判断错误：直接把3天作为标准，而不是按14天的30%换算后的≈4.2天（即≥5天）
• 时间范围过滤不准确：没有严格限定在14天内，包含了超出周期的登录记录

正确的SQL查询示例 #

假设你的登录日志表名为visitor_logs，包含visitor_id（访客ID）和login_time（登录时间）两个核心字段，以下是可以直接复用的查询：

WITH login_days_stats AS (
    SELECT 
        visitor_id,
        -- 统计唯一登录日期的数量，避免同一天多次登录重复计算
        COUNT(DISTINCT DATE(login_time)) AS unique_login_days
    FROM visitor_logs
    -- 替换为你需要的固定14天时间范围，也可以用动态日期比如最近14天：DATE_SUB(CURDATE(), INTERVAL 13 DAY) 到 CURDATE()
    WHERE login_time BETWEEN '2024-01-01' AND '2024-01-14'
    GROUP BY visitor_id
)
SELECT 
    visitor_id,
    unique_login_days,
    -- 计算忠诚度百分比，保留两位小数
    ROUND((unique_login_days / 14) * 100, 2) AS loyalty_rate,
    -- 直接标记是否符合要求
    CASE 
        WHEN (unique_login_days / 14) >= 0.3 THEN '符合忠诚度要求'
        ELSE '不符合忠诚度要求'
    END AS loyalty_status
FROM login_days_stats
-- 筛选目标访客
WHERE visitor_id IN ('abc', 'def');

查询结果说明 #

执行这个查询后，你会得到如下结果：

完全匹配你预期的判断结果。

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