快捷搜索:
来自 网络数据库 2019-06-21 08:31 的文章
当前位置: 67677新澳门手机版 > 网络数据库 > 正文

新京葡娱乐场网址:server求分组最大值

 

/*********************************************************************************************

with s as
(
select name,max(age) maxAge,min(age) minAge from students
group by name
)
select name,max(maxAge) maxAge,max(maxAgeTime) maxAgeTime,max(minAge) minAge,max(minAgeTime) minAgeTime from (
select ss.name,s.maxAge,ss.addTime maxAgeTime,0 minAge, '' minAgeTime from students ss inner join s on ss.name=s.name and ss.age=s.maxAge
union all
select ss.name,0 maxAge , '' maxAgeTime,s.minAge minAge,ss.addTime minAgeTime from students ss inner join s on ss.name=s.name and ss.age=s.minAge
) a group by name

C1 C2 C3 CNT

     a         13
     b          6
     c          5
     d          2
     f           3
(4)按c3分组总结
SELECT NULL c1, NULL c2, c3, COUNT(c4) cnt
  FROM egb_tab
GROUP BY NULL, NULL, c3
ORDER BY NULL, NULL, c3

C1 C2 C3 CNT

 

 

CREATE TABLE [dbo].[Students](
[Id] [int] IDENTITY(1,1) NOT NULL,
[age] [int] NULL,
[name] [nvarchar](50) NULL,
[addTime] [datetime]  NULL
) ON [PRIMARY]

select id,area,score,
sum(score) over (partition by id order by area ) 连id续求和, --按照id内容汇总求和
sum(score) over (partition by id) id总和, --各id的分数总和
100*round(score/sum(score) over (partition by id),4) "id份额(%)",
sum(score) over () 总和, -- 此处sum(score) over () 等同于sum(score)
100*round(score/sum(score) over (),4) "份额(%)"
from students;

求一天以内的最大值最小值   最大值时间最小值时间

C1 C2 C3 CNT

                29 

4. CUBE
若cube参数的个数为n,则总计的情势有2^n种(表示2的n次方)。
假若 CUBE(p1,p2,...,p(n-1),p(n)),则分级按:
从p1,p2,...,p(n-1),p(n)中:
选1个分组,组合为C(n,1),
选2个分组,组合为C(n,2),
...
选n-1个分组,组合为C(n,n-1),
选n个分组,组合为C(n,n)
不分组,相当于C(n,0)
实行计算,故整个计算种数为:C(n,0)   C(n,1)   C(n,2)   ...   C(n,n-1)   C(n,n) = 2^n。

例子:
SELECT c1,c2,c3,COUNT(c4) cnt
FROM egb_tab
GROUP BY CUBE(c1,c2,c3)
ORDER BY c1,c2,c3;

<=>以下查询union all(需求剔除中间的order by)
(1)不分组总计
SELECT NULL c1, NULL c2, NULL c3, COUNT(c4)
  FROM egb_tab
GROUP BY NULL, NULL, NULL
ORDER BY NULL, NULL, NULL

select distinct name,
maxAge, max(case maxAgenum when 1 then addtime else '' end) over(partition by name) maxAddTime ,
minage,max(case minAgenum when 1 then addtime else '' end) over(partition by name) minAddTime
from (
select name,addtime,
max(age) over(partition by name) maxAge,
min(age) over(partition by name) minAge,
RANK() over(partition by name order by age desc) maxAgeNum ,
RANK() over(partition by name order by age ) minAgeNum from students
) s

oracle深入分析函数--SQL*PLUS环境
--1、GROUP BY子句 

 

/*而对于last_value() over(order by id),结果是有失常态的,因为大家尚无遵守id分区,所以应当出去的效应应该全套是90(最终一条)。

INSERT [dbo].[Students] ([age], [name], [addTime]) VALUES (22, N'李四', '2015-04-08 01:00:00.000')
INSERT [dbo].[Students] ([age], [name], [addTime]) VALUES (8, N'李四', '2017-05-03 00:00:00.000')
INSERT [dbo].[Students] ([age], [name], [addTime]) VALUES (98, N'李四', '2017-10-03 00:00:00.000')
INSERT [dbo].[Students] ([age], [name], [addTime]) VALUES (34, N'张三', '2016-09-08 00:00:00.000')
INSERT [dbo].[Students] ([age], [name], [addTime]) VALUES (45, N'张三','2011-05-08 00:00:00.000')
INSERT [dbo].[Students] ( [age], [name], [addTime]) VALUES (5, N'张三', '2014-04-01 00:00:00.000')

C1 C2 C3 CNT

x  a 1  1
x  a 2  2
x  a 3  3
x  b 4  2
x  b 5  1
x  c 6  2
y  a 1  2
y  a 2  2
y  a 3  1
y  b 4  2
y  b 5  1
y  c 6  1
y  c 7  2
y  d 8  1
y  d 9  1
z  a 1  1
z  a 2  1
z  f  6  2
z  f  7  1
(2)按c1,c2分组计算
SELECT c1, c2, NULL c3, COUNT(c4) cnt
  FROM egb_tab
GROUP BY c1, c2, NULL
ORDER BY c1, c2, NULL;

 

select * from (
select a, null, null, sum( d ) from t group by a
union all
select null, b, null, sum( d ) from t group by b 
union all
select null, null, c, sum( d ) from t group by c 
)
*/

