Multi Join Tsql Coalesce Неэффективный код
Нужна помощь, чтобы сделать код более эффективным.
У меня есть следующий выбор (который станет оператором обновления), и сейчас мне нужно 8 соединений с одинаковыми данными. Я попробовал несколько вещей и потерпел неудачу, но, возможно, коллективная мудрость здесь поможет понять, как сделать ее более эффективной.
У меня есть следующие таблицы
#temp_data
customer season id percent
1 2016 835 35.93000
1 2016 836 31.82000
1 2016 837 11.88000
1 2016 841 5.09000
1 2016 839 15.28000
1 2017 836 31.40000
1 2017 837 11.45000
1 2017 839 14.04000
1 2017 841 6.40000
1 2017 835 36.70000
а также
lt_program_data
customer season value_835 value_836 value_837 value_838 value_839 value_840 value_841 value_842
1 2016 35.93000 31.82000 11.88000 NULL 15.28000 NULL 5.09000 NULL
1 2017 36.70000 31.40000 11.45000 NULL 14.04000 NULL 6.40000 NULL
1 2018 NULL NULL NULL NULL NULL NULL NULL NULL
1 2019 NULL NULL NULL NULL NULL NULL NULL NULL
Происходит то, что мы сопоставляем две таблицы значений сезона / клиента / идентификатора. В сценариях, где в таблице #temp_data нет значений, мы используем максимальное значение, которое существует. Я смог разобраться с кодом, чтобы он заработал. Тем не менее, его большой и некрасивый и не очень эффективный.
У меня либо должно быть 8 операторов обновления, где я явно указываю каждое значение идентификатора (835, 836 и т. Д.). Или одно большое утверждение с 8 объединениями практически о том же. Любой совет о том, как обновить ниже для повышения эффективности было бы здорово.
Как вы можете видеть ниже, это действительно повторение одной и той же вещи снова и снова. Хотя я понимаю, что для выбранной части могут потребоваться 8 отдельных операторов, есть ли способ исключить 8 левых внешних объединений, которые должны быть только одним.
Код:
select a.customer,
fyear,
value_835 = coalesce (( select [percent] from #temp_data d where d.season = a.fyear and d.customer = a.customer and d.id = 835),
c1.[percent] ),
value_836 = coalesce (( select [percent] from #temp_data d where d.season = a.fyear and d.customer = a.customer and d.id = 836),
c2.[percent] ),
value_837 = coalesce (( select [percent] from #temp_data d where d.season = a.fyear and d.customer = a.customer and d.id = 837),
c3.[percent] ),
value_838 = coalesce (( select [percent] from #temp_data d where d.season = a.fyear and d.customer = a.customer and d.id = 838),
c4.[percent] ),
value_839 = coalesce (( select [percent] from #temp_data d where d.season = a.fyear and d.customer = a.customer and d.id = 839),
c5.[percent] ),
value_840 = coalesce (( select [percent] from #temp_data d where d.season = a.fyear and d.customer = a.customer and d.id = 840),
c6.[percent] ),
value_841 = coalesce (( select [percent] from #temp_data d where d.season = a.fyear and d.customer = a.customer and d.id = 841),
c7.[percent] ),
value_842 = coalesce (( select [percent] from #temp_data d where d.season = a.fyear and d.customer = a.customer and d.id = 842),
c8.[percent] )
from #lt_program_data a
left outer join ( --835
select a.customer, a.id, a.[percent]
from #temp_data a
join ( select z.customer, id, season = max(z.season)
from #temp_data z
where id = 835
group by z.customer, id
) b on a.customer = b.customer and a.season = b.season and a.id = b.id
) c1 on a.customer = c1.customer
left outer join ( --836
select a.customer, a.id, a.[percent]
from #temp_data a
join ( select z.customer, id, season = max(z.season)
from #temp_data z
where id = 836
group by z.customer, id
) b on a.customer = b.customer and a.season = b.season and a.id = b.id
) c2 on a.customer = c2.customer
left outer join ( --837
select a.customer, a.id, a.[percent]
from #temp_data a
join ( select z.customer, id, season = max(z.season)
from #temp_data z
where id = 837
group by z.customer, id
) b on a.customer = b.customer and a.season = b.season and a.id = b.id
) c3 on a.customer = c3.customer
left outer join ( --838
select a.customer, a.id, a.[percent]
from #temp_data a
join ( select z.customer, id, season = max(z.season)
from #temp_data z
where id = 838
group by z.customer, id
) b on a.customer = b.customer and a.season = b.season and a.id = b.id
) c4 on a.customer = c4.customer
left outer join ( --839
select a.customer, a.id, a.[percent]
from #temp_data a
join ( select z.customer, id, season = max(z.season)
from #temp_data z
where id = 839
group by z.customer, id
) b on a.customer = b.customer and a.season = b.season and a.id = b.id
) c5 on a.customer = c5.customer
left outer join ( --840
select a.customer, a.id, a.[percent]
from #temp_data a
join ( select z.customer, id, season = max(z.season)
from #temp_data z
where id = 840
group by z.customer, id
) b on a.customer = b.customer and a.season = b.season and a.id = b.id
) c6 on a.customer = c6.customer
left outer join ( --841
select a.customer, a.id, a.[percent]
from #temp_data a
join ( select z.customer, id, season = max(z.season)
from #temp_data z
where id = 841
group by z.customer, id
) b on a.customer = b.customer and a.season = b.season and a.id = b.id
) c7 on a.customer = c7.customer
left outer join ( --842
select a.customer, a.id, a.[percent]
from #temp_data a
join ( select z.customer, id, season = max(z.season)
from #temp_data z
where id = 842
group by z.customer, id
) b on a.customer = b.customer and a.season = b.season and a.id = b.id
) c8 on a.customer = c8.customer
Это пример вывода в конце.
customer fyear value_835 value_836 value_837 value_838 value_839 value_840 value_841 value_842
1 2016 35.93000 31.82000 11.88000 NULL 15.28000 NULL 5.09000 NULL
1 2017 36.70000 31.40000 11.45000 NULL 14.04000 NULL 6.40000 NULL
1 2018 36.70000 31.40000 11.45000 NULL 14.04000 NULL 6.40000 NULL
1 2019 36.70000 31.40000 11.45000 NULL 14.04000 NULL 6.40000 NULL
1 ответ
Я обнаружил, что упаковка моих логических блоков запросов в CTE помогает мне более четко об этом думать. Вот тот, который pivots #temp_data вместо unpivot -ing lt_program_data. Если я вас правильно понял, вы сможете left outer joincross apply с LatestData, чтобы получить то, что вам нужно.
;with LatestData as (
select Customer, Season, [835],[836],[837],[838],[839],[840],[841],[842] from
(select Customer, Season, [percent], id from temp_data td) as TempSource
pivot
( sum([percent]) for id in ([835],[836],[837],[838],[839],[840],[841],[842])) as TempPivoted
)
select a.customer,
a.season,
value_835 = coalesce(a.value_835, latest.[835]),
value_836 = coalesce(a.value_836, latest.[836]),
value_837 = coalesce(a.value_837, latest.[837]),
value_838 = coalesce(a.value_838, latest.[838]),
value_839 = coalesce(a.value_839, latest.[839]),
value_840 = coalesce(a.value_840, latest.[840]),
value_841 = coalesce(a.value_841, latest.[841]),
value_842 = coalesce(a.value_842, latest.[842])
from lt_program_data a
cross apply (select top 1 * from LatestData where LatestData.customer = a.customer and latestdata.season <= a.season order by season desc) as latest