Блог

Главная - Вопросы по программированию - Как заставить мой подзапрос вернуть среднее значение?

Как заставить мой подзапрос вернуть среднее значение?

#sql #oracle

#sql #Oracle

Вопрос:

Я совершенно новичок в подзапросах, и я хотел лучше понять их. Я попытался сделать заявление, в котором мой вывод будет (транспортные средства, цена которых превышает среднюю цену транспортного средства):

 Model
-------------
Q60
Q70

Однако в настоящее время я не получаю возвращенных строк и не уверен, почему. Я действительно хочу, чтобы это было в подзапросе, а не нет.

Вот мое утверждение:

 SELECT t1.MODELNAME AS "Model"
FROM TBLMODEL t1
FULL JOIN TBLVEHICLE t2 ON t1.MODELID = t2.MODELID
WHERE t2.VEHICLEPRICE IN
(
  SELECT AVG(VEHICLEPRICE)    
  FROM TBLVEHICLE    
  GROUP BY VEHICLEPRICE
  HAVING AVG(VEHICLEPRICE) < VEHICLEPRICE  
);

И вот файл, к которому это относится:

 DROP TABLE  tblOptionDetail;
DROP TABLE  tblOption;
DROP TABLE  tblVehicle;
DROP TABLE  tblModel;
DROP TABLE  tblBrand;
DROP TABLE  tblManufacture;


CREATE TABLE tblManufacture
(
ManufactureID NUMBER(38) PRIMARY KEY,
ManufactureName CHAR(40)
);

CREATE TABLE tblBrand
(
BrandID NUMBER(38) PRIMARY KEY,
ManufactureID NUMBER(38),
BrandName CHAR(40),
FOREIGN KEY (ManufactureID) REFERENCES tblManufacture(ManufactureID)
);

CREATE TABLE tblModel
(
ModelID NUMBER(38) PRIMARY KEY,
BrandID NUMBER(38),
ModelName CHAR(40),
FOREIGN KEY (BrandID) REFERENCES tblBrand(BrandID)
);

CREATE TABLE tblVehicle
(
VehicleID NUMBER(38) PRIMARY KEY,
ModelID NUMBER(38),
VehicleYear NUMBER(38) CHECK (VehicleYear BETWEEN 1900 and 3000),
VehicleKM NUMBER(38) CHECK (VehicleKM BETWEEN 0 and 100000000),
VehiclePrice NUMBER(38) CONSTRAINT carprice CHECK (VehiclePrice BETWEEN 0 and 1000000),
FOREIGN KEY (ModelID) REFERENCES tblModel(ModelID)
);

CREATE TABLE tblOption
(
OptionID NUMBER(38) PRIMARY KEY,
OptionDesc CHAR(40)
);

CREATE TABLE tblOptionDetail
(
OptionID NUMBER(38),
VehicleID NUMBER(38),
PRIMARY KEY (OptionID, VehicleID),
FOREIGN KEY (OptionID) REFERENCES tblOption(OptionID),
FOREIGN KEY (VehicleID) REFERENCES tblVehicle(VehicleID)
);


-- populate the Manufacture table

INSERT ALL 
INTO tblManufacture (ManufactureID, ManufactureName) VALUES (1,'Chrysler Corp.')
INTO tblManufacture (ManufactureID, ManufactureName) VALUES(2,'General Motors')
INTO tblManufacture (ManufactureID, ManufactureName) VALUES(3,'Ford Motor Company')
INTO tblManufacture (ManufactureID, ManufactureName) VALUES(4,'Toyota')
INTO tblManufacture (ManufactureID, ManufactureName) VALUES (5,'Honda')
INTO tblManufacture (ManufactureID, ManufactureName) VALUES (6,'Nisan')
SELECT * FROM dual;

-- populate the Brand table

INSERT ALL
INTO tblBrand (BrandID, ManufactureID, BrandName) VALUES (1,1, 'Chrysler')
INTO tblBrand (BrandID, ManufactureID, BrandName) VALUES (2,1, 'Dodge')
INTO tblBrand (BrandID, ManufactureID, BrandName) VALUES (3,1, 'Jeep')
INTO tblBrand (BrandID, ManufactureID, BrandName) VALUES (4,2, 'Chevrolet')
INTO tblBrand (BrandID, ManufactureID, BrandName) VALUES (5,2, 'Buick')
INTO tblBrand (BrandID, ManufactureID, BrandName) VALUES (6,2, 'Cadillac')
INTO tblBrand (BrandID, ManufactureID, BrandName) VALUES (7,2, 'GMC')
INTO tblBrand (BrandID, ManufactureID, BrandName) VALUES (8,4, 'Toyota')
INTO tblBrand (BrandID, ManufactureID, BrandName) VALUES (9,6, 'Nissan')
INTO tblBrand (BrandID, ManufactureID, BrandName) VALUES (10,6,'Infiniti')
SELECT * FROM dual;

-- populate the Model table

