Получение взвешенного среднего в категории с несколькими подкатегориями

Question

Получение взвешенного среднего в категории с несколькими подкатегориями

Джо владеет всемирным магазином проката DVD. У него есть магазины по всему миру в разных регионах. Каждый месяц ему нравится видеть, обновлено ли его оборудование в его магазинах, находя процент от его инвентаря с определенным статусом "Текущий","Требуется обновление" и "Должен идти" для каждого региона. Это помогает гарантировать, что его клиенты имеют лучший опыт в своих магазинах.

Билл, его помощник, хочет создать SQL-запрос, который покажет Джо процент оборудования в каждом регионе и его статус. Парень до него использовал Excel для этого, но Билл думает, что SQL, а затем отчет SSRS, который позволит Джо выбрать регион, который он хочет просмотреть, поразит его. Он хочет, чтобы он динамически переходил от месяцев к кварталам в течение года в отчете. Это то, что Билл смог придумать до сих пор:

with
COUNTS as (
select distinct
count(distinct cnts.[Asset ID]) [Equipment Counts] 
,cnts.[Month]
,cnts.[Equipment Status]
,'Q' + convert(varchar(20),datepart(qq,[Month])) [Quarter]
,year(cnts.[Month]) [Year]
,cnts.[Region]
from TABLEIMPORT cnts
group by cnts.[Month], cnts.[Equipment Status], cnts.[Region]

),

TOT as (
select distinct
count(distinct tot.[Asset ID]) [Equipmnet Total] 
,tot.[Month]
,'Q' + convert(varchar(20),datepart(qq,[Month])) [Quarter] 
,year(tot.[Month]) [Year]
from TABLEIMPORT tot
group by tot.[Month]
 ),

DATA as (
select 
c.[Month]
,c.[Equipment Counts] / cast(t.[Equipment Total]  as decimal (18,2)) [Percents]
,c.[Equipment Status]
,c.Quarter
,c.Year
,c.[Region]
from counts c 
    inner join TOT t on t.[Month] = c.[Month]
   ),

QUARTERS as (
select distinct
d.Quarter [Month]
,avg(d.Percents) [Percents]
,d.[Equipment Status]
,d.Quarter
,d.Year
from data d
group by d.[Equipment Status], d.Quarter, d.Year
  ),

OWN as (
select distinct
o.[Region]
,case 
    when o.[Region] in ('Northeast',
                                'Southeast', 
                                'Midwest', 
                                'Northwest', 
                                'Southwest', 
                                'Mexico', 
                                'Canada',)
       then 'North America'
    when o.[Region] like 'Europe'
       then 'Europe'
    when o.[Region] like 'Africa'
       then 'Africa'
    when o.[Region] like 'Asia'
       then 'Asia'
   when o.[Region] like 'Australia'
       then 'Australia'
   else isnull(o.[Region], 'No Region')
   end [SelectRegion]

   from TABLEIMPORT o

 ),

QTRMNTH as (
select distinct
q.Quarter  [Month]
,q.Percents [Percents]
,d.[Equipment Status]
,d.Quarter
,d.Year
,o.SelectRegion
from data d
     inner join OWN o on o.[Region] = d.[Region]
     inner join QUARTERS q on q.Quarter = d.Quarter and q.year = d.Year and q.[Equipment Status] = d.[Equipment Status]
where d.[Month] < DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, -1) and   d.[Month] > dateadd(MONTH,-12,GETDATE())


UNION

select distinct
convert(varchar(3),datename(month, d.[Month]))  [Month]
,d.Percents [Percents]
,d.[Equipment Status]
,d.Quarter
,d.Year
,o.SelectRegion
from data d
       inner join OWN o on o.[Region] = d.[Region]

where d.[Month] > DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, -1) and d.Year = year(getdate())
)

select *
from QTRMNTH 
--where QTRMNTH.SelectOrg in (@SelectOrg)

Сравнивая его с Excel из предыдущего, все правильно, и его SSRS отлично работает все, за исключением Северной Америки, где процентные доли немного отличаются. Он думает, что средневзвешенное значение для субрегионов в Северной Америке исправит это, чтобы получить правильный процент, потому что некоторые субрегионы больше, чем другие.

