mysql - SQL 计数和分组

Question

好的，我正在使用嵌套的 SQL 语句:

我想查询最近120个我们业务员写的提案。我正在使用嵌套查询从系统中获取最后 120 个提案，然后根据状态等过滤它们...

我遇到的问题是如何获得每个销售员的提案数量？

SQL 显然是错误的，但这就是我卡住的地方。

SELECT 
   CASE userId 
    WHEN '4' THEN 'AT'
    WHEN '3' THEN 'EO'
    WHEN '11' THEN 'CT'
    WHEN '13' THEN 'MH'
    ELSE userId
END AS Salesman,
SUM(contractAmt) as 'Contract Total',
AVG(DATEDIFF(contractDate, proposalDate)) AS averageDays,
SUM(proposalAmt) as pTot,
Count(*) as Contracts,
Count(A.proposalAmt) as Proposals,
(SUM(contractAmt) / SUM(proposalAmt)) AS 'Hit Rate $s',
(Count(*) / Count(A.proposalAmt)) AS 'Hit Rate #s'
FROM
( /* Get the last 120 proposals not in Lead or Proposal status*/
SELECT contractAmt, proposalAmt, contractDate, status, userId, 
    CASE WHEN proposalDate = '0000-00-00'   
        THEN CAST(contractDate as Date)
        else CAST(proposalDate as Date)
    END as proposalDate
FROM project
WHERE (status != 'proposal' and status != 'lead')
/*GROUP BY id*/
ORDER BY contractDate DESC
LIMIT 0, 120) A
WHERE status = 'contract' or status = 'complete'
GROUP BY userId

“Count(A.proposalAmt) as Proposals”，这为我提供了过滤后的记录数。 (仅契约(Contract)和已完成的订单)

在嵌套位内，查询不想返回包含 100 条单独记录的单行计数。如果我按 userId 分组，我会得到用户的提案计数，但现在我没有个人记录供以后过滤。

我可以想出几种方法来解决这个问题，但所有方法都很糟糕。我可以再做一个查询，但这不够优雅，而且对我的输出效果不佳。

数据库是MySql。

万一我没有正确解释它的工作流程:

For each user ID
Get the last 100 proposals
Count # of proposals by salesman
Total proposal $ amount by salesman
Count # of contract or complete proposals
Total $ amount of contract and complete jobs
# proposals / # contracts = Hit Rate #'s
$ proposals / $ contracts = Hit Rate $'s
AVERAGE(contract date - proposal date) = Average # days in process

输出应该类似于:

   Salesman  Avg Days  ProposalTot  ContractTot  HR $'s  Prop #  Con #  HR#'s
   --------------------------------------------------------------------------------------
   EO       |   29.27 |  $30,000  |    $15,000  |  50%  |   30  |   15 | 50%

编辑:添加架构

Table project
=============
id, userId, clientId, contactId, projectName, status, description, creationDate, shipDate, estimateAmt, leadAmt, reestimateAmt, proposalAmt, contractAmt, completeAmt, type, subType, estDate, reestDate, proposalDate, contractDate, completeDate, lostDate, onHoldDate, estShip, reestShip, proposalShip, contractShip, completeShip, casperLink, statusChangeTS
-------------
id               int(11) PK
userId           int(11)
clientId         int(11)
contactId        int(11)
projectName      varchar(255)
status           enum('lead','proposal','contract','complete','onHold','lost')
description      text
creationDate     date
shipDate         date
estimateAmt      int(11)
leadAmt          int(11)
reestimateAmt    int(11)
proposalAmt      int(11)
contractAmt      int(11)
completeAmt      int(11)
type             varchar(100)
subType          varchar(100)
estDate          date
reestDate        date
proposalDate     date
contractDate     date
completeDate     date
lostDate         date
onHoldDate       date
estShip          date
reestShip        date
proposalShip     date
contractShip     date
completeShip     date
casperLink       varchar(20)
statusChangeTS   date

Answer 1

试试这个:

SELECT 
    Count(*) as 'Total Proposals',
    SUM(CASE WHEN status = 'complete' or status = 'contract' THEN 1 ELSE 0 END) as 'Total Contracts',
    CASE userId 
        WHEN '4' THEN 'AT'
        WHEN '3' THEN 'EO'
        WHEN '11' THEN 'CT'
        WHEN '13' THEN 'MH'
        ELSE userId
    END AS Salesman,
    SUM(CASE WHEN status = 'complete' or status = 'contract' THEN contractAmt ELSE 0 END) as 'Contract Total',
    AVG(DATEDIFF(contractDate, proposalDate)) AS averageDays,
    SUM(proposalAmt) as pTot,
    (SUM(CASE WHEN status = 'complete' or status = 'contract' THEN contractAmt ELSE 0 END) / SUM(proposalAmt)) AS 'Hit Rate $s',
    (SUM(CASE WHEN status = 'complete' or status = 'contract' THEN 1 ELSE 0 END) / Count(*)) AS 'Hit Rate #s'
FROM
    (/* This inner loop only selects the top 100 records */
    select * FROM project
    WHERE (status != 'proposal' and status != 'lead') and userId = '13'
    order by contractDate DESC
    LIMIT 0, 30) A
GROUP BY userId 

我想出的答案是不使用 WHERE 子句进行过滤，而是使用 CASE 子句来过滤具有契约(Contract)的记录。您需要在需要契约(Contract)总计的地方执行此操作，并且您必须将契约(Contract)金额过滤为仅具有您身份的契约(Contract)金额。 (除非您的数据库已经处理好了。)

Total Proposals | Total Contracts | Salesman | Contract Total | averageDays | pTot   | Hit Rate $s | Hit Rate #s30                15                MH         160496           46.8          324122   0.4952        0.5