单表复杂查询的场景分析二：涉及数据分组与分区/多重函数计算/SQL变种

SQL演练，带详细分析，笔记和备忘。行文不易，感谢支持！

本文是单表下的复杂场景问题分析，具体看下面的每个需求。

接上文，本文为连载的第二篇。

数据表及说明

需求8：找出指定月份每个人的总时长低于该月份所有人平均总时长的人

方案1

方案2

需求9：基于所有数据找出每个人的最新1天的记录

需求10：查询每个人(name)在该表中时长最长的一条记录,结果按时长降序排序 (分组内取最大)

方案1

方案2

方案3

需求11：基于所有数据找出每人每天的最新一条记录

数据表及说明

创建一个数据表demo_records，内容如下：

该表记录了每个人在每天做某事情的的消耗时长记录。为方便配合后续的问题场景，已有测试数据方面也做了对应特征的设置。

每人每天可能会产生多条记录，每个不同记录的时长可能会相等，duration为整数，创建时间为时刻具体时间不是天级别。

大致总结下该表的主要信息：

是四个人在三天中所消耗的时长记录，王五、孙悟空、沙僧各4条，哪吒3条。
最大时长为孙悟空在3.3号的记录。
哪吒的最大时长为5，记录有两条，分别在3.2、3.4
孙悟空在3.2号有两条记录
时长最小值为3，有三条记录。

需求8：找出指定月份每个人的总时长低于该月份所有人平均总时长的人

1，计算3月份所有人的总时长值及其平均值

2，查询每个人在3月份的总时长值

3，将每个人的3月份总score与之前计算的平均总score进行比较

方案1

SELECT name, SUM(duration) AS monthlyDurationTotalEveryPeople
FROM  demo_records
WHERE month(created_at) = 3
GROUP BY  name
HAVING  monthlyDurationTotalEveryPeople < 
(
       SELECT AVG(totalDuration) AS avgTotalDuration
                FROM(   
                           SELECT SUM(duration) AS totalDuration  
                           FROM demo_records 
                           WHERE month(created_at) = 3 
                           GROUP BY  name
         )t
)

方案2

with monthlyDurationAvgEveryPeople AS (
SELECT AVG(totalDuration) AS avgTotalDuration
         FROM(   
                    SELECT SUM(duration) AS totalDuration  
                    FROM demo_records 
                    WHERE month(created_at) = 3 
                    GROUP BY  name
  )t
)
SELECT name, SUM(duration) AS monthlyDurationTotalEveryPeople
FROM  demo_records
WHERE month(created_at) = 3
GROUP BY  name
HAVING  monthlyDurationTotalEveryPeople < (
       SELECT avgTotalDuration from monthlyDurationAvgEveryPeople
)

结果如下

方案2是先将3月份所有人的总时长、总时长的平均值，结果放在表达式中，接着按既定条件聚合，聚合结果中与monthlyDurationAvgEveryPeople对比，实际逻辑和方案1一样，只是查询形式不同

因为最后是和每个人月总时长的平均值进行比较，因此在求平均值时需要先group by name，否则就是64除以1还是64，我们需要的是64除以4。

需求9：基于所有数据找出每个人的最新1天的记录

结果有四条记录。

SELECT t.id, t.name, t.duration, t.created_at
FROM (
    SELECT id, name, duration, created_at,
           ROW_NUMBER() OVER (
                    PARTITION BY name ORDER BY created_at desc
           ) AS durationFirst
    FROM demo_records 
) t
WHERE t.durationFirst = 1

结果如下：

注意不要使用 DATE(created_at)='2022-03-04'来查，虽然结果可能一致但sql语义不对。

需求10：查询每个人(name)在该表中时长最长的一条记录,结果按时长降序排序 (分组内取最大)

这里我们先列举几个错误的SQL语句：

select id,name,max(duration) d,created_at
from demo_records
group by name
order by d desc

或

select id,name,max(duration) as d,created_at
from demo_records r
group by name
having d=max(duration)
order by d desc

第二个比第一个多了个条件过滤。