Как Билл будет рассчитывать средневзвешенные значения для каждого из регионов Северной Америки?

РЕДАКТИРОВАТЬ:

Разбивая разные регионы

with
OWN as (
select distinct
o.[Region]
,case 
    when o.[Region] in ('Northeast', 'Southeast', 'Midwest', 'Southwest', 
                            'Mexico', 'Canada', )
    then 'North America'
when o.[Region] like 'Europe'
    then 'Europe'
when o.[Region] like 'Africa'
    then 'Africa'
when o.[Region] like 'Asia'
    then 'Asia'
when o.[Region] like 'Australia'
    then 'Australia'
else isnull(o.[Region], 'No Region')
end [SelectRegion]

from TABLEIMPORT o
),

COUNTS as (
select distinct
count(distinct cnts.[Asset ID]) [Equipment Counts] 
,cnts.[Month]
,cnts.[Equipment Status]
,'Q' + convert(varchar(20),datepart(qq,[Month])) [Quarter]
,year(cnts.[Month]) [Year]
,o.[Region]
from TABLEIMPORT cnts
    inner join OWN o on o.[Area] = cnts.[Region]
group by cnts.[Month], cnts.[Equipment Status], o.[Region]

),

COUNTNE as (
select distinct
count(distinct cnts.[Asset ID]) [Equipment Counts] 
,cnts.[Month]
,cnts.[Equipment Status]
,'Q' + convert(varchar(20),datepart(qq,[Month])) [Quarter]
,year(cnts.[Month]) [Year]
,o.[Region]
from TABLEIMPORT cnts
    inner join OWN o on o.[Region] = cnts.[Region]
where o.[Region] like '%Northeast%'
group by cnts.[Month], cnts.[Equipment Status], o.[Region]
),

COUNTSE as (
select distinct
count(distinct cnts.[Asset ID]) [Equipment Counts] 
,cnts.[Month]
,cnts.[Equipment Status]
,'Q' + convert(varchar(20),datepart(qq,[Month])) [Quarter]
,year(cnts.[Month]) [Year]
,o.[Region]
from TABLEIMPORT cnts
    inner join OWN o on o.[Region] = cnts.[Region]
where o.[Region] like '%Southeast%'
group by cnts.[Month], cnts.[Equipment Status], o.[Region]
),

COUNTMW as (
select distinct
count(distinct cnts.[Asset ID]) [Equipment Counts] 
,cnts.[Month]
,cnts.[Equipment Status]
,'Q' + convert(varchar(20),datepart(qq,[Month])) [Quarter]
,year(cnts.[Month]) [Year]
,o.[Region]
from TABLEIMPORT cnts
     inner join OWN o on o.[Region] = cnts.[Region]
where o.[Region] like '%Midwest%'
group by cnts.[Month], cnts.[Equipment Status], o.[Region]
),

COUNSW as (
select distinct
count(distinct cnts.[Asset ID]) [Equipment Counts] 
,cnts.[Month]
,cnts.[Equipment Status]
,'Q' + convert(varchar(20),datepart(qq,[Month])) [Quarter]
,year(cnts.[Month]) [Year]
,o.[Region]
from TABLEIMPORT cnts
    inner join OWN o on o.[Region] = cnts.[Region]
where o.[Region] like '%Southwest%'
group by cnts.[Month], cnts.[Equipment Status], o.[Region]
),

COUNTNw as (
select distinct
count(distinct cnts.[Asset ID]) [Equipment Counts] 
,cnts.[Month]
,cnts.[Equipment Status]
,'Q' + convert(varchar(20),datepart(qq,[Month])) [Quarter]
,year(cnts.[Month]) [Year] 
,o.[Region]
from TABLEIMPORT cnts
     inner join OWN o on o.[Region] = cnts.[Region]
where o.[Region] like '%Northwest%'
group by cnts.[Month], cnts.[Equipment Status], o.[Region]
),

COUNTMX as (
select distinct
count(distinct cnts.[Asset ID]) [Equipment Counts] 
,cnts.[Month]
,cnts.[Equipment Status]
,'Q' + convert(varchar(20),datepart(qq,[Month])) [Quarter]
,year(cnts.[Month]) [Year]
,o.[Region]
from TABLEIMPORT cnts
     inner join OWN o on o.[Region] = cnts.[Region]
where o.[Region] like '%Mexico%'
group by cnts.[Month], cnts.[Equipment Status], o.[Region]
),

