SQL162 2021年11月每天的人均浏览文章时长
场景逻辑说明:artical_id-文章ID代表用户浏览的文章的ID,artical_id-文章ID为0表示用户在非文章内容页(比如App内的列表页、活动页等)
问题:统计2021年11月每天的人均浏览文章时长(秒数),结果保留1位小数,并按时长由短到长排序
SELECT
dt,
ROUND(SUM(viiew_len_sec)/COUNT(DISTINCT uid),1) AS avg_viiew_len_sec
FROM
(
SELECT
uid,
DATE(in_time) AS dt,
TIMESTAMPDIFF(SECOND,in_time,out_time) AS viiew_len_sec
FROM tb_user_log
WHERE artical_id<>0
AND YEAR(in_time)=2021 AND MONTH(in_time)=11
) AS T
GROUP BY dt
ORDER BY avg_viiew_len_sec
SQL163 每篇文章同一时刻最大在看人数
问题:统计每篇文章同一时刻最大在看人数,如果同一时刻有进入也有离开时,先记录用户数增加再记录减少,结果按最大人数降序
WITH A AS
(
SELECT artical_id,in_time AS dt,1 AS diff
FROM tb_user_log
WHERE artical_id!=0
UNION ALL
SELECT artical_id,out_time AS dt,-1 AS diff
FROM tb_user_log
WHERE artical_id!=0
)
SELECT
artical_id,
MAX(uv) AS max_uv
FROM
(
SELECT artical_id,SUM(diff)over(partition by artical_id order by dt,diff DESC) AS uv
FROM A
) AS B
GROUP BY artical_id
ORDER BY max_uv DESC
SQL164 2021年11月每天新用户的次日留存率
问题:统计2021年11月每天新用户的次日留存率(保留2位小数)
次日留存率为当天新增的用户数中第二天又活跃了的用户数占比
如果in_time-进入时间和out_time-离开时间跨天了,在两天里都记为该用户活跃过,结果按日期升序
SELECT
A.dt,
ROUND(COUNT(second_day_user)/COUNT(new_user),2) AS uv_left_rate
FROM
(SELECT
uid AS new_user,
MIN(DATE(in_time)) AS dt
FROM tb_user_log
GROUP BY uid) AS A
LEFT JOIN
(SELECT
uid AS second_day_user,
DATE((in_time)) AS dt
FROM tb_user_log
UNION
SELECT
uid AS second_day_user,
DATE((out_time)) AS dt
FROM tb_user_log) AS B
ON new_user=second_day_user AND TIMESTAMPDIFF(DAY,A.dt,B.dt)=1
WHERE YEAR(A.dt)=2021 AND MONTH(A.dt)=11
GROUP BY A.dt
ORDER BY A.dt
SQL165 统计活跃间隔对用户分级结果
问题:统计活跃间隔对用户分级后,各活跃等级用户占比,结果保留两位小数,且按占比降序排序
用户等级标准简化为:忠实用户(近7天活跃过且非新晋用户)、新晋用户(近7天新增)、沉睡用户(近7天未活跃但更早前活跃过)、流失用户(近30天未活跃但更早前活跃过)。
假设今天就是数据中所有日期的最大值。
近7天表示包含当天T的近7天,即闭区间[T-6, T]
SELECT
user_grade,
ROUND(COUNT(uid)/MAX(user_cnt),2) AS ratio
FROM
(
SELECT
uid,
user_cnt,
(CASE
WHEN TIMESTAMPDIFF(DAY,first_use_time,to_day)<7 THEN '新晋用户'
WHEN TIMESTAMPDIFF(DAY,last_use_time,to_day) BETWEEN 7 AND 29 THEN '沉睡用户'
WHEN TIMESTAMPDIFF(DAY,last_use_time,to_day)>=30 THEN '流失用户'
ELSE '忠实用户' END ) AS user_grade
FROM
(
SELECT
uid,
DATE(MAX(out_time)) AS last_use_time,
DATE(MIN(in_time)) AS first_use_time
FROM tb_user_log
GROUP BY uid
) AS T1
LEFT JOIN
(
SELECT
DATE(MAX(out_time)) AS to_day,
COUNT(DISTINCT uid) AS user_cnt
FROM tb_user_log
) AS T2
ON 1=1
)AS T3
GROUP BY user_grade
ORDER BY ratio DESC
LEFT JOIN ON 1=1
left join on 1=1表示使用左连接将两个表连接起来,并且使用1=1作为连接条件(on 1=1)
这意味着任何非空的行都会被连接
通常用于将两个表连接在一起,而不需要指定实际的连接条件
可以用于需要额外添加列的情况
使用1=1作为连接条件的效果是将左表中的所有行与右表中的所有行匹配,因此结果集中将包含左表中的所有行和右表中的所有行,不管它们是否匹配
SQL166 每天的日活数及新用户占比
统计每天的日活数及新用户占比
新用户占比=当天的新用户数÷当天活跃用户数(日活数)
如果in_time-进入时间和out_time-离开时间跨天了,在两天里都记为该用户活跃过
新用户占比保留2位小数,结果按日期升序排序
SELECT
dt,
dau,
ROUND(IFNULL(new_user_cnt,0)/dau,2) AS uv_new_ratio
FROM
(
SELECT
DATE(time) AS dt,
COUNT(uid) AS new_user_cnt
FROM (SELECT DISTINCT uid,MIN(in_time) AS time FROM tb_user_log GROUP BY uid) AS T1
GROUP BY dt
) AS T2
RIGHT JOIN
(
SELECT
dt,
COUNT(uid) AS dau
FROM
(
SELECT
uid,
DATE(in_time) AS dt
FROM tb_user_log
UNION
SELECT
uid,
DATE(out_time) AS dt
FROM tb_user_log
) AS T3
GROUP BY dt
) AS T4
USING(dt)
ORDER BY dt
SQL167 连续签到领金币
从2021年7月7日0点开始,用户每天签到可以领1金币,并可以开始累积签到天数,连续签到的第3、7天分别可额外领2、6金币
每连续签到7天后重新累积签到天数(即重置签到天数:连续第8天签到时记为新的一轮签到的第一天,领1金币)
问题:计算每个用户2021年7月以来每月获得的金币数(该活动到10月底结束,11月1日开始的签到不再获得金币)。结果按月份、ID升序排序
注:如果签到记录的in_time-进入时间和out_time-离开时间跨天了,也只记作in_time对应的日期签到了
WITH t1 AS(
SELECT
DISTINCT uid,
DATE(in_time) AS dt,
RANK() OVER (PARTITION BY uid ORDER BY DATE(in_time)) AS rn
FROM tb_user_log
WHERE
DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31'
AND artical_id = 0 AND sign_in = 1
),
t2 AS (
SELECT
*,
CASE RANK() OVER (PARTITION BY DATE_SUB(dt,INTERVAL rn day),uid ORDER BY dt )%7
WHEN 3 THEN 3
WHEN 0 THEN 7
ELSE 1
END AS day_coin
FROM t1
)
SELECT
uid,
DATE_FORMAT(dt,'%Y%m') AS month,
SUM(day_coin) AS coin
FROM t2
GROUP BY uid,DATE_FORMAT(dt,'%Y%m')
ORDER BY DATE_FORMAT(dt,'%Y%m'),uid
INTERVAL
1. 当函数使用时,interval()为比较函数,如:interval(10,1,3,5,7) 结果4
原理:10为被比较数,后面1,3,5,7为比较数,将后面四个依次与10比较,看后面数字组有多少个少于10,则返回其个数。前提是后面数字组为从小到大排列,否则返回结果0
2. 当关键词使用时,表示为设置时间间隔,常用在date_add()与date_sub()函数里,
如:interval 1 day ,解释为将时间间隔设置为1天