如何有效地求和，何时行应该在更多的组中
如何有效地求和，何时行应该在更多的组中
提问于 2019-10-01 15:06:39
我有一些记录(100多万份)是由公司组成的，但有些记录属于几个组，我需要得到组的总数(主( C216)和次( LIST_OF_PARENT_CCN))。
在LIST_OF_PARENT_CCN列中，任意数量的带有分号的组In "，"，您可以在表RR_SRC_LEK_P5中看到它。
我用companies group and sum (RR_SRC_LEK_P5)创建了一个表，并通过光标填充了该表。这一解决办法是可行的，但速度慢，效果不佳。
表RR_SRC_LEK_P5如下所示：
C216 LIST_OF_PARENT_CCN ESS_SUM
00004146 4523401 0
00005354 5487228, 2103188 0
00006203 0
00007524 2196702, 2103188 0
00009164 2163921, 4523401 0表RR_SRC_LEK_P6只包含PARENT_CCN的总和。
PARENT_CCN ESS_SUM
4523401 258418650,7711
5487228 2534044,19664
2103188 13482783356,0255
2196702 9936875,58
2163921 141329781,3349结果应该是这样，列ESS_SUM中的偏移数只是一个数字。
C216 LIST_OF_PARRENT_CCN ESS_SUM
00004146 4523401 258418650,7711
00005354 5487228, 2103188 2534044,19664 + 2534044,19664
00006203 0
00007524 2196702, 2103188 9936875,58 + 13482783356,0255
00009164 2163921, 4523401 141329781,3349 + 4523401 有光标，我使用了立即执行
CREATE OR REPLACE PROCEDURE CURSOR_TEST AS
c_sql sys_refcursor;
c_C216 RR_SRC_LEK_P5.C216%TYPE;
c_LIST_OF_PARENT_CCN RR_SRC_LEK_P5.LIST_OF_PARENT_CCN%TYPE;
BEGIN
OPEN c_sql for 'select C216, LIST_OF_PARENT_CCN from RR_SRC_LEK_P5 where LIST_OF_PARENT_CCN is not null';
LOOP
FETCH c_sql into c_C216, c_LIST_OF_PARENT_CCN;
EXIT WHEN c_sql%notfound;
execute immediate 'update RR_SRC_LEK_P5 a set
ESS_SUM = (select sum(ESS_SUM) from RR_SRC_LEK_P6 where PARENT_CCN in (' || c_LIST_OF_PARENT_CCN || '))
where a.C216 = ''' || c_C216 || '''';
END LOOP;
CLOSE c_sql;
commit;
END CURSOR_TEST;有比使用游标更好的方法吗？
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-10-01 15:48:33
可以将aggregate函数与xmlquery一起使用。
表创建：
SQL> CREATE TABLE RR_SRC_LEK_P5 AS (
2 SELECT '00004146' AS C216, '4523401' AS LIST_OF_PARENT_CCN, 0 AS ESS_SUM FROM DUAL UNION ALL
3 SELECT '00005354', '5487228, 2103188', 0 FROM DUAL UNION ALL
4 SELECT '00006203', NULL ,0 FROM DUAL UNION ALL
5 SELECT '00007524', '2196702, 2103188', 0 FROM DUAL UNION ALL
6 SELECT '00009164', '2163921, 4523401', 0 FROM DUAL);
Table created.
SQL> CREATE TABLE RR_SRC_LEK_P6 AS
2 (SELECT 4523401 AS PARENT_CCN, 2584186507711 AS ESS_SUM FROM DUAL UNION ALL
3 SELECT 5487228, 253404419664 FROM DUAL UNION ALL
4 SELECT 2103188, 134827833560255 FROM DUAL UNION ALL
5 SELECT 2196702, 993687558 FROM DUAL UNION ALL
6 SELECT 2163921, 1413297813349 FROM DUAL);
Table created.
SQL>当前数据的视图
SQL> select * from RR_SRC_LEK_P5;
C216 LIST_OF_PARENT_C ESS_SUM
-------- ---------------- ----------
00004146 4523401 0
00005354 5487228, 2103188 0
00006203 0
00007524 2196702, 2103188 0
00009164 2163921, 4523401 0
SQL> select * from RR_SRC_LEK_P6;
PARENT_CCN ESS_SUM
---------- ---------------------------------------
4523401 2584186507711
5487228 253404419664
2103188 134827833560255
2196702 993687558
2163921 1413297813349
SQL>查询以更新数据
SQL>
SQL> MERGE INTO RR_SRC_LEK_P5 P5
2 USING ( SELECT P5.C216,
3 XMLQUERY(
4 LISTAGG(P6.ESS_SUM, '+') WITHIN GROUP (ORDER BY NULL)
5 RETURNING CONTENT ).GETNUMBERVAL() AS ESS_NEW
6 FROM RR_SRC_LEK_P5 P5
7 JOIN RR_SRC_LEK_P6 P6
8 ON ( INSTR(', ' || P5.LIST_OF_PARENT_CCN || ',',
9 ', ' || P6.PARENT_CCN || ',') > 0 )
10 GROUP BY P5.C216) RES
11 ON ( P5.C216 = RES.C216 )
12 WHEN MATCHED THEN
13 UPDATE SET P5.ESS_SUM = RES.ESS_NEW;
4 rows merged.
SQL>更新数据
SQL> SELECT * FROM RR_SRC_LEK_P5;
C216 LIST_OF_PARENT_C ESS_SUM
-------- ---------------- ---------------------------------------
00004146 4523401 2584186507711
00005354 5487228, 2103188 135081237979919
00006203 0
00007524 2196702, 2103188 134828827247813
00009164 2163921, 4523401 3997484321060
SQL>更新
对于您的设置，需要使用以下查询：
MERGE INTO RR_SRC_LEK_P5 P5
USING ( SELECT P5.C216,
XMLQUERY(
LISTAGG(
to_char(p6.ESS_SUM,
'fm9999999999999999999999999999D099999999999999999',
'nls_numeric_characters = ''.,'''), '+') WITHIN GROUP (ORDER BY NULL)
RETURNING CONTENT ).GETNUMBERVAL() AS ESS_NEW
FROM RR_SRC_LEK_P5 P5
JOIN RR_SRC_LEK_P6 P6
ON ( INSTR(', ' || P5.LIST_OF_PARENT_CCN || ',',
', ' || P6.PARENT_CCN || ',') > 0 )
GROUP BY P5.C216) RES
ON ( P5.C216 = RES.C216 )
WHEN MATCHED THEN
UPDATE SET P5.ESS_SUM = RES.ESS_NEW;干杯！！
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/58187702
复制相关文章
相似问题
腾讯云开发者
