对于透视表列，需要应用分组依据

Question

我希望基于PIVOT函数生成的列应用group by函数。

在下面的查询中，我想要应用Sum(TC.MB_Usage)，这样我就可以在最后使用group by。但是，我无法将group by函数应用于透视函数(站点)生成的列。

注释以粗体显示

With TC as(

select  /*+ NO_MERGE(T1) NO_MERGE(T2)
PARALLEL(T1,4) PARALLEL(T2,4) */ 

T1.* from (

select /*+
DRIVING_SITE(A) NO_MERGE(A) NO_MERGE(fips) NO_MERGE(fw) PARALLEL(A,4)
PARALLEL(fips,4) PARALLEL(fw,4) */ 

     distinct


resource_value, 


resource_type,

     L3_IMSI as IMSI

    ,L9_Calling_Number as MDN

    ,L9_ECID as Curr_ECID

    ,trunc(START_TIME) as Usage_Date

    ,trim(TO_CHAR (TO_NUMBER  (SUBSTR (L9_ECID,1,LENGTH(L9_ECID) - 2 ),'XXXXXXXXX'),'000000')) as enodeb

    ,substr(trim(TO_CHAR (TO_NUMBER  (SUBSTR (L9_ECID,1,LENGTH(L9_ECID) - 2 ),'XXXXXXXXX'),'000000')),1,2) as fips_cd

    ,STATE_CODE

    ,STATE_NAME

    ,Sum(L3_ROUNDED_UNIT/1024) as MB_Usage





from RT_ET A

INNER JOIN

FIPS_STATEfips

ON trim(to_char(fips.FIPS_CODE,'00')) = substr(trim(TO_CHAR (TO_NUMBER  (SUBSTR (A.L9_ECID,1,LENGTH(A.L9_ECID) - 2 ),'XXXXXXXXX'),'000000')),1,2)

INNER JOIN

DVC_ADDRfw

ON trim(L3_IMSI) = trim(to_char(FW.IMSI))

Where A.L9_ECID not in (' ','0') AND A.L3_IMSI not in (' ','0')

AND trunc(A.start_time) > trunc(sysdate-8)

AND trunc(start_time) > trunc(FW.ODS_INSERT_DATE)



group by 


resource_value

    ,resource_type 

    ,L3_IMSI

    ,L9_Calling_Number

    ,L9_ECID

    ,trunc(START_TIME)

    ,trim(TO_CHAR (TO_NUMBER  (SUBSTR (L9_ECID,1,LENGTH(L9_ECID) - 2 ),'XXXXXXXXX'),'000000'))

    ,substr(trim(TO_CHAR (TO_NUMBER  (SUBSTR (L9_ECID,1,LENGTH(L9_ECID) - 2 ),'XXXXXXXXX'),'000000')),1,2)

    ,STATE_CODE

    ,STATE_NAME



) T1

where NOT EXISTS (

SELECT 

1 FROM  DVC_ENODEB T2

    WHERE T1.IMSI = trim(to_char(T2.IMSI))

    AND T1.MDN = T2.MDN

            AND T1.ENODEB = T2.ENODEB

) 

)







select

distinct

     SITES.*,

   TC.resource_value, 

TC.resource_type,

TC.IMSI,

TC.MDN,

TC.Curr_ECID,

TC.Usage_Date,

TC.enodeb,

TC.fips_cd,

TC.STATE_CODE,

TC.STATE_NAME,

**TC.MB_Usage   -- Need to apply Sum(TC.MB_Usage)**



from

(



with

endb as

(select 

  e.IMSI, e.MDN,e.site_id, E.ENODEB,

  ROW_NUMBER ()OVER (PARTITION BY e.IMSI||e.MDN

ORDER BY e.IMSI||e.MDN )  row_id

  from
FIXED_WIRELESS_DVC_ADDR_ENODEB e



)

select *



  from (select IMSI,MDN,IMSI||MDN as IMSI_MDN,site_id,row_id



          from endb



       )



pivot (max(site_id) siteid for row_id in (1,2,3,4,5,6,7,8,9,10,11,12,13)) ) SITES



INNER JOIN TC

ON ((TC.IMSI = trim(to_char(SITES.IMSI))) AND TC.MDN = SITES.MDN)





**Unable to apply Group by
based on below PIVOT columns (SITES.*), if I use SUM for MD_USAGE







Group BY

TC.resource_value, 

TC.resource_type,

TC.IMSI,

TC.MDN,

TC.Curr_ECID,

TC.Usage_Date,

TC.enodeb,

TC.fips_cd,

TC.STATE_CODE,

TC.STATE_NAME,

SITES.***

Answer 1

您需要指定GROUP BY中的各个列。

看起来您的站点别名将具有列IMSI、MDN、IMSI_MDN，然后是PIVOT子句中的1、2、3、4、5、6、7、8、9、10、11、12、13列。

因此，请尝试单独列出这些内容，对于数字部分，请将它们放在引号中：

GROUP BY
...
SITES."1", SITES."2", etc.