1、具體問題

我們經(jīng)常會遇到需要分組排序求最大值最小值的問題。舉個實際的例子，原始數(shù)據(jù)有兩列，一列是人員姓名，一列是工作時間（精確到秒），要求每個人每天最早的工作時間，最晚的工作時間，以及時間跨度（小時）。

image.png

這個問題在數(shù)據(jù)庫中用SQL語言非常容易解決，但是大部分情況下我們手頭的工具只有EXCEL，所以我提供了以下兩個解決方式：1）SQL語言 2）Excel公式

2、SQL語言

分組排序以及求最大值最小值，SQL中最常用就是用窗口函數(shù)，將上表導(dǎo)入數(shù)據(jù)庫中，將列重命名為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

運(yùn)行結(jié)果如下表所示，每個人每個日期就是一個分組，排序是組內(nèi)排序，時間跨度的單位為小時，用的是datediff函數(shù)。

?row_number() over(partition by)就是分組排序函數(shù)，partition后面加分組的字段，本問題里面分組有兩個字段，一個是人員姓名，一個是日期，這個日期是精確到天，所以要用to_date函數(shù)進(jìn)行處理。

?max() over(partition by) 是分組求最大值函數(shù)。

image.png

3、EXCEL公式

用excel公式處理數(shù)據(jù)主要分為以下五個步驟：

（1）求工作時間的日期

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

image.png

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

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

image.png

（3）分組排序

用函數(shù)=IF(D2=D1,E1+1,1)

image.png

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

這里要用數(shù)據(jù)函數(shù)，輸入公式之后要按ctrl+shift+enter，同理可以求每組最小值，用公式=MIN(IF(D199=D2,B199))

image.png

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

image.png