INSERT ALL
INTO tblModel (ModelID, BrandID, ModelName) VALUES (1,1, '200')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (2,1, '200 Convertible')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (3,1, '300')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (4,1, 'Town'||' amp; '||'Country')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (5,2, 'Durango')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (6,2, 'Avenger')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (7,2, 'Challenger')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (8,2, 'Charger')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (9,2, 'Caliber')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (10,2, 'Grand Caravan')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (11,2, 'Journey')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (12,4, 'Sonic')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (13,4, 'Cruze')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (14,4, 'Orlando')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (15,4, 'Colorado')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (16,4, 'Malibu')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (17,8, '4Runner')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (18,8, 'Prius')
INTO tblModel (ModelID, BrandID, ModelName) VALUES  (19,8, 'Camry')
INTO tblModel (ModelID, BrandID, ModelName) VALUES  (20,8, 'Corolla')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (21,9,'Rogue SL')
INTO tblModel (ModelID, BrandID, ModelName) VALUES  (22,9, 'Versa Note')
INTO tblModel (ModelID, BrandID, ModelName) VALUES  (23,9, 'Pathfinder')
INTO tblModel (ModelID, BrandID, ModelName) VALUES  (24,9, 'Altima')
INTO tblModel (ModelID, BrandID, ModelName) VALUES (25,10, 'Q70')
INTO tblModel (ModelID, BrandID, ModelName) VALUES  (26,10, 'Q50')
INTO tblModel (ModelID, BrandID, ModelName) VALUES  (27,10, 'Q60')
INTO tblModel (ModelID, BrandID, ModelName) VALUES  (28,10, 'EX37')
SELECT * FROM dual;

-- populate Option table

INSERT ALL 
INTO tblOption (OptionID,OptionDesc) VALUES (1,'Power locks')
INTO tblOption (OptionID,OptionDesc) VALUES (2,'Power windows')
INTO tblOption (OptionID,OptionDesc) VALUES (3,'Air conditioning')
INTO tblOption (OptionID,OptionDesc) VALUES (4,'Heated mirrors')
INTO tblOption (OptionID,OptionDesc) VALUES (5,'Remote keyless entry')
INTO tblOption (OptionID,OptionDesc) VALUES (6,'CD Player')
INTO tblOption (OptionID,OptionDesc) VALUES (7,'GPS')
INTO tblOption (OptionID,OptionDesc) VALUES (8,'In-dash DVD player')
INTO tblOption (OptionID,OptionDesc) VALUES (9,'Overhead DVD player')
INTO tblOption (OptionID,OptionDesc) VALUES (10,'Satellite radio')
INTO tblOption (OptionID,OptionDesc) VALUES (11,'MP3 player')
INTO tblOption (OptionID,OptionDesc) VALUES (12,'Antilock braking system')
INTO tblOption (OptionID,OptionDesc) VALUES (13,'Electronic stability system')
INTO tblOption (OptionID,OptionDesc) VALUES (14,'Traction control')
INTO tblOption (OptionID,OptionDesc) VALUES (15,'Cruise control')
INTO tblOption (OptionID,OptionDesc) VALUES (16,'Intelligent cruise control')
INTO tblOption (OptionID,OptionDesc) VALUES (17,'Parking assist system')
INTO tblOption (OptionID,OptionDesc) VALUES (18,'Xenon headlights')
INTO tblOption (OptionID,OptionDesc) VALUES (19,'Aluminum rims')
INTO tblOption (OptionID,OptionDesc) VALUES (20,'AWD')
INTO tblOption (OptionID,OptionDesc) VALUES (21,'Convertable')
INTO tblOption (OptionID,OptionDesc) VALUES (22,'Heated Seats')
SELECT * FROM dual;

-- populate Vehicle table

INSERT ALL
INTO tblVehicle (VehicleID,ModelID, VehicleYear, VehicleKM, VehiclePrice) VALUES (1,10, 2010, 45000, 18000)
INTO tblVehicle (VehicleID,ModelID, VehicleYear, VehicleKM, VehiclePrice) VALUES (2,10, 2010, 65420, 17500)
INTO tblVehicle (VehicleID,ModelID, VehicleYear, VehicleKM, VehiclePrice) VALUES (3,16, 2004, 143900, 3200)
INTO tblVehicle (VehicleID,ModelID, VehicleYear, VehicleKM, VehiclePrice) VALUES (4,11, 2010, 38900, 14500)
INTO tblVehicle (VehicleID,ModelID, VehicleYear, VehicleKM, VehiclePrice) VALUES (5,27,2014,17250,45999)
INTO tblVehicle (VehicleID,ModelID, VehicleYear, VehicleKM, VehiclePrice) VALUES (6,25,2015,2900,62300)
INTO tblVehicle (VehicleID,ModelID, VehicleYear, VehicleKM, VehiclePrice) VALUES (7,17,2010,87900,19800)
SELECT * FROM dual;

-- populate OptionDetail table