依照上边又拉开出来二个新须要。

create table test(xm varchar2(20),sfzhm varchar2(20));

 新京葡娱乐场网址 1

1 row inserted

安排几条测试数据

/*从上面的结果中大家很轻易发觉,各种总计数据所对应的行都会并发null,
如何来分别到底是基于那多少个字段做的聚集呢,grouping函数决断是不是合计列!*/

结果如下图:

1 row inserted

新京葡娱乐场网址 2

等效于

  这种写法用到了窗口函数,窗口函数的表现描述出现在函数的OVER子句中,并涉嫌几个成分,3个为主成分分别是:分区,排序和框架

C1 C2 C3 CNT

x  a 1  1
x  a 2  2
x  a 3  3
x  b 4  2
x  b 5  1
x  c 6  2
y  a 1  2
y  a 2  2
y  a 3  1
y  b 4  2
y  b 5  1
y  c 6  1
y  c 7  2
y  d 8  1
y  d 9  1
z  a 1  1
z  a 2  1
z  f 6  2
z  f 7  1

3. ROLLUP
若rollup的参数个数为n,则总计的法门有n 1种。
假若 ROLLUP(p1,p2,...,p(n-1),p(n)),则分别按
p1,p2,..,p(n-1),p(n)
p1,p2,...p(n-1)
... ...
p1
NULL
分组总计,当中 NULL 表示不分组。

例子:
SELECT c1, c2, c3, COUNT(c4) cnt
  FROM egb_tab
GROUP BY ROLLUP(c1, c2, c3)
ORDER BY c1, c2, c3;

 

insert into students values(1, '111', 'g', 80 );
insert into students values(1, '111', 'j', 80 );
insert into students values(1, '222', 'g', 89 );
insert into students values(1, '222', 'g', 68 );
insert into students values(2, '111', 'g', 80 );
insert into students values(2, '111', 'j', 70 );
insert into students values(2, '222', 'g', 60 );
insert into students values(2, '222', 'j', 65 );
insert into students values(3, '111', 'g', 75 );
insert into students values(3, '111', 'j', 58 );
insert into students values(3, '222', 'g', 58 );
insert into students values(3, '222', 'j', 90 );
insert into students values(4, '111', 'g', 89 );
insert into students values(4, '111', 'j', 90 );
insert into students values(4, '222', 'g', 90 );
insert into students values(4, '222', 'j', 89 );
commit;

先是种写法:

Commit complete

