自链接算平均次日留存

Question

select count(t2.date)/count(t1.date) from
(select distinct device_id,date from question_practice_detail) t1
left join
(select distinct device_id,date from question_practice_detail) t2
on t1.device_id = t2.device_id
and t2.date = date_add(t1.date,interval 1 day)
这个表格链接的逻辑是啥啊
为啥链接键后面加and就可以直接连date_add函数
不应该where t2.date = date_add(t1.date,interval 1 day) 吗

Answer 1

1、表连接中ON中的条件与WHERE中的条件是有区别的，ON中的条件是在表关联时判定，WHERE中的条件是在表完成关联后进行数据筛选时判定（当然，可能不是所有数据库或所有情况都是这样的）
2、左/右连接时，一定不要把ON中的条件放到WHERE中，这意味着不同的SQL逻辑（内连接则结果一样），这里在ON中增加：t2.date = date_add(t1.date,interval 1 day)这个条件，表明右表即 t2 的记录要在结果集中保留，必须同时满足ON中的两个条件才行；而如果将 t2.date = date_add(t1.date,interval 1 day) 放到WHERE中，你的写的左连接的语法，执行的则是内连接的结果，统计结果必然100%，因为t2.date为null的情况全部被过滤掉了……