group by 的妙用 ---- 优快云上一个帖子对我的启发

本文链接：https://blog.youkuaiyun.com/lmtz/article/details/464339

最近在优快云上看到了这样一个帖子：

原帖子地址： http://community.youkuaiyun.com/Expert/topic/4227/4227862.xml?temp=.9717371

其中的需求是这样的-------

数据表：
TestTime TestVal

2005/7/6 0:00:00         2
2005/7/6 0:00:10         5
2005/7/6 0:00:20         2

2005/7/6 0:00:30         2
2005/7/6 0:00:40         1
2005/7/6 0:00:50         3

2005/7/6 0:01:00         5
2005/7/6 0:01:10         3
2005/7/6 0:01:20         1

2005/7/6 0:01:30         1
2005/7/6 0:01:40         1
2005/7/6 0:01:40         4
        。               。
        。               。
        。               。

需要结果1：
说明：取每三条的TestVal的最大值，TestTime取最小值
TestTime TestVal

2005/7/6 0:00:00         5
2005/7/6 0:00:30         3
2005/7/6 0:01:00         5
2005/7/6 0:01:30         4

需要结果2：
说明：取每三条的TestVal的平均值，TestTime取最小值
TestTime TestVal

2005/7/6 0:00:00         3
2005/7/6 0:00:30         2
2005/7/6 0:01:00         3
2005/7/6 0:01:30         2

这个问题的确很让人头疼，但是最后终于有位高手给解决了，他在SQLSever中写了如下SQL语句：

--生成测试数据
create table #t(TestTime datetime,TestVal int)
insert into #t select '2005/07/06 00:00:00',2
insert into #t select '2005/07/06 00:00:10',5
insert into #t select '2005/07/06 00:00:20',2
insert into #t select '2005/07/06 00:00:30',2
insert into #t select '2005/07/06 00:00:40',1
insert into #t select '2005/07/06 00:00:50',3
insert into #t select '2005/07/06 00:01:00',5
insert into #t select '2005/07/06 00:01:10',3
insert into #t select '2005/07/06 00:01:20',1
insert into #t select '2005/07/06 00:01:30',1
insert into #t select '2005/07/06 00:01:40',1
insert into #t select '2005/07/06 00:01:40',4

--执行查询：取每三条的TestVal的最大值，TestTime取最小值
select
    TestTime = min(TestTime),
    TestVal = max(TestVal)
from
    (select
         a.*,
         cnt=isnull(count(b.TestTime),0)
     from
         #t a,
         #t b
     where
         a.TestTime>b.TestTime
     group by
         a.TestTime,a.TestVal) c
group by
     (c.cnt/3)

--输出结果
/*
TestTime              TestVal
-------------------   -------
2005-07-06 00:00:10   5
2005-07-06 00:00:30   3
2005-07-06 00:01:00   5
2005-07-06 00:01:30   4
*/

--执行查询：取每三条的TestVal的平均值，TestTime取最小值
select
    TestTime = min(TestTime),
    TestVal = avg(TestVal)
from
    (select
         a.*,
         cnt=isnull(count(b.TestTime),0)
     from
         #t a,
         #t b
     where
         a.TestTime>b.TestTime
     group by
         a.TestTime,a.TestVal) c
group by
     (c.cnt/3)

--输出结果
/*
TestTime              TestVal
-------------------   -------
2005-07-06 00:00:10   3
2005-07-06 00:00:30   2
2005-07-06 00:01:00   3
2005-07-06 00:01:30   2
*/

这位高手的思路是：先虚拟出按照TestTime排序后每一条记录的行号；

然后对这个行号进行分组。

受这个他的方法的启发，我写出了在Oracle中实现此需求的SQL语句，如下：

Select Min(newTestTable.TestTime),
             Max(newTestTable.TestVal)
From
             (Select TestTime, TestVal, row_number() over(order by TestTime) rowIndex From TestTable)
             newTestTable
Group By
( Floor( (newTestTable.rowIndex-1)/3 ) );

也得出了同样的结果，拿出来与大家一起讨论研究，看有没有更好的方式！