#sql-server
Вопрос:
Я пишу SQL-запрос для добавления суммы значений столбцов в новую строку, т. Е. Новая строка будет содержать сумму всех вышеперечисленных значений.
Кроме того, новая строка должна иметь название ALL COUNTRY
на рынке. А также, я не могу использовать UNION
здесь, так как мне нужно добавить Market
здесь group by
заявление, и использование рынка здесь приводит к неправильным расчетам.
Как я могу выполнить эту задачу?
Таблица образцов:
Основной год выпуска | Месяц | Категория | Торговая площадка | ПодкАтегория | Сегмент | Подсегмент | Big_C | Small_C | IB_Type | Period_Type | Geography_Type | Target_Group | TV_Spends | Print_Spends | Radio_Spends | Cinema_Spends | Mobile_Spends | Youtube_Spends | OTT_Spends | Facebook_Spends | OOH_Spends | Others_Spends | Digital_Spends | Total_Spends |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Демонстрация1 | 2021 | ФЕВРАЛЬ | Чай | LOC1 | Чай | Упакованный Чай | Упакованный Чай | Еда и Напитки | Tea | Feb-21 | Cluster | 3751200.65 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
Демонстрация1 | 2021 | ФЕВРАЛЬ | Чай | LOC1 | Чай | Чай | Чай | Еда и Напитки | Tea | Feb-21 | Cluster | 3751200.65 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
Демонстрация1 | 2021 | ФЕВРАЛЬ | Чай | LOC10 | Чай | Упакованный Чай | Упакованный Чай | Еда и Напитки | Tea | Feb-21 | Cluster | 9440964.73 | 0 | 0 | 0 | 0 | 90254.14 | 0 | 0 | 0 | 0 | 90254.14 | ||
Демонстрация1 | 2021 | ФЕВРАЛЬ | Чай | LOC10 | Чай | Чай | Чай | Еда и Напитки | Tea | Feb-21 | Cluster | 9440964.73 | 0 | 0 | 0 | 0 | 90254.14 | 0 | 0 | 0 | 0 | 90254.14 | ||
Демонстрация1 | 2021 | ФЕВРАЛЬ | Чай | LOC11 | Чай | Упакованный Чай | Упакованный Чай | Еда и Напитки | Tea | Feb-21 | Cluster | 1342.66 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
Демонстрация1 | 2021 | ФЕВРАЛЬ | Чай | LOC11 | Чай | Чай | Чай | Еда и Напитки | Tea | Feb-21 | Cluster | 1342.66 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Таблица ожидаемых результатов (просто проверьте последние 2 строки, это сумма всех вышеуказанных расходов)
Основной год выпуска | Месяц | Категория | Торговая площадка | ПодкАтегория | Сегмент | Подсегмент | Big_C | Small_C | IB_Type | Period_Type | Geography_Type | Target_Group | TV_Spends | Print_Spends | Radio_Spends | Cinema_Spends | Mobile_Spends | Youtube_Spends | OTT_Spends | Facebook_Spends | OOH_Spends | Others_Spends | Digital_Spends | Total_Spends |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Демонстрация1 | 2021 | ФЕВРАЛЬ | Чай | LOC1 | Чай | Упакованный Чай | Упакованный Чай | Еда и Напитки | Tea | Feb-21 | Cluster | 3751200.65 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
Демонстрация1 | 2021 | ФЕВРАЛЬ | Чай | LOC1 | Чай | Чай | Чай | Еда и Напитки | Tea | Feb-21 | Cluster | 3751200.65 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
Демонстрация1 | 2021 | ФЕВРАЛЬ | Чай | LOC10 | Чай | Упакованный Чай | Упакованный Чай | Еда и Напитки | Tea | Feb-21 | Cluster | 9440964.73 | 0 | 0 | 0 | 0 | 90254.14 | 0 | 0 | 0 | 0 | 90254.14 | ||
Демонстрация1 | 2021 | ФЕВРАЛЬ | Чай | LOC10 | Чай | Чай | Чай | Еда и Напитки | Tea | Feb-21 | Cluster | 9440964.73 | 0 | 0 | 0 | 0 | 90254.14 | 0 | 0 | 0 | 0 | 90254.14 | ||
Демонстрация1 | 2021 | ФЕВРАЛЬ | Чай | LOC11 | Чай | Упакованный Чай | Упакованный Чай | Еда и Напитки | Tea | Feb-21 | Cluster | 1342.66 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
Демонстрация1 | 2021 | ФЕВРАЛЬ | Чай | LOC11 | Чай | Чай | Чай | Еда и Напитки | Tea | Feb-21 | Cluster | 1342.66 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||
Демонстрация1 | 2021 | ФЕВРАЛЬ | Чай | ПО ВСЕЙ СТРАНЕ | Чай | Чай | Чай | Еда и Напитки | Tea | Feb-21 | Cluster | 34638332.66 | 0 | 0 | 0 | 0 | 180508.28 | 0 | 0 | 0 | 0 | 180508.28 | 34818840.94 | |
Демонстрация1 | 2021 | ФЕВРАЛЬ | Чай | ПО ВСЕЙ СТРАНЕ | Чай | Упакованный Чай | Упакованный Чай | Еда и Напитки | Tea | Feb-21 | Cluster | 34638332.66 | 0 | 0 | 0 | 0 | 180508.28 | 0 | 0 | 0 | 0 | 180508.28 | 34818840.94 |
Весь запрос целиком:
IF OBJECT_ID('spend_v1') IS NOT NULL
DROP TABLE spend_v1;
select distinct A.Primary_Brand_Key, A.Year, A.Month, A.Category, Medium ,A.Market,b.Sub_Category, b.Segment as Segment, b.Sub_Segment as Sub_Segment, b.Big_C, b.Small_C,
case IB_TYPE when 'CWBS' then 'FATMAN' end as IB_Type,
concat(Month,' ',Year) as Period_Type,
Geography_Type,
A.LSM as Target_Group
into spend_v1
from table1 a
left join
table2 b
on a.Category = b.Small_C and a.Primary_Brand_Key = b.PBRT_KEY
group by A.Primary_Brand_Key, A.Year, A.Month, A.Category, Medium ,A.Market,b.Sub_Category, b.Segment , b.Sub_Segment, b.Big_C, b.Small_C,
IB_TYPE, LSM
--select * from spend_v1
--Pulling spend summary as expected in the spend output excel file
IF OBJECT_ID('spend_v2') IS NOT NULL
DROP TABLE spend_v2;
select distinct Primary_Brand_Key,year, month, category, Market,
sum(distinct case when (Medium in ('TV') and Market is not NULL ) then Amount_Spent_INR else 0 end) as TV_Spends,
sum(case when (Medium in ('Print') and Market is not NULL ) then Amount_Spent_INR else 0 end) as Print_Spends,
sum(case when (Medium in ('Radio') and Market is not NULL ) then Amount_Spent_INR else 0 end) as Radio_Spends,
sum(case when (Medium in ('Cinema') and Market is not NULL ) then Amount_Spent_INR else 0 end) as Cinema_Spends,
sum(case when (Medium in ('Mobile') and Market is not NULL ) then Amount_Spent_INR else 0 end) as Mobile_Spends,
sum(case when (Medium in ('YT') and Market is not NULL ) then Amount_Spent_INR else 0 end) as YouTube_Spends,
sum(case when (Medium in ('OTT') and Market is not NULL ) then Amount_Spent_INR else 0 end) as OTT_Spends,
sum(case when (Medium in ('FB') and Market is not NULL ) then Amount_Spent_INR else 0 end) as Facebook_Spends,
sum(case when (Medium in ('OOH') and Market is not NULL ) then Amount_Spent_INR else 0 end) as OOH_Spends,
sum(case when (Medium in ('Others') and Market is not NULL ) then Amount_Spent_INR else 0 end) as Others_Spends,
sum(case when (Medium in ('YT', 'OTT', 'FB') and Market is not NULL ) then Amount_Spent_INR else 0 end) as Digital_Spends,
sum(Amount_Spent_INR) as Total_Spends
into spend_v2
from table1
group by year, month, category, Primary_Brand_Key, Market
order by Primary_Brand_Key, Market;
--select * from spend_v2
--order by Primary_Brand_Key, Market
IF OBJECT_ID('spend_v3') IS NOT NULL
DROP TABLE spend_v3;
select distinct A.Primary_Brand_Key as Primary_Brand , A.Year, A.Month, A.Category, IsNull(A.Market, '') as Market, IsNull(A.Sub_Category, '')as Sub_Category, IsNull(A.Segment, '') as Segment,
IsNull(A.Sub_Segment, '') as Sub_Segment, IsNull(A.Big_C, '') as Big_C, IsNull(A.Small_C, '') as Small_C,
IsNull(A.IB_Type, '') as IB_Type, A.Period_Type, IsNull(A.Geography_Type, '')as Geography_Type, IsNull(A.Target_Group, '') as Target_Group, c.ACD,
B.TV_Spends,
B.Print_Spends,
B.Radio_Spends,
B.Cinema_Spends,
B.Mobile_Spends,
B.Youtube_Spends,
B.OTT_Spends,
B.Facebook_Spends,
B.OOH_Spends,
B.Others_Spends,
B.Digital_Spends,
Total_Spends
into spend_v3
from spend_v1 a
inner join spend_v2 b
on a.Primary_Brand_Key=b.Primary_Brand_Key
and a.Market=b.Market
inner join msrs.UL_MEDIA_IN_PBRT_HULACD_MASTER_OUTPUT c
on a.Primary_Brand_Key=c.Primary_Brand_Key
and a.Market=c.Market
--and a.Year=c.year
--and a.Month=c.Month
group by A.Primary_Brand_Key, A.Year, A.Month, A.Category, A.Market, Sub_Category, A.Segment , Sub_Segment, Big_C, Small_C,
IB_Type, Period_Type, Geography_Type, Target_Group,
TV_spends, Print_Spends, Radio_Spends, Cinema_Spends, Mobile_Spends, Youtube_Spends, OTT_Spends, Facebook_Spends, OOH_Spends, Others_Spends,
Digital_Spends, Total_Spends, ACD
UNION ALL
select distinct A.Primary_Brand_Key as Primary_Brand , A.Year, A.Month, A.Category, 'ALL COUNTRY' as Market, IsNull(A.Sub_Category, '')as Sub_Category, IsNull(A.Segment, '') as Segment,
IsNull(A.Sub_Segment, '') as Sub_Segment, IsNull(A.Big_C, '') as Big_C, IsNull(A.Small_C, '') as Small_C,
IsNull(A.IB_Type, '') as IB_Type, A.Period_Type, IsNull(A.Geography_Type, '')as Geography_Type, IsNull(A.Target_Group, '') as Target_Group, c.ACD,
sum(TV_spends) as TV_spends, sum(Print_Spends)as Print_Spends,
sum(Radio_Spends) as Radio_Spends, sum(Cinema_Spends) as Cinema_Spends, sum(Mobile_Spends) as Mobile_Spends, sum(Youtube_Spends) Youtube_Spends,
sum(OTT_Spends) OTT_Spends,
sum(Facebook_Spends) as Facebook_Spends, sum(OOH_Spends) as OOH_Spends, sum(Others_Spends) as Others_Spends,
sum(Digital_Spends) as Digital_Spends,sum(Total_Spends) as Total_Spends
--into spend_v3
from spend_v1 a
inner join spend_v2 b
on a.Primary_Brand_Key=b.Primary_Brand_Key
and a.Market=b.Market
inner join table3 c
on a.Primary_Brand_Key=c.Primary_Brand_Key
and a.Market=c.Market
--and a.Year=c.year
--and a.Month=c.Month
group by A.Primary_Brand_Key, c.ACD, A.Year, A.Month, A.Category, --A.Market,
Sub_Category, A.Segment , Sub_Segment, Big_C, Small_C,
IB_Type, Period_Type, Geography_Type, Target_Group
--TV_spends, Print_Spends, Radio_Spends, Cinema_Spends, Mobile_Spends, Youtube_Spends, OTT_Spends, Facebook_Spends, OOH_Spends, Others_Spends,
--Digital_Spends, Total_Spends
order by A.Primary_Brand_Key, Market
select * from spend_v3
where Primary_Brand='Demo1'
and Market!='ALL COUNTRY'
--and Segment='Tea'
select Primary_Brand, Sum(TV_Spends) from spend_v3
--where Primary_Brand='Demo1' and
where Market!='ALL COUNTRY'
--and Segment='Tea'
group by Primary_Brand
order by Primary_Brand
Комментарии:
1. Трудно сказать, потому что в этих запросах так много всего происходит (несколько, непонятно, что к чему), а также ваши ожидаемые результаты, похоже, не содержат исходных данных. Но
GROUPING SETS
, вероятно, ответ здесь
Ответ №1:
Я добился результата, создав одну временную таблицу специально для ALL COUNTRY
нее и UNION
объединив ее значения с основной таблицей. И установил рынок как ALL COUNTRY