blaze
blaze copied to clipboard
Published
20 hours ago •
kwai
kwai
kyuubi on spark error
environment： cdp:7.x spark:3.3.3 kyuubi:1.7.1 Occasionally reporting an error today. error message：
Error: org.apache.kyuubi.KyuubiSQLException: org.apache.kyuubi.KyuubiSQLException: Error operating ExecuteStatement: org.apache.spark.SparkException: Job aborted due to stage failure: Task 83 in stage 1411.0 failed 4 times, most recent failure: Lost task 83.3 in stage 1411.0 (TID 429682) (node77 executor 390): java.lang.RuntimeException: native executing [partition=83] panics: Execution error: Arrow error: Invalid argument error: all columns in a record batch must have the same length
Driver stacktrace:
at org.apache.spark.scheduler.DAGScheduler.failJobAndIndependentStages(DAGScheduler.scala:2668)
at org.apache.spark.scheduler.DAGScheduler.$anonfun$abortStage$2(DAGScheduler.scala:2604)
at org.apache.spark.scheduler.DAGScheduler.$anonfun$abortStage$2$adapted(DAGScheduler.scala:2603)
at scala.collection.mutable.ResizableArray.foreach(ResizableArray.scala:62)
at scala.collection.mutable.ResizableArray.foreach$(ResizableArray.scala:55)
at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:49)
at org.apache.spark.scheduler.DAGScheduler.abortStage(DAGScheduler.scala:2603)
at org.apache.spark.scheduler.DAGScheduler.$anonfun$handleTaskSetFailed$1(DAGScheduler.scala:1178)
at org.apache.spark.scheduler.DAGScheduler.$anonfun$handleTaskSetFailed$1$adapted(DAGScheduler.scala:1178)
at scala.Option.foreach(Option.scala:407)
at org.apache.spark.scheduler.DAGScheduler.handleTaskSetFailed(DAGScheduler.scala:1178)
at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.doOnReceive(DAGScheduler.scala:2856)
at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:2798)
at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:2787)
at org.apache.spark.util.EventLoop$$anon$1.run(EventLoop.scala:49)
Caused by: java.lang.RuntimeException: native executing [partition=83] panics: Execution error: Arrow error: Invalid argument error: all columns in a record batch must have the same length
at org.apache.kyuubi.KyuubiSQLException$.apply(KyuubiSQLException.scala:69)
at org.apache.kyuubi.engine.spark.operation.SparkOperation$$anonfun$onError$1.$anonfun$applyOrElse$1(SparkOperation.scala:189)
at org.apache.kyuubi.Utils$.withLockRequired(Utils.scala:395)
at org.apache.kyuubi.operation.AbstractOperation.withLockRequired(AbstractOperation.scala:51)
at org.apache.kyuubi.engine.spark.operation.SparkOperation$$anonfun$onError$1.applyOrElse(SparkOperation.scala:177)
at org.apache.kyuubi.engine.spark.operation.SparkOperation$$anonfun$onError$1.applyOrElse(SparkOperation.scala:172)
at scala.runtime.AbstractPartialFunction.apply(AbstractPartialFunction.scala:38)
at org.apache.kyuubi.engine.spark.operation.ExecuteStatement.$anonfun$executeStatement$1(ExecuteStatement.scala:88)
at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23)
at org.apache.kyuubi.engine.spark.operation.SparkOperation.$anonfun$withLocalProperties$1(SparkOperation.scala:155)
at org.apache.spark.sql.execution.SQLExecution$.withSQLConfPropagated(SQLExecution.scala:169)
at org.apache.kyuubi.engine.spark.operation.SparkOperation.withLocalProperties(SparkOperation.scala:139)
at org.apache.kyuubi.engine.spark.operation.ExecuteStatement.executeStatement(ExecuteStatement.scala:78)
at org.apache.kyuubi.engine.spark.operation.ExecuteStatement$$anon$1.run(ExecuteStatement.scala:100)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.apache.spark.SparkException: Job aborted due to stage failure: Task 83 in stage 1411.0 failed 4 times, most recent failure: Lost task 83.3 in stage 1411.0 (TID 429682) (node77 executor 390): java.lang.RuntimeException: native executing [partition=83] panics: Execution error: Arrow error: Invalid argument error: all columns in a record batch must have the same length
Driver stacktrace:
at org.apache.spark.scheduler.DAGScheduler.failJobAndIndependentStages(DAGScheduler.scala:2668)
at org.apache.spark.scheduler.DAGScheduler.$anonfun$abortStage$2(DAGScheduler.scala:2604)
at org.apache.spark.scheduler.DAGScheduler.$anonfun$abortStage$2$adapted(DAGScheduler.scala:2603)
at scala.collection.mutable.ResizableArray.foreach(ResizableArray.scala:62)
at scala.collection.mutable.ResizableArray.foreach$(ResizableArray.scala:55)
at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:49)
at org.apache.spark.scheduler.DAGScheduler.abortStage(DAGScheduler.scala:2603)
at org.apache.spark.scheduler.DAGScheduler.$anonfun$handleTaskSetFailed$1(DAGScheduler.scala:1178)
at org.apache.spark.scheduler.DAGScheduler.$anonfun$handleTaskSetFailed$1$adapted(DAGScheduler.scala:1178)
at scala.Option.foreach(Option.scala:407)
at org.apache.spark.scheduler.DAGScheduler.handleTaskSetFailed(DAGScheduler.scala:1178)
at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.doOnReceive(DAGScheduler.scala:2856)
at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:2798)
at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:2787)
at org.apache.spark.util.EventLoop$$anon$1.run(EventLoop.scala:49)
Caused by: java.lang.RuntimeException: native executing [partition=83] panics: Execution error: Arrow error: Invalid argument error: all columns in a record batch must have the same length
at org.apache.kyuubi.KyuubiSQLException$.apply(KyuubiSQLException.scala:69)
at org.apache.kyuubi.operation.ExecuteStatement.waitStatementComplete(ExecuteStatement.scala:129)
at org.apache.kyuubi.operation.ExecuteStatement.$anonfun$runInternal$1(ExecuteStatement.scala:161)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748) (state=,code=0)
SQL has been encrypted：
with st_xxxxxxxxxxxxxxx_day as (select
dt.xxxxx
,xxxx
,xxxxxx
,xxxxxxx
,maxxxxxxxxx
,ismme5gxxxxxxxx
,iss1u5gxxxxxxxx
,dcnr
,is_5g_on_off
,is_flow_limit
,dv.vendor as fact_id
,dv.vendor as cat_one
,dv.model_id
,null as mt_type
,dv.model_name as terminalname
,dv.sw_version as software_version
,null as card_slot
,dv.support_volte as is_support_volte
,xxxxxxx_tranoctets
,xxxxxxx_5g_tranoctets
,xxxxxxx_4g_tranoctets
,xxxxxxx_webdlvalidoctets
,xxxxxxx_webdlvaliddelay
,xxxxxxx_5g_webdlvalidoctets
,xxxxxxx_5g_webdlvaliddelay
,xxxxxxx_4g_webdlvalidoctets
,xxxxxxx_4g_webdlvaliddelay
,xxxxxx_cnt_totalattach_req
,xxxxxx_cnt_totalattach_succ
,xxxxxx_cnt_md_totalattach_req
,xxxxxx_cnt_md_totalattach_succ
,xxxxxx_cnt_md_totalattach_fail
,xxxxxx_erbmodify_req
,xxxxxx_erbmodify_succ
,xxxxxx_erbmodify_err
,mdcellcnt
,nomdcellcnt
,xxx_tranoctets
,xxx_webdlvalidoctets
,xxx_webdlvaliddelay
,thzl
,swzl
,cpyzf
,sfzqx
,yytfw
,rexian
,predict
,xxx_nbrallWebReq
,xxx_nbrallWebResSucc
,xxx_WebResDelay
,xxx_nbrTcpConn1stReq
,xxx_nbrTcpConn1stSucc
,xxx_nbrTcpConn2ndSucc
,xxx_tcpConn1stDelay
,xxx_tcpConn2ndDelay
,xxx_tcpConnDelay
,xxx_FirstScreenFincnt
,xxx_FirstScreenFinTime
,xxx_VideoBufferServCnt
,xxx_VideoServCnt
,xxx_VideoDlOctets
,xxx_VideoDlDuxxxxxxxxion
,xxx_videoThroughput_cnt
,xxx_videoThroughput
,xxx_videoBufferCnt
,xxx_VideoPlayTime
,xxx_InitBufferReq
,xxx_InitBufferSucc
,xxx_InitBufferDuxxxxxxxxion
,lte_flux
,g5_under_lte_flux
,g5_flux
from (select
dt.xxxxx as xxxxx
,max(tabletype) as tabletype
,max(xxxx) as xxxx
,max(case when rank=1 then xxxxxx end) as xxxxxx
,max(case when rank=1 then xxxxxxx end) as xxxxxxx
,max(maxxxxxxxxx) as maxxxxxxxxx
,max(ismme5gxxxxxxxx) ismme5gxxxxxxxx
,max(iss1u5gxxxxxxxx) iss1u5gxxxxxxxx
,max(dcnr) as dcnr
,max(is_5g_on_off) as is_5g_on_off
,sum(xxxxxxx_tranoctets) as xxxxxxx_tranoctets
,sum(xxxxxxx_5g_tranoctets) as xxxxxxx_5g_tranoctets
,sum(xxxxxxx_4g_tranoctets) as xxxxxxx_4g_tranoctets
,sum(xxxxxxx_webdlvalidoctets) as xxxxxxx_webdlvalidoctets
,sum(xxxxxxx_webdlvaliddelay) as xxxxxxx_webdlvaliddelay
,sum(xxxxxxx_5g_webdlvalidoctets) as xxxxxxx_5g_webdlvalidoctets
,sum(xxxxxxx_5g_webdlvaliddelay) as xxxxxxx_5g_webdlvaliddelay
,sum(xxxxxxx_4g_webdlvalidoctets) as xxxxxxx_4g_webdlvalidoctets
,sum(xxxxxxx_4g_webdlvaliddelay) as xxxxxxx_4g_webdlvaliddelay
,sum(xxxxxx_cnt_totalattach_req) as xxxxxx_cnt_totalattach_req
,sum(xxxxxx_cnt_totalattach_succ) as xxxxxx_cnt_totalattach_succ
,sum(xxxxxx_cnt_md_totalattach_req) as xxxxxx_cnt_md_totalattach_req
,sum(xxxxxx_cnt_md_totalattach_succ) as xxxxxx_cnt_md_totalattach_succ
,sum(xxxxxx_cnt_md_totalattach_fail) as xxxxxx_cnt_md_totalattach_fail
,sum(xxxxxx_erbmodify_req) as xxxxxx_erbmodify_req
,sum(xxxxxx_erbmodify_succ) as xxxxxx_erbmodify_succ
,sum(xxxxxx_erbmodify_err) as xxxxxx_erbmodify_err
,max(mdcellcnt) as mdcellcnt
,max(nomdcellcnt) as nomdcellcnt
,sum(xxx_tranoctets) as xxx_tranoctets
,sum(xxx_webdlvalidoctets) as xxx_webdlvalidoctets
,sum(xxx_webdlvaliddelay) as xxx_webdlvaliddelay
,max(is_flow_limit) as is_flow_limit
,max(thzl) as thzl
,max(swzl) as swzl
,max(cpyzf) as cpyzf
,max(sfzqx) as sfzqx
,max(yytfw) as yytfw
,max(rexian) as rexian
,max(predict) as predict
,sum(xxx_nbrallWebReq) as xxx_nbrallWebReq
,sum(xxx_nbrallWebResSucc) as xxx_nbrallWebResSucc
,sum(xxx_WebResDelay) as xxx_WebResDelay
,sum(xxx_nbrTcpConn1stReq) as xxx_nbrTcpConn1stReq
,sum(xxx_nbrTcpConn1stSucc) as xxx_nbrTcpConn1stSucc
,sum(xxx_nbrTcpConn2ndSucc) as xxx_nbrTcpConn2ndSucc
,sum(xxx_tcpConn1stDelay) as xxx_tcpConn1stDelay
,sum(xxx_tcpConn2ndDelay) as xxx_tcpConn2ndDelay
,sum(xxx_tcpConnDelay) as xxx_tcpConnDelay
,sum(xxx_FirstScreenFincnt) as xxx_FirstScreenFincnt
,sum(xxx_FirstScreenFinTime) as xxx_FirstScreenFinTime
,sum(xxx_VideoBufferServCnt) as xxx_VideoBufferServCnt
,sum(xxx_VideoServCnt) as xxx_VideoServCnt
,sum(xxx_VideoDlOctets) as xxx_VideoDlOctets
,sum(xxx_VideoDlDuxxxxxxxxion) as xxx_VideoDlDuxxxxxxxxion
,sum(xxx_videoThroughput_cnt) as xxx_videoThroughput_cnt
,sum(xxx_videoThroughput) as xxx_videoThroughput
,sum(xxx_videoBufferCnt) as xxx_videoBufferCnt
,sum(xxx_VideoPlayTime) as xxx_VideoPlayTime
,sum(xxx_InitBufferReq) as xxx_InitBufferReq
,sum(xxx_InitBufferSucc) as xxx_InitBufferSucc
,sum(xxx_InitBufferDuxxxxxxxxion) as xxx_InitBufferDuxxxxxxxxion
,sum(lte_flux) as lte_flux
,sum(g5_under_lte_flux) as g5_under_lte_flux
,sum(g5_flux) as g5_flux
from (
select
xxxxx as xxxxx
,max(tabletype) as tabletype
,xxxx as xxxx
,xxxxxx as xxxxxx
,xxxxxxx as xxxxxxx
,max(case when cellid is not null then cast(xxxxxxxx as bigint) end) as maxxxxxxxxx
,max(case when tabletype=1 and xxxxxxxx in ('10','9') and cellid is not null then 1 else 0 end) ismme5gxxxxxxxx
,max(case when tabletype=2 and xxxxxxxx in ('10','9') and cellid is not null then 1 else 0 end) iss1u5gxxxxxxxx
,max(case when xxxxxxxx in ('10','9') then 1 else dcnr end) as dcnr
,max(is_5g_on_off) as is_5g_on_off
,nvl(sum(xxx_ultranoctets),0)+nvl(sum(xxx_dltranoctets),0) as xxxxxxx_tranoctets
,sum(case when xxxxxxxx in ('10','9') then xxxxxxx_ultranoctets end)+sum(case when xxxxxxxx in ('10','9') then xxxxxxx_dltranoctets end) as xxxxxxx_5g_tranoctets
,sum(case when xxxxxxxx='6' then xxxxxxx_ultranoctets end)+sum(case when xxxxxxxx='6' then xxxxxxx_dltranoctets end) as xxxxxxx_4g_tranoctets
,sum(xxxxxxx_webdlvalidoctets) as xxxxxxx_webdlvalidoctets
,sum(xxxxxxx_webdlvaliddelay) as xxxxxxx_webdlvaliddelay
,sum(case when xxxxxxxx='9' then xxxxxxx_webdlvalidoctets end) as xxxxxxx_5g_webdlvalidoctets
,sum(case when xxxxxxxx='9' then xxxxxxx_webdlvaliddelay end) as xxxxxxx_5g_webdlvaliddelay
,sum(case when xxxxxxxx='6' then xxxxxxx_webdlvalidoctets end) as xxxxxxx_4g_webdlvalidoctets
,sum(case when xxxxxxxx='6' then xxxxxxx_webdlvaliddelay end) as xxxxxxx_4g_webdlvaliddelay
,sum(xxxxxx_cnt_totalattach_req) as xxxxxx_cnt_totalattach_req
,sum(xxxxxx_cnt_totalattach_succ) as xxxxxx_cnt_totalattach_succ
,sum(case when ismaodian=1 then xxxxxx_cnt_totalattach_req end) as xxxxxx_cnt_md_totalattach_req
,sum(case when ismaodian=1 then xxxxxx_cnt_totalattach_succ end) as xxxxxx_cnt_md_totalattach_succ
,sum(case when ismaodian=1 then xxxxxx_cnt_totalattach_req else 0 end)- sum(case when ismaodian=1 then xxxxxx_cnt_totalattach_succ else 0 end) as xxxxxx_cnt_md_totalattach_fail
,sum(xxxxxx_erbmodify_req) as xxxxxx_erbmodify_req
,sum(xxxxxx_erbmodify_succ) as xxxxxx_erbmodify_succ
,sum(xxxxxx_erbmodify_err) as xxxxxx_erbmodify_err
,count(distinct case when ismaodian=1 then cellid end) as mdcellcnt
,count(distinct cellid)-count(distinct case when ismaodian=1 then cellid end) as nomdcellcnt
,sum(case when xxxxxxxx='10' then xxx_ultranoctets end)+sum(case when xxxxxxxx='10' then xxx_dltranoctets end) as xxx_tranoctets
,sum(case when xxxxxxxx='10' then xxx_webdlvalidoctets end) as xxx_webdlvalidoctets
,sum(case when xxxxxxxx='10' then xxx_webdlvaliddelay end) as xxx_webdlvaliddelay
,max(is_flow_limit) as is_flow_limit
,max(thzl) as thzl
,max(swzl) as swzl
,max(cpyzf) as cpyzf
,max(sfzqx) as sfzqx
,max(yytfw) as yytfw
,max(rexian) as rexian
,max(predict) as predict
,sum(xxx_nbrallWebReq) as xxx_nbrallWebReq
,sum(xxx_nbrallWebResSucc) as xxx_nbrallWebResSucc
,sum(xxx_WebResDelay) as xxx_WebResDelay
,sum(xxx_nbrTcpConn1stReq) as xxx_nbrTcpConn1stReq
,sum(xxx_nbrTcpConn1stSucc) as xxx_nbrTcpConn1stSucc
,sum(xxx_nbrTcpConn2ndSucc) as xxx_nbrTcpConn2ndSucc
,sum(xxx_tcpConn1stDelay) as xxx_tcpConn1stDelay
,sum(xxx_tcpConn2ndDelay) as xxx_tcpConn2ndDelay
,sum(xxx_tcpConnDelay) as xxx_tcpConnDelay
,sum(xxx_FirstScreenFincnt) as xxx_FirstScreenFincnt
,sum(xxx_FirstScreenFinTime) as xxx_FirstScreenFinTime
,sum(xxx_VideoBufferServCnt) as xxx_VideoBufferServCnt
,sum(xxx_VideoServCnt) as xxx_VideoServCnt
,sum(xxx_VideoDlOctets) as xxx_VideoDlOctets
,sum(xxx_VideoDlDuxxxxxxxxion) as xxx_VideoDlDuxxxxxxxxion
,sum(xxx_videoThroughput_cnt) as xxx_videoThroughput_cnt
,sum(xxx_videoThroughput) as xxx_videoThroughput
,sum(xxx_videoBufferCnt) as xxx_videoBufferCnt
,sum(xxx_VideoPlayTime) as xxx_VideoPlayTime
,sum(xxx_InitBufferReq) as xxx_InitBufferReq
,sum(xxx_InitBufferSucc) as xxx_InitBufferSucc
,sum(xxx_InitBufferDuxxxxxxxxion) as xxx_InitBufferDuxxxxxxxxion
,sum(xdrcnt) as xdrcnt
,sum(lte_flux) as lte_flux
,sum(g5_under_lte_flux) as g5_under_lte_flux
,sum(g5_flux) as g5_flux
,row_number() over(partition by xxxxx order by (nvl(sum(xxx_ultranoctets),0)+nvl(sum(xxx_dltranoctets),0)) desc,sum(xdrcnt) desc) rank
from (select
1 tabletype
,xxxxxxxx
,eci as cellid
,xxxxx
,xxxx
,substr(xxxxxx,1,8) as xxxxxxx
,xxxxxx as xxxxxx
,cast(null as string) as dcnr
,case when xxxxxx_erbmodify_succ>0 or xxxxxxxx='9' then 1 else 0 end as ismaodian
,null as xxxxxxx_ultranoctets
,null as xxxxxxx_dltranoctets
,null as xxxxxxx_webdlvalidoctets
,null as xxxxxxx_webdlvaliddelay
,xxxxxx_cnt_totalattach_req
,xxxxxx_cnt_totalattach_succ
,xxxxxx_erbmodify_req
,xxxxxx_erbmodify_succ
,xxxxxx_erbmodify_err
,null as xxx_ultranoctets
,null as xxx_dltranoctets
,case when xxxxxxxx='10' then 1 else 0 end as is_5g_on_off
,cast(null as double) as xxx_webdlvalidoctets
,cast(null as double) as xxx_webdlvaliddelay
,cast(null as string) as is_flow_limit
,cast(null as double) as thzl
,cast(null as double) as swzl
,cast(null as double) as cpyzf
,cast(null as double) as sfzqx
,cast(null as double) as yytfw
,cast(null as double) as rexian
,cast(null as double) as predict
,cast(null as double) as xxx_nbrallWebReq
,cast(null as double) as xxx_nbrallWebResSucc
,cast(null as double) as xxx_WebResDelay
,cast(null as double) as xxx_nbrTcpConn1stReq
,cast(null as double) as xxx_nbrTcpConn1stSucc
,cast(null as double) as xxx_nbrTcpConn2ndSucc
,cast(null as double) as xxx_tcpConn1stDelay
,cast(null as double) as xxx_tcpConn2ndDelay
,cast(null as double) as xxx_tcpConnDelay
,cast(null as double) as xxx_FirstScreenFincnt
,cast(null as double) as xxx_FirstScreenFinTime
,cast(null as double) as xxx_VideoBufferServCnt
,cast(null as double) as xxx_VideoServCnt
,cast(null as double) as xxx_VideoDlOctets
,cast(null as double) as xxx_VideoDlDuxxxxxxxxion
,cast(null as double) as xxx_videoThroughput_cnt
,cast(null as double) as xxx_videoThroughput
,cast(null as double) as xxx_videoBufferCnt
,cast(null as double) as xxx_VideoPlayTime
,cast(null as double) as xxx_InitBufferReq
,cast(null as double) as xxx_InitBufferSucc
,cast(null as double) as xxx_InitBufferDuxxxxxxxxion
,xxxxxx_xdrcnt as xdrcnt
,cast(null as string) as lte_flux
,cast(null as string) as g5_under_lte_flux
,cast(null as string) as g5_flux
from xxx_xxx.xxxxxxxxxxxxxxxxxxxx_day
where day=20231002 and interfaceid='5'
union all
select
1 as tabletype
,xxxxxxxx as xxxxxxxx
,nci as cellid
,xxxxx as xxxxx
,xxxx as xxxx
,substr(xxxxxx,1,8) as xxxxxxx
,xxxxxx as xxxxxx
,null as dcnr
,case when xxxxxxxx='9' then 1 else 0 end as ismaodian
,null as xxxxxxx_ultranoctets
,null as xxxxxxx_dltranoctets
,null as xxxxxxx_webdlvalidoctets
,null as xxxxxxx_webdlvaliddelay
,null as xxxxxx_cnt_totalattach_req
,null as xxxxxx_cnt_totalattach_succ
,null as xxxxxx_erbmodify_req
,null as xxxxxx_erbmodify_succ
,null as xxxxxx_erbmodify_err
,null as xxx_ultranoctets
,null as xxx_dltranoctets
,case when xxxxxxxx='10' then 1 else 0 end is_5g_on_off
,null as xxx_webdlvalidoctets
,null as xxx_webdlvaliddelay
,null as is_flow_limit
,null as thzl
,null as swzl
,null as cpyzf
,null as sfzqx
,null as yytfw
,null as rexian
,null as predict
,null as xxx_nbrallWebReq
,null as xxx_nbrallWebResSucc
,null as xxx_WebResDelay
,null as xxx_nbrTcpConn1stReq
,null as xxx_nbrTcpConn1stSucc
,null as xxx_nbrTcpConn2ndSucc
,null as xxx_tcpConn1stDelay
,null as xxx_tcpConn2ndDelay
,null as xxx_tcpConnDelay
,null as xxx_FirstScreenFincnt
,null as xxx_FirstScreenFinTime
,null as xxx_VideoBufferServCnt
,null as xxx_VideoServCnt
,null as xxx_VideoDlOctets
,null as xxx_VideoDlDuxxxxxxxxion
,null as xxx_videoThroughput_cnt
,null as xxx_videoThroughput
,null as xxx_videoBufferCnt
,null as xxx_VideoPlayTime
,null as xxx_InitBufferReq
,null as xxx_InitBufferSucc
,null as xxx_InitBufferDuxxxxxxxxion
,sac_xdrcnt as xdrcnt
,null as lte_flux
,null as g5_under_lte_flux
,null as g5_flux
from xxx_xxx.xxxxxx_day
where day=20231002 and interfaceid='39'
union all
select
2 as tabletype
,xxxxxxxx as xxxxxxxx
,cgi as cellid
,xxxxx
,xxxx
,substr(xxxxxx,1,8) as xxxxxxx
,xxxxxx as xxxxxx
,null dcnr
,case when xxxxxxxx='9' then 1 else 0 end as ismaodian
,xxx_ultranoctets as xxxxxxx_ultranoctets
,xxx_dltranoctets as xxxxxxx_dltranoctets
,xxx_webdlvalidoctets as xxxxxxx_webdlvalidoctets
,xxx_webdlvaliddelay as xxxxxxx_webdlvaliddelay
,null as xxxxxx_cnt_totalattach_req
,null as xxxxxx_cnt_totalattach_succ
,null as xxxxxx_erbmodify_req
,null as xxxxxx_erbmodify_succ
,null as xxxxxx_erbmodify_err
,xxx_ultranoctets
,xxx_dltranoctets
,case when xxxxxxxx='10' then 1 else 0 end as is_5g_on_off
,xxx_webdlvalidoctets
,xxx_webdlvaliddelay
,null as is_flow_limit
,null as thzl
,null as swzl
,null as cpyzf
,null as sfzqx
,null as yytfw
,null as rexian
,null as predict
,xxx_nbrallWebReq
,xxx_nbrallWebResSucc
,xxx_WebResDelay
,xxx_nbrTcpConn1stReq
,xxx_nbrTcpConn1stSucc
,xxx_nbrTcpConn2ndSucc
,xxx_tcpConn1stDelay
,xxx_tcpConn2ndDelay
,xxx_tcpConnDelay
,xxx_FirstScreenFincnt
,xxx_FirstScreenFinTime
,xxx_VideoBufferServCnt
,xxx_VideoServCnt
,xxx_VideoDlOctets
,xxx_VideoDlDuxxxxxxxxion
,cast(null as double) as xxx_videoThroughput_cnt
,cast(null as double) as xxx_videoThroughput
,xxx_videoBufferCnt
,cast(null as double) as xxx_VideoPlayTime
,xxx_InitBufferReq
,xxx_InitBufferSucc
,xxx_InitBufferDuxxxxxxxxion
,xxx_xdrcnt as xdrcnt
,null as lte_flux
,null as g5_under_lte_flux
,null as g5_flux
from xxx_xxx.xxxxxxx_day
where day=20231002 and xxx_xdrcnt>0
) dt group by xxxxx
,xxxx
,xxxxxxx
,xxxxxx
) dt group by dt.xxxxx ) dt
left join (select ue_tac_id,model_id,model_name,vendor,support_volte,sw_version from ods_xxxxx.device where day=20230712 group by ue_tac_id,model_id,model_name,vendor,support_volte,sw_version) dv on dt.xxxxxxx=dv.ue_tac_id
where dt.xxxxx is not null and tabletype>0)
,xxxx_day as (select
da.xxxxxxxxx
,da.xxxxxxxxxxx
,da.xxxxxxxxxxxx
,da.cellid as cellid
,da.earlyresident
,da.nightresident
,da.xxxxx
,da.xxxx
,db.user_lv
,db.nearlthreemonth_arpu
,db.inner_months
,db.dinner_name
,db.dinner_type
,db.is_5gdinner
,da.xxxxxx as xxxxxx
,substr(da.xxxxxx,1,8) as xxxxxxx
,db.xxx_kpimrcnt as xxx_kpimrcnt
,db.xxx_kpimr110cnt as xxx_kpimr110cnt
,db.xxx_kpimrweakcoverage_xxxxxxxxe as xxx_kpimrweakcoverage_xxxxxxxxe
,case when xxx_kpimrweakcoverage_xxxxxxxxe<=10 then 25
when xxx_kpimrweakcoverage_xxxxxxxxe is null then 25
when xxx_kpimrweakcoverage_xxxxxxxxe >=30 then 0
else 25-(xxx_kpimrweakcoverage_xxxxxxxxe-10)*(25/(30-10)) end as xxx_Coverage_score
,xxx_nbrTcpConn2ndSucc
,xxx_nbrTcpConn1stReq
,xxx_tcpConnSuccxxxxxxxxio
,case when xxx_tcpConnSuccxxxxxxxxio<=60 then 0
when xxx_tcpConnSuccxxxxxxxxio>=95 then 5
when xxx_tcpConnSuccxxxxxxxxio is null then 5
else 5-(xxx_tcpConnSuccxxxxxxxxio-95)*(5/(60-95)) end as xxx_tcpConnSucc_score
,xxx_nbrallWebReq
,xxx_nbrallWebResSucc
,xxx_WeballResxxxxxxxxio
,case when xxx_WeballResxxxxxxxxio<=60 then 0
when xxx_WeballResxxxxxxxxio>=95 then 5
when xxx_WeballResxxxxxxxxio is null then 5
else 5-(xxx_WeballResxxxxxxxxio-95)*(5/(60-95)) end as xxx_WebSucc_core
,xxx_shoppingxdrcnt
,xxx_shoppingxdrsucccnt
,xxx_shoppingxdrPoorCnt
,xxx_shoppingtcpDLRttGt200Delay
,xxx_shoppingtcpULRttGt200Delay
,xxx_shoppingtcpRtt200_xxxxxxxxe
,case when xxx_shoppingtcpRtt200_xxxxxxxxe <= 5 then 5
when xxx_shoppingtcpRtt200_xxxxxxxxe is null then 5
when xxx_shoppingtcpRtt200_xxxxxxxxe>=15 then 0
else 5-(xxx_shoppingtcpRtt200_xxxxxxxxe-5)*(5/(15-5)) end as xxx_Shopping_score
,xxx_gamexdrcnt
,xxx_gamexdrsucccnt
,xxx_gamexdrPoorCnt
,xxx_gametcpDLRttGt200Delay
,xxx_gametcpULRttGt200Delay
,xxx_gametcpRtt200_xxxxxxxxe
,case when xxx_gametcpRtt200_xxxxxxxxe<=5 then 10
when xxx_gametcpRtt200_xxxxxxxxe is null then 10
when xxx_gametcpRtt200_xxxxxxxxe>=15 then 0
else 10-(xxx_gametcpRtt200_xxxxxxxxe-5)*(10/(15-5)) end as xxx_game_score
,xxx_avgVideoDlcnt
,xxx_VideoDl3Mbpscnt
,xxx_VideoDl3Mbps_xxxxxxxxe
,case when xxx_VideoDl3Mbps_xxxxxxxxe<=5 then 10
when xxx_VideoDl3Mbps_xxxxxxxxe is null then 10
when xxx_VideoDl3Mbps_xxxxxxxxe>=15 then 0
else 10-(xxx_VideoDl3Mbps_xxxxxxxxe-5)*(10/(15-5)) end as xxx_Video_score
,Call_Union_Req
,Call_Union_Suss
,Call_Union_xxxxxxxxe
,case when Call_Union_xxxxxxxxe<=60 then 0
when Call_Union_xxxxxxxxe>=95 then 20
when Call_Union_xxxxxxxxe is null then 20
else 20-(Call_Union_xxxxxxxxe-95)*(20/(60-95)) end as Volte_successxxxxxxxxe_score
,call_k1
,Drop_Union_Cnt
,Drop_Union_xxxxxxxxe
,case when Drop_Union_xxxxxxxxe<=0.2 then 20
when Drop_Union_xxxxxxxxe is null then 20
when Drop_Union_xxxxxxxxe>=1 then 0
else 20-(Drop_Union_xxxxxxxxe-0.2)*(20/(1-0.2)) end as Volte_dropxxxxxxxxe_score
,case when xxx_kpimrweakcoverage_xxxxxxxxe<=10 then 25
when xxx_kpimrweakcoverage_xxxxxxxxe is null then 25
when xxx_kpimrweakcoverage_xxxxxxxxe >=30 then 0
else 25-(xxx_kpimrweakcoverage_xxxxxxxxe-10)*(25/(30-10)) end +
case when xxx_tcpConnSuccxxxxxxxxio<=60 then 0
when xxx_tcpConnSuccxxxxxxxxio>=95 then 5
when xxx_tcpConnSuccxxxxxxxxio is null then 5
else 5-(xxx_tcpConnSuccxxxxxxxxio-95)*(5/(60-95)) end +
case when xxx_WeballResxxxxxxxxio<=60 then 0
when xxx_WeballResxxxxxxxxio>=95 then 5
when xxx_WeballResxxxxxxxxio is null then 5
else 5-(xxx_WeballResxxxxxxxxio-95)*(5/(60-95)) end +
case when xxx_shoppingtcpRtt200_xxxxxxxxe <= 5 then 5
when xxx_shoppingtcpRtt200_xxxxxxxxe is null then 5
when xxx_shoppingtcpRtt200_xxxxxxxxe>=15 then 0
else 5-(xxx_shoppingtcpRtt200_xxxxxxxxe-5)*(5/(15-5)) end +
case when xxx_gametcpRtt200_xxxxxxxxe<=5 then 10
when xxx_gametcpRtt200_xxxxxxxxe is null then 10
when xxx_gametcpRtt200_xxxxxxxxe>=15 then 0
else 10-(xxx_gametcpRtt200_xxxxxxxxe-5)*(10/(15-5)) end +
case when xxx_VideoDl3Mbps_xxxxxxxxe<=5 then 10
when xxx_VideoDl3Mbps_xxxxxxxxe is null then 10
when xxx_VideoDl3Mbps_xxxxxxxxe>=15 then 0
else 10-(xxx_VideoDl3Mbps_xxxxxxxxe-5)*(10/(15-5)) end +
case when Call_Union_xxxxxxxxe<=60 then 0
when Call_Union_xxxxxxxxe>=95 then 20
when Call_Union_xxxxxxxxe is null then 20
else 20-(Call_Union_xxxxxxxxe-95)*(20/(60-95)) end +
case when Drop_Union_xxxxxxxxe<=0.2 then 20
when Drop_Union_xxxxxxxxe is null then 20
when Drop_Union_xxxxxxxxe>=1 then 0
else 20-(Drop_Union_xxxxxxxxe-0.2)*(20/(1-0.2)) end as Total_perceived_score
,case when (case when xxx_kpimrweakcoverage_xxxxxxxxe<=10 then 25
when xxx_kpimrweakcoverage_xxxxxxxxe is null then 25
when xxx_kpimrweakcoverage_xxxxxxxxe >=30 then 0
else 25-(xxx_kpimrweakcoverage_xxxxxxxxe-10)*(25/(30-10)) end +
case when xxx_tcpConnSuccxxxxxxxxio<=60 then 0
when xxx_tcpConnSuccxxxxxxxxio>=95 then 5
when xxx_tcpConnSuccxxxxxxxxio is null then 5
else 5-(xxx_tcpConnSuccxxxxxxxxio-95)*(5/(60-95)) end +
case when xxx_WeballResxxxxxxxxio<=60 then 0
when xxx_WeballResxxxxxxxxio>=95 then 5
when xxx_WeballResxxxxxxxxio is null then 5
else 5-(xxx_WeballResxxxxxxxxio-95)*(5/(60-95)) end +
case when xxx_shoppingtcpRtt200_xxxxxxxxe <= 5 then 5
when xxx_shoppingtcpRtt200_xxxxxxxxe is null then 5
when xxx_shoppingtcpRtt200_xxxxxxxxe>=15 then 0
else 5-(xxx_shoppingtcpRtt200_xxxxxxxxe-5)*(5/(15-5)) end +
case when xxx_gametcpRtt200_xxxxxxxxe<=5 then 10
when xxx_gametcpRtt200_xxxxxxxxe is null then 10
when xxx_gametcpRtt200_xxxxxxxxe>=15 then 0
else 10-(xxx_gametcpRtt200_xxxxxxxxe-5)*(10/(15-5)) end +
case when xxx_VideoDl3Mbps_xxxxxxxxe<=5 then 10
when xxx_VideoDl3Mbps_xxxxxxxxe is null then 10
when xxx_VideoDl3Mbps_xxxxxxxxe>=15 then 0
else 10-(xxx_VideoDl3Mbps_xxxxxxxxe-5)*(10/(15-5)) end +
case when Call_Union_xxxxxxxxe<=60 then 0
when Call_Union_xxxxxxxxe>=95 then 20
when Call_Union_xxxxxxxxe is null then 20
else 20-(Call_Union_xxxxxxxxe-95)*(20/(60-95)) end +
case when Drop_Union_xxxxxxxxe<=0.2 then 20
when Drop_Union_xxxxxxxxe is null then 20
when Drop_Union_xxxxxxxxe>=1 then 0
else 20-(Drop_Union_xxxxxxxxe-0.2)*(20/(1-0.2)) end) >= 85 then '优秀'
when (case when xxx_kpimrweakcoverage_xxxxxxxxe<=10 then 25
when xxx_kpimrweakcoverage_xxxxxxxxe is null then 25
when xxx_kpimrweakcoverage_xxxxxxxxe >=30 then 0
else 25-(xxx_kpimrweakcoverage_xxxxxxxxe-10)*(25/(30-10)) end +
case when xxx_tcpConnSuccxxxxxxxxio<=60 then 0
when xxx_tcpConnSuccxxxxxxxxio>=95 then 5
when xxx_tcpConnSuccxxxxxxxxio is null then 5
else 5-(xxx_tcpConnSuccxxxxxxxxio-95)*(5/(60-95)) end +
case when xxx_WeballResxxxxxxxxio<=60 then 0
when xxx_WeballResxxxxxxxxio>=95 then 5
when xxx_WeballResxxxxxxxxio is null then 5
else 5-(xxx_WeballResxxxxxxxxio-95)*(5/(60-95)) end +
case when xxx_shoppingtcpRtt200_xxxxxxxxe <= 5 then 5
when xxx_shoppingtcpRtt200_xxxxxxxxe is null then 5
when xxx_shoppingtcpRtt200_xxxxxxxxe>=15 then 0
else 5-(xxx_shoppingtcpRtt200_xxxxxxxxe-5)*(5/(15-5)) end +
case when xxx_gametcpRtt200_xxxxxxxxe<=5 then 10
when xxx_gametcpRtt200_xxxxxxxxe is null then 10
when xxx_gametcpRtt200_xxxxxxxxe>=15 then 0
else 10-(xxx_gametcpRtt200_xxxxxxxxe-5)*(10/(15-5)) end +
case when xxx_VideoDl3Mbps_xxxxxxxxe<=5 then 10
when xxx_VideoDl3Mbps_xxxxxxxxe is null then 10
when xxx_VideoDl3Mbps_xxxxxxxxe>=15 then 0
else 10-(xxx_VideoDl3Mbps_xxxxxxxxe-5)*(10/(15-5)) end +
case when Call_Union_xxxxxxxxe<=60 then 0
when Call_Union_xxxxxxxxe>=95 then 20
when Call_Union_xxxxxxxxe is null then 20
else 20-(Call_Union_xxxxxxxxe-95)*(20/(60-95)) end +
case when Drop_Union_xxxxxxxxe<=0.2 then 20
when Drop_Union_xxxxxxxxe is null then 20
when Drop_Union_xxxxxxxxe>=1 then 0
else 20-(Drop_Union_xxxxxxxxe-0.2)*(20/(1-0.2)) end)<85 and (case when xxx_kpimrweakcoverage_xxxxxxxxe<=10 then 25
when xxx_kpimrweakcoverage_xxxxxxxxe is null then 25
when xxx_kpimrweakcoverage_xxxxxxxxe >=30 then 0
else 25-(xxx_kpimrweakcoverage_xxxxxxxxe-10)*(25/(30-10)) end +
case when xxx_tcpConnSuccxxxxxxxxio<=60 then 0
when xxx_tcpConnSuccxxxxxxxxio>=95 then 5
when xxx_tcpConnSuccxxxxxxxxio is null then 5
else 5-(xxx_tcpConnSuccxxxxxxxxio-95)*(5/(60-95)) end +
case when xxx_WeballResxxxxxxxxio<=60 then 0
when xxx_WeballResxxxxxxxxio>=95 then 5
when xxx_WeballResxxxxxxxxio is null then 5
else 5-(xxx_WeballResxxxxxxxxio-95)*(5/(60-95)) end +
case when xxx_shoppingtcpRtt200_xxxxxxxxe <= 5 then 5
when xxx_shoppingtcpRtt200_xxxxxxxxe is null then 5
when xxx_shoppingtcpRtt200_xxxxxxxxe>=15 then 0
else 5-(xxx_shoppingtcpRtt200_xxxxxxxxe-5)*(5/(15-5)) end +
case when xxx_gametcpRtt200_xxxxxxxxe<=5 then 10
when xxx_gametcpRtt200_xxxxxxxxe is null then 10
when xxx_gametcpRtt200_xxxxxxxxe>=15 then 0
else 10-(xxx_gametcpRtt200_xxxxxxxxe-5)*(10/(15-5)) end +
case when xxx_VideoDl3Mbps_xxxxxxxxe<=5 then 10
when xxx_VideoDl3Mbps_xxxxxxxxe is null then 10
when xxx_VideoDl3Mbps_xxxxxxxxe>=15 then 0
else 10-(xxx_VideoDl3Mbps_xxxxxxxxe-5)*(10/(15-5)) end +
case when Call_Union_xxxxxxxxe<=60 then 0
when Call_Union_xxxxxxxxe>=95 then 20
when Call_Union_xxxxxxxxe is null then 20
else 20-(Call_Union_xxxxxxxxe-95)*(20/(60-95)) end +
case when Drop_Union_xxxxxxxxe<=0.2 then 20
when Drop_Union_xxxxxxxxe is null then 20
when Drop_Union_xxxxxxxxe>=1 then 0
else 20-(Drop_Union_xxxxxxxxe-0.2)*(20/(1-0.2)) end) >= 60 then '一般'
else '差' end as Perceived_results
,sum(1) over(partition by da.cellid,da.xxxxxxxxx) as users_cell
,sum(case when db.user_lv like '四星%' or db.user_lv like '五星%' then 1 end) over(partition by da.cellid,da.xxxxxxxxx) as user_lv45_cell
,da.county as xxxxxxxxxx
,dp.preference_app_type1
,dp.preference_app_type2
,dp.preference_app_type3
,maxxxxxxxxx
,cast(null as bigint) as complain_cnt
from (select
max(datatable) as datatable
,xxxxx
,cellid
,max(xxxx) as xxxx
,max(xxxxxx) as xxxxxx
,max(cast(xxxxxxxx as bigint)) as xxxxxxxx
,max(max(cast(xxxxxxxx as bigint))) over(partition by xxxxx) as maxxxxxxxxx
,max(earlyresident) as earlyresident
,max(nightresident) as nightresident
,max(xxxxxxxxx) as xxxxxxxxx
,max(xxxxxxxxxxxx) as xxxxxxxxxxxx
,max(xxxxxxxxxxx) as xxxxxxxxxxx
,max(county) as county
,sum(xxx_nbrTcpConn2ndSucc) as xxx_nbrTcpConn2ndSucc
,sum(xxx_nbrTcpConn1stReq) as xxx_nbrTcpConn1stReq
,round(sum(xxx_nbrTcpConn2ndSucc)/sum(xxx_nbrTcpConn1stReq),4)*100 as xxx_tcpConnSuccxxxxxxxxio
,sum(xxx_nbrallWebReq) as xxx_nbrallWebReq
,sum(xxx_nbrallWebResSucc) as xxx_nbrallWebResSucc
,round(sum(xxx_nbrallWebResSucc)/sum(xxx_nbrallWebReq),4)*100 as xxx_WeballResxxxxxxxxio
,sum(xxx_shoppingxdrcnt) as xxx_shoppingxdrcnt
,sum(xxx_shoppingxdrsucccnt) as xxx_shoppingxdrsucccnt
,sum(xxx_shoppingxdrPoorCnt) as xxx_shoppingxdrPoorCnt
,sum(xxx_shoppingtcpDLRttGt200Delay) as xxx_shoppingtcpDLRttGt200Delay
,sum(xxx_shoppingtcpULRttGt200Delay) as xxx_shoppingtcpULRttGt200Delay
,round((sum(xxx_shoppingtcpDLRttGt200Delay)+sum(xxx_shoppingtcpULRttGt200Delay))/sum(xxx_shoppingxdrcnt),4)*100 as xxx_shoppingtcpRtt200_xxxxxxxxe
,sum(xxx_gamexdrcnt) as xxx_gamexdrcnt
,sum(xxx_gamexdrsucccnt) as xxx_gamexdrsucccnt
,sum(xxx_gamexdrPoorCnt) as xxx_gamexdrPoorCnt
,sum(xxx_gametcpDLRttGt200Delay) as xxx_gametcpDLRttGt200Delay
,sum(xxx_gametcpULRttGt200Delay) as xxx_gametcpULRttGt200Delay
,round((sum(xxx_gametcpDLRttGt200Delay)+sum(xxx_gametcpULRttGt200Delay))/sum(xxx_gamexdrcnt),4)*100 as xxx_gametcpRtt200_xxxxxxxxe
,sum(xxx_avgVideoDlcnt) as xxx_avgVideoDlcnt
,sum(xxx_VideoDl3Mbpscnt) as xxx_VideoDl3Mbpscnt
,round((sum(xxx_VideoDl3Mbpscnt)/sum(xxx_avgVideoDlcnt)),4)*100 as xxx_VideoDl3Mbps_xxxxxxxxe
,sum(Call_Union_Req) as Call_Union_Req
,sum(Call_Union_Suss) as Call_Union_Suss
,round((sum(Call_Union_Suss)/sum(Call_Union_Req)),4)*100 as Call_Union_xxxxxxxxe
,sum(call_k1) as call_k1
,sum(Drop_Union_Cnt) as Drop_Union_Cnt
,round((sum(Drop_Union_Cnt)/sum(call_k1)),4)*100 as Drop_Union_xxxxxxxxe
from (
select
1 as datatable
,xxxxx
,null as xxxxxxxx
,max(case when stathour='4' then cellid end) as cellid
,max(xxxx) as xxxx
,max(xxxxxx) as xxxxxx
,max(case when stathour='1' then cellid end) as earlyresident
,max(case when stathour='3' then cellid end) as nightresident
,max(case when stathour='4' then xxxxxxxxx end) as xxxxxxxxx
,max(case when stathour='4' then xxxxxxxxxxxx end) as xxxxxxxxxxxx
,max(case when stathour='4' then xxxxxxxxxxx end) as xxxxxxxxxxx
,max(case when stathour='4' then county end) as county
,null as xxx_nbrTcpConn2ndSucc
,null as xxx_nbrTcpConn1stReq
,null as xxx_nbrallWebReq
,null as xxx_nbrallWebResSucc
,null as xxx_shoppingxdrcnt
,null as xxx_shoppingxdrsucccnt
,null as xxx_shoppingxdrPoorCnt
,null as xxx_shoppingtcpDLRttGt200Delay
,null as xxx_shoppingtcpULRttGt200Delay
,null as xxx_gamexdrcnt
,null as xxx_gamexdrsucccnt
,null as xxx_gamexdrPoorCnt
,null as xxx_gametcpDLRttGt200Delay
,null as xxx_gametcpULRttGt200Delay
,null as xxx_avgVideoDlcnt
,null as xxx_VideoDl3Mbpscnt
,null as Call_Union_Req
,null as Call_Union_Suss
,null as call_k1
,null as Drop_Union_Cnt
from xxx_xxx.st_xxxxxxxxxxxx_day
where day=20231002 and rank=1
group by xxxxx
union all
select
1 as datatable
,xxxxx
,xxxxxxxx
,cgi as cellid
,xxxx
,xxxxxx
,null as earlyresident
,null as nightresident
,xxxxxxxxx
,xxxxxxxxxxxx
,xxxxxxxxxxx
,null as county
,xxx_nbrTcpConn2ndSucc as xxx_nbrTcpConn2ndSucc
,xxx_nbrTcpConn1stReq as xxx_nbrTcpConn1stReq
,xxx_nbrallWebReq as xxx_nbrallWebReq
,xxx_nbrallWebResSucc as xxx_nbrallWebResSucc
,case when substr(appunicode,1,2)='13' then xxx_xdrcnt end as xxx_shoppingxdrcnt
,case when substr(appunicode,1,2)='13' then xxx_xdrsucccnt end as xxx_shoppingxdrsucccnt
,case when substr(appunicode,1,2)='13' then xxx_xdrPoorCnt end as xxx_shoppingxdrPoorCnt
,case when substr(appunicode,1,2)='13' then xxx_tcpDLRttGt200Delay end as xxx_shoppingtcpDLRttGt200Delay
,case when substr(appunicode,1,2)='13' then xxx_tcpULRttGt200Delay end as xxx_shoppingtcpULRttGt200Delay
,case when substr(appunicode,1,2)='05' then xxx_xdrcnt end as xxx_gamexdrcnt
,case when substr(appunicode,1,2)='05' then xxx_xdrsucccnt end as xxx_gamexdrsucccnt
,case when substr(appunicode,1,2)='05' then xxx_xdrPoorCnt end as xxx_gamexdrPoorCnt
,case when substr(appunicode,1,2)='05' then xxx_tcpDLRttGt200Delay end as xxx_gametcpDLRttGt200Delay
,case when substr(appunicode,1,2)='05' then xxx_tcpULRttGt200Delay end as xxx_gametcpULRttGt200Delay
,case when substr(appunicode,1,2)='02' then xxx_xdrcnt end as xxx_avgVideoDlcnt
,case when substr(appunicode,1,2)='02' then xxx_VideoDl3Mbpscnt end as xxx_VideoDl3Mbpscnt
,null as Call_Union_Req
,null as Call_Union_Suss
,null as call_k1
,null as Drop_Union_Cnt
from xxx_xxx.xxxxxxx_day
where day=20231002
union all
select
0 as datatable
,xxxxx
,null as xxxxxxxx
,cellid
,null as xxxx
,null as xxxxxx
,null as earlyresident
,null as nightresident
,null as xxxxxxxxx
,null as xxxxxxxxxxxx
,null as xxxxxxxxxxx
,null as county
,null as xxx_nbrTcpConn2ndSucc
,null as xxx_nbrTcpConn1stReq
,null as xxx_nbrallWebReq
,null as xxx_nbrallWebResSucc
,null as xxx_shoppingxdrcnt
,null as xxx_shoppingxdrsucccnt
,null as xxx_shoppingxdrPoorCnt
,null as xxx_shoppingtcpDLRttGt200Delay
,null as xxx_shoppingtcpULRttGt200Delay
,null as xxx_gamexdrcnt
,null as xxx_gamexdrsucccnt
,null as xxx_gamexdrPoorCnt
,null as xxx_gametcpDLRttGt200Delay
,null as xxx_gametcpULRttGt200Delay
,null as xxx_avgVideoDlcnt
,null as xxx_VideoDl3Mbpscnt
,Call_Gm_Req as Call_Union_Req
,Call_Gm_Suss as Call_Union_Suss
,Call_Gm_Ring as call_k1
,Drop_Gm_Cnt as Drop_Union_Cnt
from xxx_xxx.xxxxxxxxxxxxxxxxxxxxxx_hour
where day=20231002
) da
where xxxxx is not null
group by
xxxxx
,cellid
) da
left join (select
service_nbr
,max(install_month) as inner_months
,max(ofr_name) as dinner_name
,max(case when ofr_5g_flag='1' then '5G' when ofr_5g_flag='0' then '4G' end) as dinner_type
,max(ofr_5g_flag) as is_5gdinner
,max(star_flag) as user_lv
,max(net_fee) as nearlthreemonth_arpu
,sum(nvl(cast(rsrp_count_5g as double),0)+nvl(cast(rsrp_count as double),0)) as xxx_kpimrcnt
,round(sum(nvl(cast(rsrp_count_5g as double)*(1-cast(mr_cover_xxxxxxxxe_110_5g as double)/100),0)+nvl(cast(rsrp_count as double)*(1-cast(mr_cover_xxxxxxxxe_110 as double)/100),0))) as xxx_kpimr110cnt
,round(round(sum(nvl(cast(rsrp_count_5g as double)*(1-cast(mr_cover_xxxxxxxxe_110_5g as double)/100),0)
+nvl(cast(rsrp_count as double)*(1-cast(mr_cover_xxxxxxxxe_110 as double)/100),0)))/sum(nvl(cast(rsrp_count_5g as double),0)+nvl(cast(rsrp_count as double),0)),4)*100 as xxx_kpimrweakcoverage_xxxxxxxxe
from xxx_xxx.xxxxxxxxxxxxxxxxxxxxxxx_day
where day=20231002 and net_num='1'
group by service_nbr) db on da.xxxxx=db.service_nbr
left join (select
xxxxx
,max(case when rank=1 then app_type end) as preference_app_type1
,max(case when rank=2 then app_type end) as preference_app_type2
,max(case when rank=3 then app_type end) as preference_app_type3
from(select
xxxxx
,app_type
,sum(xxx_xdrcnt) as xxx_xdrcnt
,row_number() over(partition by xxxxx order by sum(xxx_xdrcnt) desc) rank
from xxx_xxx.xxxxxxxxxxxxxxxxxxxxxxxx_hour
where day=20231002 and appunicode not in ('22006500','22999900')
group by xxxxx
,app_type) da where rank<=3
group by xxxxx) dp on da.xxxxx=dp.xxxxx
where datatable=1)
insert overwrite table xxx_xxx.st_userportrait_day partition(day=20231002,minute='0000',vprovince=200)
select /* +REPARTITION(2) */
cast(from_unixtime(unix_timestamp('20231002','yyyyMMdd')+28800) as timestamp) as timevalue
,xxxxxxxxx
,xxxxxxxxxx
,xxxxxxxxxxx
,xxxxxxxxxxxx
,fullresident
,earlyresident
,nightresident
,da.xxxxx
,xxxx
,xxxxxx
,da.xxxxxxx
,fact_id
,cat_one
,model_id
,terminalname
,mt_type
,null as software_version
,card_slot
,is_support_volte
,dcnr
,maxxxxxxxxx
,ismme5gxxxxxxxx
,iss1u5gxxxxxxxx
,is_5g_on_off
,preference_app_type1
,preference_app_type2
,preference_app_type3
,dinner_type
,is_5gdinner
,is_flow_limit
,user_lv
,inner_months
,nearlthreemonth_arpu
,complain_cnt
,thzl
,swzl
,cpyzf
,sfzqx
,yytfw
,rexian
,predict
,xxxxxx_cnt_totalattach_req
,xxxxxx_cnt_totalattach_succ
,xxxxxx_cnt_md_totalattach_req
,xxxxxx_cnt_md_totalattach_succ
,xxxxxx_cnt_md_totalattach_fail
,mdcellcnt
,nomdcellcnt
,xxxxxx_erbmodify_req
,xxxxxx_erbmodify_succ
,xxxxxx_erbmodify_err
,xxxxxxx_tranoctets
,xxxxxxx_5g_tranoctets
,xxxxxxx_4g_tranoctets
,xxxxxxx_webdlvalidoctets
,xxxxxxx_webdlvaliddelay
,xxxxxxx_5g_webdlvalidoctets
,xxxxxxx_5g_webdlvaliddelay
,xxxxxxx_4g_webdlvalidoctets
,xxxxxxx_4g_webdlvaliddelay
,xxx_tranoctets
,xxx_webdlvalidoctets
,xxx_webdlvaliddelay
,xxx_nbrTcpConn1stReq
,xxx_nbrTcpConn1stSucc
,xxx_tcpConn1stDelay
,xxx_nbrTcpConn2ndSucc
,xxx_tcpConn2ndDelay
,xxx_tcpConnDelay
,xxx_nbrallWebReq
,xxx_nbrallWebResSucc
,xxx_WebResDelay
,xxx_FirstScreenFincnt
,xxx_FirstScreenFinTime
,xxx_VideoBufferServCnt
,xxx_VideoServCnt
,xxx_VideoDlOctets
,xxx_VideoDlDuxxxxxxxxion
,xxx_videoThroughput_cnt
,xxx_videoThroughput
,xxx_videoBufferCnt
,xxx_VideoPlayTime
,xxx_InitBufferReq
,xxx_InitBufferSucc
,xxx_InitBufferDuxxxxxxxxion
,cast(null as double) as xxx_kpimrcnt
,cast(null as double) as xxx_kpimr110cnt
,cast(null as double) as xxx_kpimrweakcoverage_xxxxxxxxe
,cast(null as double) as xxx_coverage_score
,cast(null as double) as xxx_nbrtcpconn2ndsucc_cell
,cast(null as double) as xxx_nbrtcpconn1streq_cell
,cast(null as double) as xxx_tcpconnsuccxxxxxxxxio_cell
,cast(null as double) as xxx_tcpconnsucc_score
,cast(null as double) as xxx_nbrallwebreq_cell
,cast(null as double) as xxx_nbrallwebressucc_cell
,cast(null as double) as xxx_weballresxxxxxxxxio_cell
,cast(null as double) as xxx_websucc_core
,cast(null as double) as xxx_shoppingxdrcnt
,cast(null as double) as xxx_shoppingxdrsucccnt
,cast(null as double) as xxx_shoppingxdrpoorcnt
,cast(null as double) as xxx_shoppingtcpdlrttgt200delay
,cast(null as double) as xxx_shoppingtcpulrttgt200delay
,cast(null as double) as xxx_shoppingtcprtt200_xxxxxxxxe
,cast(null as double) as xxx_shopping_score
,cast(null as double) as xxx_gamexdrcnt
,cast(null as double) as xxx_gamexdrsucccnt
,cast(null as double) as xxx_gamexdrpoorcnt
,cast(null as double) as xxx_gametcpdlrttgt200delay
,cast(null as double) as xxx_gametcpulrttgt200delay
,cast(null as double) as xxx_gametcprtt200_xxxxxxxxe
,cast(null as double) as xxx_game_score
,cast(null as double) as xxx_avgvideodlcnt
,cast(null as double) as xxx_videodl3mbpscnt
,cast(null as double) as xxx_videodl3mbps_xxxxxxxxe
,cast(null as double) as xxx_video_score
,cast(null as double) as call_union_req
,cast(null as double) as call_union_suss
,cast(null as double) as call_union_xxxxxxxxe
,cast(null as double) as volte_successxxxxxxxxe_score
,cast(null as double) as call_k1
,cast(null as double) as drop_union_cnt
,cast(null as double) as drop_union_xxxxxxxxe
,cast(null as double) as volte_dropxxxxxxxxe_score
,cast(null as double) as total_perceived_score
,cast(null as double) as perceived_results
,cast(null as double) as users_cell
,cast(null as double) as user_lv45_cell
,cast(null as double) as dinner_name
,cast(null as double) as user_tag
,cast(null as double) as terminallist_flag
,cast(null as double) as imsflag
,cast(null as double) as lte_flux
,cast(null as double) as g5_under_lte_flux
,cast(null as double) as g5_flux
from (select
max(xxxxxxxxx) as xxxxxxxxx
,max(xxxxxxxxxx) as xxxxxxxxxx
,max(xxxxxxxxxxx) as xxxxxxxxxxx
,max(xxxxxxxxxxxx) as xxxxxxxxxxxx
,max(fullresident) as fullresident
,max(earlyresident) as earlyresident
,max(nightresident) as nightresident
,xxxxx
,max(xxxx) as xxxx
,max(xxxxxx) as xxxxxx
,max(xxxxxxx) as xxxxxxx
,max(fact_id) as fact_id
,max(cat_one) as cat_one
,max(model_id) as model_id
,max(terminalname) as terminalname
,max(mt_type) as mt_type
,max(software_version) as software_version
,max(card_slot) as card_slot
,max(is_support_volte) as is_support_volte
,max(case when mt_type='5' then dcnr end) as dcnr
,max(cast(maxxxxxxxxx as bigint)) as maxxxxxxxxx
,max(ismme5gxxxxxxxx) as ismme5gxxxxxxxx
,max(iss1u5gxxxxxxxx) as iss1u5gxxxxxxxx
,max(is_5g_on_off) as is_5g_on_off
,max(preference_app_type1) as preference_app_type1
,max(preference_app_type2) as preference_app_type2
,max(preference_app_type3) as preference_app_type3
,max(dinner_type) as dinner_type
,max(is_5gdinner) as is_5gdinner
,max(case when is_flow_limit is not null and is_flow_limit<>'' then cast(is_flow_limit as string) else '0' end) as is_flow_limit
,max(user_lv) as user_lv
,max(cast(inner_months as double)) as inner_months
,max(cast(nearlthreemonth_arpu as double)) as nearlthreemonth_arpu
,max(complain_cnt) as complain_cnt
,sum(thzl) as thzl
,sum(swzl) as swzl
,sum(cpyzf) as cpyzf
,sum(sfzqx) as sfzqx
,sum(yytfw) as yytfw
,sum(rexian) as rexian
,sum(predict) as predict
,sum(xxxxxx_cnt_totalattach_req) as xxxxxx_cnt_totalattach_req
,sum(xxxxxx_cnt_totalattach_succ) as xxxxxx_cnt_totalattach_succ
,sum(xxxxxx_cnt_md_totalattach_req) as xxxxxx_cnt_md_totalattach_req
,sum(xxxxxx_cnt_md_totalattach_succ) as xxxxxx_cnt_md_totalattach_succ
,sum(xxxxxx_cnt_md_totalattach_fail) as xxxxxx_cnt_md_totalattach_fail
,sum(mdcellcnt) as mdcellcnt
,sum(nomdcellcnt) as nomdcellcnt
,sum(xxxxxx_erbmodify_req) as xxxxxx_erbmodify_req
,sum(xxxxxx_erbmodify_succ) as xxxxxx_erbmodify_succ
,sum(xxxxxx_erbmodify_err) as xxxxxx_erbmodify_err
,sum(xxxxxxx_tranoctets) as xxxxxxx_tranoctets
,sum(xxxxxxx_5g_tranoctets) as xxxxxxx_5g_tranoctets
,sum(xxxxxxx_4g_tranoctets) as xxxxxxx_4g_tranoctets
,sum(xxxxxxx_webdlvalidoctets) as xxxxxxx_webdlvalidoctets
,sum(xxxxxxx_webdlvaliddelay) as xxxxxxx_webdlvaliddelay
,sum(xxxxxxx_5g_webdlvalidoctets) as xxxxxxx_5g_webdlvalidoctets
,sum(xxxxxxx_5g_webdlvaliddelay) as xxxxxxx_5g_webdlvaliddelay
,sum(xxxxxxx_4g_webdlvalidoctets) as xxxxxxx_4g_webdlvalidoctets
,sum(xxxxxxx_4g_webdlvaliddelay) as xxxxxxx_4g_webdlvaliddelay
,sum(xxx_tranoctets) as xxx_tranoctets
,sum(xxx_webdlvalidoctets) as xxx_webdlvalidoctets
,sum(xxx_webdlvaliddelay) as xxx_webdlvaliddelay
,sum(xxx_nbrTcpConn1stReq) as xxx_nbrTcpConn1stReq
,sum(xxx_nbrTcpConn1stSucc) as xxx_nbrTcpConn1stSucc
,sum(xxx_tcpConn1stDelay) as xxx_tcpConn1stDelay
,sum(xxx_nbrTcpConn2ndSucc) as xxx_nbrTcpConn2ndSucc
,sum(xxx_tcpConn2ndDelay) as xxx_tcpConn2ndDelay
,sum(xxx_tcpConnDelay) as xxx_tcpConnDelay
,sum(xxx_nbrallWebReq) as xxx_nbrallWebReq
,sum(xxx_nbrallWebResSucc) as xxx_nbrallWebResSucc
,sum(xxx_WebResDelay) as xxx_WebResDelay
,sum(xxx_FirstScreenFincnt) as xxx_FirstScreenFincnt
,sum(xxx_FirstScreenFinTime) as xxx_FirstScreenFinTime
,sum(xxx_VideoBufferServCnt) as xxx_VideoBufferServCnt
,sum(xxx_VideoServCnt) as xxx_VideoServCnt
,sum(xxx_VideoDlOctets) as xxx_VideoDlOctets
,sum(xxx_VideoDlDuxxxxxxxxion) as xxx_VideoDlDuxxxxxxxxion
,sum(xxx_videoThroughput_cnt) as xxx_videoThroughput_cnt
,sum(xxx_videoThroughput) as xxx_videoThroughput
,sum(xxx_videoBufferCnt) as xxx_videoBufferCnt
,sum(xxx_VideoPlayTime) as xxx_VideoPlayTime
,sum(xxx_InitBufferReq) as xxx_InitBufferReq
,sum(xxx_InitBufferSucc) as xxx_InitBufferSucc
,sum(xxx_InitBufferDuxxxxxxxxion) as xxx_InitBufferDuxxxxxxxxion
from (
select
null as xxxxxxxxx
,null as xxxxxxxxxxx
,null as xxxxxxxxxxxx
,null as xxxxxxxxxx
,null as earlyresident
,null as nightresident
,null as fullresident
,dcnr
,case when mt_type='4' then '6' else maxxxxxxxxx end as maxxxxxxxxx
,ismme5gxxxxxxxx
,iss1u5gxxxxxxxx
,case when mt_type='4' then '0' else is_5g_on_off end as is_5g_on_off
,xxxxx
,xxxx
,xxxxxx
,xxxxxxx
,fact_id
,cat_one
,model_id
,terminalname
,mt_type
,software_version
,card_slot
,is_support_volte
,null as dinner_name
,null as dinner_type
,null as is_5gdinner
,is_flow_limit
,null as inner_months
,null as user_lv
,null as nearlthreemonth_arpu
,null as complain_cnt
,null as preference_app_type1
,null as preference_app_type2
,null as preference_app_type3
,thzl
,swzl
,cpyzf
,sfzqx
,yytfw
,rexian
,predict
,xxxxxx_cnt_totalattach_req
,xxxxxx_cnt_totalattach_succ
,xxxxxx_cnt_md_totalattach_req
,xxxxxx_cnt_md_totalattach_succ
,xxxxxx_cnt_md_totalattach_fail
,mdcellcnt
,nomdcellcnt
,xxxxxx_erbmodify_req
,xxxxxx_erbmodify_succ
,xxxxxx_erbmodify_err
,xxxxxxx_tranoctets
,xxxxxxx_5g_tranoctets
,xxxxxxx_4g_tranoctets
,xxxxxxx_webdlvalidoctets
,xxxxxxx_webdlvaliddelay
,xxxxxxx_5g_webdlvalidoctets
,xxxxxxx_5g_webdlvaliddelay
,xxxxxxx_4g_webdlvalidoctets
,xxxxxxx_4g_webdlvaliddelay
,xxx_tranoctets
,xxx_webdlvalidoctets
,xxx_webdlvaliddelay
,xxx_nbrTcpConn1stReq
,xxx_nbrTcpConn1stSucc
,xxx_tcpConn1stDelay
,xxx_nbrTcpConn2ndSucc
,xxx_tcpConn2ndDelay
,xxx_tcpConnDelay
,xxx_nbrallWebReq
,xxx_nbrallWebResSucc
,xxx_WebResDelay
,xxx_FirstScreenFincnt
,xxx_FirstScreenFinTime
,xxx_VideoBufferServCnt
,xxx_VideoServCnt
,xxx_VideoDlOctets
,xxx_VideoDlDuxxxxxxxxion
,xxx_videoThroughput_cnt
,xxx_videoThroughput
,xxx_videoBufferCnt
,xxx_VideoPlayTime
,xxx_InitBufferReq
,xxx_InitBufferSucc
,xxx_InitBufferDuxxxxxxxxion
from st_xxxxxxxxxxxxxxx_day
union all
select
xxxxxxxxx
,xxxxxxxxxxx
,xxxxxxxxxxxx
,xxxxxxxxxx
,earlyresident
,nightresident
,cellid as fullresident
,null as dcnr
,null as maxxxxxxxxx
,null as ismme5gxxxxxxxx
,null as iss1u5gxxxxxxxx
,null as is_5g_on_off
,xxxxx
,xxxx
,null as xxxxxx
,null as xxxxxxx
,null as fact_id
,null as cat_one
,null as model_id
,null as terminalname
,null as mt_type
,null as software_version
,null as card_slot
,null as is_support_volte
,dinner_name
,dinner_type
,is_5gdinner
,null as is_flow_limit
,inner_months
,user_lv
,nearlthreemonth_arpu
,complain_cnt
,preference_app_type1
,preference_app_type2
,preference_app_type3
,null as thzl
,null as swzl
,null as cpyzf
,null as sfzqx
,null as yytfw
,null as rexian
,null as predict
,null as xxxxxx_cnt_totalattach_req
,null as xxxxxx_cnt_totalattach_succ
,null as xxxxxx_cnt_md_totalattach_req
,null as xxxxxx_cnt_md_totalattach_succ
,null as xxxxxx_cnt_md_totalattach_fail
,null as mdcellcnt
,null as nomdcellcnt
,null as xxxxxx_erbmodify_req
,null as xxxxxx_erbmodify_succ
,null as xxxxxx_erbmodify_err
,null as xxxxxxx_tranoctets
,null as xxxxxxx_5g_tranoctets
,null as xxxxxxx_4g_tranoctets
,null as xxxxxxx_webdlvalidoctets
,null as xxxxxxx_webdlvaliddelay
,null as xxxxxxx_5g_webdlvalidoctets
,null as xxxxxxx_5g_webdlvaliddelay
,null as xxxxxxx_4g_webdlvalidoctets
,null as xxxxxxx_4g_webdlvaliddelay
,null as xxx_tranoctets
,null as xxx_webdlvalidoctets
,null as xxx_webdlvaliddelay
,null as xxx_nbrTcpConn1stReq
,null as xxx_nbrTcpConn1stSucc
,null as xxx_tcpConn1stDelay
,null as xxx_nbrTcpConn2ndSucc
,null as xxx_tcpConn2ndDelay
,null as xxx_tcpConnDelay
,null as xxx_nbrallWebReq
,null as xxx_nbrallWebResSucc
,null as xxx_WebResDelay
,null as xxx_FirstScreenFincnt
,null as xxx_FirstScreenFinTime
,null as xxx_VideoBufferServCnt
,null as xxx_VideoServCnt
,null as xxx_VideoDlOctets
,null as xxx_VideoDlDuxxxxxxxxion
,null as xxx_videoThroughput_cnt
,null as xxx_videoThroughput
,null as xxx_videoBufferCnt
,null as xxx_VideoPlayTime
,null as xxx_InitBufferReq
,null as xxx_InitBufferSucc
,null as xxx_InitBufferDuxxxxxxxxion
from xxxx_day
) da group by xxxxx) da
left join xxx_xxx.cfg_xxxxx dc on da.xxxxxxx=dc.xxxxxxx
left join xxx_xxx.cfg_xxxxxxl_core dt on da.fullresident=dt.cgi
