SQL优化组通过同一表，同一列

Question

我有此GROUP BY查询，我需要从TABLE_1选择一些记录并汇总它们。

SELECTs是相似的，但是我需要分别汇总LON和BHAM，因为它们是两个不同的概念，但位于同一表中。

我的问题是，我可以在Oracle上写下以下内容，以优化查询的性能吗？

SELECT  *
FROM (
        (    SELECT  /*+ full(t1) */ 
                t3.custId AS ID,       
                t2.secID AS SEC_ID, 
                t1.org_date AS SETT_DATE,
                SUM(t1.amount) AS TOTAL
            FROM test.TABLE_1 t1 
            INNER JOIN test.TABLE_2 t2 on t2.a_code = t1.a_code  and t2.c_code = t1.c_code and t2.expiry_date > trunc(sysdate)
            INNER JOIN test.TABLE_3 t3 on t3.account_id = t1.account_id 
            WHERE t1.city = 'LON'         
                AND t1.amount < 50000 and t1.amount > -50000
            GROUP BY t3.custId,  t2.secID, t1.org_date
        )
          UNION ALL
        (    SELECT  /*+ full(t1) */ 
                t3.custId AS ID,      
                t2.secID AS SEC_ID,
                t1.org_date AS SETT_DATE,
                SUM(t1.amount) AS TOTAL
            FROM test.TABLE_1 t1 
            INNER JOIN test.TABLE_2 t2 on t2.a_code = t1.a_code  and t2.c_code = t1.c_code and t2.expiry_date > trunc(sysdate)
            INNER JOIN test.TABLE_3 t3 on t3.account_id = t1.account_id 
            WHERE t1.city = 'BHAM'
                AND t3.alias = 'ABC' 
                AND t1.amount < 50000 and t1.amount > -50000
            GROUP BY t3.custId,  t2.secID, t1.org_date
        )   
    )
 ORDER BY ID,  SEC_ID, 
          CASE WHEN SETT_DATE < TRUNC(sysdate) THEN trunc(sysdate) ELSE TRUNC(SETT_DATE) end       

Answer 1

删除 union all及之后的所有内容，请删除外部select，在此处像：

一样写where子句

where -50000 < t1.amount and t1.amount < 50000 
  and (t1.city = 'LON' or (t1.city = 'BHAM' and t3.alias = 'ABC'))

Answer 2

您需要通过将城市列添加到组中，然后更新Where子句以获得两组行，例如：

SELECT custid,
       sec_id,
       sett_date,
       total
FROM   (SELECT t3.custid AS id,
               t2.secid AS sec_id,
               CASE
                 WHEN t1.org_date < trunc(SYSDATE) THEN
                  trunc(SYSDATE)
                 ELSE
                  trunc(t1.org_date)
               END AS sett_date,
               t1.city,
               SUM(t1.amount) AS total
        FROM   test.table_1 t1
        INNER  JOIN test.table_2 t2
        ON     t2.a_code = t1.a_code
        AND    t2.c_code = t1.c_code
        AND    t2.expiry_date > trunc(SYSDATE)
        INNER  JOIN test.table_3 t3
        ON     t3.account_id = t1.account_id
        WHERE  (t1.city = 'LON' OR (t1.city = 'BHAM' AND t3.alias = 'ABC'))
        AND    t1.amount < 50000
        AND    t1.amount > -50000
        GROUP  BY t3.custid,
                  t2.secid,
                  CASE
                    WHEN t1.org_date < trunc(SYSDATE) THEN
                     trunc(SYSDATE)
                    ELSE
                     trunc(t1.org_date)
                  END)
ORDER  BY id,
          sec_id,
          sett_date;

但是，我很惊讶您都需要这两个行，因为您无法判断哪个城市属于哪个城市。我怀疑您需要将城市(也许是别名(列包括在最终结果中。