1: 留存率统计分析
SELECT
log_day AS `日期`,
COUNT(user_id_day0) AS `新增数量`,
COUNT(user_id_day1) / COUNT(user_id_day0) AS `次日留存率`,
COUNT(user_id_day2) / COUNT(user_id_day0) AS `3日留存率`,
COUNT(user_id_day7) / COUNT(user_id_day0) AS `7日留存率`,
COUNT(user_id_day30) / COUNT(user_id_day0) AS `30日留存率`
FROM (
SELECT DISTINCT log_day,
a.user_id_day0,
b.user_id AS user_id_day1,
c.user_id AS user_id_day3,
d.user_id AS user_id_day7,
e.user_id AS user_id_day30
FROM (
SELECT DISTINCT
DATE(log_time) AS log_day,
user_id AS user_id_day0
FROM t_user_login
WHERE login_device = 'Android'
GROUP BY user_id
ORDER BY log_day
) a
LEFT JOIN t_user_login b ON DATEDIFF(DATE(b.log_time), a.log_day) = 1
AND a.user_id_day0 = b.user_id
LEFT JOIN t_user_login c ON DATEDIFF(DATE(c.log_time), a.log_day) = 2
AND a.user_id_day0 = c.user_id
LEFT JOIN t_user_login d ON DATEDIFF(DATE(d.log_time), a.log_day) = 6
AND a.user_id_day0 = d.user_id
LEFT JOIN t_user_login e ON DATEDIFF(DATE(e.log_time), a.log_day) = 29
AND a.user_id_day0 = e.user_id
) temp
GROUP BY log_day;
2: 计算直播同时在线人数最大值
分析思路：
- 将上线时间作为 +1，下线时间作为 -1
- 运用
UNION ALL 合并两列
- 利用
SUM() OVER() 对其分区排序，求出不同时间段的人数
- 对时间进行分组，求出所有时间点中最大的同时在线总人数
-- 1) 对数据分类,在开始数据后添加正1,表示有主播上线,同时在关播数据后添加-1,表示有主播下线
SELECT id, stt AS dt, 1 AS p FROM test
UNION ALL
SELECT id, edt AS dt, -1 AS p FROM test;
-- 2) 按照时间排序,计算累加人数
SELECT
t1.id,
t1.dt,
SUM(p) OVER(PARTITION BY DATE_FORMAT(t1.dt, 'yyyy-MM-dd') ORDER BY t1.dt) AS sum_p
FROM (
SELECT id, stt AS dt, 1 AS p FROM test
UNION ALL
SELECT id, edt AS dt, -1 AS p FROM test
) t1;
-- 3) 找出同时在线人数最大值
SELECT
DATE_FORMAT(t2.dt, 'yyyy-MM-dd') AS `date`,
MAX(sum_p) AS con
FROM (
SELECT
t1.id,
t1.dt,
SUM(p) OVER(PARTITION BY DATE_FORMAT(t1.dt, 'yyyy-MM-dd') ORDER BY t1.dt) AS sum_p
FROM (
SELECT id, stt AS dt, 1 AS p FROM test
UNION ALL
SELECT id, edt AS dt, -1 AS p FROM test
) t1
) t2
GROUP BY DATE_FORMAT(t2.dt, 'yyyy-MM-dd');
3: 区间分段统计
建表和数据准备：
CREATE TABLE tmp_tc.tmp_test_20230303 (
name STRING COMMENT '姓名',
number STRING COMMENT '比赛场次',
score INT COMMENT '成绩',
address STRING COMMENT '地址',
dt STRING COMMENT '日期'
)
STORED AS ORC TBLPROPERTIES ("orc.compress"="SNAPPY");
WITH tmp AS (
SELECT '张三' AS name, '1' AS number, 30 AS score, '北京' AS address, '20220202' AS dt UNION ALL
SELECT '张三' AS name, '2' AS number, 28 AS score, '上海' AS address, '20220207' AS dt UNION ALL
SELECT '张三' AS name, '3' AS number, 36 AS score, '广州' AS address, '20220212' AS dt UNION ALL
SELECT '张三' AS name, '4' AS number, 57 AS score, '深圳' AS address, '20220217' AS dt UNION ALL
SELECT '张三' AS name, '5' AS number, 19 AS score, '南京' AS address, '20220222' AS dt UNION ALL
SELECT '张三' AS name, '6' AS number, 22 AS score, '武汉' AS address, '20220227' AS dt UNION ALL
SELECT '张三' AS name, '7' AS number, 32 AS score, '成都' AS address, '20220304' AS dt UNION ALL
SELECT '张三' AS name, '8' AS number, 23 AS score, '厦门' AS address, '20220309' AS dt
)
INSERT OVERWRITE TABLE tmp_tc.tmp_test_20230303
SELECT * FROM tmp_tc.tmp_test_20230303
DISTRIBUTE BY RAND();
统计分析：
WITH tmp AS (
SELECT *,
SUM(score) OVER(PARTITION BY name ORDER BY dt) AS total_score,
COALESCE(SUM(score) OVER(PARTITION BY name ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS total_score2
FROM tmp_tc.tmp_test_20230303
),
tmp2 AS (
-- 区间分数两者之间 上半部分
SELECT
name,
number,
score,
address,
dt,
CASE
WHEN FLOOR(total_score / 100) = 0 THEN '1-100'
WHEN FLOOR(total_score / 100) = 1 THEN '101-200'
WHEN FLOOR(total_score / 100) = 2 THEN '201-300'
ELSE '300+'
END AS tag,
(CEIL(total_score2 / 100) * 100 - total_score2) AS tag_score
FROM tmp
WHERE FLOOR(total_score2 / 100) != FLOOR(total_score / 100)
UNION ALL
-- 区间分数两者之间 下半部分
SELECT
name,
number,
score,
address,
dt,
CASE
WHEN FLOOR(total_score / 100) = 0 THEN '1-100'
WHEN FLOOR(total_score / 100) = 1 THEN '101-200'
WHEN FLOOR(total_score / 100) = 2 THEN '201-300'
ELSE '300+'
END AS tag,
(score - (CEIL(total_score2 / 100) * 100 - total_score2)) AS tag_score
FROM tmp
WHERE FLOOR(total_score2 / 100) != FLOOR(total_score / 100)
UNION ALL
-- 区间内分数
SELECT
name,
number,
score,
address,
dt,
CASE
WHEN FLOOR(total_score / 100) = 0 THEN '1-100'
WHEN FLOOR(total_score / 100) = 1 THEN '101-200'
WHEN FLOOR(total_score / 100) = 2 THEN '201-300'
ELSE '300+'
END AS tag,
score AS tag_score
FROM tmp
WHERE !(FLOOR(total_score2 / 100) != FLOOR(total_score / 100))
)
SELECT
name,
number,
score,
address,
dt,
tag,
tag_score
FROM tmp2
ORDER BY dt, tag_score;
4: Hive中怎么统计array中非零的个数？
方案一：
SELECT LENGTH(TRANSLATE(CONCAT_WS(',', ARRAY('0', '1', '3', '6', '0')), ',0', ''));
方案二：
SELECT COUNT(a)
FROM (
SELECT EXPLODE(ARRAY('0', '1', '3', '6', '0')) AS a
) t
WHERE a != '0';
5: 断点重分组问题分析
数据准备：
uid start_time end_time num
1 2020-02-18 14:20:30 2020-02-18 14:46:30 20
1 2020-02-18 14:47:20 2020-02-18 15:20:30 30
1 2020-02-18 15:37:23 2020-02-18 16:05:26 40
1 2020-02-18 16:06:27 2020-02-18 17:20:49 50
1 2020-02-18 17:21:50 2020-02-18 18:03:27 60
2 2020-02-18 14:18:24 2020-02-18 15:01:40 20
2 2020-02-18 15:20:49 2020-02-18 15:30:24 30
2 2020-02-18 16:01:23 2020-02-18 16:40:32 40
2 2020-02-18 16:44:56 2020-02-18 17:40:52 50
3 2020-02-18 14:39:58 2020-02-18 15:35:53 20
3 2020-02-18 15:36:39 2020-02-18 15:24:54 30
完整SQL实现：
WITH tmp AS (
SELECT
uid,
start_time,
end_time,
LAG(end_time, 1, NULL) OVER(PARTITION BY uid ORDER BY start_time) AS pre_end_time
FROM t
)
SELECT
uid,
MIN(start_time) AS start_time,
MAX(end_time) AS end_time,
SUM(num) AS amount
FROM (
SELECT
uid,
start_time,
end_time,
num,
SUM(flag) OVER(PARTITION BY uid ORDER BY start_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS groupid
FROM (
SELECT
uid,
start_time,
end_time,
num,
IF(UNIX_TIMESTAMP(start_time) - NVL(UNIX_TIMESTAMP(pre_end_time), UNIX_TIMESTAMP(start_time)) < 10 * 60, 0, 1) AS flag
FROM tmp
) o1
) o2
GROUP BY uid, groupid;
6: SQL重叠交叉区间问题分析
思路：
- 根据相差的天数生成序列值
- 根据索引生成时间补齐所有时间段值
- 去掉重复值，计算剩余点个数
SELECT
id,
COUNT(DISTINCT DATE_ADD(stt, pos - 1)) AS day_count
FROM (
SELECT id, stt, edt FROM brand
) tmp LATERAL VIEW POSEXPLODE(
SPLIT(SPACE(DATEDIFF(edt, stt) + 1), '')
) t AS pos, val
WHERE t.pos <> 0
GROUP BY id;
7: SQL之存在性问题分析
数据准备：
dt stu_id
2020-01-02 1001
2020-01-02 1002
2020-02-02 1001
2020-02-02 1002
2020-02-02 1003
2020-02-02 1004
2020-03-02 1001
2020-03-02 1002
2020-04-02 1005
2020-05-02 1006
完整SQL实现：
SELECT month,
SUM(lag_month_cnt) OVER(ORDER BY month)
FROM (
SELECT month,
LAG(next_month_cnt, 1, 0) OVER(ORDER BY month) AS lag_month_cnt
FROM (
SELECT DISTINCT t0.month AS month,
SUM(IF(!ARRAY_CONTAINS(t1.lag_stu_id_arr, t0.stu_id), 1, 0)) OVER(PARTITION BY t0.month) AS next_month_cnt
FROM (
SELECT SUBSTR(day, 1, 7) AS month,
stu_id
FROM stu
) t0
LEFT JOIN (
SELECT month,
LEAD(stu_id_arr, 1) OVER(ORDER BY month) AS lag_stu_id_arr
FROM (
SELECT SUBSTR(day, 1, 7) AS month,
COLLECT_LIST(stu_id) AS stu_id_arr
FROM stu
GROUP BY SUBSTR(day, 1, 7)
) m
) t1
ON t0.month = t1.month
) n
) o;
8: 水位线思想在解决SQL复杂场景问题中的应用与研究
解决思路一：
SELECT *
FROM (
SELECT *,
SUM(COALESCE(id, 0)) OVER(ORDER BY ts) AS water_mark
FROM test01
) t
WHERE water_mark != 0;
11: SQL之定位连续区间的起始位置和结束位置
第一步：
SELECT
a.log_id,
a.log_id - ROW_NUMBER() OVER(ORDER BY a.log_id) AS rn
FROM Logs a;
第二步：
SELECT
MIN(a.log_id) AS start_id,
MAX(a.log_id) AS end_id
FROM (
SELECT
a.log_id,
a.log_id - ROW_NUMBER() OVER(ORDER BY a.log_id) AS rn
FROM Logs a
) a
GROUP BY a.rn;
end
ckckck2025年1月10日25 浏览