Oracle关联两张表查询movie_qty高于平均值1个标准差的影片信息

原SQL问题汇总 #

• 表名拼写错误：实际表名为movie_types，原SQL写为movie_type，会触发表不存在报错
• 分组逻辑错误：需求需要统计所有影片的全局平均库存和标准差，原SQL按分类分组计算的是各分类的统计值，不符合需求
• 语法错误：Oracle要求GROUP BY后的SELECT字段要么为聚合函数，要么包含在GROUP BY子句中，原SQL中的movie_title、movie_category未包含在GROUP BY里，会触发语法报错
• 缺少筛选逻辑：没有加movie_qty的阈值过滤条件

正确实现方案 #

方法1：子查询预计算全局统计值 #

先通过子查询算出全局的平均库存和标准差，再关联业务表过滤，写法兼容性高：

SELECT m.movie_title, mt.movie_category, m.movie_qty
FROM movie m
INNER JOIN movie_types mt 
  ON m.movie_cat_id = mt.movie_cat_id
CROSS JOIN (
    -- 计算所有影片的全局平均库存、标准差
    SELECT AVG(movie_qty) AS avg_qty, STDDEV(movie_qty) AS std_qty
    FROM movie
) global_stat
-- 筛选库存高于平均值至少1个标准差的记录
WHERE m.movie_qty >= global_stat.avg_qty + global_stat.std_qty;

方法2：窗口函数实现 #

用开窗函数直接计算全局统计值，写法更简洁：

SELECT movie_title, movie_category, movie_qty
FROM (
    SELECT 
        m.movie_title, 
        mt.movie_category, 
        m.movie_qty,
        AVG(m.movie_qty) OVER() AS global_avg,
        STDDEV(m.movie_qty) OVER() AS global_std
    FROM movie m
    INNER JOIN movie_types mt 
      ON m.movie_cat_id = mt.movie_cat_id
) t
WHERE movie_qty >= global_avg + global_std;

补充说明 #

你提供的测试数据中，movie_types表的第4条插入语句缺少右括号，修正后才能正常插入数据：

-- 修正后的插入语句
INSERT INTO movie_types VALUES(4, 'Comedy');

修正测试数据后运行上述SQL，返回结果如下：

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