[
https://issues.apache.org/jira/browse/CARBONDATA-3403?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Akash R Nilugal updated CARBONDATA-3403:
----------------------------------------
Description:
MV is not working for like and filter AND and OR queries
Steps:
create table brinjal (imei string,AMSize string,channelsId string,ActiveCountry
string, Activecity string,gamePointId double,deviceInformationId
double,productionDate Timestamp,deliveryDate timestamp,deliverycharge double)
STORED BY 'org.apache.carbondata.format' ;
create datamap brinjal_mv_tab_nlz_aa016 on table brinjal using 'mv' as select
imei,AMSize,channelsId from brinjal where ActiveCountry NOT LIKE 'US' group by
imei,AMSize,channelsId;
create datamap brinjal_mv_tab_nlz_aa018 on table brinjal using 'mv' as select
imei,AMSize,channelsId,ActiveCountry from brinjal where ActiveCountry
='Chinese' or channelsId =4 group by imei,AMSize,channelsId,ActiveCountry;
then
select imei,AMSize,channelsId from brinjal where ActiveCountry NOT LIKE 'US'
group by imei,AMSize,channelsId; and
select imei,AMSize,channelsId,ActiveCountry from brinjal where ActiveCountry
='Chinese' or channelsId =4 group by imei,AMSize,channelsId,ActiveCountry;
are not hitting the datamap cretaed
CREATE DATAMAP sc_mvdm_nlz_003 USING 'MV' AS
SELECT CAST(floor((STARTTIME + 28800) / 900) * 900 - 28800 AS INT) AS a900
,S1MME_ATTACH_SUCC_TIMES AS S1MME_S1MME_ATTACH_SUCC_TIMES_C
,S1MME_EPS_ATTACH_SUCC_TIMES AS S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C
,S1MME_COMBINED_EPS_IMSI_ATTACH_DELAY_MSEL AS
S1MME_COMBINED_EPS_IMSI_ATTACH_DELAY_MSEL_C
,S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES AS
S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C
,S1MME_ATTACH_DELAY_MSEL AS S1MME_S1MME_ATTACH_DELAY_MSEL_C
,S1MME_EPS_ATTACH_REQ_TIMES AS S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C
,S1MME_EPS_ATTACH_DELAY_MSEL AS S1MME_S1MME_EPS_ATTACH_DELAY_MSEL_C
,S1MME_ATTACH_REQ_TIMES AS S1MME_S1MME_ATTACH_REQ_TIMES_C
,S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES AS
S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C
,STARTTIME
FROM sdr_signal_s1mme_cgisai_15min_17938_1kw;
CREATE DATAMAP sc_mvdm_nlz_001 USING 'MV' AS
SELECT CAST(floor((STARTTIME + 28800) / 900) * 900 - 28800 AS INT) AS a900
,S1MME_ATTACH_SUCC_TIMES AS S1MME_S1MME_ATTACH_SUCC_TIMES_C
,S1MME_EPS_ATTACH_SUCC_TIMES AS S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C
,S1MME_COMBINED_EPS_IMSI_ATTACH_DELAY_MSEL AS
S1MME_COMBINED_EPS_IMSI_ATTACH_DELAY_MSEL_C
,S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES AS
S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C
,S1MME_ATTACH_DELAY_MSEL AS S1MME_S1MME_ATTACH_DELAY_MSEL_C
,S1MME_EPS_ATTACH_REQ_TIMES AS S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C
,S1MME_EPS_ATTACH_DELAY_MSEL AS S1MME_S1MME_EPS_ATTACH_DELAY_MSEL_C
,S1MME_ATTACH_REQ_TIMES AS S1MME_S1MME_ATTACH_REQ_TIMES_C
,S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES AS
S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C
,DIM_LOC_4G_CGI_ENODEB.a202080101 AS a202080101
,STARTTIME
FROM sdr_signal_s1mme_cgisai_15min_17938_1kw
LEFT JOIN (
SELECT CGISAI AS a202050101
,CASE
WHEN ACCESS_TYPE_ID = 2
THEN CGISAI
END AS a202080101
,CASE
WHEN ACCESS_TYPE_ID = 2
THEN CELL_NAME
END AS NAME_202080101
FROM DIM_LOC_CGISAI
GROUP BY CGISAI
,CASE
WHEN ACCESS_TYPE_ID = 2
THEN CGISAI
END
,CASE
WHEN ACCESS_TYPE_ID = 2
THEN CELL_NAME
END
) DIM_LOC_4G_CGI_ENODEB ON CGISAI = DIM_LOC_4G_CGI_ENODEB.a202050101;
create datamap sc_mvdm_lz_001 using 'mv' with deferred rebuild as select
cdr_id,max(record_id) from tbl_data_event_410000_carbon group by cdr_id;
CREATE DATAMAP sc_mvdm_nlz_002 USING 'MV' AS
SELECT MT.a900 AS a900
,CASE
WHEN COALESCE(SUM(S1MME_S1MME_ATTACH_REQ_TIMES_C), 0) = 0
THEN NULL
ELSE CASE
WHEN CAST(COALESCE(SUM(S1MME_S1MME_ATTACH_REQ_TIMES_C), 0) AS decimal)
= 0
THEN 0
ELSE CAST(COALESCE(SUM(S1MME_S1MME_ATTACH_SUCC_TIMES_C), 0) AS decimal)
/ CAST(COALESCE(SUM(S1MME_S1MME_ATTACH_REQ_TIMES_C), 0) AS decimal)
END * 100
END AS EPC_KPI_S1_MME_ATTACH_SUCCESS_RATE
,COALESCE(SUM(S1MME_S1MME_ATTACH_REQ_TIMES_C), 0) AS
S1MME_S1MME_ATTACH_REQ_TIMES
,COALESCE(SUM(S1MME_S1MME_ATTACH_SUCC_TIMES_C), 0) AS
S1MME_S1MME_ATTACH_SUCC_TIMES
,COALESCE(SUM(S1MME_S1MME_ATTACH_REQ_TIMES_C), 0) -
COALESCE(SUM(S1MME_S1MME_ATTACH_SUCC_TIMES_C), 0) AS
EPC_KPI_S1MME_ATTACH_FAIL_TIMES
,CASE
WHEN COALESCE(SUM(S1MME_S1MME_ATTACH_SUCC_TIMES_C), 0) = 0
THEN NULL
ELSE CASE
WHEN CAST(COALESCE(SUM(S1MME_S1MME_ATTACH_SUCC_TIMES_C), 0) AS decimal)
= 0
THEN 0
ELSE CAST(COALESCE(SUM(S1MME_S1MME_ATTACH_DELAY_MSEL_C), 0) AS decimal)
/ CAST(COALESCE(SUM(S1MME_S1MME_ATTACH_SUCC_TIMES_C), 0) AS decimal)
END
END AS EPC_KPI_S1_MME_ATTACH_DELAY
,CASE
WHEN COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C), 0) = 0
THEN NULL
ELSE CASE
WHEN CAST(COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C), 0) AS
decimal) = 0
THEN 0
ELSE CAST(COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C), 0) AS
decimal) / CAST(COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C), 0) AS decimal)
END * 100
END AS EPC_KPI_S1_MME_EPS_ATTACH_SUCCESS_RATE
,COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C), 0) AS
S1MME_S1MME_EPS_ATTACH_REQ_TIMES
,COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C), 0) AS
S1MME_S1MME_EPS_ATTACH_SUCC_TIMES
,COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C), 0) -
COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C), 0) AS
EPC_KPI_S1MME_EPS_ATTACH_FAIL_TIMES
,CASE
WHEN COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C), 0) = 0
THEN NULL
ELSE CASE
WHEN CAST(COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C), 0) AS
decimal) = 0
THEN 0
ELSE CAST(COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_DELAY_MSEL_C), 0) AS
decimal) / CAST(COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C), 0) AS
decimal)
END
END AS EPC_KPI_S1_MME_EPS_ATTACH_DELAY
,CASE
WHEN COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C), 0) = 0
THEN NULL
ELSE CASE
WHEN CAST(COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C), 0)
AS decimal) = 0
THEN 0
ELSE CAST(COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C), 0)
AS decimal) / CAST(COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C), 0)
AS decimal)
END * 100
END AS EPC_KPI_S1_MME_COMBINED_EPS_IMSI_ATTACH_SUCCESS_RATE
,COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C), 0) AS
S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES
,COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C), 0) AS
S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES
,COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C), 0) -
COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C), 0) AS
EPC_KPI_S1MME_COMBINED_EPS_IMSI_ATTACH_FAIL_TIMES
,CASE
WHEN COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C), 0) = 0
THEN NULL
ELSE CASE
WHEN CAST(COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C), 0)
AS decimal) = 0
THEN 0
ELSE CAST(COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_DELAY_MSEL_C), 0)
AS decimal) / CAST(COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C),
0) AS decimal)
END
END AS EPC_KPI_S1_MME_COMBINED_EPS_IMSI_ATTACH_DELAY
,MT.a202080101 AS a202080101
,COUNT(1) OVER () AS countNum
FROM (
SELECT CAST(floor((STARTTIME + 28800) / 900) * 900 - 28800 AS INT) AS a900
,S1MME_ATTACH_SUCC_TIMES AS S1MME_S1MME_ATTACH_SUCC_TIMES_C
,S1MME_EPS_ATTACH_SUCC_TIMES AS S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C
,S1MME_COMBINED_EPS_IMSI_ATTACH_DELAY_MSEL AS
S1MME_COMBINED_EPS_IMSI_ATTACH_DELAY_MSEL_C
,S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES AS
S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C
,S1MME_ATTACH_DELAY_MSEL AS S1MME_S1MME_ATTACH_DELAY_MSEL_C
,S1MME_EPS_ATTACH_REQ_TIMES AS S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C
,S1MME_EPS_ATTACH_DELAY_MSEL AS S1MME_S1MME_EPS_ATTACH_DELAY_MSEL_C
,S1MME_ATTACH_REQ_TIMES AS S1MME_S1MME_ATTACH_REQ_TIMES_C
,S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES AS
S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C
,DIM_LOC_4G_CGI_ENODEB.a202080101 AS a202080101
FROM sdr_signal_s1mme_cgisai_15min_17938_1kw
LEFT JOIN (
SELECT CGISAI AS a202050101
,CASE
WHEN ACCESS_TYPE_ID = 2
THEN CGISAI
END AS a202080101
,CASE
WHEN ACCESS_TYPE_ID = 2
THEN CELL_NAME
END AS NAME_202080101
FROM DIM_LOC_CGISAI
GROUP BY CGISAI
,CASE
WHEN ACCESS_TYPE_ID = 2
THEN CGISAI
END
,CASE
WHEN ACCESS_TYPE_ID = 2
THEN CELL_NAME
END
) DIM_LOC_4G_CGI_ENODEB ON CGISAI = DIM_LOC_4G_CGI_ENODEB.a202050101
) MT
GROUP BY MT.a900
,MT.a202080101
ORDER BY a900 ASC;
was:
MV is not working for like and filter AND and OR queries
Steps:
create table brinjal (imei string,AMSize string,channelsId string,ActiveCountry
string, Activecity string,gamePointId double,deviceInformationId
double,productionDate Timestamp,deliveryDate timestamp,deliverycharge double)
STORED BY 'org.apache.carbondata.format' ;
create datamap brinjal_mv_tab_nlz_aa016 on table brinjal using 'mv' as select
imei,AMSize,channelsId from brinjal where ActiveCountry NOT LIKE 'US' group by
imei,AMSize,channelsId;
create datamap brinjal_mv_tab_nlz_aa018 on table brinjal using 'mv' as select
imei,AMSize,channelsId,ActiveCountry from brinjal where ActiveCountry
='Chinese' or channelsId =4 group by imei,AMSize,channelsId,ActiveCountry;
then
select imei,AMSize,channelsId from brinjal where ActiveCountry NOT LIKE 'US'
group by imei,AMSize,channelsId; and
select imei,AMSize,channelsId,ActiveCountry from brinjal where ActiveCountry
='Chinese' or channelsId =4 group by imei,AMSize,channelsId,ActiveCountry;
are not hitting the datamap cretaed
> MV is not working for like and filter AND and OR queries
> --------------------------------------------------------
>
> Key: CARBONDATA-3403
> URL: https://issues.apache.org/jira/browse/CARBONDATA-3403
> Project: CarbonData
> Issue Type: Bug
> Reporter: Akash R Nilugal
> Priority: Minor
> Fix For: 1.6.0
>
> Time Spent: 1h 40m
> Remaining Estimate: 0h
>
> MV is not working for like and filter AND and OR queries
>
> Steps:
> create table brinjal (imei string,AMSize string,channelsId
> string,ActiveCountry string, Activecity string,gamePointId
> double,deviceInformationId double,productionDate Timestamp,deliveryDate
> timestamp,deliverycharge double) STORED BY 'org.apache.carbondata.format' ;
>
> create datamap brinjal_mv_tab_nlz_aa016 on table brinjal using 'mv' as select
> imei,AMSize,channelsId from brinjal where ActiveCountry NOT LIKE 'US' group
> by imei,AMSize,channelsId;
> create datamap brinjal_mv_tab_nlz_aa018 on table brinjal using 'mv' as select
> imei,AMSize,channelsId,ActiveCountry from brinjal where ActiveCountry
> ='Chinese' or channelsId =4 group by imei,AMSize,channelsId,ActiveCountry;
>
> then
> select imei,AMSize,channelsId from brinjal where ActiveCountry NOT LIKE 'US'
> group by imei,AMSize,channelsId; and
> select imei,AMSize,channelsId,ActiveCountry from brinjal where
> ActiveCountry ='Chinese' or channelsId =4 group by
> imei,AMSize,channelsId,ActiveCountry;
> are not hitting the datamap cretaed
> CREATE DATAMAP sc_mvdm_nlz_003 USING 'MV' AS
> SELECT CAST(floor((STARTTIME + 28800) / 900) * 900 - 28800 AS INT) AS a900
> ,S1MME_ATTACH_SUCC_TIMES AS S1MME_S1MME_ATTACH_SUCC_TIMES_C
> ,S1MME_EPS_ATTACH_SUCC_TIMES AS S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C
> ,S1MME_COMBINED_EPS_IMSI_ATTACH_DELAY_MSEL AS
> S1MME_COMBINED_EPS_IMSI_ATTACH_DELAY_MSEL_C
> ,S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES AS
> S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C
> ,S1MME_ATTACH_DELAY_MSEL AS S1MME_S1MME_ATTACH_DELAY_MSEL_C
> ,S1MME_EPS_ATTACH_REQ_TIMES AS S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C
> ,S1MME_EPS_ATTACH_DELAY_MSEL AS S1MME_S1MME_EPS_ATTACH_DELAY_MSEL_C
> ,S1MME_ATTACH_REQ_TIMES AS S1MME_S1MME_ATTACH_REQ_TIMES_C
> ,S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES AS
> S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C
> ,STARTTIME
> FROM sdr_signal_s1mme_cgisai_15min_17938_1kw;
> CREATE DATAMAP sc_mvdm_nlz_001 USING 'MV' AS
> SELECT CAST(floor((STARTTIME + 28800) / 900) * 900 - 28800 AS INT) AS a900
> ,S1MME_ATTACH_SUCC_TIMES AS S1MME_S1MME_ATTACH_SUCC_TIMES_C
> ,S1MME_EPS_ATTACH_SUCC_TIMES AS S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C
> ,S1MME_COMBINED_EPS_IMSI_ATTACH_DELAY_MSEL AS
> S1MME_COMBINED_EPS_IMSI_ATTACH_DELAY_MSEL_C
> ,S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES AS
> S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C
> ,S1MME_ATTACH_DELAY_MSEL AS S1MME_S1MME_ATTACH_DELAY_MSEL_C
> ,S1MME_EPS_ATTACH_REQ_TIMES AS S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C
> ,S1MME_EPS_ATTACH_DELAY_MSEL AS S1MME_S1MME_EPS_ATTACH_DELAY_MSEL_C
> ,S1MME_ATTACH_REQ_TIMES AS S1MME_S1MME_ATTACH_REQ_TIMES_C
> ,S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES AS
> S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C
> ,DIM_LOC_4G_CGI_ENODEB.a202080101 AS a202080101
> ,STARTTIME
> FROM sdr_signal_s1mme_cgisai_15min_17938_1kw
> LEFT JOIN (
> SELECT CGISAI AS a202050101
> ,CASE
> WHEN ACCESS_TYPE_ID = 2
> THEN CGISAI
> END AS a202080101
> ,CASE
> WHEN ACCESS_TYPE_ID = 2
> THEN CELL_NAME
> END AS NAME_202080101
> FROM DIM_LOC_CGISAI
> GROUP BY CGISAI
> ,CASE
> WHEN ACCESS_TYPE_ID = 2
> THEN CGISAI
> END
> ,CASE
> WHEN ACCESS_TYPE_ID = 2
> THEN CELL_NAME
> END
> ) DIM_LOC_4G_CGI_ENODEB ON CGISAI = DIM_LOC_4G_CGI_ENODEB.a202050101;
>
>
> create datamap sc_mvdm_lz_001 using 'mv' with deferred rebuild as select
> cdr_id,max(record_id) from tbl_data_event_410000_carbon group by cdr_id;
> CREATE DATAMAP sc_mvdm_nlz_002 USING 'MV' AS
> SELECT MT.a900 AS a900
> ,CASE
> WHEN COALESCE(SUM(S1MME_S1MME_ATTACH_REQ_TIMES_C), 0) = 0
> THEN NULL
> ELSE CASE
> WHEN CAST(COALESCE(SUM(S1MME_S1MME_ATTACH_REQ_TIMES_C), 0) AS
> decimal) = 0
> THEN 0
> ELSE CAST(COALESCE(SUM(S1MME_S1MME_ATTACH_SUCC_TIMES_C), 0) AS
> decimal) / CAST(COALESCE(SUM(S1MME_S1MME_ATTACH_REQ_TIMES_C), 0) AS decimal)
> END * 100
> END AS EPC_KPI_S1_MME_ATTACH_SUCCESS_RATE
> ,COALESCE(SUM(S1MME_S1MME_ATTACH_REQ_TIMES_C), 0) AS
> S1MME_S1MME_ATTACH_REQ_TIMES
> ,COALESCE(SUM(S1MME_S1MME_ATTACH_SUCC_TIMES_C), 0) AS
> S1MME_S1MME_ATTACH_SUCC_TIMES
> ,COALESCE(SUM(S1MME_S1MME_ATTACH_REQ_TIMES_C), 0) -
> COALESCE(SUM(S1MME_S1MME_ATTACH_SUCC_TIMES_C), 0) AS
> EPC_KPI_S1MME_ATTACH_FAIL_TIMES
> ,CASE
> WHEN COALESCE(SUM(S1MME_S1MME_ATTACH_SUCC_TIMES_C), 0) = 0
> THEN NULL
> ELSE CASE
> WHEN CAST(COALESCE(SUM(S1MME_S1MME_ATTACH_SUCC_TIMES_C), 0) AS
> decimal) = 0
> THEN 0
> ELSE CAST(COALESCE(SUM(S1MME_S1MME_ATTACH_DELAY_MSEL_C), 0) AS
> decimal) / CAST(COALESCE(SUM(S1MME_S1MME_ATTACH_SUCC_TIMES_C), 0) AS decimal)
> END
> END AS EPC_KPI_S1_MME_ATTACH_DELAY
> ,CASE
> WHEN COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C), 0) = 0
> THEN NULL
> ELSE CASE
> WHEN CAST(COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C), 0) AS
> decimal) = 0
> THEN 0
> ELSE CAST(COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C), 0) AS
> decimal) / CAST(COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C), 0) AS
> decimal)
> END * 100
> END AS EPC_KPI_S1_MME_EPS_ATTACH_SUCCESS_RATE
> ,COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C), 0) AS
> S1MME_S1MME_EPS_ATTACH_REQ_TIMES
> ,COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C), 0) AS
> S1MME_S1MME_EPS_ATTACH_SUCC_TIMES
> ,COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C), 0) -
> COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C), 0) AS
> EPC_KPI_S1MME_EPS_ATTACH_FAIL_TIMES
> ,CASE
> WHEN COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C), 0) = 0
> THEN NULL
> ELSE CASE
> WHEN CAST(COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C), 0) AS
> decimal) = 0
> THEN 0
> ELSE CAST(COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_DELAY_MSEL_C), 0) AS
> decimal) / CAST(COALESCE(SUM(S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C), 0) AS
> decimal)
> END
> END AS EPC_KPI_S1_MME_EPS_ATTACH_DELAY
> ,CASE
> WHEN COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C), 0) = 0
> THEN NULL
> ELSE CASE
> WHEN CAST(COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C),
> 0) AS decimal) = 0
> THEN 0
> ELSE CAST(COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C),
> 0) AS decimal) /
> CAST(COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C), 0) AS decimal)
> END * 100
> END AS EPC_KPI_S1_MME_COMBINED_EPS_IMSI_ATTACH_SUCCESS_RATE
> ,COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C), 0) AS
> S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES
> ,COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C), 0) AS
> S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES
> ,COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C), 0) -
> COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C), 0) AS
> EPC_KPI_S1MME_COMBINED_EPS_IMSI_ATTACH_FAIL_TIMES
> ,CASE
> WHEN COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C), 0) = 0
> THEN NULL
> ELSE CASE
> WHEN CAST(COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C),
> 0) AS decimal) = 0
> THEN 0
> ELSE CAST(COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_DELAY_MSEL_C),
> 0) AS decimal) /
> CAST(COALESCE(SUM(S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C), 0) AS decimal)
> END
> END AS EPC_KPI_S1_MME_COMBINED_EPS_IMSI_ATTACH_DELAY
> ,MT.a202080101 AS a202080101
> ,COUNT(1) OVER () AS countNum
> FROM (
> SELECT CAST(floor((STARTTIME + 28800) / 900) * 900 - 28800 AS INT) AS a900
> ,S1MME_ATTACH_SUCC_TIMES AS S1MME_S1MME_ATTACH_SUCC_TIMES_C
> ,S1MME_EPS_ATTACH_SUCC_TIMES AS S1MME_S1MME_EPS_ATTACH_SUCC_TIMES_C
> ,S1MME_COMBINED_EPS_IMSI_ATTACH_DELAY_MSEL AS
> S1MME_COMBINED_EPS_IMSI_ATTACH_DELAY_MSEL_C
> ,S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES AS
> S1MME_COMBINED_EPS_IMSI_ATTACH_REQ_TIMES_C
> ,S1MME_ATTACH_DELAY_MSEL AS S1MME_S1MME_ATTACH_DELAY_MSEL_C
> ,S1MME_EPS_ATTACH_REQ_TIMES AS S1MME_S1MME_EPS_ATTACH_REQ_TIMES_C
> ,S1MME_EPS_ATTACH_DELAY_MSEL AS S1MME_S1MME_EPS_ATTACH_DELAY_MSEL_C
> ,S1MME_ATTACH_REQ_TIMES AS S1MME_S1MME_ATTACH_REQ_TIMES_C
> ,S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES AS
> S1MME_COMBINED_EPS_IMSI_ATTACH_SUCC_TIMES_C
> ,DIM_LOC_4G_CGI_ENODEB.a202080101 AS a202080101
> FROM sdr_signal_s1mme_cgisai_15min_17938_1kw
> LEFT JOIN (
> SELECT CGISAI AS a202050101
> ,CASE
> WHEN ACCESS_TYPE_ID = 2
> THEN CGISAI
> END AS a202080101
> ,CASE
> WHEN ACCESS_TYPE_ID = 2
> THEN CELL_NAME
> END AS NAME_202080101
> FROM DIM_LOC_CGISAI
> GROUP BY CGISAI
> ,CASE
> WHEN ACCESS_TYPE_ID = 2
> THEN CGISAI
> END
> ,CASE
> WHEN ACCESS_TYPE_ID = 2
> THEN CELL_NAME
> END
> ) DIM_LOC_4G_CGI_ENODEB ON CGISAI = DIM_LOC_4G_CGI_ENODEB.a202050101
> ) MT
> GROUP BY MT.a900
> ,MT.a202080101
> ORDER BY a900 ASC;
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
