php - mySQL 中的条件和？

Question

我不太擅长 SQL，所以我一直在尝试通过 PHP 来处理这个问题并执行非常基本的 mySQL 查询，但显然我一直在用尽内存(在 PHP 中)，因为我处理了很多数据(并做大量可怕的嵌套 foreach)。我希望可能有一种方法可以重写我的 SQL 来避免这种情况发生。

这是我正在处理的表格(仅显示相关字段):

组织

• 组织ID

资金周期

• 周期编号
• 非UnityCut​​BackAmount
• UnityCut​​BackAmount

管理请求

• 周期编号
• 组织ID
• 申请金额
• 资助金额
• 关闭

资本请求

• 周期编号
• 组织ID
• 申请金额
• 资助金额
• 关闭

事件请求

• 周期编号
• 组织ID
• 申请金额
• 资助金额
• 关闭
• Unity请求

所以，这是复杂的部分。我需要为 AmountRequested 的每个组织创建总和, AmountFunded ，(这两个看起来相当容易，虽然不确定跨多个表求和)和一个计算字段 AmountAfterCutback来自所有请求表中的请求(AdministrativeRequests、CapitalRequests、EventRequests)。

AmountAfterCutback等于:AmountFunded - (AmountFunded * Cutback ).

如果削减只是一个值，那可能就足够简单了。然而，Cutback来自 FundingCycles table 。所以我不能直接求和并应用缩减，我必须找出每个请求的缩减，为此我必须查看每个请求，然后查看其 CycleID。检查并查看 FundingCycles 中该请求的削减是什么表。

默认情况下，Cutback将等于 NonUnityCutBack .但是，如果请求是 Unity请求(仅 EventRequests，UnityReq == 1)，缩减等于 UnityCutBack .然而，它变得更具挑战性，因为削减可以单独应用于每个请求，覆盖统一或非统一削减(如果 PerOff is > 0 )。

因此，我必须以某种方式汇总组织提出的每个请求，并正确计算上述字段。我将在下面展示我的解决方案，我会警告它绝对不是很好，并且在这一点上甚至无法正常工作，因为它在尝试通过 PHP 处理它时内存不足。我知道必须有更好的方法来做到这一点。某种方法可以对每个请求进行条件计算，以便可以在查询中对其进行求和，而不是我下面笨拙的 PHP 解决方案？有任何想法吗？感谢所有有耐心阅读到这里的人，我知道这是一个 PITA 问题。非常感谢任何帮助!

