最近在做题时碰到很多次最终的结果要计算“平均值”的情况。而这种平均值的分子往往是分组后各自有“几行”，分母往往是“组内有几个”（要去重）的问题
组内求和、计数很简单，我做题时很喜欢开窗出来看一下分组求和的结果，思维习惯使然，我总是不想在最开始就把题目抽象到去group by哪几列，我喜欢站在大局上去观察这个表，很可能我还需要去观察其他列的情况，一开始就用group by抽象掉太没意思了。
那么问题来了，如何解决count(distinct uid)只能用在普通聚合、无法运用在开窗函数的情况？
场景是这样的：
题目
有一张用户登录情况表（TB_USER_LOG）如上图，建表语句如下（神通数据库中时间戳为timestamp，sql、mysql中应为datetime，如需要，自行修改）：
1
2
3
4
5
6
7
8
| CREATE TABLE tb_user_log (
id INT PRIMARY KEY AUTO_INCREMENT,
uid INT NOT NULL,
artical_id INT NOT NULL,
in_time TIMESTAMP,
out_time TIMESTAMP,
sign_in TINYINT DEFAULT 0
);
|
测试数据：
1
2
3
4
5
6
| INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
(101, 9001, '2021-11-01 10:00:00', '2021-11-01 10:00:31', 0),
(102, 9001, '2021-11-01 10:00:00', '2021-11-01 10:00:24', 0),
(102, 9002, '2021-11-01 11:00:00', '2021-11-01 11:00:11', 0),
(101, 9001, '2021-11-02 10:00:00', '2021-11-02 10:00:50', 0),
(102, 9002, '2021-11-02 11:00:01', '2021-11-02 11:00:24', 0);
|
表结构：uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到
业务逻辑：artical_id-文章ID代表用户浏览的文章的ID，artical_id-文章ID为0表示用户在非文章内容页（比如App内的列表页、活动页等）。
问题：
统计2021年11月每天的人均浏览文章时长（秒数），结果保留1位小数，并按时长由短到长排序。
输出结果如图：
普通常规思路
最后结果是两列，dt及avg_viiew_len_sec
那么group by 部分肯定dt咯，前面去做聚合就行了。
1
2
3
4
5
6
| SELECT date_format(IN_TIME,'%Y-%m-%d') dt,
ROUND(SUM(datediff('ss',IN_TIME,OUT_TIME))/COUNT(DISTINCT UID),1) as avg_view_len_sec
FROM TB_USER_LOG
WHERE date_format(OUT_TIME,'%Y%m')='202111' AND ARTICAL_ID <>0
GROUP BY dt
ORDER BY avg_view_len_sec DESC;
|
如果我就是要用开窗做呢？
首先很容易想到的语句是这个：不管三七二十一先把那些可能用的上的列选上，sum来求一下时间差的和，语句和图分别为：
1
2
3
4
5
6
| SELECT ID,
UID,
ARTICAL_ID,
date_format(IN_TIME,'%Y-%m-%d') dt,
SUM(datediff('ss',IN_TIME,OUT_TIME)) OVER(PARTITION BY date_format(IN_TIME,'%Y-%m-%d')) sum_vt
FROM TB_USER_LOG;
|
那么下一步就很容易想到，接着上count啊，把uid里同一天的数出来有几个就好了，所以写成这样，当然是要报错的，distinct在窗口函数中不被支持：
1
2
3
4
5
6
7
| SELECT ID,
UID,
ARTICAL_ID,
date_format(IN_TIME,'%Y-%m-%d') dt,
SUM(datediff('ss',IN_TIME,OUT_TIME)) OVER(PARTITION BY date_format(IN_TIME,'%Y-%m-%d')) sum_vt,
COUNT(DISTINCT UID) OVER(PARTITION BY date_format(IN_TIME,'%Y-%m-%d')) cnt
FROM TB_USER_LOG;
|
理解dense_rank()
对于计算机来说，你要它去计数，它至少要分别得出两样东西，一是“这个东西是啥？”——字符串？int?二是“它跟其他的一样么？”——“A”与“B”是一个东西么？于是它与排序的关系是密不可分的，如果我们使用dense_rank()来对uid进行排序呢？别忘了，字符串也能排序，底层的ASCII编码极大的帮助了我们，A-Z,a-z,0-9顺序和我们的int也是极契合的。
1
2
3
4
5
6
7
| SELECT ID,
UID,
ARTICAL_ID,
date_format(IN_TIME,'%Y-%m-%d') dt,
DENSE_RANK() OVER(ORDER BY UID) rn,
SUM(datediff('ss',IN_TIME,OUT_TIME)) OVER(PARTITION BY date_format(IN_TIME,'%Y-%m-%d')) sum_vt
FROM TB_USER_LOG;
|
看，rn这列，UID为102的在同一天rn都为2，这样无论他有多少重复的uid都能”count”了
所以，你也想到了，**dense_rank() over()结合max() over()应该能解决我们的问题了**，来看一下最后的代码（没有选前面的id,uid,artical_id等）
1
2
3
4
5
6
7
8
9
10
| SELECT DISTINCT dt,
ROUND(sum_vt/MAX(rn) OVER(PARTITION BY dt),1) avg_view_len_sec
FROM (
SELECT date_format(IN_TIME,'%Y-%m-%d') dt,
DENSE_RANK() OVER(ORDER BY UID) rn,
SUM(datediff('ss',IN_TIME,OUT_TIME)) OVER(PARTITION BY date_format(IN_TIME,'%Y-%m-%d')) sum_vt
FROM TB_USER_LOG
WHERE date_format(OUT_TIME,'%Y%m')='202111' AND ARTICAL_ID <>0
)
ORDER BY avg_view_len_sec DESC;
|