这条语句从所有记录中按人分组并求各自最大值，name和最大值是匹配的，即如果仅是把人和最大值查出来，那么这个语句是符合的，但目前我们需要查询的是符合的每条记录，这样查出来id、时间和其它两列数据不匹配，查出来数据是错误的。不正确的原因是group by按name分组后记录数会变少，而我们并没有按id、时间等字段分组，因此这两字段成了多选一，自然不正确，其它字段和max(duration内容不匹配。

也贴一下结果：

2，使用子查询

select a.id,a.name,a.duration,a.created_at  
from demo_records a,(
       select id,name,max(duration) as d,created_at from demo_records
       group by name) r
where a.id=r.id and a.created_at = r.created_at

或

select a.id,a.name,a.duration,a.created_at  
from demo_records a
join(
       select id,name,max(duration) as d,created_at from demo_records
       group by name) b
on a.id = b.id

这几个语句可能很多人都思考过，内部子查询的语句实际和上一条语句一样，已经group by name了，但是又select了其余全部字段，查出来数据必然不匹配。

3，这个可能最迷茫

select a.id,a.name,a.duration,a.created_at  
from demo_records a
join(
       select name,max(duration) d from demo_records
       group by name) b
on a.duration  = b.d and a.name=b.name
order by duration  desc

结果如下：

首先此语句的查询结果内的每条数据是匹配的，但是结果看着问题不大SQL就一定正确吗？

来分析一下：

首先子查询内部查询了最大时长和对应的人，数据正确，然后和本表连接，为了找到人和最大时长在表中对应的记录，所以采用了这个条件：

on a.duration  = b.d and a.name=b.name

即这个结果包含了正确结果（为什么是包含？因为需求是求每个人时长最长的一条记录，而哪吒最长的有两条记录）

但这个条件查出来的是未经分组的，需要再加一个按人分组才行，即方案1。

方案1

select a.id,a.name,a.duration,a.created_at  
from demo_records a
join(
       select name,max(duration) d from demo_records
       group by name) b
on a.duration  = b.d and a.name=b.name
group by name
order by duration  desc

查询结果：

如果对结果中最大时长的记录没有特别要求（如哪吒最大时长是两条记录，结果中要不要求具体显示哪条？），那么此时该语句和结果均是正确的。

方案2

SELECT t.id, t.name, t.maxDuration, t.created_at
FROM (
    SELECT id, name, duration, created_at,
           max(duration) OVER (PARTITION BY name) AS maxDuration
    FROM demo_records 
    ORDER BY created_at desc
) t     
where t.duration =t.maxDuration
group by name
ORDER BY duration desc

分析

首先基于OVER (PARTITION BY name)对已有数据按名称分区，接着基于现有分组后的数据、增加max(duration)来计算每个name中的最大时长，相当于增加了的maxDuration列在每个分组下是当前name的最大值；

分步看，只执行子查询，结果如下：

接着将当前查询结果记为t，取出时长和最大时长相等的记录，最终按时长降序排序。

注意到时长相等的可能有多条记录，因此结果中要处理同name的记录只保留一条，即group by name

此语句在group之前拿到的数据中会是5条记录（哪吒符合条件的有两条记录），即每人每天时长记录本身可能有多条，相同时长的更可能有多条，因此需要group一下。经过最后分组之后，数据准确。

ORDER BY created_at desc可加可不加，加上只是为了在相同时长下拿到的时长是时间最新的时长。

最终查询结果：

方案3

SELECT t.id, t.name, t.duration maxDuration, t.created_at
FROM (
    SELECT id, name, duration, created_at,
           ROW_NUMBER() OVER (
                    PARTITION BY name ORDER BY duration desc
           ) AS durationFirst
    FROM demo_records 
) t
WHERE t.durationFirst = 1
ORDER BY duration desc

这个语句细心的会发现没有出现group by，那是怎么分组的？答案就是PARTITION BY

查询结果：

区别：

方案3子查询内部必须基于时长来排序（因为最终要编号并获取每个分区内的1号），且最终的ORDER BY duration desc是必要的，因为需求是要按时长字段降序排序，因此最终相同时长的记录不一定是时间最新的记录；方案2兼顾到了相同时长的记录获得时间最新的记录。

对于这个需求来说，上述三个方案均正确，可按实际情况选择。

需求11：基于所有数据找出每人每天的最新一条记录

SELECT  t.id, t.name, t.duration, t.created_at
FROM (
    SELECT id, name, duration, created_at,
           ROW_NUMBER() OVER (
                    PARTITION BY name, DATE(created_at) ORDER BY created_at desc
           ) AS daily_num
    FROM demo_records
) t
WHERE t.daily_num = 1

上述SQL中，DATE函数可直接转换具体时间为天；

详细过程分三步走：

1、PARTITION BY可将数据按名称、天级别时间来进行分区；分区后面的order by表示分区了的数据按创建时间降序排序，便于找出每个分区最新的一条记录。通俗的说就是把每人每天的数据各自单独放在了一起，各自内部已按创建时间降序排序了。

2、ROW_NUMBER() OVER()窗口函数可对已产生的所有分区的每个记录（每行）分配一个编号，由于每个分区内部已经排好序，编号从1开始，因此最新的那条记录为1。以实际数据来说明，孙悟空有四条数据，其中在3月2日有两条分别是10点、15点，那么孙悟空在3月2日15点的这条记录将被编号为1，10点的那条记录被编号为2。

3、编号完成后，过滤daily_num = 1的即可。（如果要获得最新两天的，则变更where条件daily_num = 2）

结果如下：