function calculate_public_records()
{
      $total_req_total = 0;
      $total_funded_total = 0;
      $total_cutback_total = 0;

      $organizations = array();

      foreach($this->organizations as $org)
      {
          $id = $org['OrgID'];
          $org_req_total = 0;
          $org_funded_total = 0;
          $org_cutback_total = 0;

          $cycles = array();

          foreach($this->cycles as $cycle)
          {
                $cycle_req_total = 0;
                $cycle_funded_total = 0;
                $cycle_cutback_total = 0;

                $cycle_requests = array();

                foreach($this->request_types as $type)
                {
                    $reqs = $this->funding_request_model->getRequests($type, $cycle['CycleID'], $id);
                    foreach($reqs as $r)
                    {
                        $cutback = $cycle['NonUnityCutBack'];
                        if ($type == "Event") $cutback = ($r->UnityReq == 1) ? $cycle['UnityCutBack'] : $cutback;
                        $cutback = ($r->PerOff != 0) ? $r->PerOff : $cutback;

                        $request = array();
                        $request['id']        = $r->ReqID;
                        $request['type']      = $type;
                        $request['name']      = $r->Title;
                        $request['requested'] = number_format($r->RequestTotal, 2);
                        $request['funded']    = number_format($r->AmtFunded, 2);
                        $request['cutback']   = number_format(($r->AmtFunded - ($r->AmtFunded * $cutback)), 2);

                        $cycle_req_total += $request['requested'];
                        $cycle_funded_total += $request['funded'];
                        $cycle_cutback_total += $request['cutback'];

                        $cycle_requests[] = $request;
                    }

                }

                $cycle_totals = array();
                $cycle_totals['requested'] = number_format($cycle_req_total, 2);
                $cycle_totals['funded'] = number_format($cycle_funded_total, 2);
                $cycle_totals['cutback'] = number_format($cycle_cutback_total, 2);

                $org_req_total += $cycle_req_total;
                $org_funded_total += $cycle_funded_total;
                $org_cutback_total += $cycle_cutback_total;

                $cycles[] = array('name' => $cycle['CycleName'], 'requests' => $cycle_requests, 'totals' => $cycle_totals);
          }

          $org_totals = array();
          $org_totals['requested'] = number_format($org_req_total, 2);
          $org_totals['funded'] = number_format($org_funded_total, 2);
          $org_totals['cutback'] = number_format($org_cutback_total, 2);

          $total_req_total += $org_req_total;
          $total_funded_total += $org_funded_total;
          $total_cutback_total += $org_cutback_total;

          $organizations[] = array('id' => $org['OrgID'], 'name' => $org['Organization'], 'cycles' => $cycles, 'totals' => $org_totals);
      }

      $totals = array();
      $totals['requested'] = number_format($total_req_total, 2);
      $totals['funded'] = number_format($total_funded_total, 2);
      $totals['cutback'] = number_format($total_cutback_total, 2);

      return array('organizations' => $organizations, 'totals' => $totals);

计算总结:

# For tables AdministrativeRequests, CapitalRequests & EventRequests, calculate:

    SUM(AmountRequested)
    SUM(AmountFunded)

# For tables AdministrativeRequests, CapitalRequests & EventRequests, calculate:

    AmountAfterCutback = AmountFunded - (AmountFunded * Cutback)

# Cutback calculation pseudocode

if( <table>.PerOff > 0 ) {
    Cutback = <table>.PerOff
} elseif ( <table> == EventRequests && EventRequests.UnityReq == 1 ) {
    Cutback = FundingCycles.UnityCutBack
} else {
    Cutback = FundingCycles.NonUnityCutBack
}

Answer 1

这是我的尝试 - 更新为使用 UNION ALL 而不是 UNION:

SELECT OrgID,
       SUM(AmountRequested),
       SUM(AmountFunded),
       SUM(AmountFunded - (
           AmountFunded * 
           IF( PerOff > 0,
               PerOff,
               IF( UnityReq = 1,
                   UnityCutBackAmount,
                   NonUnityCutBackAmount
               )
           )
       )) AS AmountAfterCutback
  FROM (
SELECT o.OrgID,
       ar.AmountRequested,
       ar.AmountFunded,
       ar.PerOff,
       null AS UnityReq,
       fc_ar.CycleID,
       fc_ar.NonUnityCutBackAmount,
       fc_ar.UnityCutBackAmount
  FROM Organizations o
  JOIN AdministrativeRequests ar
    ON ar.OrgID = o.OrgID
  JOIN FundingCycles fc_ar
    ON fc_ar.CycleID = ar.CycleID
 UNION ALL
SELECT o.OrgID,
       cr.AmountRequested,
       cr.AmountFunded,
       cr.PerOff,
       null AS UnityReq,
       fc_cr.CycleID,
       fc_cr.NonUnityCutBackAmount,
       fc_cr.UnityCutBackAmount
  FROM Organizations o
  JOIN CapitalRequests cr
    ON cr.OrgID = o.OrgID
  JOIN FundingCycles fc_cr
    ON fc_cr.CycleID = cr.CycleID
 UNION ALL
SELECT o.OrgID,
       er.AmountRequested,
       er.AmountFunded,
       er.PerOff,
       er.UnityReq,
       fc_er.CycleID,
       fc_er.NonUnityCutBackAmount,
       fc_er.UnityCutBackAmount
  FROM Organizations o
  JOIN EventRequests er
    ON er.OrgID = o.OrgID
  JOIN FundingCycles fc_er
    ON fc_er.CycleID = er.CycleID
) tmp
 GROUP BY OrgID;