COUNTCN as (
select distinct
count(distinct cnts.[Asset ID]) [Equipment Counts] 
,cnts.[Month]
,cnts.[Equipment Status]
,'Q' + convert(varchar(20),datepart(qq,[Month])) [Quarter]
,year(cnts.[Month]) [Year]
,o.[Region]
from TABLEIMPORT cnts
     inner join OWN o on o.[Region] = cnts.[Region]
where o.[Region] like '%Canada%'
group by cnts.[Month], cnts.[Equipment Status], o.[Region]
),

COUNTEU as (
select distinct
count(distinct cnts.[Asset ID]) [Equipment Counts] 
,cnts.[Month]
,cnts.[Equipment Status]
,'Q' + convert(varchar(20),datepart(qq,[Month])) [Quarter]
,year(cnts.[Month]) [Year]
,o.[Region]
from TABLEIMPORT cnts
     inner join OWN o on o.[Region] = cnts.[Region]
where o.SelectRegion like 'Europe'
group by cnts.[Month], cnts.[Equipment Status], o.[Region]
),

COUNTAS as (
select distinct
count(distinct cnts.[Asset ID]) [Equipment Counts] 
,cnts.[Month]
,cnts.[Equipment Status]
,'Q' + convert(varchar(20),datepart(qq,[Month])) [Quarter]
,year(cnts.[Month]) [Year]
,o.[Region]
from TABLEIMPORT cnts
    inner join OWN o on o.[Region] = cnts.[Region]
where o.SelectRegion like 'Asia'
group by cnts.[Month], cnts.[Equipment Status], o.[Region]
),

COUNTAF as (
select distinct
count(distinct cnts.[Asset ID]) [Equipment Counts] 
,cnts.[Month]
,cnts.[Equipment Status]
,'Q' + convert(varchar(20),datepart(qq,[Month])) [Quarter]
,year(cnts.[Month]) [Year]
,o.[Region]
from TABLEIMPORT cnts
      inner join OWN o on o.[Region] = cnts.[Region]
where o.SelectRegion like 'Africa'
group by cnts.[Month], cnts.[Equipment Status], o.[Region]
),

COUNTaus as (
select distinct
count(distinct cnts.[Asset ID]) [Equipment Counts] 
,cnts.[Month]
,cnts.[Equipment Status]
,'Q' + convert(varchar(20),datepart(qq,[Month])) [Quarter]
,year(cnts.[Month]) [Year]
,o.[Region]
from TABLEIMPORT cnts
    inner join OWN o on o.[Region] = cnts.[Region]
where o.SelectRegion like 'Australia'
group by cnts.[Month], cnts.[Equipment Status], o.[Region]
),

TOT as (
select distinct
count(distinct tot.[Asset ID]) [Equipment Total] 
,tot.[Month]
,'Q' + convert(varchar(20),datepart(qq,[Month])) [Quarter] 
,year(tot.[Month]) [Year]
,o.[Region]
from TABLEIMPORT tot
     inner join OWN o on o.[Region] = tot.[Region]
group by tot.[Month], o.[Region]
),

TOTNA as (
select distinct
count(distinct tot.[Asset ID]) [Equipment Total] 
,tot.[Month]
,'Q' + convert(varchar(20),datepart(qq,[Month])) [Quarter] 
,year(tot.[Month]) [Year]
,o.[Region]
from TABLEIMPORT tot
    inner join OWN o on o.[Region] = tot.[Region]
where o.SelectRegion like 'North America'
group by tot.[Month], o.[Region]
 ),

TOTEU as (
select distinct
count(distinct tot.[Asset ID]) [Equipment Total] 
,tot.[Month]
,'Q' + convert(varchar(20),datepart(qq,[Month])) [Quarter] 
,year(tot.[Month]) [Year]
,o.[Region]
from TABLEIMPORT tot
     inner join OWN o on o.[Region] = tot.[Region]
where o.SelectRegion like 'Europe'
group by tot.[Month], o.[Region]
),

