Внешнее объединение всей таблицы дат с агрегатом в бизнес-таблице
Я пытаюсь создать запись для каждого человека и года, где может быть год, когда этого человека не существует. Раньше я использовал перекрестное соединение, но думаю, что агрегат здесь может мешать предыдущему подходу.
Целью является одна запись на комбинацию «человек/год» за все годы 2021–2024 и подсчет этих записей, когда человек существует. Любая помощь приветствуется!
with person as
(
select 2021 as cov_year, 'A' as person
union all
select 2021, 'A'
union all
select 2022, 'A'
union all
select 2024, 'A'
union all
select 2024, 'A'
union all
select 2024, 'A'
),
all_years as
(
select 2021 as year
union all
select 2022 as year
union all
select 2023 as year
union all
select 2024 as year
)
select
y.year, p.person, count(p.person) as ct
from
all_years y
left join
person p on y.year = p.cov_year
group by
y.year, p.person
order by
y.year;
Желаемый результат:
year person ct
------------------
2021 A 2
2022 A 1
2023 A (0 or null)
2024 A 3
Спасибо!
я настраиваю для вас базу данных fillde dbfiddle.uk/jQEDbbbW
@PowerMouse, спасибо, но я полагаю, что значение «A» в моем образце данных неясно. Буквальное значение «А» не работает. Существуют миллионы записей, поэтому представьте, что существуют записи о людях B, C, D и т. д.
Ответы 1
Если вам нужны строки результатов для всех людей за все годы, вам нужно использовать cross join между distinct person и year:
with person as
(
select 2021 as cov_year, 'A' as person
union all
select 2021, 'A'
union all
select 2022, 'A'
union all
select 2024, 'A'
union all
select 2024, 'A'
union all
select 2024, 'A'
),
all_persons as
(
select distinct person from person
),
all_years as
(
select 2021 as year
union all
select 2022 as year
union all
select 2023 as year
union all
select 2024 as year
)
select
y.year, ap.person, count(p.person) as ct
from
all_years y
cross join
all_persons ap
left join
person p on p.person = ap.person and y.year = p.cov_year
group by
y.year, ap.person
order by
y.year;
Я действительно надеялся избежать отличий, но это определенно работает. В реальной таблице person содержатся десятки миллионов записей. Но я посмотрю, как оно вырвется. Спасибо @HABO.
Ну, это меняет вопрос. Индекс на person все еще может помочь с distinct, поскольку сканирование индекса может быть быстрее, чем сканирование таблицы. Для действительно быстрого решения см. этот ответ, в котором view используется с index для сохранения значений distinct.
в строке 3 желаемого не будет человека A - значение будет равно нулю. вы можете сделать COALESCE(p.Person, 'A') или сделать случай, когда count(P.Person) = 0 - установите A