MySQL 重构冗长的查询 [英] MySQL Refactoring Lengthy Query

问题描述

好吧，我非常讨厌自己提出这个问题；但我正在寻找另一种方法来执行此查询，这种方法可能更快也更优雅(这看起来像废话).请告诉我您的想法:

Alright, I totally hate myself for asking this; but I'm looking for another way to do this query, something that might be faster as well as more elegant (this looks like crap). Tell me what you think please:

  SELECT TRUNCATE(SUM(sub.Total),3) AS GrpTotal, sub.ActualDate, 
         TRUNCATE(SUM(sub.BonusAmt),3) AS GrpBonusAmt, sub.UID, 
         sub.CUSTID, YEAR(MIN(sub.ActualDate)) AS Year, pusers.username
  FROM ( SELECT a.UID, a.ActualDate, 'Global Report' AS Report, 
                SUM(a.totalpayment) AS Total, a.CUSTID,
               ((SUM(a.totalpayment)*IFNULL((u.retention_percent/100),1))+IFNULL(u.bonus_amount,0)) AS BonusAmt
         FROM `globalreport` a
              LEFT JOIN `users` u ON u.uid = a.UID
         WHERE true AND a.CUSTID = 1020
         GROUP BY a.ActualDate, a.UID
         UNION ALL
         SELECT a.UID, a.ActualDate, 'Amex Residuals' AS Report, 
                SUM(a.payment) AS Total, a.CUSTID,
                ((SUM(a.payment)*IFNULL((u.retention_percent/100),1))+IFNULL(u.bonus_amount,0)) AS BonusAmnt
         FROM `amexresiduals` a
              LEFT JOIN `users` u ON u.uid = a.UID
         WHERE true AND a.CUSTID = 1020
         GROUP BY a.ActualDate, a.UID
         UNION ALL 
         SELECT a.UID, a.ActualDate, 'Compliance Fee' AS Report, 
                SUM(a.profit) AS Total, a.CUSTID, 
                ((SUM(a.profit)*IFNULL((u.retention_percent/100),1))+IFNULL(u.bonus_amount,0)) AS BonusAmnt
         FROM `compliancefee` a
              LEFT JOIN `users` u ON u.uid = a.UID
         WHERE true AND a.CUSTID = 1020
         GROUP BY a.ActualDate, a.UID
         UNION ALL
         SELECT a.UID, a.ActualDate, 'Checks On Demand' AS Report, 
                SUM(a.myprofit) AS Total, a.CUSTID, 
                ((SUM(a.myprofit)*IFNULL((u.retention_percent/100),1))+IFNULL(u.bonus_amount,0)) AS BonusAmnt
         FROM `geticheck` a
              LEFT JOIN `users` u ON u.uid = a.UID
         WHERE true AND a.CUSTID = 1020
         GROUP BY a.ActualDate, a.UID
         UNION ALL
         SELECT a.UID, a.ActualDate, 'Gift Cards on Demand' AS Report, 
                SUM(a.payment) AS Total, a.CUSTID,
                ((SUM(a.payment)*IFNULL((u.retention_percent/100),1))+IFNULL(u.bonus_amount,0)) AS BonusAmnt
         FROM `gcod` a
              LEFT JOIN `users` u ON u.uid = a.UID
         WHERE true AND a.CUSTID = 1020
         GROUP BY a.ActualDate, a.UID
         UNION ALL
         SELECT a.UID, a.ActualDate, 'Global Check' AS Report, 
                SUM(a.myprofit) AS Total, a.CUSTID,
                ((SUM(a.myprofit)*IFNULL((u.retention_percent/100),1))+IFNULL(u.bonus_amount,0)) AS BonusAmnt
         FROM `globalcheck` a
              LEFT JOIN `users` u ON u.uid = a.UID
         WHERE true AND a.CUSTID = 1020
         GROUP BY a.ActualDate, a.UID
         UNION ALL
         SELECT a.UID, a.ActualDate, 'Bonus True Up' AS Report, 
                SUM(a.finalpayment) AS Total, a.CUSTID,
                ((SUM(a.finalpayment)*IFNULL((u.retention_percent/100),1))+IFNULL(u.bonus_amount,0)) AS BonusAmnt
         FROM `bonustrueup` a
              LEFT JOIN `users` u ON u.uid = a.UID
         WHERE true AND a.CUSTID = 1020
         GROUP BY a.ActualDate, a.UID
         UNION ALL
         SELECT a.UID, a.ActualDate, 'Bonus Take Back - Did Not Activate' AS Report, 
                SUM(a.amount) AS Total, a.CUSTID,
                ((SUM(a.amount)*IFNULL((u.retention_percent/100),1))+IFNULL(u.bonus_amount,0)) AS BonusAmnt 
         FROM `bonusadjnosetup` a
              LEFT JOIN `users` u ON u.uid = a.UID
         WHERE true AND a.CUSTID = 1020
         GROUP BY a.ActualDate, a.UID
         UNION ALL
         SELECT a.UID, a.ActualDate, 'Bonus Take Back - Closed Less Than 6 Months' AS Report, 
                SUM(a.amount) AS Total, a.CUSTID,
                ((SUM(a.amount)*IFNULL((u.retention_percent/100),1))+IFNULL(u.bonus_amount,0)) AS BonusAmnt
         FROM `bonusadjclosed6mo` a
             LEFT JOIN `users` u ON u.uid = a.UID
         WHERE true AND a.CUSTID = 1020
         GROUP BY a.ActualDate, a.UID
         UNION ALL
         SELECT a.UID, a.ActualDate, 'Month End Fee Rejects' AS Report, 
                SUM(a.amount) AS Total, a.CUSTID, SUM(a.amount) AS BonusAmnt        
         FROM `merchantloss` a
             LEFT JOIN `users` u ON u.uid = a.UID
         WHERE true AND a.CUSTID = 1020
         GROUP BY a.ActualDate, a.UID
         UNION ALL
         SELECT a.UID, a.ActualDate, 'Direct ACH Debits and Credits' AS Report, 
                SUM(a.amount*-1) AS Total, a.CUSTID, SUM(a.amount*-1) AS BonusAmnt
         FROM `dirachdebcred` a
             LEFT JOIN `users` u ON u.uid = a.UID
         WHERE true AND a.CUSTID = 1020
         GROUP BY a.ActualDate, a.UID
         UNION ALL
         SELECT a.UID, a.ActualDate, 'Merchant Adjustments' AS Report, 
                SUM(a.amount) AS Total, a.CUSTID,
                ((SUM(a.amount)*IFNULL((u.retention_percent/100),1))+IFNULL(u.bonus_amount,0)) AS BonusAmnt
         FROM `merchantadj` a
             LEFT JOIN `users` u ON u.uid = a.UID
         WHERE true AND a.CUSTID = 1020
         GROUP BY a.ActualDate, a.UID
  ) sub
      LEFT JOIN `pending_users` pusers ON pusers.UID = sub.UID
  WHERE sub.CUSTID = 1020
       AND sub.`UID` NOT IN 
                ( SELECT `UID` 
                  FROM `users` 
                  WHERE `is_admin` AND `company_id` = sub.`CUSTID`)
  GROUP BY sub.ActualDate, sub.UID, sub.Report
  ORDER BY sub.ActualDate ASC

