mysql 从一个月的30天中找出每种语言的百分比份额：获取错误

dohp0rv5 于 2023-03-22 发布在 Mysql

关注(0)|答案(2)|浏览(105)

我用的是this MySQL table。
我试图找出每种语言在30天内的百分比。

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;

The error is highlighted in the 'Output' tab.

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'
我明白了我在尝试第二种方法时所犯的错误。

mysql

来源：https://stackoverflow.com/questions/75768735/find-the-percentage-share-of-each-language-from-the-a-month-of-30-days-getting

2条答案

按热度按时间

nzrxty8p1#

我想你需要窗口函数：

select language, count(*) cnt, count(*) / sum(count(*)) over() ratio
from job_data 
where event in ('transfer', 'decision') 
    and ds >= '2020-11-01' 
    and ds <  '2020-12-01'
group by language

这将按语言聚合表，然后计算计数;表达式sum(count(*)) over()计算所有计数的总和-您可以将其用作除法的denumerator。
注：

窗口函数仅在MySQL 8.0中可用
对文字字符串（和日期）使用单引号而不是双引号;这遵循所有数据库都支持的SQL标准
我建议使用半开放间隔而不是between来过滤日期（如果日期有时间部分，通常效果会更好）

赞(0）回复(0）举报 2023-03-22

e0bqpujr2#

select language, count(distinct language),
    round(100*count(*)/(select count(*) from job_data),2) as percentage_share
from job_data
group by language order by language desc;

赞(0）回复(0）举报 2023-03-22

我来回答

mysql 从一个月的30天中找出每种语言的百分比份额：获取错误

2条答案

相关问题

热门标签

最新问答