TOTAS as (
select distinct
count(distinct tot.[Asset ID]) [Equipment Total] 
,tot.[Month]
,'Q' + convert(varchar(20),datepart(qq,[Month])) [Quarter] 
,year(tot.[Month]) [Year]
,o.[Region]
from TABLEIMPORT tot
     inner join OWN o on o.[Region] = tot.[Region]
where o.SelectRegion like 'Asia'
group by tot.[Month], o.[Region]
 ),

TOTAF as (
select distinct
count(distinct tot.[Asset ID]) [Equipment Total] 
,tot.[Month]
,'Q' + convert(varchar(20),datepart(qq,[Month])) [Quarter] 
,year(tot.[Month]) [Year]
,o.[Region]
from TABLEIMPORT tot
    inner join OWN o on o.[Region] = tot.[Region]
where o.SelectRegion like 'Africa'
group by tot.[Month], o.[Region]
 ),

TOTAUS as (
select distinct
count(distinct tot.[Asset ID]) [Equipment Total] 
,tot.[Month]
,'Q' + convert(varchar(20),datepart(qq,[Month])) [Quarter] 
,year(tot.[Month]) [Year]
,o.[Region]
from TABLEIMPORT tot
     inner join OWN o on o.[Region] = tot.[Region]
where o.SelectRegion like 'Australia'
group by tot.[Month], o.[Region]
),

DATANE as (
select distinct
cne.[Month]
,cne.[Equipment Counts] / cast(T.[Equipment Total]  as decimal (18,2)) [Percents]
,cne.[Equipment Status]
,cne.Quarter
,cne.Year
,cne.[Region]
 from COUNTNE cne
    inner join TOTNA t on t.[Month] = cne.[Month]
 ),

DATASE as (
select distinct
cse.[Month]
,cse.[Equipment Counts] / cast(T.[Equipment Total]  as decimal (18,2)) [Percents]
,cse.[Equipment Status]
,cse.Quarter
,cse.Year
,cse.[Region]
from COUNTSE cse
     inner join TOTNA t on t.[Month] = cse.[Month]
 ),

DATAMW as (
select distinct
cmw.[Month]
,cmw.[Equipment Counts] / cast(T.[Equipment Total]  as decimal (18,2)) [Percents]
,cmw.[Equipment Status]
,cmw.Quarter
,cmw.Year
,cmw.[Region]
from COUNTMW cmw
    inner join TOTNA t on t.[Month] = cmw.[Month]
 ),

DATASW as (
select distinct
csw.[Month]
,csw.[Equipment Counts] / cast(T.[Equipment Total]  as decimal (18,2)) [Percents]
,csw.[Equipment Status]
,csw.Quarter
,csw.Year
,csw.[Region]
from COUNTSW csw
    inner join TOTNA t on t.[Month] = csw.[Month]
),

DATANW as (
select distinct
cnw.[Month]
,cnw.[Equipment Counts] / cast(T.[Equipment Total]  as decimal (18,2)) [Percents]
,cnw.[Equipment Status]
,cnw.Quarter
,cnw.Year
,cnw.[Region]
from COUNTNW cnw
    inner join TOTNA t on t.[Month] = cnw.[Month]
),

DATAMX as (
select distinct
cmx.[Month]
,cmx.[Equipment Counts] / cast(T.[Equipment Total]  as decimal (18,2)) [Percents]
,cmx.[Equipment Status]
,cmx.Quarter
,cmx.Year
,cmx.[Region]
from COUNTMX cmx
    inner join TOTna t on t.[Month] = cmx.[Month]
),

DATACN as (
select distinct
ccn.[Month]
,ccn.[Equipment Counts] / cast(T.[Equipment Total]  as decimal (18,2)) [Percents]
,ccn.[Equipment Status]
,ccn.Quarter
,ccn.Year
,ccn.[Region]
from COUNTCN ccn
    inner join TOTna t on t.[Month] = ccn.[Month]
),

DATAEU as (
select distinct
ceu.[Month]
,ceu.[Equipment Counts] / cast(t.[Equipment Total]  as decimal (18,2)) [Percents]
,ceu.[Equipment Status]
,ceu.Quarter
,ceu.Year
,ceu.[Region]
from COUNTEU ceu
    inner join TOTEU t on t.[Month] = ceu.[Month]
),