显然，这是一个冗长的查询.我只是不确定它是否必须如此.从本质上讲，我正在收集和总结每个联合表中的不同列，并在最后按该数量分组，以便我可以从所有表中获得总和.

Clearly, this is a lengthy query. I'm just not sure it has to be. Essentially, I'm gathering and summing a different column in each of the unioned tables and grouping by that amount at the very end so I can get a total sum from all tables.

推荐答案

UPDATE

好像我之前的回答不够长......

As if my previous answer wasn't long enough ...

我还会将 JOIN 从内部查询移动到用户表，从内部查询到外部查询，看起来在每种情况下都是相同的.

I'd also move the JOIN to the users table from the inner queries to the outer queries, it looks like it's identical in every case.

但是，我们需要它来计算 BonusAmnt(它似乎总是基于 Total 是什么；除了两个之外，所有这些的计算都是相同的.所以，我也移动了BonusAmnt 计算也适用于外部查询，并使用 CASE 表达式添加检查要执行的计算.

But, we need that for the calculation of BonusAmnt (which appears to always be based on whatever Total is; the calculation is the same for all of them except for two. So, I also moved the BonusAmnt calculation to the outer query as well, and just adding a check for which calculation to do, using a CASE expression.

我可能错过了一些东西.

I may have missed something.

但这是我编写查询的方式.

But here's how I'd write the query.

我将分两部分展示它，将 sub 内联视图的查询与外部查询分开.

I'll show it in two pieces, with the query for the sub inline view separate from the outer query.

  SELECT TRUNCATE(sub.Total,3) AS GrpTotal
       , sub.ActualDate
       , CASE
           WHEN sub.Report IN ('Month End Fee Rejects','Direct ACH Debits and Credits') THEN
             TRUNCATE(sub.Total,3)
           ELSE
             TRUNCATE(sub.Total*IFNULL((u.retention_percent/100),1)+IFNULL(u.bonus_amount,0)),3)
         END AS GrpBonusAmt
       , sub.UID
       , sub.CUSTID
       , YEAR(sub.ActualDate) AS Year
       , pusers.username
    FROM (
           -- query to produce sub goes here
         ) sub
    LEFT
    JOIN `users` u
      ON u.uid = sub.UID
    LEFT
    JOIN `pending_users` pusers
      ON pusers.UID = sub.UID
    LEFT
    JOIN `users` n
      ON n.UID = sub.CUSTID
     AND n.is_admin
   WHERE n.UID IS NULL
   ORDER BY sub.ActualDate, sub.UID, sub.Report

这是第二部分.这是作为内联视图卡在上面部分中间的查询.这是为外部查询生成 sub 行源的查询:

And here is the second part. This is the query that gets stuck into the middle of the part above, as an inline view. This is the query that that produces sub row source for the outer query:

           SELECT 'Global Report' AS Report
                , a.UID, a.ActualDate, a.CUSTID
                , SUM(a.totalpayment) AS Total
             FROM `globalreport` a
            WHERE a.CUSTID = 1020
            GROUP BY a.ActualDate, a.UID
            UNION ALL
           SELECT 'Amex Residuals'
                , a.UID, a.ActualDate, a.CUSTID
                , SUM(a.payment) AS Total
             FROM `amexresiduals` a
            WHERE a.CUSTID = 1020
            GROUP BY a.ActualDate, a.UID
            UNION ALL
           SELECT 'Compliance Fee'
                , a.UID, a.ActualDate, a.CUSTID
                , SUM(a.profit) AS Total
             FROM `compliancefee` a
            WHERE a.CUSTID = 1020
            GROUP BY a.ActualDate, a.UID
            UNION ALL
           SELECT 'Checks On Demand'
                , a.UID, a.ActualDate, a.CUSTID
                , SUM(a.myprofit) AS Total
             FROM `geticheck` a
            WHERE a.CUSTID = 1020
            GROUP BY a.ActualDate, a.UID
            UNION ALL
           SELECT 'Gift Cards on Demand'
                , a.UID, a.ActualDate, a.CUSTID
                , SUM(a.payment) AS Total
             FROM `gcod` a
            WHERE a.CUSTID = 1020
            GROUP BY a.ActualDate, a.UID
            UNION ALL
           SELECT 'Global Check' AS Report
                , a.UID, a.ActualDate, a.CUSTID
                , SUM(a.myprofit) AS Total
             FROM `globalcheck` a
            WHERE a.CUSTID = 1020
            GROUP BY a.ActualDate, a.UID
            UNION ALL
           SELECT 'Bonus True Up'
                , a.UID, a.ActualDate, a.CUSTID
                , SUM(a.finalpayment) AS Total
             FROM `bonustrueup` a
            WHERE a.CUSTID = 1020
            GROUP BY a.ActualDate, a.UID
            UNION ALL
           SELECT 'Bonus Take Back - Did Not Activate'
                , a.UID, a.ActualDate, a.CUSTID
                , SUM(a.amount) AS Total
             FROM `bonusadjnosetup` a
            WHERE a.CUSTID = 1020
            GROUP BY a.ActualDate, a.UID
            UNION ALL
           SELECT 'Bonus Take Back - Closed Less Than 6 Months'
                , a.UID, a.ActualDate, a.CUSTID
                , SUM(a.amount) AS Total
             FROM `bonusadjclosed6mo` a
            WHERE a.CUSTID = 1020
            GROUP BY a.ActualDate, a.UID
            UNION ALL
           SELECT 'Month End Fee Rejects'
                , a.UID, a.ActualDate, a.CUSTID
                , SUM(a.amount) AS Total
             FROM `merchantloss` a
            WHERE a.CUSTID = 1020
            GROUP BY a.ActualDate, a.UID
            UNION ALL
           SELECT 'Direct ACH Debits and Credits'
                , a.UID, a.ActualDate, a.CUSTID
                , SUM(a.amount*-1) AS Total
             FROM `dirachdebcred` a
            WHERE a.CUSTID = 1020
            GROUP BY a.ActualDate, a.UID
            UNION ALL
           SELECT 'Merchant Adjustments'
                , a.UID, a.ActualDate, a.CUSTID
                , SUM(a.amount) AS Total
             FROM `merchantadj` a
            WHERE a.CUSTID = 1020
            GROUP BY a.ActualDate, a.UID

这篇关于MySQL 重构冗长的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！