SQL运算中别名的正确使用语法及average ratio计算问题

一、SQL运算中别名的正确语法规则 #

• 别名可用于字段、表、子查询等对象，通过AS关键字声明，AS也可以省略不写
• 若别名包含空格、特殊字符，或是SQL保留字，需要用对应数据库的标识符包裹：MySQL用反引号`，PostgreSQL、SQL Server等可使用双引号（需开启对应配置）
• SQL执行优先级高于SELECT的子句（FROM、JOIN、WHERE、GROUP BY、聚合运算）无法识别SELECT中声明的别名，同层级的SELECT子句中也不能直接引用刚定义的别名，只有ORDER BY子句可以直接引用SELECT里的别名。如果要使用别名参与运算，需要通过子查询、CTE等方式嵌套一层，让别名先生成后再调用。

二、average ratio的正确写法 #

你原代码的核心错误有两点：

1. 用双引号包裹别名参与运算，会被SQL识别为字符串常量，无法进行数值计算
2. 在同层级SELECT中直接引用前面刚定义的average appraised price和average sell price别名，不符合SQL执行顺序，无法正确取值

修正后的代码（直接重复聚合逻辑，适配所有SQL版本） #

SELECT 
    `account`.`company`,
    `account`.`name`, 
    `inventory`.`sellernum`,
    ROUND(
        AVG(inventory.appraisedprice *
            IF(v_winning_clerks.dateentered >= NOW() - INTERVAL 12 MONTH,3,
               IF(v_winning_clerks.dateentered BETWEEN NOW() - INTERVAL 24 MONTH AND NOW() - INTERVAL 12 MONTH,2, 1))), 2) 
        AS `average appraised price`,
    ROUND(AVG(`v_winning_clerks`.`sellprice`),2) AS `average sell price`,
    ROUND(
        AVG(`v_winning_clerks`.`sellprice`) 
        / 
        AVG(inventory.appraisedprice *
            IF(v_winning_clerks.dateentered >= NOW() - INTERVAL 12 MONTH,3,
               IF(v_winning_clerks.dateentered BETWEEN NOW() - INTERVAL 24 MONTH AND NOW() - INTERVAL 12 MONTH,2, 1)))
    ,2) AS `average ratio`
FROM `v_winning_clerks`
JOIN `inventory` USING(`itemnum`, `auctionnum`)
JOIN `attendance` USING(`auctionnum`, `sellernum`)
JOIN `account` USING(`accountnum`)
GROUP BY `inventory`.`sellernum`, `account`.`company`, `account`.`name`

补充说明：GROUP BY子句需要包含所有未参与聚合的非计算字段，原代码只分组了sellernum，缺少company和name，在开启ONLY_FULL_GROUP_BY模式的MySQL中会报错，以上代码已同步修正该问题。

优化写法（用CTE避免重复写聚合逻辑，适配MySQL 8.0+、PostgreSQL、SQL Server等） #

WITH base_calc AS (
    SELECT 
        `account`.`company`,
        `account`.`name`, 
        `inventory`.`sellernum`,
        AVG(inventory.appraisedprice *
            IF(v_winning_clerks.dateentered >= NOW() - INTERVAL 12 MONTH,3,
               IF(v_winning_clerks.dateentered BETWEEN NOW() - INTERVAL 24 MONTH AND NOW() - INTERVAL 12 MONTH,2, 1))) AS avg_appraised,
        AVG(`v_winning_clerks`.`sellprice`) AS avg_sell
    FROM `v_winning_clerks`
    JOIN `inventory` USING(`itemnum`, `auctionnum`)
    JOIN `attendance` USING(`auctionnum`, `sellernum`)
    JOIN `account` USING(`accountnum`)
    GROUP BY `inventory`.`sellernum`, `account`.`company`, `account`.`name`
)
SELECT 
    company,
    name,
    sellernum,
    ROUND(avg_appraised,2) AS `average appraised price`,
    ROUND(avg_sell,2) AS `average sell price`,
    ROUND(avg_sell / avg_appraised,2) AS `average ratio`
FROM base_calc

内容的提问来源于stack exchange，提问作者Gerreth Schliep