分析函数 头疼

create table bricks (

  brick_id integer,

  colour   varchar2(10),

  shape    varchar2(10),

  weight   integer

);

insert into bricks values ( 1, 'blue', 'cube', 1 );

insert into bricks values ( 2, 'blue', 'pyramid', 2 );

insert into bricks values ( 3, 'red', 'cube', 1 );

insert into bricks values ( 4, 'red', 'cube', 2 );

insert into bricks values ( 5, 'red', 'pyramid', 3 );

insert into bricks values ( 6, 'green', 'pyramid', 1 );

commit;

计算表中的总行数。它返回一行

select count(*) from bricks;

添加 over 子句将其转换为解析。这将保留输入行。

select count(*) over () from bricks;

您查看所有其他列的值,您不能使用 group by

select b.*,

       count(*) over () total_count

from   bricks b;

分组依据 

group by 子句将行拆分为具有相同值的组。例如,以下获取每种颜色的行数和总重量

select colour, count(*), sum ( weight )

from   bricks

group  by colour;

 

使用 partition by 子句将输入分割为这样

select b.*,

       count(*) over (

         partition by colour

       ) bricks_per_colour,

       sum ( weight ) over (

         partition by colour

       ) weight_per_colour

from   bricks b;

 

 

 

Order By

按brick_id 对行进行排序。然后显示brick_id小于或等于当前行的行的总行数和重量之和:

select b.*,

       count(*) over (

         order by brick_id

       ) running_total,

       sum ( weight ) over (

         order by brick_id

       ) running_weight

from   bricks b;

 

分组+ 排序

组合 partition by 和 order by 子句以获得组内的行总数。

按颜色拆分行。然后获取每种颜色的运行行数和行重量

select b.*,

       count(*) over (

         partition by colour

         order by brick_id

       ) running_total,

       sum ( weight ) over (

         partition by colour

         order by brick_id

       ) running_weight

from   bricks b;

 

有几行具有相同的重量。因此,当您按此排序时,所有具有相同重量的行都具有相同的运行计数和重量

select b.*,

       count(*) over (

         order by weight

       ) running_total,

       sum ( weight ) over (

         order by weight

       ) running_weight

from   bricks b

order  by weight;

 

select b.*,

       count(*) over (

         order by weight

         rows between unbounded preceding and current row

       ) running_total,

       sum ( weight ) over (

         order by weight

         rows between unbounded preceding and current row

       ) running_weight

from   bricks b

order  by weight;

过滤分析函数

select colour from bricks

group  by colour

having count(*) >= 2;

 

不能在 where 中使用它

select colour from bricks

where  count(*) over ( partition by colour ) >= 2;

您必须在子查询中使用分析。然后在外部查询中过滤它 

select * from (

  select b.*,

         count(*) over ( partition by colour ) colour_count

  from   bricks b

)

where  colour_count >= 2;

 

上一个和下一个值(不显示第一行和不显示最后一行)

select b.*,

       lag ( shape ) over ( order by brick_id ) prev_shape,

       lead ( shape ) over ( order by brick_id ) next_shape

from   bricks b;

 

使用 first_value 和 last_value 获取有序集合中的第一个或最后一个值

first_value 的结果保持不变。但是对于 last_value 它每行都会改变。这是因为默认窗口子句在当前行停止。

select b.*,

       first_value ( weight ) over (

         order by brick_id

       ) first_weight_by_id,

       last_value ( weight ) over (

         order by brick_id

       ) last_weight_by_id

from   bricks b;

select b.*,

       first_value ( weight ) over (

         order by brick_id

       ) first_weight_by_id,

       last_value ( weight ) over (

         order by brick_id

         range between current row and unbounded following

       ) last_weight_by_id

from   bricks b;

 

 

 

 

 

 

 

 

 

 

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值