mysql - SQL:泛化查询

Question

我有以下 SQL 查询，它计算 ID 为 1800 的项目的前 100 条目的平均分数。我想将其概括为计算每个项目的前 100 条目的平均分数，因此，如果有 10 个项目，则将处理的记录量为 1000，其中每 100 条对应于一个不同的project_id。任何帮助将不胜感激!

  SELECT 
    score1.project_id, AVG(score1.final_score)
FROM
    (SELECT 
        project_id, final_score
    FROM
        consultant_project_score
    WHERE
        project_id = 1800
    ORDER BY final_score DESC
    LIMIT 100) score1;

Answer 1

将计算平均值的逻辑放在函数中，并在针对表的选择查询中调用它。

select project_id,calaverage(project_id) as `average`
from
consultant_project_score

平均函数可以创建如下:

CREATE 
    FUNCTION `calaverage`(projectid INT)
    RETURNS FLOAT
    BEGIN
    DECLARE average FLOAT;
    SELECT 
    AVG(score1.final_score) INTO average
    FROM
    (SELECT 
        project_id, final_score
    FROM
        consultant_project_score
    WHERE
        project_id = projectid
    ORDER BY final_score DESC
    LIMIT 100) score1;
    RETURN average;
    END