Как правильно составить sql запрос по выборке данных?

Question

[evgeniy1982]

Задание состоит вот в чем: " Нужно отсортировать заведения по рейтингу из их отзывов, если заведение является сетью (например Макдональдс) то нужно считать его рейтингом среднее арифметическое рейтингов его филиалов, если заведение является филиалом его не нужно выводить, так как его рейтинг входит в рейтинг сети, если у заведения нет review то его рейтингом будет 0,

Для сетей нужно считать именно среднее арифметическое рейтингов филиалов а не среднее арифметическое рейтингов review всез филиалов,
если у филиала ко-во review будет не чётным такой подосчет будет не верным"
вот пример решения
Пример 1
(не четное ко-во review)
Заведение 1
Рейтинги - 1,5,6
Заведение 2
Рейтинги - 2,6,8,4
Если мы считаем рейтинг для первого заведения (1+5+6)/3 = 4
Рейтинг второго (2+6+8+4)/4 = 5
Значит рейтинг для сети будет - (4 + 5 )/2 = 4.5
Если считать среднее арифметическое рейтингов review - (1+5+6+2+6+8+4)/7 = 4.57
а вот пример подсчета вручную

А вот сам скрипт:

CREATE SCHEMA IF NOT EXISTS EstablishmentHomeWork;
USE EstablishmentHomeWork;
CREATE TABLE IF NOT EXISTS Establishment
(
    id       BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    name     VARCHAR(100)                      NOT NULL,
    is_chain BOOLEAN default false,
    chain_id BIGINT,
    FOREIGN KEY (chain_id)
        REFERENCES Establishment (id)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
);
CREATE TABLE IF NOT EXISTS Review
(
    id               BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    establishment_id BIGINT                            NOT NULL,
    FOREIGN KEY (establishment_id)
        REFERENCES Establishment (id)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
);
CREATE TABLE IF NOT EXISTS Rate
(
    id        BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    rate      DOUBLE,
    type      VARCHAR(100)                      NOT NULL,
    review_id BIGINT                            NOT NULL,
    FOREIGN KEY (review_id)
        REFERENCES Review (id)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
);


INSERT
INTO Establishment(name, is_chain, chain_id)
VALUES ('Starbucks', true, null),
       ('Starbucks New York1', false, 1),
       ('Starbucks Philadelphia', false, 1),
       ('Starbucks New York2', false, 1),
       ('Starbucks Los Angeles1', false, 1),
       ('Starbucks Los Angeles2', false, 1),
       ('Masa', false, null),
       ('Beard Papas', false, null),
       ('Joanne', false, null),
       ('Brooklyn Fare', false, null),
       ('KFC', true, null),
       ('KFC Los Angeles', false, 11),
       ('KFC New York', false, 11),
       ('KFC Philadelphia', false, 11),
       ('McDonald''s', true, null),
       ('McDonald''s Philadelphia', false, 15),
       ('McDonald''s Los Angeles', false, 15),
       ('McDonald''s New York', false, 15);
INSERT INTO Review(establishment_id)
VALUES (2),
       (2),
       (3),
       (3),
       (4),
       (5),
       (7),
       (8),
       (8),
       (8),
       (9),
       (10),
       (12),
       (13),
       (14),
       (16),
       (16),
       (16),
       (17),
       (17),
       (18),
       (18),
       (18),
       (18);
