最燃HiveSQL

INSERT INTO OP_MME_IUCS_5G_IMEI_M (MONTH_ID,
                                   PROVINCE_I,
                                   REGION_ID,
                                   NET_TYPE,
                                   TAC,
                                   ECI,
                                   WORK_USE_NUM,
                                   HOME_USE_NUM,
                                   WORK_5G_USE_NUM,
                                   HOME_5G_USE_NUM,
                                   G5_DATA,
                                   ALL_DATA,
                                   USE_NUM)
     SELECT MONTH_ID,
            PROVINCE_I,
            REGION_ID,
            NET_TYPE,
            TAC,
            ECI,
            SUM ( (CASE WHEN WORK_SITE >= HOME_SITE THEN 1 ELSE 0 END))
               WORK_USE_NUM,                                     --工作常驻时段常驻用户数
            SUM ( (CASE WHEN WORK_SITE < HOME_SITE THEN 1 ELSE 0 END))
               HOME_USE_NUM,                                     --住宅常驻时段常驻用户数
            SUM (
               (CASE
                   WHEN WORK_SITE >= HOME_SITE AND IS_5G_IMEI = 1 THEN 1
                   ELSE 0
                END))
               WORK_5G_USE_NUM,                              --工作常驻时段常驻5G终端用户数
            SUM (
               (CASE
                   WHEN WORK_SITE < HOME_SITE AND IS_5G_IMEI = 1 THEN 1
                   ELSE 0
                END))
               HOME_5G_USE_NUM,                              --住宅常驻时段常驻用5G终端户数
            SUM ( (CASE WHEN IS_5G_IMEI = 1 THEN UL_DATA + DL_DATA ELSE 0 END))
               G5_DATA,                                               --5G网络流量
            SUM (UL_DATA + DL_DATA) ALL_DATA,                          --小区总流量
            COUNT (IMEI) USE_NUM                                      --小区总用户数
       FROM (SELECT OP.MONTH_ID MONTH_ID,
                    OP.PROVINCE_I PROVINCE_I,
                    OP.REGION_ID REGION_ID,
                    OP.TAC TAC,
                    OP.ECI ECI,
                    OP.NET_TYPE NET_TYPE,
                    OP.IMEI IMEI,
                    OP.WORK_SITE WORK_SITE,
                    OP.HOME_SITE HOME_SITE,
                    DW.UL_DATA UL_DATA,
                    DW.DL_DATA DL_DATA,
                    DW.IS_5G_IMEI IS_5G_IMEI
               FROM (SELECT ULDL.DAY_ID DAY_ID,
                            ULDL.IMEI IMEI,
                            ULDL.LAC TAC,
                            ULDL.CI ECI,
                            ULDL.UL_DATA UL_DATA,
                            ULDL.DL_DATA DL_DATA,
                            WG.IS_5G_IMEI IS_5G_IMEI
                       FROM (SELECT IMEI,
                                    DAY_ID,
                                    LAC,
                                    CI,
                                    UL_DATA,
                                    DL_DATA
                               FROM DW_A_XDR_GNHTTP_FTUCSV_USR_D
                              WHERE DAY_ID BETWEEN '$MONTH'01 AND '$MONTH'31
                             UNION
                             SELECT IMEI,
                                    DAY_ID,
                                    TAC,
                                    ECI,
                                    UL_DATA,
                                    DL_DATA
                               FROM DW_A_XDR_S1HTTP_FTECSV_USR_D
                              WHERE DAY_ID BETWEEN '$MONTH'01 AND '$MONTH'31) ULDL
                            LEFT JOIN
                            DW_MME_IUCS_5G_IMEI_D DW
                               ON (    ULDL.DAY_ID = DW.DAY_ID
                                   AND ULDL.TAC = DW.TAC
                                   AND ULDL.ECI = DW.EC
                                   AND ULDL.IMEI = DW.IMEI)) DW
                    LEFT JOIN
                    (  SELECT MONTH_ID,
                              PROVINCE_I,
                              REGION_ID,
                              TAC,
                              ECI,
                              NET_TYPE,
                              IMEI,
                              SUM ( (CASE WHEN WORK_SITE = 0 THEN 0 ELSE 1 END))
                                 WORK_SITE,
                              SUM ( (CASE WHEN HOME_SITE = 0 THEN 0 ELSE 1 END))
                                 HOME_SITE
                         FROM (  SELECT SUBSTR (DAY_ID, 0, 6) MONTH_ID,
                                        DAY_ID,
                                        PROVINCE_I,
                                        PROVINCE_D,
                                        REGION_ID,
                                        REGION_DES,
                                        TAC,
                                        ECI,
                                        NET_TYPE,
                                        IMEI,
                                        SUM (
                                           (CASE
                                               WHEN HOUR_ID IN
                                                       ('9',
                                                        '10',
                                                        '11',
                                                        '12',
                                                        '14',
                                                        '15',
                                                        '16',
                                                        '17')
                                               THEN
                                                  1
                                               ELSE
                                                  0
                                            END))
                                           WORK_SITE,
                                        SUM (
                                           (CASE
                                               WHEN HOUR_ID IN
                                                       ('22',
                                                        '23',
                                                        '0',
                                                        '1',
                                                        '2',
                                                        '3',
                                                        '4',
                                                        '5',
                                                        '6')
                                               THEN
                                                  1
                                               ELSE
                                                  0
                                            END))
                                           HOME_SITE
                                   FROM OP_MME_IUCS_5G_IMEI_H
                               GROUP BY DAY_ID,
                                        PROVINCE_I,
                                        PROVINCE_D,
                                        REGION_ID,
                                        REGION_DES,
                                        TAC,
                                        ECI,
                                        NET_TYPE,
                                        IMEI)
                     GROUP BY MONTH_ID,
                              PROVINCE_I,
                              PROVINCE_D,
                              REGION_ID,
                              REGION_DES,
                              TAC,
                              ECI,
                              NET_TYPE,
                              IMEI) OP
                       ON (    DW.DAY_ID = OP.DAY_ID
                           AND DW.TAC = OP.TAC
                           AND DW.ECI = OP.EC
                           AND DW.IMEI = OP.IMEI))
   GROUP BY MONTH_ID,
            PROVINCE_I,
            REGION_ID,
            NET_TYPE,
            TAC,
            ECI