hive中datediff函数跨年计算异常是因为其2.x版本底层采用字段级伪计算：将日期截断为字符串后按年、月、日整数减法并粗略加权（如year×365+month×30+day），完全忽略闰年、大小月及日历规则，导致datediff('2023-01-01','2022-12-31')返回2而非1。
用 COUNT(DISTINCT) 做分母时，为什么结果总偏高？
因为没限定「首日必须落在观察期内的新用户」。直接 COUNT(DISTINCT user_id) 某天所有登录用户，会把历史老用户、跨期注册用户全算进来，分母失真。
正确做法是先圈定基准日范围，再取这批用户的首次行为时间。例如只分析 2026-05-01 新注册用户，则分母必须是：
SELECT COUNT(DISTINCT user_id) FROM register_info WHERE DATE(register_time) = '2026-05-01'
常见错误包括：
- 用行为表
login_info的date当注册日，混淆新增与活跃 - 没加
WHERE过滤注册时间，导致分母含多日用户 - 忽略时区——
register_time是 UTC，但业务口径按东八区，需先CONVERT_TZ(register_time, '+00:00', '+08:00')再DATE()
LAG() 和 ROW_NUMBER() 该选哪个来标首次行为？
要看你是否需要保留首条记录的完整字段（比如渠道、设备类型）。MIN(login_date) OVER (PARTITION BY user_id) 只给日期，ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) 才能带出整行上下文。
实操建议：
- 如果只关心「哪天首次登录」，用
MIN(event_time)窗口函数，性能更好 - 如果要关联首次登录的
source或device_id，必须用ROW_NUMBER()，且ORDER BY event_time不可写成DESC - 毫秒级时间戳建议先
CAST(event_time AS DATE)再排序，避免同日多次行为被误判为不同天 - 务必加
WHERE event_time IS NOT NULL，否则ROW_NUMBER()可能排到 NULL 行前面
分桶分析时，GROUP BY 错在哪？
直接 GROUP BY DATE_SUB(login_date, INTERVAL first_login DAY) 看似合理，但会漏掉「零留存」用户——那些首日注册后第二天完全没登录的人，在日志里压根不出现，GROUP BY 自然无法统计。
解决方法不是硬补空行，而是用条件聚合提前标记存在性：
SELECT
first_day,
COUNT(DISTINCT user_id) AS new_users,
COUNT(DISTINCT CASE WHEN DATEDIFF(login_date, first_day) = 1 THEN user_id END) AS d1_retained,
COUNT(DISTINCT CASE WHEN DATEDIFF(login_date, first_day) = 7 THEN user_id END) AS d7_retained
FROM (
SELECT
r.user_id,
DATE(r.register_time) AS first_day,
DATE(l.login_time) AS login_date
FROM register_info r
LEFT JOIN login_info l
ON r.user_id = l.user_id
AND DATE(l.login_time) BETWEEN DATE(r.register_time) AND DATE_ADD(DATE(r.register_time), INTERVAL 7 DAY)
WHERE DATE(r.register_time) = '2026-05-01'
) t
GROUP BY first_day
关键点：
-
LEFT JOIN保证注册用户都在，哪怕没登录记录 -
ON里限制右表日期范围，避免笛卡尔积 -
CASE WHEN ... THEN user_id END配合COUNT(DISTINCT)，自动跳过 NULL，不干扰分母 - 别在
WHERE里过滤login_date，否则会把未留存用户踢掉
MySQL 和 Hive 的 DATEDIFF 顺序不一致，怎么防踩坑？
MySQL 是 DATEDIFF(expr1, expr2) = expr1 - expr2；Hive 是 datediff(dt1, dt2) = dt1 - dt2，表面一样，但实际常因字段类型隐式转换翻车。
最稳妥写法：
- 统一转成
DATE类型：DATEDIFF(CAST(login_date AS DATE), CAST(first_day AS DATE))（MySQL）或datediff(to_date(login_date), to_date(first_day))（Hive） - 加
WHERE DATEDIFF(...) >= 0过滤未来时间（埋点延迟上报常见） - 测试时用固定
user_id查看排序和差值，比如WHERE user_id = 'u_12345' - 别依赖字符串截取（如
SUBSTR(login_date, 1, 10)），丢失精度且跨数据库不兼容
真正容易被忽略的是：分桶边界必须对齐业务定义。比如「7日留存」指注册后第 8 天登录，不是「7天内任意一天」——差这一天，结果可能偏差 15% 以上。