INSERT ALL
INTO tblOptionDetail (OptionID, VehicleID) VALUES (2, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (1, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (3, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (5, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (6, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (7, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (8, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (11, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (12, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (14, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (15, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (19, 2)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (2, 1)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (1, 1)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (3, 1)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (6, 1)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (12, 1)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (14, 1)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (15, 1)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (19, 1)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (2, 3)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (1, 3)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (3, 3)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (6, 3)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (12, 3)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (14, 3)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (15, 3)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (19, 3)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (2, 4)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (1, 4)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (3, 4)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (6, 4)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (11, 4)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (12, 4)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (14, 4)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (15, 4)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (19, 4)
INTO tblOptionDetail (OptionID, VehicleID) VALUES  (4,5)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (8,5)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (1,5)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (2,5)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (5,5)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (21,6)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (22,6)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (3,6)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (1,6)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (2,6)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (1,7)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (4,7)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (5,7)
INTO tblOptionDetail (OptionID, VehicleID) VALUES (2,7)
SELECT * FROM dual;

COMMIT;

Ответ №1:

Сначала давайте проанализируем вашу попытку — возможно, из этого можно извлечь несколько уроков. (Я предоставлю рабочий ответ ниже, не волнуйтесь!)

В подзапросе вы берете все строки из tblVehicle таблицы. Затем вы группируете их по цене; таким образом, если три автомобиля имеют одинаковую цену в 25 000 долларов, они будут в одной группе. (В большинстве групп, скорее всего, будет только одна строка, поскольку цены на разные транспортные средства очень редко совпадают.)

Затем внутри каждой группы вы вычисляете среднюю цену для группы и сравниваете с ценой внутри той же группы. Вот как работает HAVING: он обрабатывает каждую группу изолированно, как если бы это были единственные рассматриваемые строки.

Итак, в вашем случае все строки в группе имеют одинаковую цену — и, следовательно, среднее значение также одинаково, равно цене каждого транспортного средства в этой группе. Таким образом, < сравнение, очевидно, завершится неудачей в каждом отдельном случае — вы не получите строк. То есть: даже в подзапросе нет строк. Затем вы используете его для IN — но поскольку подзапрос не возвращает строк, в списке из подзапроса ничего нет, поэтому общий запрос также не возвращает строк.

Еще одна вещь — вам повезло, что vehicleprice это было доступно даже в предложении HAVING. Он был доступен, потому что вы сгруппировали по этому столбцу; другие столбцы (не включенные в список ГРУПП ПО) были бы недоступны в предложении HAVING, вы бы получили синтаксическую ошибку.

Теперь к правильному решению: вам нужно сравнить цену каждого транспортного средства со средним значением по всем транспортным средствам. Это среднее значение по всем транспортным средствам является единственной «странной» величиной, которую вы должны вычислить, пройдя всю таблицу. Итак, это единственное, что вам нужно вычислить в подзапросе.

Вот так: (обратите внимание, мне не нравится «…» для имен столбцов — не очень хорошая практика)

 SELECT t1.MODELNAME AS model
FROM TBLMODEL t1
FULL JOIN TBLVEHICLE t2 ON t1.MODELID = t2.MODELID
WHERE t2.VEHICLEPRICE >
(
  SELECT AVG(VEHICLEPRICE)    
  FROM TBLVEHICLE     
);

Обратите внимание, что подзапрос возвращает только одну строку с одним столбцом — одно значение. Это называется «скалярным» подзапросом, и возвращаемое им значение может использоваться так, как если бы это было «автономное» значение (а не результат подзапроса). Вы можете сравнить его с > условием, как если бы вы сравнивали с простым числом.

Комментарии:

1. Теперь это ответ, спасибо за обучение, а не просто за ответ, это мне очень помогло. Время поэкспериментировать еще немного!

Ответ №2:

Это ответ на ваш вопрос:

 SELECT m.MODELNAME AS "Model"
FROM TBLMODEL m JOIN
     TBLVEHICLE v
     ON m.MODELID = v.MODELID
WHERE t2.VEHICLEPRICE > (SELECT AVG(VEHICLEPRICE)    
                         FROM TBLVEHICLE  
                        )  ;

С чего начать решение проблем с вашим запросом.

  • FULL JOIN Не требуется. Фактически, WHERE отфильтровывает несовпадающие строки, превращая full join в left или right внешнее соединение.
  • Подзапрос принимает AVG(VEHICLEPRICE) и группирует по VEHICLEPRICE . Ну, это легко вычислить. Если все значения одинаковы, значение просто VEHICLEPRICE .
  • HAVING Предложение довольно сложно выполнить. Вы сохраняете только строки, где среднее значение строго меньше внешнего значения.
  • Ну, IN никогда не будет соответствовать значениям, которые строго меньше, чем оно.
  • И, когда вы используете псевдонимы таблиц, делайте их сокращениями для таблиц.

Комментарии:

1. Я рад, что вы мне это объяснили, однако, когда я попытался протестировать предоставленное вами утверждение, оно, похоже, не работает?

2. HAVING Предложение не сравнивается с внешним значением — в этом весь смысл. Он сравнивает среднее значение со значением; здесь «значение» означает цену ТРАНСПОРТНОГО СРЕДСТВА, все в пределах одной группы в таблице в подзапросе! Поскольку группировка выполняется по цене ТРАНСПОРТНОГО СРЕДСТВА, все значения равны (и, следовательно, также равны среднему) и < всегда будут завершаться ошибкой.