sql - 将 WM_CONCAT 转换为 Listagg

Question

我的 DBA 正在将我的 oracle 数据库从 v10 升级到 v12。
我有一些使用 wm_concat 的旧 SP我需要把它改成 listagg .
有问题的代码是这样的:

Select  registration_id,package_set_id,
        REPLACE(REPLACE(WM_CONCAT(REPLACE( (case when ROW_NUMBER() over (partition by product_id,product_detail_set_id,registration_id,product_family_id,application_id,package_Set_id,
               legal_status order by packset_country)=1 then legal_status else null end), ',' , '#')) OVER (PARTITION BY PRODUCT_ID,  PRODUCT_DETAIL_SET_ID,
               REGISTRATION_ID  ,PRODUCT_FAMILY_ID,APPLICATION_ID,PACKAGE_SET_ID   ORDER BY Packset_country  ), ',' , ' | '), '#', ',') as legal_status,

        (REPLACE(REPLACE(WM_CONCAT(REPLACE(ev_code, ',' , '#')) OVER (PARTITION BY PRODUCT_ID,  PRODUCT_DETAIL_SET_ID,
               REGISTRATION_ID  ,PRODUCT_FAMILY_ID,APPLICATION_ID,PACKAGE_SET_ID   ORDER BY ev_code  ), ',' , ' | '), '#', ',')) EV_CODES,

         min(marketed_date) over (PARTITION BY PRODUCT_ID,  PRODUCT_DETAIL_SET_ID,REGISTRATION_ID  ,PRODUCT_FAMILY_ID,APPLICATION_ID,PACKAGE_SET_ID) as marketed_date,

         (REPLACE(REPLACE(WM_CONCAT(REPLACE(Packset_country, ',' , '#')) OVER (PARTITION BY PRODUCT_ID,  PRODUCT_DETAIL_SET_ID, REGISTRATION_ID ,PRODUCT_FAMILY_ID,
                APPLICATION_ID,PACKAGE_SET_ID   ORDER BY Packset_country, reg_packset_country_id ), ',' , ' | '), '#', ',')) REGISTRATION_PACKSET_COUNTRIES,
         ROW_NUMBER() OVER (PARTITION BY PRODUCT_ID,  PRODUCT_DETAIL_SET_ID,REGISTRATION_ID  ,PRODUCT_FAMILY_ID,APPLICATION_ID,PACKAGE_SET_ID  
                ORDER BY Packset_country desc ,reg_packset_country_id)  ROW_NUM,     
         REPLACE(REPLACE(WM_CONCAT(REPLACE( (case when currently_marketed_in_country='Y' then packset_country end), ',' , '#')) OVER (PARTITION BY PRODUCT_ID,  PRODUCT_DETAIL_SET_ID,
                REGISTRATION_ID  ,PRODUCT_FAMILY_ID,APPLICATION_ID,PACKAGE_SET_ID  ORDER BY packset_country ,currently_marketed_in_country,reg_packset_country_id ), ',' , ' | '), '#', ',') as CURRENTLY_MARKETED_COUNTRIES
from radw_dwh.dw202_fact_reg_pack_countries

预期结果是:

我尝试更改它，但是当我尝试在“LISTAGG”旁边使用“ROW_NUMBER()”时出现问题。

我怎样才能解决这个问题？

Answer 1

的基本语法LISTAGG 是:

LISTAGG(col_name_to_be_aggregated, ',') WITHIN GROUP (ORDER BY col)

在您的情况下，由于您有一个子查询作为结果集为 WM_CONCAT ，你可以用相同的子查询代替 col_name_to_be_aggregated在 LISTAGG .

我想你也可以摆脱所有 更换 函数，因为 LISTAGG 可以接受 分隔符 你的选择。

尝试，

LISTAGG
(
  CASE
  WHEN ROW_NUMBER() OVER (PARTITION BY product_id,
                                       product_detail_set_id,
                                       registration_id,
                                       product_family_id,
                                       application_id,
                                       package_Set_id, 
                                       legal_status 
                                       order by packset_country)=1 THEN
    legal_status
  ELSE
    NULL
  END), ',') WITHIN GROUP (ORDER BY required_col)

另外，我想解释一下为什么您需要在 12c 中迁移到 LISTAGG。由于 t 已从最新的 12c 版本中删除。因此，任何一直依赖 WM_CONCAT 功能的应用程序一旦升级到 12c 将无法运行。阅读 Why not use WM_CONCAT function in Oracle?

对于 11g 第 2 版之前的版本，您不能使用 LISTAGG。字符串聚合技术有很多，看看我的回答 here .

更多详情 Oracle String Aggregation Techniques