【SQL&Excel技巧】实现分组排序求最大值最小值

1、具体问题

我们经常会遇到需要分组排序求最大值最小值的问题。举个实际的例子，原始数据有两列，一列是人员姓名，一列是工作时间（精确到秒），要求每个人每天最早的工作时间，最晚的工作时间，以及时间跨度（小时）。

image.png

这个问题在数据库中用SQL语言非常容易解决，但是大部分情况下我们手头的工具只有EXCEL，所以我提供了以下两个解决方式：1）SQL语言 2）Excel公式

2、SQL语言

分组排序以及求最大值最小值，SQL中最常用就是用窗口函数，将上表导入数据库中，将列重命名为name和work_date，表明取为temp_table，具体代码如下：

select t.name as `人员`

,t.work_date as `时间`

,t.work_day as `日期`

,t.rank as `排序`

,t.max_date as `每组最大值`

,t.min_date as `每组最小值`

,datediff(hour,t.min_date,t.max_date) as `时间跨度`

from

(

select name

,work_date

,to_date(work_date,'YYYY-MM-DD') as work_day

,row_number() over(partition by name,to_date(work_date,'YYYY-MM-DD') order by work_date) as rank

,max(work_date) over(partition by name,to_date(work_date,'YYYY-MM-DD')) as max_date

,min(work_date) over(partition by name,to_date(work_date,'YYYY-MM-DD')) as min_date

from temp_table

)t

运行结果如下表所示，每个人每个日期就是一个分组，排序是组内排序，时间跨度的单位为小时，用的是datediff函数。

​row_number() over(partition by)就是分组排序函数，partition后面加分组的字段，本问题里面分组有两个字段，一个是人员姓名，一个是日期，这个日期是精确到天，所以要用to_date函数进行处理。

​max() over(partition by) 是分组求最大值函数。

image.png

3、EXCEL公式

用excel公式处理数据主要分为以下五个步骤：

（1）求工作时间的日期

用公式=TEXT(B2,"yyyy-mm-dd")

image.png

（2）第二步，合并分组信息

即人员姓名和日期，用公式=A2&C2

image.png

（3）分组排序

用函数=IF(D2=D1,E1+1,1)

image.png

（4）求每组最大值，用公式=MAX(IF(D199=D2,B199))

这里要用数据函数，输入公式之后要按ctrl+shift+enter，同理可以求每组最小值，用公式=MIN(IF(D199=D2,B199))

image.png

（5）最后求一下时间跨度(小时)，用公式=HOUR(F2-G2)

image.png