如何合并两个SQL查询实现公司特征的标准化计算？

需求 #

将两个独立SQL查询合并为一个：

1. 先计算company_feature_test表中F1列的均值（AVG）和标准差（STDDEV）
2. 利用上述统计值，结合company表的in_ref_set与size字段，对company_feature_test表每行数据计算标准化后的feature_1值

合并后的SQL语句 #

SELECT
    DATA.company_id,
    (
        CASE
            WHEN DATA.in_ref_set = 0 AND DATA.size = 'SMALL'
            THEN 1 * ((LN(DATA.F1 + 1) - stats.F1_mean) / stats.F1_std_dev) * 1
            WHEN DATA.in_ref_set = 0 AND DATA.size = 'MEDIUM'
            THEN 2 * ((LN(DATA.F1 + 1) - stats.F1_mean) / stats.F1_std_dev) * 2
            WHEN DATA.in_ref_set = 0 AND DATA.size = 'LARGE'
            THEN 3 * ((LN(DATA.F1 + 1) - stats.F1_mean) / stats.F1_std_dev) * 3
            WHEN DATA.in_ref_set = 0 AND DATA.size = 'VERY_LARGE'
            THEN 4 * ((LN(DATA.F1 + 1) - stats.F1_mean) / stats.F1_std_dev) * 4
            ELSE 5 * ((LN(DATA.F1 + 1) - stats.F1_mean) / stats.F1_std_dev)
        END
    ) AS feature_1
FROM (
    SELECT 
        cft.company_id,
        cft.F1,
        company.in_ref_set,
        company.size
    FROM company_feature_test cft
    JOIN company ON company.id = cft.company_id
) AS DATA
CROSS JOIN (
    SELECT 
        AVG(F1) AS F1_mean, 
        STDDEV(F1) AS F1_std_dev
    FROM company_feature_test
) AS stats
GROUP BY DATA.company_id, stats.F1_mean, stats.F1_std_dev;

表结构说明 #

company表

company_feature_test表

预期输出 #

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