一个数据集多次操作优化

Question

由于SQL语句比较长，所以放在附件中了其中分为三个大部分，d.oldnum 求出老用户数，b.newnum求出新用户数，a为其他的统计信息，这三部分都有个共同的语句段：
SELECT calling_id, start_time, service_id,
              service_type_id, acct_type_id
        FROM dat_dr_200705
            WHERE TO_CHAR (start_time, 'yyyyMMdd')
                       BETWEEN '20070501' AND '20071114'
 UNION ALL
 SELECT calling_id, start_time, service_id,
              service_type_id, acct_type_id
        FROM dat_dr_200705_other
             WHERE TO_CHAR (start_time, 'yyyyMMdd')
                       BETWEEN '20070501' AND '20071114'
 UNION ALL
 SELECT calling_id, start_time, service_id,
               service_type_id, acct_type_id
        FROM dat_dr_200706
             WHERE TO_CHAR (start_time, 'yyyyMMdd')
                        BETWEEN '20070501' AND '20071114'
 UNION ALL
 SELECT calling_id, start_time, service_id,
               service_type_id, acct_type_id
        FROM dat_dr_200706_other
              WHERE TO_CHAR (start_time, 'yyyyMMdd')
                        BETWEEN '20070501' AND '20071114'
 UNION ALL
 SELECT calling_id, start_time, service_id,
               service_type_id, acct_type_id
        FROM dat_dr_200707
               WHERE TO_CHAR (start_time, 'yyyyMMdd')
                        BETWEEN '20070501' AND '20071114'
 UNION ALL
 SELECT calling_id, start_time, service_id,
               service_type_id, acct_type_id
        FROM dat_dr_200707_other
               WHERE TO_CHAR (start_time, 'yyyyMMdd')
                        BETWEEN '20070501' AND '20071114'
 UNION ALL
 SELECT calling_id, start_time, service_id,
                service_type_id, acct_type_id
        FROM dat_dr_200708
                WHERE TO_CHAR (start_time, 'yyyyMMdd')
                        BETWEEN '20070501' AND '20071114'
 UNION ALL
 SELECT calling_id, start_time, service_id,
                service_type_id, acct_type_id
        FROM dat_dr_200708_other
                WHERE TO_CHAR (start_time, 'yyyyMMdd')
                        BETWEEN '20070501' AND '20071114'
 UNION ALL
 SELECT calling_id, start_time, service_id,
                service_type_id, acct_type_id
        FROM dat_dr_200711
                WHERE TO_CHAR (start_time, 'yyyyMMdd')
                        BETWEEN '20070501' AND '20071114'我试着把这三段写成一段，但因为条件差别，没成功。请问有什么办法能够优化一下这个语句。

Answer 1

太长了!!
你说说要实现什么功能,给个表名,条件,或许能用个相对简洁一点的SQL语句来实现!!

Answer 2

就是想能不能把上边写的那一段UNION   ALL的语句，想使用变量一样，只查一次就好了，那个sql里一共查了3次，太影响效率

Answer 3

谁能指点一下哦，看着这个超长sql我无奈啊

Answer 4

自己改了一下如下,把求b.newnum和 b.oldnum的写在一个里边，用了DECODE ，速度上有所提升SELECT a.*, b.newnum, b.oldnum
  FROM (SELECT   RESULT.report_date, service.service_type_id,
                 c.service_type_name, SUM (RESULT.fee) fee,
                 COUNT (DISTINCT calling_id) num, SUM (RESULT.times) times,
                 SUM (RESULT.teltime) teltime, SUM (RESULT.seconds) seconds,
                 SUM (citytimes) citytimes,
                 SUM (RESULT.servicetimes) servicetimes
            FROM (SELECT   TO_CHAR (start_time, 'yyyyMM') report_date,
                           a.service_id, SUM (fee) AS fee, COUNT (*) AS times,
                           SUM (DURATION) seconds, calling_id,
                           ROUND (SUM (DURATION / 60), 2) teltime,
                           SUM (local_units) citytimes,
                           SUM (charge_units) servicetimes
                      FROM (SELECT calling_id, acct_type_id, owner_area_code,
                                   start_time, service_id, service_type_id,
                                   fee, DURATION, local_units, charge_units,
                                   calling_type_id
                              FROM dat_dr_200708
                             WHERE TO_CHAR (start_time, 'yyyyMMdd')
                                      BETWEEN '20070501'
                                          AND '20071114'
                            UNION ALL
                            SELECT calling_id, acct_type_id, owner_area_code,
                                   start_time, service_id, service_type_id,
                                   fee, DURATION, local_units, charge_units,
                                   calling_type_id
                              FROM dat_dr_200708_other
                             WHERE TO_CHAR (start_time, 'yyyyMMdd')
                                      BETWEEN '20070501'
                                          AND '20071114') a,
                           cfg_service b
                     WHERE a.service_id = b.service_id
                       AND NOT (    fee = 0
                                AND TO_CHAR (ADD_MONTHS (SYSDATE + 1, -3),
                                             'yyyymmdd'
                                            ) >
                                              TO_CHAR (start_time, 'yyyymmdd')
                               )
                  GROUP BY TO_CHAR (start_time, 'yyyyMM'),
                           a.service_id,
                           a.calling_id) RESULT,
                 cfg_service service,
                 cfg_service_type c
           WHERE RESULT.service_id = service.service_id
             AND service.service_type_id = c.service_type_id
        GROUP BY report_date, service.service_type_id, c.service_type_name
        ORDER BY report_date, service_type_id, fee DESC) a,
       (SELECT   COUNT (DISTINCT (DECODE (b.status, 2, a.calling_id))) newnum,
                 COUNT (DISTINCT (DECODE (b.status,
                                          3, a.calling_id,
                                          4, a.calling_id
                                         )
                         )
                       ) oldnum,
                 a.service_type_id, a.report_date
            FROM (SELECT   a.calling_id,
                           TO_CHAR (a.start_time, 'yyyyMM') report_date,
                           a.service_id, a.service_type_id
                      FROM (SELECT calling_id, start_time, service_id,
                                   service_type_id, acct_type_id
                              FROM dat_dr_200708
                             WHERE TO_CHAR (start_time, 'yyyyMMdd')
                                      BETWEEN '20070501'
                                          AND '20071114'
                            UNION ALL
                            SELECT calling_id, start_time, service_id,
                                   service_type_id, acct_type_id
                              FROM dat_dr_200708_other
                             WHERE TO_CHAR (start_time, 'yyyyMMdd')
                                      BETWEEN '20070501'
                                          AND '20071114') a,
                           cfg_service b,
                           cfg_service_type c
                     WHERE a.service_id = b.service_id
                       AND c.service_type_id = b.service_type_id
                  GROUP BY TO_CHAR (a.start_time, 'yyyyMM'),
                           a.service_id,
                           a.calling_id,
                           a.service_type_id) a,
                 (SELECT service_id, account_id, status
                    FROM cfg_order_relation
                   WHERE last_charge_month BETWEEN '200705' AND '200711'
                     AND TO_CHAR (confirm_time, 'yyyymmdd') BETWEEN '20070501'
                                                                AND '20071114') b
           WHERE a.service_id = b.service_id AND a.calling_id = b.account_id
        GROUP BY a.service_type_id, a.report_date) b
 WHERE a.service_type_id = b.service_type_id(+) AND a.report_date = b.report_date(+);