Блог

Я создал простую скрипку SQL, чтобы попытаться легче проиллюстрировать мою проблему.

Учитывая следующую структуру базы данных:

 create table cities (id int, name varchar(100), state varchar(2), population int);
create table citizens (id int, name varchar(100), age int, cityId int);

insert into cities values (1, 'Dallas', 'TX', 100);
insert into cities values (2, 'Houston', 'TX', 200);

insert into citizens values (1, 'John', 20, 1); --Dallas
insert into citizens values (2, 'Peter', 30, 1); --Dallas
insert into citizens values (3, 'Paul', 40, 2); --Houston
insert into citizens values (4, 'Steven', 50, 2); --Houston
 

Если я хочу выбрать сумму всех жителей городов Техаса и средний возраст его граждан, я бы сначала сделал что-то вроде:

 select sum(cities.population), avg(citizens.age)
from cities
inner join citizens on citizens.cityId = cities.id
where cities.state = 'TX'
 

Он возвращает 600 и 35 . Средний возраст указан правильно, но сумма населения — нет. Это потому, что для каждого города он возвращает две строки, по одной для каждого гражданина, что в конечном итоге удваивает правильный результат, который был бы 300 .

Я придумал два разных решения этой проблемы, и я хочу знать «лучшее» из них или более рекомендуемое (то есть то, которое работает лучше) — или даже другое решение, если кто-нибудь может мне его предложить.

решение # 1

 select sum(cities.population), avg(citizens.age)
from cities
inner join (select cityId, avg(age) as age from citizens group by cityId) as citizens 
on citizens.cityId = cities.id
where cities.state = 'TX'
 

решение # 2

 with temp as
(select cityId, avg(age) as age from citizens group by cityId)

select sum(cities.population), avg(temp.age)
from cities
inner join temp on temp.cityId = cities.id
where cities.state = 'TX'
 

Мне кажется (и я могу в этом совершенно ошибаться), что с with помощью предложения он сначала выбирает всю citizens таблицу, создавая временную таблицу с результатом, а затем выполняет основной выбор (выше), соединяясь с этой временной таблицей. Но я не уверен inner join (select...) в решении # 1.

Примечание: это для Microsoft SQL Server 2019

Первый метод близок, но не дает правильных результатов — среднее значение средних значений не является общим средним значением. Вместо этого используйте sum() и count() и вручную вычислите среднее значение.

Вторая проблема заключается в том, что города без граждан (если таковые имеются) будут отфильтрованы. Итак, я бы предложил:

 select sum(c.population), sum(cz.sum_age) / sum(cz.cnt)
from cities c left join
     (select cityId, sum(age) as sum_age, count(*) as cnt
      from citizens cz
      group by cz.cityId
     ) cz 
     on cz.cityId = c.id
where c.state = 'TX';
 

В дополнение к тому, что говорит Гордон, CTE в WITH блоке решительно не действует как временная таблица. Это точно так же, как поместить его непосредственно в запрос в качестве производной таблицы.

Вы можете убедиться в этом, сравнив планы запросов. Компилятор не видит CTE как что-либо иное, кроме другого способа размещения производной таблицы / подзапроса, и оптимизирует его любым возможным способом. Вы вряд ли увидите точную настройку CTE в буфере таблицы в плане запроса.

Существует еще одна конструкция, которую вы не использовали: an APPLY :

 select sum(c.population), sum(cz.sum_age) / sum(cz.cnt)
from cities c
outer apply    -- or cross apply
     (select cityId, sum(age) as sum_age, count(*) as cnt
      from citizens cz
     where cz.cityId = c.id
      group by ()
     ) cz 
where c.state = 'TX';
 

Сформулированное таким образом, суммирование концептуально выполняется по одному запросу citizens таблицы.

Однако компилятор обычно может преобразовать формы join и apply друг в друга и выберет лучшую, поэтому обычно это не имеет значения.

Я придумал два разных решения этой проблемы, и я хочу знать «лучшее» из них или более рекомендуемое (то есть то, которое работает лучше) — или даже другое решение, если кто-нибудь может мне его предложить.

На моем месте я бы выполнил два отдельных запроса, потому что вы в основном пытаетесь вычислить два разных агрегата из двух разных областей — city и citizen. С таким же успехом может быть два запроса, их намного легче читать.

 select sum(cities.population)
from cities
where cities.state = 'TX'

select avg(citizens.age)
from cities
inner join citizens on citizens.cityId = cities.id
where cities.state = 'TX'