mysql - MySQL中具有不同条件的多个聚合函数

Question

我正在运行以下查询

SELECT t2.lender_name, COUNT(t1.id) as total,    
SUM(t1.submit_date IS NULL) AS num_incomplete,

(SELECT AVG(DATEDIFF(due_date,now())) 
  FROM table_1 WHERE submit_date IS NULL ) as avg_incomplete_due_in,
(SELECT AVG(DATEDIFF(due_date,submit_date))
  FROM table_1 WHERE submit_date IS NOT NULL) as avg_complete_turnaround

FROM table_1
INNER JOIN table_2 t2 ON t2.fid = t1.id
WHERE t1.due_date <= '2010-12-31'
GROUP BY t2.lender_name

总数、num_incomplete 和分组效果很好。每行的子选择值都相同。我希望这些值也按 lender_name 分组并作为同一记录集的一部分返回。有什么建议么？

Answer 1

您当前的代码只是缺少外部查询和子查询之间的关系。理论上，您只需要关联查询:

SELECT t2.lender_name, COUNT(t1.id) as total,
SUM(t1.submit_date IS NULL) AS num_incomplete,
 (SELECT AVG(DATEDIFF(due_date,now())) 
  FROM table_1 t3
  WHERE submit_date IS NULL
    AND t3.lender_name = t2.lender_name) as avg_incomplete_due_in,
 (SELECT AVG(DATEDIFF(due_date,submit_date))
  FROM table_1
  WHERE submit_date IS NOT NULL
    AND t3.lender_name = t2.lender_name) as avg_complete_turnaround
FROM table_1 t1
INNER JOIN table_2 t2 ON t2.fid = t1.id
WHERE t1.due_date <= '2010-12-31'
GROUP BY t2.lender_name

实际上，MySQL 中的查询效率不是很高。您可以通过以下方式重写它:

SELECT
  t2.lender_name,
  COUNT(*) as total,
  SUM(t1.submit_date IS NULL) AS num_incomplete,
  AVG(IF(t1.submit_date IS NULL,
         DATEDIFF(t1.due_date, NOW()),
         NULL)) AS avg_incomplete_due_in,
  AVG(DATEDIFF(due_date,submit_date)) AS avg_complete_turnaround
FROM table_1 t1
INNER JOIN table_2 t2 ON t2.fid = t1.id
WHERE t1.due_date <= '2010-12-31'
GROUP BY t2.lender_name