#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