вычислить среднее значение за квартал

#sql #sql-server

#sql #sql-сервер

Вопрос:

У меня есть данные за разные годы, как показано ниже:

 yearmonth value1 
---------  ------
2016/01     33
2016/02     22
2016/03     22
2016/04     22
2016/05     22
2016/06     25
2016/07     44
2016/08     44
2016/09     44
2016/10     66
2016/11     44
2016/12     34
  

Я хочу вычислить среднее значение value1 за квартал.Кварталы должны быть
рассмотрено как показано ниже:
Quarter 1 = month 01 till month 03
Quarter 2 = month 01 till month 06
Quarter 3 = month 01 till month 09
Quarter 4 = month 01 till month 12

Я пытался использовать over, но он не извлекает правильные данные.

Комментарии:

1. квартал должен быть от 4 до 6, верно?

2. Пожалуйста, предоставьте образцы данных для работы

3. Нет сараваната. Это должно быть с января по июнь для второго квартала и так далее. Примерные данные приведены в вопросе выше.

Ответ №1:

В SQL server 2008 :

Я сделал это за 1 квартал, вы можете попробовать аналогичный метод :

 create table calculation
(
 yearmonth varchar(200),
 value1 float
 );

  insert into calculation values('2016/01',33);
  insert into calculation values('2016/02',22);
  insert into calculation values('2016/03',22);
  insert into calculation values('2016/04',22);
  insert into calculation values('2016/05',22);
  insert into calculation values('2016/06',25);

  select
  avg(case when yearmonth between '2016/01' and '2016/03' then value1 end) as qtr1
  from calculation
  

Примечание:
Сохраните дату в надлежащем формате

Комментарии:

1. Поскольку я новичок в sql, я не понимаю вашей логики использования «между». Можете ли вы показать мне, как это можно рассчитать для других кварталов за все годы

2. Значение от 1 до 3 означает, что оно примет значение (1,2,3)

Ответ №2:

 drop table tab1;
create table tab1 (
    yearmonth char(7),
    val number
);
insert into tab1 values('2016/02', 12);
insert into tab1 values('2017/09', 10);
insert into tab1 values('2018/11', 8);
insert into tab1 values('2013/07', 6);
insert into tab1 values('2014/12', 5);
insert into tab1 values('2016/08', 25);
insert into tab1 values('2013/06', 6);
insert into tab1 values('2013/04', 6);


--version 1
select q, round(avg(val),4) as avg_val
from (
    select a.*,
    to_char(to_date(yearmonth, 'yyyy/MM'),'Q') as Q -- convert string to date and get Q
    from tab1 a
)
group by q
order by q

-- version 2
select 
avg(case when q <= 1 then val end) as q1_cum,
avg(case when q <= 2 then val end) as q2_cum,
avg(case when q <= 3 then val end) as q3_cum,
avg(case when q <= 4 then val end) as q4_cum
from (
    select a.*,
    to_number(to_char(to_date(yearmonth, 'yyyy/MM'),'Q')) as q -- convert string to date and get Q
    from tab1 a
) a
  

Комментарии:

1. Спасибо, Томаш. Но ваш запрос версии 2 выдает ошибку, поскольку «Числовое значение ‘Q’ не распознано». Почему это так? Любая помощь будет оценена

Ответ №3:

Попробуйте добавить день в свой столбец, yearmonth чтобы иметь возможность использовать DATEPART функцию. И тогда у вас будет возможность получить среднее значение VALUE по PARTITIONs кварталу:

 SELECT 
  q.yearmonth
, q.QuarterPart
, AVG(q.val)  OVER (PARTITION BY q.QuarterPart ORDER BY q.QuarterPart) AvgByQuarter
FROM 
(
SELECT 
  t.yearmonth
, t.val
, DATEPART(QUARTER, concat(t.yearmonth, '/01')) QuarterPart
FROM @table t
)q
  

И примеры данных:

 DECLARE @table TABLE 
(
    yearmonth VARCHAR(10),
    val int
);
insert into @table values('2016/01', 33);
insert into @table values('2016/02', 22);
insert into @table values('2016/03', 22);
insert into @table values('2016/04', 22);
insert into @table values('2016/05', 22);
insert into @table values('2016/06', 25);
insert into @table values('2016/07', 44);
insert into @table values('2016/08', 44);
insert into @table values('2016/09', 44);
insert into @table values('2016/10', 66);
insert into @table values('2016/11', 44);
insert into @table values('2016/12', 34);
  

ВЫВОД:

 yearmonth   val QuarterPart AvgByQuarter
2016/01     33      1           25
2016/02     22      1           25
2016/03     22      1           25
2016/04     22      2           23
2016/05     22      2           23
2016/06     25      2           23
2016/07     44      3           44
2016/08     44      3           44
2016/09     44      3           44
2016/10     66      4           48
2016/11     44      4           48
2016/12     34      4           48
  

Ответ №4:

Я прилагаю версию SQL-Server — предыдущая была Oracle : 🙂

 create table tab1 (
    yearmonth varchar(7),
    val int
);
insert into tab1 values('2016/02', 12);
insert into tab1 values('2017/09', 10);
insert into tab1 values('2018/11', 8);
insert into tab1 values('2013/07', 6);
insert into tab1 values('2014/12', 5);
insert into tab1 values('2016/08', 25);
insert into tab1 values('2013/06', 6);
insert into tab1 values('2013/04', 6);

select 
avg(case when b.q <= 1 then val end) as q1_cum,
avg(case when b.q <= 2 then val end) as q2_cum,
avg(case when b.q <= 3 then val end) as q3_cum,
avg(case when b.q <= 4 then val end) as q4_cum
from (
    select a.*, 
    datepart(quarter,convert(date,(yearmonth   '/01'),120)) as q
    from tab1 a
) b
  

Ответ №5:

Забавно, вам нужны совокупные кварталы. Это немного усложняет задачу. В любом случае, я бы не стал использовать арифметику даты для этого, потому что исходные данные не являются датами.

 select q, avg(value1)
from t cross apply
     (values ('q1'),
             (case when right(yearmonth, 2) > '03' then 'q2'  end),
             (case when right(yearmonth, 2) > '06' then 'q3' end),
             (case when right(yearmonth, 2) > '09' then 'q4' end)
     ) q
where q is not null
group by q;
  

На самом деле, я бы также включил год, left(yearmonth, 4) как в select , так и group by в.

Если вам не нужны кумулятивные результаты, то:

 select left(yearmonth, 4) as yyyy, q, 
       avg(value1)
from t cross apply
     (values (case when right(yearmonth, 2) <= '03' then 'q1'
                   when right(yearmonth, 2) <= '06' then 'q2'
                   when right(yearmonth, 2) <= '09' then 'q3'
                   else 'q4'
              end)
     ) q
where q is not null
group by eft(yearmonth, 4), q;
  

Вы также могли бы получить кумулятивное среднее значение:

 select left(yearmonth, 4) as yyyy, q, 
       avg(value1),
       (sum(sum(value1)) over (partition by left(yearmonth, 4) order by q) /
        sum(count(*)) over (partition by left(yearmonth, 4) order by q)
       ) as cumulative_average