当前位置： 移动技术网 > IT编程>数据库>Oracle > Oracle之 LISTAGG () OVER(PARTITION BY)函数

Oracle之 LISTAGG () OVER(PARTITION BY)函数

2020年07月30日  | 移动技术网IT编程  | 我要评论

文章目录

基础方法介绍

拼接的字符串没有超过4000字符

SELECT department_id AS "Dept.",
       LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) AS "Employees"
  FROM employees
  GROUP BY department_id
  ORDER BY department_id;


SELECT department_id AS "Dept.",
       LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) 
       OVER (PARTITION BY department_id) AS "Employees"
  FROM employees
  ORDER BY department_id;

拼接的字符串超过4000字符，转为CLOB 字段处理


SELECT department_id AS "Dept.",
      xmlagg(xmlparse(content  last_name||’,’ wellformed )  ORDER BY hire_date).getclobval()
       AS "Employees"
  FROM employees
  GROUP BY department_id;

ORA-01489: result of string concatenation is too long 解决思路

Oracle 12.2 以及以上

select listagg(product_name, ',' on overflow truncate) within group (order by product_name)
from products;

Oracle 11.2至12.1

分析函数可以生成字符串聚合的运行总长度。然后，内联视图可以删除长度大于4000的所有值。
在实际查询中，可能需要在分析函数添加partition by 以仅对每个组进行计数。

select
    --Save a little space for a ' ...' to imply that there is more data not shown.
    case when max(total_length) > 3996 then
        listagg(product_name, ', ') within group (order by product_name)||
            ' ...'
    else
        listagg(product_name, ', ') within group (order by product_name)
    end product_names
from
(
    --Get names and count lengths.
    select
        product_name,
        --Add 2 for delimiters.
        sum(length(product_name) + 2) over (order by product_name) running_length,
        sum(length(product_name) + 2) over () total_length
    from products
    order by product_name
)
where running_length <= 3996

OVER(PARTITION BY)函数相关

测试数据如下：

create table T2_TEMP(
    NAME varchar2(10) primary key,
    CLASS varchar2(10),
    SROCE NUMBER
)

insert into T2_TEMP (NAME, CLASS, SROCE)
values ('cfe', '2', 74);

insert into T2_TEMP (NAME, CLASS, SROCE)
values ('dss', '1', 95);

insert into T2_TEMP (NAME, CLASS, SROCE)
values ('ffd', '1', 95);

insert into T2_TEMP (NAME, CLASS, SROCE)
values ('fda', '1', 80);

insert into T2_TEMP (NAME, CLASS, SROCE)
values ('gds', '2', 92);

insert into T2_TEMP (NAME, CLASS, SROCE)
values ('gf', '3', 99);

insert into T2_TEMP (NAME, CLASS, SROCE)
values ('ddd', '3', 99);

insert into T2_TEMP (NAME, CLASS, SROCE)
values ('adf', '3', 45);

insert into T2_TEMP (NAME, CLASS, SROCE)
values ('asdf', '3', 55);

insert into T2_TEMP (NAME, CLASS, SROCE)
values ('3dd', '3', 78);

（1）、查询每个班的第一名的成绩：如下

注意：在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果。

1 SELECT * FROM (select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm from T2_TEM

（2）、rank()和dense_rank()可以将所有的都查找出来，rank可以将并列第一名的都查找出来；rank()和dense_rank()区别：rank()是跳跃排序，有两个第二名时接下来就是第四名。dense_rank()是连续排序，有两个第二名时仍然跟着第三名

select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

select t.name,t.class,t.sroce,dense_rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

(3)

select t.name,t.class,t.sroce,sum(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

(4)

select t.name,t.class,t.sroce,first_value(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;
select t.name,t.class,t.sroce,last_value(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

count() over(partition by … order by …)：求分组后的总数。
max() over(partition by … order by …)：求分组后的最大值。
min() over(partition by … order by …)：求分组后的最小值。
avg() over(partition by … order by …)：求分组后的平均值。
lag() over(partition by … order by …)：取出前n行数据。　　
lead() over(partition by … order by …)：取出后n行数据。
ratio_to_report() over(partition by … order by …)：Ratio_to_report() 括号中就是分子，over() 括号中就是分母。
percent_rank() over(partition by … order by …)：

本文地址：https://blog.csdn.net/QQ736238785/article/details/107674204

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网