INSERT INTO Rate(rate,type,review_id)
VALUES (9.2,'FOOD',1),
       (9.1,'SERVICE',1),
       (7.1,'PRICES',1),
       (10,'INTERIOR',1),
       (7.8,'AMBIENCE',1),
       (3.9,'FOOD',2),
       (5.1,'SERVICE',2),
       (3.5,'PRICES',2),
       (4.9,'INTERIOR',2),
       (6.9,'AMBIENCE',2),

       (7,'FOOD',3),
       (8.5,'SERVICE',3),
       (5.4,'PRICES',3),
       (6.2,'INTERIOR',3),
       (5.4,'AMBIENCE',3),
       (7.6,'FOOD',4),
       (6.1,'SERVICE',4),
       (4.3,'PRICES',4),
       (7.8,'INTERIOR',4),
       (4.6,'AMBIENCE',4),

       (9.6,'FOOD',5),
       (9,'SERVICE',5),
       (7.4,'PRICES',5),
       (8.7,'INTERIOR',5),
       (8.3,'AMBIENCE',5),

       (0,'FOOD',6),
       (0,'SERVICE',6),
       (0,'PRICES',6),
       (0,'INTERIOR',6),
       (10,'AMBIENCE',6),

       (2,'FOOD',7),
       (9.1,'SERVICE',7),
       (8.8,'PRICES',7),
       (9,'INTERIOR',7),
       (8.9,'AMBIENCE',7),

       (9.1,'FOOD',8),
       (9.1,'SERVICE',8),
       (7.3,'PRICES',8),
       (9,'INTERIOR',8),
       (8.1,'AMBIENCE',8),
       (8.2,'FOOD',9),
       (8.1,'SERVICE',9),
       (7.5,'PRICES',9),
       (5.7,'INTERIOR',9),
       (8.4,'AMBIENCE',9),
       (4.9,'FOOD',10),
       (5.2,'SERVICE',10),
       (0,'PRICES',10),
       (8.2,'INTERIOR',10),
       (3.3,'AMBIENCE',10),

       (6.6,'FOOD',11),
       (5.1,'SERVICE',11),
       (5.3,'PRICES',11),
       (5.8,'INTERIOR',11),
       (6.4,'AMBIENCE',11),

       (9.4,'FOOD',12),
       (1.6,'SERVICE',12),
       (2.4,'PRICES',12),
       (2.3,'INTERIOR',12),
       (1.7,'AMBIENCE',12),

       (8.7,'FOOD',13),
       (8.4,'SERVICE',13),
       (7.1,'PRICES',13),
       (7.8,'INTERIOR',13),
       (6.8,'AMBIENCE',13),

       (5.3,'FOOD',14),
       (5.3,'SERVICE',14),
       (3.9,'PRICES',14),
       (7.4,'INTERIOR',14),
       (6.2,'AMBIENCE',14),

       (6.1,'FOOD',15),
       (5.6,'SERVICE',15),
       (6.3,'PRICES',15),
       (4,'INTERIOR',15),
       (5.3,'AMBIENCE',15),

       (10,'FOOD',16),
       (6,'SERVICE',16),
       (6.8,'PRICES',16),
       (9.7,'INTERIOR',16),
       (5,'AMBIENCE',16),
       (9.1,'FOOD',17),
       (5.1,'SERVICE',17),
       (8.2,'PRICES',17),
       (10,'INTERIOR',17),
       (8.2,'AMBIENCE',17),
       (8.8,'FOOD',18),
       (8.3,'SERVICE',18),
       (7.7,'PRICES',18),
       (7.8,'INTERIOR',18),
       (6.5,'AMBIENCE',18),

       (10,'FOOD',19),
       (10,'SERVICE',19),
       (10,'PRICES',19),
       (10,'INTERIOR',19),
       (10,'AMBIENCE',19),
       (9.4,'FOOD',20),
       (10,'SERVICE',20),
       (6.3,'PRICES',20),
       (7.8,'INTERIOR',20),
       (9.3,'AMBIENCE',20),

       (6.9,'FOOD',21),
       (6.2,'SERVICE',21),
       (4.2,'PRICES',21),
       (4.8,'INTERIOR',21),
       (9,'AMBIENCE',21),
       (0,'FOOD',22),
       (0,'SERVICE',22),
       (0,'PRICES',22),
       (0,'INTERIOR',22),
       (0,'AMBIENCE',22),
       (9.3,'FOOD',23),
       (7.9,'SERVICE',23),
       (6.6,'PRICES',23),
       (6.9,'INTERIOR',23),
       (6.4,'AMBIENCE',23),
       (4.4,'FOOD',24),
       (4.9,'SERVICE',24),
       (7.1,'PRICES',24),
       (3.4,'INTERIOR',24),
       (3.5,'AMBIENCE',24);

по заданию нужно получить запрос именно как на этом фото

Answer 1

[Slava Rozhnev]

SELECT 
	name,
	ROUND(AVG(establishment_avg_rate), 2) avg_rate
FROM (
  SELECT 
	  chains.name,
	  ROUND(AVG(rate), 2) establishment_avg_rate
  FROM Establishment
  JOIN Review ON Review.establishment_id = Establishment.id
  JOIN Rate ON Rate.review_id = Review.id
  JOIN Establishment chains ON chains.id = COALESCE(Establishment.chain_id, Establishment.id)
  GROUP BY chains.name, Establishment.name
) establishment_avg
GROUP BY name
ORDER BY avg_rate DESC;

https://sqlize.online/sql/mysql57/d98f1cbb0bb793af...

Comments

[evgeniy1982]

Огромная благодарность вам.

[Slava Rozhnev]

Donations are welcome on sqlize.online :)

[evgeniy1982]

OK I understud

[evgeniy1982]

Slava Rozhnev, Здравствуйте, как и обещал я пожертвовал на чашечку кофе. Но я вынужден к вам обратиться еще раз. Starbucks Los Angeles2 имеет значение null а для получения среднеарифметического значения по филиалам его нужно перевести из null в 0. Я так понимаю нужно использовать метод COALESCE (rate,0), или другой запрос?

[Slava Rozhnev]

evgeniy1982, Да следует использовать COALESCE и кроме этого LEFT JOIN иначе Starbucks Los Angeles2 не будет включен в подсчёт

https://sqlize.online/sql/mysql57/ecba6cfd60907259...

[evgeniy1982]

Slava Rozhnev, Да так и вправду подсчитывает. Я значит делал правильный запрос только не делал LEFT JOIN поэтому и была ошибка. Но только не пойму почему в конечном результате все выводится правильно кроме Starbucks? Должно быть Starbucks 4.73 , а сейчас выдает Starbucks 3.94.

[Slava Rozhnev]

You right, it's followed by LEFT JOIN. Need to add one WHERE condition (not is_chain) , Sorry

https://sqlize.online/sql/mysql57/3b274547b2fa5172...

[evgeniy1982]

Slava Rozhnev, Огромнейшее спасибо. С меня еще донаты обещаю. Хорошо что есть такие люди как вы которые всегда могут помочь.