select name,d '日期',maxAge,maxAddTime,minage,minAddTime from
(
          select distinct name,d,
          maxAge, max(case maxAgenum when 1 then addtime else '' end) over(partition by name,d) maxAddTime ,
          minage,max(case minAgenum when 1 then addtime else '' end) over(partition by name,d) minAddTime
          from
         (
              select name,addtime,DAY(addtime) d,
              max(age) over(partition by name,DAY(addtime)) maxAge,
              min(age) over(partition by name,DAY(addtime)) minAge,
              RANK() over(partition by name,DAY(addtime) order by age desc) maxAgeNum ,
              RANK() over(partition by name,DAY(addtime) order by age ) minAgeNum from students where addtime>='2017-5-1' and addtime<'2017-6-1'
         ) s
) a order by name,d

oracle解析函数 

 

C1 C2 C3 CNT

x  a 1  1
x  a 2  2
x  a 3  3
x  a     6
x  b 4  2
x  b 5  1
x  b     3
x  c 6  2
x  c     2
x       11 
y  a 1  2
y  a 2  2
y  a 3  1
y  a     5
y  b 4  2
y  b 5  1
y  b     3
y  c 6  1
y  c 7  2
y  c     3
y  d 8  1
y  d 9  1
y  d     2
y       13 
z  a 1  1
z  a 2  1
z  a     2
z  f 6   2
z  f 7   1
z  f      3
z         5
         29 
<=> 以下查询union all(必要剔除中间的order by)
(1)按c1,c2,c3分组计算
SELECT c1, c2, c3, COUNT(c4) cnt
  FROM egb_tab
GROUP BY c1, c2, c3
ORDER BY c1, c2, c3;

新京葡娱乐场网址 3

/*ID LAST_VALUE(SCORE)OVER(ORDERBYR SCORE
---------------- ------------------------------ ----------------------
1 80 80.00
1 80 80.00
1 89 89.00
1 68 68.00
2 80 80.00
2 70 70.00
2 60 60.00
2 65 65.00
3 75 75.00
3 58 58.00
3 58 58.00
3 90 90.00
4 89 89.00
4 90 90.00
4 90 90.00
4 89 89.00

先创建Students表

ID MC SL MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)KEEP(DENSE_RANKFIRSTORD MIN(MC)KEEP(DENSE_RANKLASTORDE MAX(MC)KEEP(DENSE_RANKLASTORDE
-------------------- -------------------- ------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
1 111 1 111 222 555 666
1 222 1 111 222 555 666
1 333 2 111 222 555 666
1 555 3 111 222 555 666
1 666 3 111 222 555 666
2 111 1 111 222 333 555
2 222 1 111 222 333 555
2 333 2 111 222 333 555
2 555 2 111 222 333 555

第三种写法:

SQL> insert into test values('5');

 

select id,rank() over(partition by id order by score desc) rk,score from students;

新京葡娱乐场网址 4

--查找重复记录的办法,除了用count(*),还是能够用row_number()等函数完毕

 

select cume_dist() over(order by id) a, --该组最大row_number/全数记录row_number 
row_number() over (order by id) rn,id,area,score from students;

--5、FIRST_VALUE()、LAST_VALUE()

INSERT INTO TEST VALUES(5,6);
INSERT INTO TEST VALUES(6,7);
INSERT INTO TEST VALUES(7,8);
INSERT INTO TEST VALUES(8,NULL);

SQL> insert into test values('8');

SQL> insert into test values('1');

select id,first_value(score) over(order by id) fv,score from students;

不要混淆keep内(first、last)外(min、max也许其余):
min是足以对应last的
max是足以对应first的

INSERT INTO egb_tab VALUES('x','c','6','xc6');

INSERT INTO egb_tab VALUES('y','a','1','xa1');
INSERT INTO egb_tab VALUES('y','a','1','xa1');
INSERT INTO egb_tab VALUES('y','a','2','xa1');
INSERT INTO egb_tab VALUES('y','a','2','xa1');
INSERT INTO egb_tab VALUES('y','a','3','xa1');
INSERT INTO egb_tab VALUES('y','b','4','xb2');
INSERT INTO egb_tab VALUES('y','b','4','xb2');
INSERT INTO egb_tab VALUES('y','b','5','xb2');
INSERT INTO egb_tab VALUES('y','c','6','xa1');
INSERT INTO egb_tab VALUES('y','c','7','xb2');
INSERT INTO egb_tab VALUES('y','c','7','xb2');
INSERT INTO egb_tab VALUES('y','d','8','xb2');

*********************************************************************************************/

