In some cases, it is necessary to aggregate data from number of rows into a single row, giving a list of data associated with a specific value. There are some methods to achieve this.
- LISTAGG Analystic Function in 11g Release 2
The LISTAGG analytic function was introduced in Oracle 11g Release 2, making it very easy to aggregate strings.
Example:
SELECT type, LISTAGG(oid, ',') FROM rcp_company group by type;
- WM_CONCAT function
If you are running a version of the database where WM_CONCAT function is available, then use this function to aggregate strings. Be note that this function is undocumented, and Tom doesn’t suggest to use it.
Example:
SELECT type, wm_concat(oid) AS oids FROM rcp_company group by type;
- Generic Function using Ref Cursor
Write a generic function to concatenate values passed using a ref cursor.
Example:
create or replace function join
(
pv_cursor sys_refcursor,
pv_del varchar2 := ','
) return varchar2
is
lv_value varchar2(4000);
lv_result varchar2(4000);
begin
loop
fetch pv_cursor into lv_value;
exit when pv_cursor%notfound;
if lv_result is not null then
lv_result := lv_result || pv_del;
end if;
lv_result := lv_result || lv_value;
end loop;
return lv_result;
end join;
The cursor function is used as below:
select type,
join(CURSOR (select t2.oid from rcp_company t2 where t2.type = t1.type)
from rcp_company t1
group by type;
- User-Defined Aggregate Function
Create a user-defined aggregate function, using the ODCIAggregate interface.
Exmaple:
CREATE OR REPLACE TYPE gtyp_strcat_object IS OBJECT
(
catstr VARCHAR2(32767),
STATIC FUNCTION odciaggregateinitialize(cs_ctx IN OUT gtyp_strcat_object)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate(SELF IN OUT gtyp_strcat_object,
VALUE IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge(SELF IN OUT gtyp_strcat_object,
ctx2 IN OUT gtyp_strcat_object)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate(SELF IN OUT gtyp_strcat_object,
returnvalue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER
)
;
/
CREATE OR REPLACE TYPE BODY gtyp_strcat_object IS
STATIC FUNCTION odciaggregateinitialize(cs_ctx IN OUT gtyp_strcat_object)
RETURN NUMBER IS
BEGIN
cs_ctx := gtyp_strcat_object(NULL);
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregateiterate(SELF IN OUT gtyp_strcat_object,
VALUE IN VARCHAR2) RETURN NUMBER IS
BEGIN
SELF.catstr := SELF.catstr || ',' || VALUE;
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregateterminate(SELF IN OUT gtyp_strcat_object,
returnvalue OUT VARCHAR2,
flags IN NUMBER) RETURN NUMBER IS
BEGIN
returnvalue := ltrim(rtrim(SELF.catstr, ','), ',');
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregatemerge(SELF IN OUT gtyp_strcat_object,
ctx2 IN OUT gtyp_strcat_object)
RETURN NUMBER IS
BEGIN
SELF.catstr := SELF.catstr || ',' || ctx2.catstr;
RETURN odciconst.success;
END;
END;
/
CREATE OR REPLACE FUNCTION fun_agg_strcat(pv_str varchar2) RETURN varchar2
PARALLEL_ENABLE
AGGREGATE USING gtyp_strcat_object;
The aggregate function is implemented using a type and type body, and is used within a query as below:
select type,fun_agg_strcat(oid) from rcp_company group by type
- Hierarchical function
Use SYS_CONNECT_BY_PATH functions to achieve the same result without the use of PL/SQL or additional type definitions.
Example:
select type, ltrim(sys_connect_by_path(sp.oid, ','), ',')
from (select s.oid,
s.type,
row_number() over(partition by s.type order by s.oid) as num
from rcp_company s) sp
where connect_by_isleaf = 1
start with sp.num = 1
connect by prior sp.num + 1 = sp.num
and sp.type = prior sp.type;
- COLLECT function in Oracle 10g
Use the COLLECT function in Oracle 10g to get the same result. This method requires a table type and a function to convert the contents of the table type to a string.
Example:
CREATE OR REPLACE TYPE GTYP_STR_TABLE IS TABLE OF VARCHAR2 (32767);
/
CREATE OR REPLACE FUNCTION fun_tab_to_str(pv_tab IN gtyp_str_table,
pv_del IN VARCHAR2 DEFAULT ',')
RETURN VARCHAR2 IS
lv_result VARCHAR2(32767);
BEGIN
FOR i IN pv_tab.FIRST .. pv_tab.LAST
LOOP
IF i != pv_tab.FIRST
THEN
lv_result := lv_result || pv_del;
END IF;
lv_result := lv_result || pv_tab(i);
END LOOP;
RETURN lv_result;
END fun_tab_to_str;
/
The function is used as below:
select type, fun_tab_to_str(cast(collect(to_char(oid)) as gtyp_str_table))
from rcp_company c
group by type;
All above methods works fine if the combined string length is no more than 4000, however, there are occasions that the data from number of rows is so much that the combined length might more than 4000,to handle this problem, the return type of combined data can be lob type. There are some methods.
- Expanded generic Function using Ref Cursor
Example:
CREATE OR REPLACE FUNCTION join_clob(pv_cursor SYS_REFCURSOR,
pv_del VARCHAR2 := ',') RETURN CLOB IS
lv_value VARCHAR2(4000);
lv_result CLOB;
BEGIN
dbms_lob.createtemporary(lv_result, TRUE, dbms_lob.CALL);
LOOP
FETCH pv_cursor
INTO lv_value;
EXIT WHEN pv_cursor%NOTFOUND;
dbms_lob.writeappend(lv_result, length(pv_del), pv_del);
dbms_lob.writeappend(lv_result, length(lv_value), lv_value);
--lv_result := lv_result || lv_value;
END LOOP;
RETURN TRIM(pv_del FROM lv_result);
END join_clob;
The cursor function is used as below:
select type,
join_clob(CURSOR (select t2.oid from rcp_company t2 where t2.type = t1.type)
from rcp_company t1
group by type;
- Expended User-Defined Aggregate Function
CREATE OR REPLACE TYPE "GTYP_CLOBCAT2_OBJECT" IS OBJECT
(
catstr CLOB,
STATIC FUNCTION odciaggregateinitialize(cs_ctx IN OUT gtyp_clobcat2_object)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate(SELF IN OUT gtyp_clobcat2_object,
VALUE IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge(SELF IN OUT gtyp_clobcat2_object,
ctx2 IN OUT gtyp_clobcat2_object)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate(SELF IN OUT gtyp_clobcat2_object,
returnvalue OUT CLOB,
flags IN NUMBER)
RETURN NUMBER
)
/
CREATE OR REPLACE TYPE BODY gtyp_clobcat2_object IS
STATIC FUNCTION odciaggregateinitialize(cs_ctx IN OUT gtyp_clobcat2_object)
RETURN NUMBER IS
BEGIN
cs_ctx := gtyp_clobcat2_object(NULL);
dbms_lob.createtemporary(cs_ctx.catstr, TRUE, dbms_lob.CALL);
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregateiterate(SELF IN OUT gtyp_clobcat2_object,
VALUE IN VARCHAR2) RETURN NUMBER IS
BEGIN
--dbms_lob.append(SELF.catstr, VALUE);
--dbms_lob.append(SELF.catstr, ',');
dbms_lob.writeappend(SELF.catstr, lengthb(VALUE), VALUE);
dbms_lob.writeappend(SELF.catstr, 1, ',');
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregatemerge(SELF IN OUT gtyp_clobcat2_object,
ctx2 IN OUT gtyp_clobcat2_object)
RETURN NUMBER IS
BEGIN
dbms_lob.writeappend(SELF.catstr, 1, ',');
dbms_lob.append(SELF.catstr, ctx2.catstr);
RETURN odciconst.success;
END;
MEMBER FUNCTION odciaggregateterminate(SELF IN OUT gtyp_clobcat2_object,
returnvalue OUT CLOB,
flags IN NUMBER) RETURN NUMBER IS
BEGIN
returnvalue := TRIM(',' FROM SELF.catstr);
RETURN odciconst.success;
END;
END;
/
create or replace function fun_agg_clobcat2(pv_str varchar2) return clob
PARALLEL_ENABLE
AGGREGATE USING gtyp_clobcat2_object;
/
The aggregate function is implemented using a type and type body, and is used within a query as below:
select type, fun_agg_clobcat2(oid) from rcp_company group by type
- Expended COLLECT function in Oracle 10g
CREATE OR REPLACE TYPE GTYP_STR_TABLE IS TABLE OF VARCHAR2 (32767);
/
CREATE OR REPLACE FUNCTION fun_tab_to_clob(pv_tab IN gtyp_str_table,
pv_del IN VARCHAR2 DEFAULT ',')
RETURN CLOB IS
lv_result CLOB;
BEGIN
dbms_lob.createtemporary(lv_result, TRUE, dbms_lob.CALL);
FOR i IN pv_tab.FIRST .. pv_tab.LAST
LOOP
IF i != pv_tab.FIRST
THEN
dbms_lob.writeappend(lv_result, length(pv_del), pv_del);
--lv_result := lv_result || pv_del;
END IF;
dbms_lob.writeappend(lv_result, length(pv_tab(i)), pv_tab(i));
--lv_result := lv_result || pv_tab(i);
END LOOP;
RETURN lv_result;
END fun_tab_to_clob;
/
The function is used as below:
SELECT TYPE, fun_tab_to_clob(CAST(COLLECT(to_char(OID)) AS gtyp_str_table))
FROM rcp_company
GROUP BY TYPE;
As the number of rows might very large and return type is clob, the performance is badly impacted. Here is a simple test result for the three methods under the same circumstance in which the total records of rcp_company is 1190:
| Sql statement |
SELECT TYPE,
join_clob(CURSOR (SELECT t2.OID
FROM rcp_company t2
WHERE t2.TYPE = t1.TYPE))
FROM rcp_company t1
GROUP BY TYPE; |
SELECT TYPE, fun_agg_clobcat2(OID)
FROM rcp_company
GROUP BY TYPE; |
SELECT TYPE, fun_tab_to_clob(CAST(COLLECT(to_char(OID)) AS gtyp_str_table))
FROM rcp_company
GROUP BY TYPE; |
| Response time(s) |
0.215 |
1.497 |
0.185 |
See from the test result, Collect function method should be the better choose.
分享到：
相关推荐
### 解决字符长度8000限制 在数据库操作中，特别是使用SQL语言进行数据处理时，经常会遇到一些限制，比如SQL语句的最大长度限制。本文将深入探讨如何解决SQL语句长度超过8000个字符的问题，并提供具体的解决方案。...
- **条件查询**: 通过方法名称指定查询条件，如 `findByLastNameAndFirstName(String lastName, String firstName)`。 - **分页查询**: 通过 `Pageable` 参数实现分页功能。 - **排序查询**: 通过 `Sort` 参数...
Hash tables are the fundamental data structure for analytical database workloads, such as aggregation, joining, set filtering and records deduplication. The performance aspects of hash tables differ ...
在调整网络时遇到一个问题： ... aggregation=aggregation) File "D:\python\lib\site-packages\tensorflow_core\python\training\tracking\base.py", line 712, in _add_variable_with_custom_getter **kwarg
Aggregation aggregation = Aggregation.newAggregation( Aggregation.group("age").count().as("count"), Aggregation.project("age", "count") ); AggregationResults<CountResult> results = mongoTemplate....
Aggregation agg = Aggregation.newAggregation( Aggregation.match(Criteria.where("age").exists(true)), Aggregation.group().avg("age").as("averageAge") ); return mongoTemplate.aggregate(agg, "users...
List<User> findByUsernameAndPassword(String username, String password); ``` **8. Aggregation操作** 对于更复杂的聚合操作，如统计分析、分组等，Spring Data MongoDB提供了`Aggregation` API。我们可以构建一...
searchSourceBuilder.aggregation(aggregation); searchRequest.source(searchSourceBuilder); SearchResponse searchResponse = client.search(searchRequest).actionGet(); Terms terms = searchResponse....
其值可以是`AGGREGATION_MODE_DEFAULT`（默认模式）、`AGGREGATION_MODE_DISABLED`（禁用聚合）或`AGGREGATION_MODE_SUSPENDED`（暂停聚合）。 - **`int DELETED`**: 可读写，表示联系人是否被标记为删除；0表示未...
Aggregation agg = Aggregation.newAggregation( unwind("address.city"), group("city").count().as("total")); AggregationResults<CityCount> results = mongoTemplate.aggregate(agg, "users", CityCount....
或者 String s = new String("Welcome to Java"); 哪个更好？为什么? **解析:** 在Java中创建字符串时，推荐使用 `String s = "Welcome to Java";` 的方式，原因如下： - **性能:** 直接赋值时，字符串常量会被...
Aggregation aggregation = Aggregation.newAggregation( unwind("friends"), match(Criteria.where("friends.age").gt(30)) ); List<User> result = mongoTemplate.aggregate(aggregation, "users", User.class)...
例如，你可以使用`Aggregation`来实现统计分析或者分组查询。 在实际应用中，你可能还需要处理异常，如`MongoException`，并考虑数据的备份、恢复和安全性。MongoDB提供了复制集功能以确保高可用性，还有权限控制来...
Required signature: Map: string[] × (string → string) → string[]. ### Fold #### Definition (Fold) Fold is another higher-order function that aggregates the elements of a sequence into a single ...
Aggregation aggregation = searchQueryBuilder.addAggregation termsAggregation("author_terms").field("author").execute().getAggregations().get("author_terms"); ``` **7. 集成测试** 为了确保代码的正确性...
例如，实时更新可以通过 `@Update` 注解实现，聚合分析则可以通过 `Aggregation` API 进行。 总结，Spring Data Elasticsearch 为开发者提供了简单、高效的 Elasticsearch 集成方案，通过 Spring 的强大功能，我们...
public DBObject group(DBObject key, DBObject cond, DBObject initial, String reduce) { return this.group(key, cond, initial, reduce, (String)null); } ``` 在上面的代码中，我们使用了 MongoTemplate 的 ...
创建一个接口，继承`MongoRepository, ID>`，其中`T`是你的实体类类型，`ID`是主键类型（通常为`String`）： ```java public interface UserRepository extends MongoRepository, String> { } ``` 4. 实体类...
- Site search- Data aggregation- etc.仓库包含部署上下文和源代码；[如何使用]1.添加表格对象请参阅：src / main / java / com / alibaba / otter / index / UserInfo.java 具有TableField批注和name attr的Table...
5. **聚合(Aggregation)**: Spring Data MongoDB支持MongoDB的聚合框架，允许开发者进行复杂的数据分析。你可以使用`Aggregation`类和相关的操作符（如$match, $group, $project等）构建聚合管道。 6. **地理空间...