计算30天内各语言占比时遇MySQL报错，寻求解决方案

我有一个MySQL表，想要计算2020年11月1日至11月30日（30天）内，每种语言的占比。

我先写了这段SQL：

select language as languages, round(100*count(*)/total, 2)
from job_data
cross join (select count(*) as total from job_data) sub
group by language;

这段SQL运行报错（错误信息在输出标签页高亮显示）。

之后我又尝试了另一段SQL：

WITH CTE AS (
    SELECT Language, COUNT(job_id) AS num_jobs
    FROM job_data
    WHERE event IN("transfer","decision")
    AND ds >= "2020-11-01" AND  ds <= "2020-11-30"
    GROUP BY language),
total AS (
    SELECT COUNT(job_id) AS total_jobs
    FROM job_data
    WHERE event IN("transfer","decision")
    AND ds >= "2020-11-01" AND  ds <= "2020-11-30"
    GROUP BY language)
SELECT language, ROUND(100.0*num_jobs/total_jobs, 2) AS perc_jobs
FROM CTE
ORDER BY perc_job DESC;

出现错误：

Error Code: 1054. Unknown column 'total_jobs' in 'field list'

我知道第二段SQL的问题所在，但还是需要解决计算30天内各语言占比的需求。

解决方案 #

方法一：窗口函数（MySQL 8.0+适用）

窗口函数可以直接计算分组后的总数量，写法简洁高效：

SELECT 
    language,
    ROUND(100.0 * COUNT(job_id) / SUM(COUNT(job_id)) OVER (), 2) AS perc_jobs
FROM job_data
WHERE event IN("transfer","decision")
  AND ds BETWEEN "2020-11-01" AND "2020-11-30"
GROUP BY language
ORDER BY perc_jobs DESC;

SUM(COUNT(job_id)) OVER ()会直接计算出30天内符合条件的总职位数，作为占比计算的分母。

方法二：子查询关联（兼容低版本MySQL）

如果你的MySQL版本不支持窗口函数，可以用子查询先算出总数量，再关联计算：

SELECT 
    j.language,
    ROUND(100.0 * COUNT(j.job_id) / t.total_jobs, 2) AS perc_jobs
FROM job_data j
CROSS JOIN (
    SELECT COUNT(job_id) AS total_jobs
    FROM job_data
    WHERE event IN("transfer","decision")
      AND ds BETWEEN "2020-11-01" AND "2020-11-30"
) t
WHERE j.event IN("transfer","decision")
  AND j.ds BETWEEN "2020-11-01" AND "2020-11-30"
GROUP BY j.language, t.total_jobs
ORDER BY perc_jobs DESC;

注意主表和子查询都要加上时间和事件过滤，避免用全表总数计算占比。

方法三：修正原CTE写法

你的第二段CTE错误在于total子查询多了GROUP BY language，导致返回的是单语言数量而非总数量，修正后：

WITH CTE AS (
    SELECT language, COUNT(job_id) AS num_jobs
    FROM job_data
    WHERE event IN("transfer","decision")
      AND ds BETWEEN "2020-11-01" AND "2020-11-30"
    GROUP BY language
),
total AS (
    SELECT COUNT(job_id) AS total_jobs
    FROM job_data
    WHERE event IN("transfer","decision")
      AND ds BETWEEN "2020-11-01" AND "2020-11-30"
)
SELECT 
    c.language,
    ROUND(100.0 * c.num_jobs / t.total_jobs, 2) AS perc_jobs
FROM CTE c
CROSS JOIN total t
ORDER BY perc_jobs DESC;

内容的提问来源于stack exchange，提问作者Mir Asaduddin Adil