ID MC SL MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)KEEP(DENSE_RANKFIRSTORD MIN(MC)KEEP(DENSE_RANKLASTORDE MAX(MC)KEEP(DENSE_RANKLASTORDE
-------------------- -------------------- ------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
1 111 1 111 222 555 666
1 222 1 111 222 555 666
1 333 2 111 222 555 666
1 555 3 111 222 555 666
1 666 3 111 222 555 666

Table created

SQL>

=========================================================== 
作者: zhouwf0726()
发表于:2006.07.25 12:51
分类: oracle开发 
出处:
--------------------------------------------------------------- 

--允许并列排名、排行不间断
select id,dense_rank() over(partition by id order by score desc) rk,score from students;

再看个例证就清楚了:*/
select id,last_value(score) over(order by rownum),score from students;

SQL> select id,mc,sl,
2 min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
3 max(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
4 min(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id),
5 max(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id)
6 from test
7 ;

select id,last_value(score) over(order by rownum RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),score from students;

SQL> 
SQL> select id,mc,sl,
2 min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
3 max(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id)
4 from test
5 ;

C1 C2 C3 CNT

x  a   6
x  b   3
x  c   2
y  a   5
y  b   3
y  c   3
y  d   2
z   a   2
z   f   3 
(3)按c1分组计算
SELECT c1, NULL c2, NULL c3, COUNT(c4) cnt
  FROM egb_tab
GROUP BY c1, NULL, NULL
ORDER BY c1, NULL, NULL;

/*
ID LAST_VALUE(SCORE)OVER(ORDERBYR SCORE
---------------- ------------------------------ ----------------------
1 89 80.00
1 89 80.00
1 89 89.00
1 89 68.00
2 89 80.00
2 89 70.00
2 89 60.00
2 89 65.00
3 89 75.00
3 89 58.00
3 89 58.00
3 89 90.00
4 89 89.00
4 89 90.00
4 89 90.00
4 89 89.00

--以下是选择剖判函数的四个总结解答:
--start with connect by能够参见

select id,lag(score,1,0) over(order by id) lg,score from students;

--给出三个例证再一次领悟深入分析函数

/*--------理解cube
select a, b, c, sum( d ) from t
group by cube( a, b, c)

--C、CUBE

SQL> insert into test values('1');

--D、GROUPING

--4、LAG(COL,n,default)、LEAD(OL,n,default) --取前后面N条数据

等效于

SQL> select id,mc,sl,
2 min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
3 max(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
4 min(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id),
5 max(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id)
6 from test
7 ;

SQL> create table test(id varchar2(20));

1 row inserted

有些人对oracle深入分析函数中select over(partition by col1 order by col2) from test order by ...关于partition by 和 组内order by以及最终的order by的推行各类发生嫌疑。

等效于

min(mc) keep (DENSE_RANK first OEvoqueDEXC90 BY sl) over(partition by id):id等于1的数码不大的(DENSE_RANK first )为
1 111 1 
1 222 1 
在那一个结果中取min(mc) 正是111
max(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id)
取max(mc) 就是222;
min(mc) keep (DENSE_RANK last OLX570DE途观 BY sl) over(partition by id):id等于1的数目最大的(DENSE_RANK first )为
1 555 3 
1 666 3 
在这几个结果中取min(mc) 正是222,取max(mc)就是666

/*****************深入分析函数的总括顺序难点*************/

越来越多探讨看之下地方:

col score format 999999999999.99

在keep (DENSE_RANK first O奥迪Q7DEENVISION BY sl) 结果集中再取max、min的例证。

本文由67677新澳门手机版发布于网络数据库,转载请注明出处:新京葡娱乐场网址:server求分组最大值

关键词: