Блог

Это запрос, и я пытаюсь написать его лучше.

Рассчитайте среднее количество языков в каждой стране региона.

 CREATE TABLE region3 AS SELECT regions.name, count(country_languages.country_id) FROM regions RIGHT OUTER JOIN countries on countries.region_id = regions.region_id RIGHT OUTER JOIN country_languages on countries.country_id = country_languages.country_id GROUP BY regions.name;  CREATE TABLE region2 AS SELECT regions.name, count(countries.country_id) FROM regions RIGHT OUTER JOIN countries on countries.region_id = regions.region_id GROUP BY regions.name;  SELECT region2.name, region2.count as total_countries, region3.count as langs from region2 LEFT OUTER JOIN region3 on region2.name = region3.name; SELECT name, ROUND(langs::decimal/total_countries, 1) as avg_lang_count_per_country from regions_new ORDER BY avg_lang_count_per_country DESC;  

Вот как это должно выглядеть.

Я думаю, это так же просто, как:

 SELECT regions.name, AVG(country_language_count) AS average_country_language_count FROM regions JOIN (  SELECT countries.region_id, COUNT(*) AS country_language_count  FROM countries  JOIN country_languages on countries.country_id = country_languages.country_id  GROUP BY countries.country_id, countries.region_id ) AS subquery1 ON regions.region_id = subquery1.region_id GROUP BY regions.name