select count(q2.device_id) / count(q1.device_id) from ( #表中所有时间内刷过题的人数 select distinct device_id,date from question_practice_detail ) as q1 left joi_牛客网_牛客在手,offer不愁
实现: SELECTCOUNT(DISTINCTq2.device_id,q2.date)/count(DISTINCTq1.device_id,q1.date)asavg_retfromquestion_practice_detailasq1leftouterjoinquestion_practice_detailasq2onq1.device_id=q2.device_idandDATEDIFF(q2.date,q1.date)=1; 将两张表进行左外连接,连接规则是 用户id相同并且日期相差为1(确保连接...
SELECT count(distinct q2.device_id,q2.date)/count(distinct q1.device_id,q1.date) as avg_ret FROM question_practice_detail as q1 left outer join question_practice_detail as q2 on q1.device_id=q2.device_id and datediff(q2.date,q1.date)=1; 示例:question_practice_detail 根据示例,你的查询...
datediff(date1,date2),返回两个日期的间隔时间. select count(distinct q2.device_id,q2.date)/count(distinct q1.device_id,q1.date) as avg_ret from question_practice_detail as q1 left outer join question_practice_detail as q2 on q1.device_id=q2.device_id and datediff(q2.date,q1.date)=1;...
#用count(q2.device_id) / count(q1.device_id) 替换上面代码的*selectcount(q2.device_id)/count(q1.device_id)asavg_retfrom(selectdistinct device_id,datefromquestion_practice_detail)asq1 leftjoin(selectdistinct device_id,datefromquestion_practice_detail)asq2 ...
SELECT COUNT(DISTINCT q2.device_id, q2.date) / COUNT(DISTINCT q1.device_id, q1.date) AS avg_ret FROM question_practice_detail AS q1 LEFT JOIN question_practice_detail AS q2 ON q1.device_id = q2.device_id AND DATEDIFF(q2.date, q1.date) = 1; ...
SELECT COUNT(q2.device_id) / COUNT(q1.device_id) AS avg_ret FROM ( SELECT DISTINCT device_id, date FROM question_practice_detail ) AS q1 LEFT JOIN ( SELECT DISTINCT device_id, date FROM question_practice_detail ) AS q2 ON q1.device_id=q2.device_id AND q2.date = DATE_ADD(q1.date...
Activity table: +---+---+---+---+ | player_id | device_id | event_date | games_played | +---+---+---+---+ | 1 | 2 | 2016-03-01 | 5 | | 1 | 2 | 2016-05-02 | 6 | | 2 | 3 | 2017-06-25 | 1 | | 3 | 1 | 2016-03-02 | 0 | | 3 | 4 | 2018-...
1、distinct :单列去重 2、limit 偏移量,显示个数 :偏移量可不写 3、n between a and b : a <= n <= b 4、in(条件1,条件2...) : 有限个多选 5、like :匹配 6、max\count\avg\sum 7、 group by ... having ... : 1)是否需要对多个条件进行分组。
('01-apr-1997', 'dd-mon-yyyy')), PARTITION q2_1997 VALUES LESS THAN (TO_DATE('01-jul-1997', 'dd-mon-yyyy')), PARTITION q3_1997 VALUES LESS THAN (TO_DATE('01-oct-1997', 'dd-mon-yyyy')) (SUBPARTITION q3_1997_s1 TABLESPACE ts1, SUBPARTITION q3_1997_s2 TABLESPACE ts3, ...