问stddev和avg of 2表oracle
EN

Stack Overflow用户

提问于 2021-11-29 14:08:17

回答 2查看 38关注 0票数 0

如果我在Oracle SQL Developer中有两个表: movies和movie_types。

包含值的表'movies‘：movie_id、movie_title、movie_qty、movie_value和movie_cat_id。

表'movie_types‘包含值: movie_cat_id和movie_category。

如何显示movie_title、movie_category，然后显示大于movie_qty的所有电影movie_qty的至少1个标准开发版本的movie_qty？

这是一个家庭作业问题，这个问题本身让我有点困惑，我找不到类似的例子。

以下是表格数据：

CREATE TABLE movie
(movie_id number(2),
movie_title varchar2(40),
movie_qty number(2),
movie_value number(2),
movie_cat_id number(2));

CREATE TABLE movie_types
(movie_cat_id number(2),
movie_category varchar2(30));

INSERT INTO movie VALUES(1, 'Alien', 5, 10, 1);
INSERT INTO movie VALUES(2, 'Bladerunner', 3, 8, 1);
INSERT INTO movie VALUES(3, 'Star Wars', 11, 15, 1);
INSERT INTO movie VALUES(4, 'Texas Chainsaw Massacre', 2, 7, 2);
INSERT INTO movie VALUES(5, 'Jaws', 1, 7, 2);
INSERT INTO movie VALUES(6, 'The Good, the Bad, and the Ugly', 2, 7, 3);
INSERT INTO movie VALUES(7, 'Silverado', 1, 7, 3);
INSERT INTO movie VALUES(8, 'Duck Soup', 1, 5, 4);
INSERT INTO movie VALUES(9, 'Planes, Trains, and Automobiles', 3, 5, 4);
INSERT INTO movie VALUES(10, 'Waking Ned Devine', 4, 12, 4);
INSERT INTO movie VALUES(11, 'Deep Blue Sea', 3, 14, 5);
INSERT INTO movie VALUES(12, 'The Fifth Element', 5, 15, 5);

INSERT INTO movie_types VALUES(1, 'SciFi');
INSERT INTO movie_types VALUES(2, 'Horror');
INSERT INTO movie_types VALUES(3, 'Western');
INSERT INTO movie_types VALUES(4, 'Comedy';
INSERT INTO movie_types VALUES(5, 'Drama');

我试过了：

select movie_category, movie_title,
(avg(movie_qty)),
(stddev(movie_qty))
from movie, movie_type
where movie_type.movie_cat_id = movie.movie_cat_id
group by movie.movie_cat_id;

我知道这是错误的，如果有人能提供一些指导，我将不胜感激！提前感谢让我知道，如果需要澄清。

oracle

select

group-by

oracle-sqldeveloper

sql

回答 2

Stack Overflow用户

发布于 2021-11-29 14:38:03

因为这是家庭作业。

这可以使用analytic functions来完成。查询就快完成了，您只需通过删除GROUP BY子句并添加OVER (...)子句将聚合函数转换为分析函数即可：

SELECT movie_category,
       movie_title,
       movie_qty,
       AVG(movie_qty)    OVER () AS avg_movie_qty,
       STDDEV(movie_qty) OVER () AS stddev_movie_qty
FROM   movie
       INNER JOIN movie_types
       ON movie_types.movie_cat_id = movie.movie_cat_id

将输出每部电影的类别、标题和数量，以及整个结果集上的平均和标准偏差OVER窗口。

然后，您需要更进一步，并将其包装在一个外部查询中，该查询将过滤行，添加一个WHERE子句，将数量与平均值加一个标准差进行比较。我会把它留给你来完成。

另一种方法是接受您的查询，同样没有GROUP BY子句，并添加一个过滤器来比较数量：

SELECT movie_category,
       movie_title
FROM   movie
       INNER JOIN movie_types
       ON movie_types.movie_cat_id = movie.movie_cat_id
WHERE  movie_qty > ( ... )

然后，可以在大括号中放置子查询，该子查询计算整个结果集的数量加上一个标准差的平均值(注意:如果要计算整个结果集的平均值，则不需要GROUP BY子句)。

db<>fiddle

票数 0

Stack Overflow用户

发布于 2021-11-29 14:53:34

一种老式的方法是在子查询中计算STDDEV。

因为它只有1个结果，所以你可以不用担心交叉连接到它。

SELECT movie_category, movie_title, movie_qty
-- , stddev_movie_qty
FROM movie
JOIN movie_types
  ON movie_types.movie_cat_id = movie.movie_cat_id
CROSS JOIN (
   SELECT 
     STDDEV(movie_qty) AS stddev_movie_qty 
   FROM movie
) stats
WHERE movie_qty > stddev_movie_qty