DATAAS as (
select distinct
cas.[Month]
,cas.[Equipment Counts] / cast(t.[Equipment Total]  as decimal (18,2)) [Percents]
,cas.[Equipment Status]
,cas.Quarter
,cas.Year
,cas.[Region]
from COUNTAS cas
    inner join TOTAS t on t.[Month] = cas.[Month]
),

DATAAF as (
select distinct
caf.[Month]
,caf.[Equipment Counts] / cast(t.[Equipment Total]  as decimal (18,2)) [Percents]
,caf.[Equipment Status]
,caf.Quarter
,caf.Year
,caf.[Region]
from COUNTAF caf
    inner join TOTAF t on t.[Month] = caf.[Month]
 ),

 DATAAUS as (
 select distinct
 caus.[Month]
 ,caus.[Equipment Counts] / cast(t.[Equipment Total]  as decimal (18,2)) [Percents]
 ,caus.[Equipment Status]
 ,caus.Quarter
 ,caus.Year
 ,caus.[Region]
 from COUNTAUS caus
      inner join TOTAUS t on t.[Month] = caus.[Month]
 ),

DATA as (
select *
from DATANE dne

UNION 

select * 
from DATAse dse

UNION

select * 
from DATAmw dmw

UNION

select *
from DATAsw dsw

UNION

select * 
from DATAnw dnw

UNION 

select * 
from DATAcn dcn

UNION 

select * 
from DATAmx dmx

UNION

select * 
from DATAeu deu

UNION

select *
from DATAas das

UNION

select *
from DATAaf daf

UNION

select * 
from DATAaus daus
),

QUARTERS as (
select distinct 
d.Quarter [Month]
,avg(d.Percents) [Percents]
,d.[Equipment Status]
,d.Quarter
,d.Year
from data d
group by d.[Equipment Status], d.Quarter, d.Year
),

QTRMNTH as (
select distinct
q.Quarter  [Month]
,q.Percents [Percents]
,d.[Equipment Status]
,d.Quarter
,d.Year
,o.SelectRegion
from data d
    inner join OWN o on o.[Region] = d.[Region]
    inner join QUARTERS q on q.Quarter = d.Quarter and q.year = d.Year and q.[Equipment Status] = d.[Equipment Status]
where d.[Month] < DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, -1) and d.[Month] > dateadd(MONTH,-12,GETDATE())

UNION

select distinct
convert(varchar(3),datename(month, d.[Month]))  [Month]
,d.Percents [Percents]
,d.[Equipment Status]
,d.Quarter
,d.Year
,o.SelectRegion
from data d
    inner join OWN o on o.[Region] = d.[Region]

where d.[Month] > DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, -1) and d.Year = year(getdate())
)

select *
from QTRMNTH

0

sql sql-server weighted-average

Источник

user7753835 09 июл '17 в 01:01

1 ответ

Другие вопросы по тегам sql sql-server weighted-average

user6305294 09 июл '17 в 10:35 2017-07-09 10:35 · Answer 1 · 2017-07-09 10:35

Этот запрос немного похож на завтрак с собаками. Основные проблемы:

SELECT DISTINCT не нужен в GROUP BY запросы как значения уже различны. Я могу понять использование DISTINCT в COUNTS а также TOT запросы при условии, что данные в TABLEIMPORT не нормируется.
Общая удача комментариев, объясняющих намерение запроса.
Имя переменной / псевдонима очень плохое.

Я подозреваю, что проблема с первым запросом здесь:

QTRMNTH as (
select distinct
q.Quarter  [Month]
,q.Percents [Percents]
,d.[Equipment Status]
,d.Quarter
,d.Year
,o.SelectRegion
from data d
     inner join OWN o on o.[Region] = d.[Region]
     inner join QUARTERS q on q.Quarter = d.Quarter and q.year = d.Year and q.[Equipment Status] = d.[Equipment Status]
where d.[Month] < DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, -1) and   d.[Month] > dateadd(MONTH,-12,GETDATE())

Я предполагаю, что приведенный выше код должен возвращать квартальные результаты на "больший регион" [SelectRegion] на основе ежемесячных данных (data). Если это так, то вам нужно удалить DISTINCT и добавить GROUP BY,

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