Проводятся соревнования по автогонкам из нескольких заездов с неопределенным количеством машин. Результаты содержатся в таблице и измеряются в очках. Имеется описание (цвет, модель, мощность в л.с.) машин (НЕ ВСЕХ), которое находится в отдельной таблице, однако, ВСЕ машины зарегистрированы (их гос. номера).
Вопрос:
Написать запрос: выдать лучший результат, средний результат, среднеквадратичное отклонение результата в каждом заезде, при условии, что до финиша (с очками) доехало не менее 3 машин и их суммарная мощность не меньше 1000 л.с. Если точного значения мощности для конкретной машины не имеется, то берется мощность, равная 334 л.с. У лучших результатов номера машин и их описание, если есть. Упорядочить результаты по возрастанию. Создать индексы для эффективной работы запроса.
Создадим необходимые объекты (таблицы, ограничения, индексы):
CREATE TABLE CAR (CAR_ID NUMBER, GOVERNMENT_NUMBER VARCHAR2(32) ) TABLESPACE MIRAVILD_DATA; COMMENT ON TABLE CAR IS 'Автомобиль'; COMMENT ON COLUMN CAR.CAR_ID IS 'Идентификатор автомобиля'; COMMENT ON COLUMN CAR.GOVERNMENT_NUMBER IS 'Государтсвенный номер'; CREATE UNIQUE INDEX U_IDX_CAR ON CAR (CAR_ID) TABLESPACE MIRAVILD_INDEX; ALTER TABLE CAR ADD CONSTRAINT PK_CAR PRIMARY KEY (CAR_ID) USING INDEX U_IDX_CAR ENABLE VALIDATE; ALTER TABLE CAR MODIFY (GOVERNMENT_NUMBER CONSTRAINT CAR_NUMBER_NN NOT NULL ENABLE); / CREATE TABLE CAR_DSC (CAR_ID NUMBER, COLOR VARCHAR2(32), "MODEL" VARCHAR2(128), ENGINE_POWER NUMBER ) TABLESPACE MIRAVILD_DATA; COMMENT ON TABLE CAR_DSC IS 'Описание автомобиля'; COMMENT ON COLUMN CAR_DSC.CAR_ID IS 'Идентификатор автомобиля'; COMMENT ON COLUMN CAR_DSC.COLOR IS 'Цвет автомобиля'; COMMENT ON COLUMN CAR_DSC.MODEL IS 'Модель автомобиля'; COMMENT ON COLUMN CAR_DSC.ENGINE_POWER IS 'Мощность двигателя в л.с.'; CREATE UNIQUE INDEX U_IDX_CAR_DSC ON CAR_DSC (CAR_ID) TABLESPACE MIRAVILD_INDEX; ALTER TABLE CAR_DSC ADD CONSTRAINT FK_CAR_DSC FOREIGN KEY (CAR_ID) REFERENCES CAR (CAR_ID) ENABLE; ALTER TABLE CAR_DSC MODIFY (CAR_ID CONSTRAINT CAR_DSC_CAR_ID_NN NOT NULL ENABLE); / CREATE TABLE "RACE" ("RACE_ID" NUMBER, CONSTRAINT RACE_PK PRIMARY KEY ("RACE_ID") ) ORGANIZATION INDEX TABLESPACE MIRAVILD_INDEX; COMMENT ON TABLE "RACE" IS 'Заезд'; COMMENT ON COLUMN "RACE"."RACE_ID" IS 'Идентификатор заезда'; / CREATE TABLE RESULT_RACE (CAR_ID NUMBER, RACE_ID NUMBER, "RESULT" NUMBER ) TABLESPACE MIRAVILD_DATA; COMMENT ON TABLE RESULT_RACE IS 'Результат заезда'; COMMENT ON COLUMN RESULT_RACE.CAR_ID IS 'Идентификатор автомобиля'; COMMENT ON COLUMN RESULT_RACE.RACE_ID IS 'Идентификатор заезда'; COMMENT ON COLUMN RESULT_RACE.RESULT IS 'Результат заезда'; ALTER TABLE RESULT_RACE ADD CONSTRAINT FK_CAR FOREIGN KEY (CAR_ID) REFERENCES CAR (CAR_ID) ENABLE; ALTER TABLE RESULT_RACE ADD CONSTRAINT FK_RACE FOREIGN KEY (RACE_ID) REFERENCES RACE (RACE_ID) ENABLE; CREATE UNIQUE INDEX U_IDX_RESULT_RACE ON RESULT_RACE (CAR_ID, RACE_ID) TABLESPACE MIRAVILD_INDEX; /
Запрос:
WITH r AS (SELECT rr.race_id, rr.result, c.government_number, d.color, d.model, d.engine_power FROM result_race rr JOIN car c ON c.car_id = rr.car_id LEFT JOIN car_dsc d ON d.car_id = c.car_id) SELECT t.max_result, -- лучший результат t.avg_result, -- средний результат t.stddev_result, -- среднеквадратичное отклонение r.government_number, -- гос. номер машины r.color, -- цвет машины r.model, -- модель машины r.engine_power -- мощность в л.с машины FROM (SELECT rr.race_id, MAX(rr.result) AS max_result, AVG(rr.result) AS avg_result, STDDEV(rr.result) AS stddev_result FROM result_race rr LEFT JOIN car_dsc d ON d.car_id = rr.car_id GROUP BY rr.race_id HAVING COUNT(rr.result) >= 3 AND SUM(CASE WHEN d.engine_power IS NULL THEN 334 ELSE d.engine_power END) >= 1000) t JOIN r ON r.race_id = t.race_id AND r.result = t.max_result ORDER BY max_result ASC; /
P.S.
Конфигурация СУБД — Oracle Database 11g Express Edition Release 11.2.0.2.0
IDE — PL/SQL Developer Version 11.0.6.1776