Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Сумма строк  [new]
Etamin419
Member

Откуда: Stk
Сообщений: 30
Добрый день.
Неожиданно застал врасплох запрос, который не могу не как придумать. есть данные получасовок и нужно суммировать так, чтобы 30 минутные значения не отображались в выводе, но данные их учитывались
т.е.
Дата Значения
13:30 110
14:00 220

При сумме должно получится так
Дата Значения
14:00 330
21 фев 13, 07:47    [13958483]     Ответить | Цитировать Сообщить модератору
 Re: Сумма строк  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
Etamin419,
применить соответствующую группировку
21 фев 13, 08:03    [13958493]     Ответить | Цитировать Сообщить модератору
 Re: Сумма строк  [new]
euthanatos
Member

Откуда:
Сообщений: 33
А мне пока видится только решение выполнить это отдельной функцией, которая за первый проход наполнит временную табличную переменную значениями с целыми часами (как '14:00', '15:00', '16:00'), а во второй проход пройдется курсором по получившейся таблице и просуммирует соответствующие ячейки из первой, которые находятся в интервалах второй таблицы.

Хотя вероятно при достаточно развитых мозгах обычный селект решает эту проблему более элегантно
21 фев 13, 08:10    [13958502]     Ответить | Цитировать Сообщить модератору
 Re: Сумма строк  [new]
Добрый Э - Эх
Guest
euthanatos,

то есть проверить время на целый или половинный час и в случае "половины" округлить до ближайшего большего целого часа - не судьба?
21 фев 13, 08:33    [13958541]     Ответить | Цитировать Сообщить модератору
 Re: Сумма строк  [new]
AxuliON
Member

Откуда: оттуда, и на лыжах
Сообщений: 574
HandKot
Etamin419,
применить соответствующую группировку

Это 5+
Тут все просто же
with cte as
(
select '2013-02-21 13:00' as dt, 666 as val
union
select '2013-02-21 13:30' as dt, 111 as val
union
select '2013-02-21 14:00' as dt, 222 as val
union
select '2013-02-21 14:30' as dt, 333 as val
)
select DATEPART(hh, DATEADD(mi, -1, dt)), SUM(val) from cte -- Вычитаем минуту, чтобы час попал в предыдущий отчетный
group by DATEPART(hh, DATEADD(mi, -1, dt))
21 фев 13, 09:00    [13958661]     Ответить | Цитировать Сообщить модератору
 Re: Сумма строк  [new]
AxuliON
Member

Откуда: оттуда, и на лыжах
Сообщений: 574
Etamin419
Добрый день.
Неожиданно застал врасплох запрос, который не могу не как придумать. есть данные получасовок и нужно суммировать так, чтобы 30 минутные значения не отображались в выводе, но данные их учитывались
т.е.
Дата Значения
13:30 110
14:00 220

При сумме должно получится так
Дата Значения
14:00 330

Да не заметил, у Вас час отчетный идет 14:00
Тогда надо будет +1 сделать
Если надо по дате и времени группировать - то разобраться сами должны.
Я лишь путь указал (цэ) "не моё"
with cte as
(
select '2013-02-21 13:00' as dt, 666 as val
union
select '2013-02-21 13:30' as dt, 111 as val
union
select '2013-02-21 14:00' as dt, 222 as val
union
select '2013-02-21 14:30' as dt, 333 as val
)
select DATEPART(hh, DATEADD(mi, -1, dt)) +1, SUM(val) from cte -- Вычитаем минуту, чтобы час попал в предыдущий отчетный
group by DATEPART(hh, DATEADD(mi, -1, dt))+1
21 фев 13, 09:06    [13958687]     Ответить | Цитировать Сообщить модератору
 Re: Сумма строк  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3422
Элегантно в один запрос не получится, т.к. группировать придется по вычисляемому полю.

Если данных много, а запрос будет выполняться часто, я бы сделал таблицу типа "справочник часов" и джойнил бы с ней. Если данных мало, то можно и на лету соорудить, что-то типа:
declare @StartDate datetime, @EndDate datetime;

select @StartDate = '20130203 13:00', @EndDate = '20130204 15:00';

declare @t table (
	Id int identity (1,1) primary key,
	MeasureDate datetime not null,
	Value money not null
);


insert into @t (MeasureDate, Value)
values ('20130203 13:00', 25),
	('20130203 13:30', 124),
	('20130203 14:00', 71),
	('20130203 14:30', 89);

select * from @t order by MeasureDate;

select ca.DT, sum(t.Value) as [Amount]
from @t t
	inner join (
		select top (datediff(hh, @StartDate, @EndDate) + 1) dateadd(hh, row_number() over(order by v.type) - 1, @StartDate) as [DT]
		from master.dbo.spt_values v--, master.dbo.spt_values v2
		order by DT
	) ca on t.MeasureDate <= ca.DT and t.MeasureDate > dateadd(hh, -1, ca.DT)
group by ca.DT;

Только план все равно отстойный, как видите, ибо row_number() нумерует все строки, а не только те, которые подпадают под условие в top (). Лучше хотя бы табличную переменную объявить и сгрузить в нее результат подзапроса. Все быстрее будет.
21 фев 13, 09:09    [13958700]     Ответить | Цитировать Сообщить модератору
 Re: Сумма строк  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Группировать по часам, но для [Дата] выводить MAX([Дата]), а для [Значения] - SUM([Значения])
21 фев 13, 09:13    [13958722]     Ответить | Цитировать Сообщить модератору
 Re: Сумма строк  [new]
AxuliON
Member

Откуда: оттуда, и на лыжах
Сообщений: 574
Ennor Tiegael,

Говнокодом попахивает....
21 фев 13, 09:16    [13958736]     Ответить | Цитировать Сообщить модератору
 Re: Сумма строк  [new]
Etamin419
Member

Откуда: Stk
Сообщений: 30
AxuliON
Да не заметил, у Вас час отчетный идет 14:00
Тогда надо будет +1 сделать
Если надо по дате и времени группировать - то разобраться сами должны.
Я лишь путь указал (цэ) "не моё"
with cte as
(
select '2013-02-21 13:00' as dt, 666 as val
union
select '2013-02-21 13:30' as dt, 111 as val
union
select '2013-02-21 14:00' as dt, 222 as val
union
select '2013-02-21 14:30' as dt, 333 as val
)
select DATEPART(hh, DATEADD(mi, -1, dt)) +1, SUM(val) from cte -- Вычитаем минуту, чтобы час попал в предыдущий отчетный
group by DATEPART(hh, DATEADD(mi, -1, dt))+1

Благодарю за идею, складывает как задумывалось, но прошу прощения я оказывается не до конца раскрыл проблему.... он получается складывает все часы за любые дни, т.е.
Дата Значение
2013-02-21 13:30 111
2013-02-21 14:00 222
2013-02-22 13:30 444
2013-02-22 14:00 222

то итог выходит такой
Дата Значение
14:00 999

а результат хотелось бы получить такой, чтобы разграничение было на каждый день
Дата Значение
2013-02-21 14:00 333
2013-02-22 14:00 666
21 фев 13, 09:51    [13958927]     Ответить | Цитировать Сообщить модератору
 Re: Сумма строк  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
with cte as
(
select '2013-02-21 13:00' as dt, 666 as val
union
select '2013-02-21 13:30' as dt, 111 as val
union
select '2013-02-21 14:00' as dt, 222 as val
union
select '2013-02-21 14:30' as dt, 333 as val
)
select a.b,sum(val)
from cte c
cross apply (select dateadd(MINUTE, datepart(HOUR,c.dt)*60+(datepart(MINUTE,c.dt)/30)*30,cast(cast(c.dt as date)as datetime))b) a
group by a.b
21 фев 13, 09:53    [13958943]     Ответить | Цитировать Сообщить модератору
 Re: Сумма строк  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3422
AxuliON,

Человек хотел "в один запрос". Я показал, что у него получится и объяснил, как сделать лучше.

Сначала на больших объемах сравните мой код с вашим "творением", а потом высказывайтесь. Вы план своего запроса видели? Там же 3 мерджа. На миллионе записей ваш group by DATEPART(hh, DATEADD(mi, -1, dt))+1 тупо ляжет.

Кроме того, ваш вариант еще и кривой, вдобавок. Если у ТС на самом деле записей в часе много и они не округляются до получаса, как в примере, то записи с разницей меньше минуты от верхней границы (ближайший час сверху) у вас будут отнесены на следующий интервал. Будете вычитать секунду вместо минуты - будет то же самое, не принципиально.
21 фев 13, 10:03    [13958994]     Ответить | Цитировать Сообщить модератору
 Re: Сумма строк  [new]
стрим vs хеш
Guest
Добрый Э - Эх
euthanatos,

то есть проверить время на целый или половинный час и в случае "половины" округлить до ближайшего большего целого часа - не судьба?


все зависит от качества данных.
если данных чуть более чем дохера, а периодов(часов) не много, то отдельные сик на каждый диапазон + стримагрегация могут быть значительно выгоднее, чем вычисление нового поля и последующая хешагрегация по нему.
21 фев 13, 10:11    [13959059]     Ответить | Цитировать Сообщить модератору
 Re: Сумма строк  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
эээ... не совсем понял... округлять до часа или до получаса? до часа - еще проще
21 фев 13, 10:11    [13959066]     Ответить | Цитировать Сообщить модератору
 Re: Сумма строк  [new]
Добрый Э - Эх
Guest
Cygapb-007,

судя по первому посту автора - до круглого часа в большую сторону.
21 фев 13, 10:13    [13959080]     Ответить | Цитировать Сообщить модератору
 Re: Сумма строк  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Добрый Э - Эх, спасибо, тогда так
select a.b,sum(val)
from (values('20130221 13:00',1),('20130221 13:10',1),('20130221 13:40',1),('20130221 13:59',1),('20130221 14:40',1)) c(dt,val)
cross apply (select dateadd(MINUTE, datepart(HOUR,c.dt)*60+(datepart(MINUTE,c.dt)/30)*60,cast(cast(c.dt as date)as datetime))b) a
group by a.b
21 фев 13, 10:16    [13959098]     Ответить | Цитировать Сообщить модератору
 Re: Сумма строк  [new]
AxuliON
Member

Откуда: оттуда, и на лыжах
Сообщений: 574
Ennor Tiegael,
Бредите дяденька!
Да хоть микросекунду. Я ему путь указал - в какую сторону копать.
А На миллионе записей это интересно какой интервал времени вы обрабатывать собрались?
57 лет получается охватить надо на 1М его записях

Идите уже в свою утопию дальше....
21 фев 13, 11:37    [13959692]     Ответить | Цитировать Сообщить модератору
 Re: Сумма строк  [new]
Etamin419
Member

Откуда: Stk
Сообщений: 30
Cygapb-007
Добрый Э - Эх, спасибо, тогда так
select a.b,sum(val)
from cte c
cross apply (select dateadd(MINUTE, datepart(HOUR,c.dt)*60+(datepart(MINUTE,c.dt)/30)*60,cast(cast(c.dt as date)as datetime))b) a
group by a.b


Всем большое спасибо! этот вариант как раз подходит, данные группируются согласно условию.
ps: данные у меня были только с получасовыми значениями(14:00;14:30), других вариантов нет, т.к. счетчик считывает данные через каждый полчаса.
21 фев 13, 11:45    [13959749]     Ответить | Цитировать Сообщить модератору
 Re: Сумма строк  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
AxuliON
Ennor Tiegael,
Бредите дяденька!
Да хоть микросекунду. Я ему путь указал - в какую сторону копать.
А На миллионе записей это интересно какой интервал времени вы обрабатывать собрались?
57 лет получается охватить надо на 1М его записях

Идите уже в свою утопию дальше....
А почему вы решили, что там один датчик?
Etamin419
этот вариант как раз подходит, данные группируются согласно условию
только в работу запускайте с перобразованием не в минуты, а в часы:
select a.b,sum(val)
from cte c
cross apply (select dateadd(HOUR, datepart(HOUR,c.dt)+datepart(MINUTE,c.dt)/30,cast(cast(c.dt as date)as datetime))b) a
group by a.b
21 фев 13, 12:39    [13960156]     Ответить | Цитировать Сообщить модератору
 Re: Сумма строк  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Etamin419
данные у меня были только с получасовыми значениями(14:00;14:30), других вариантов нет, т.к. счетчик считывает данные через каждый полчаса.
если точность до 30 минут, тогда еще проще:
select dateadd(MINUTE,(datepart(MINUTE,c.dt)/30)*30, c.dt), sum(val)
from cte c
group by dateadd(MINUTE,(datepart(MINUTE,c.dt)/30)*30, c.dt)
21 фев 13, 12:53    [13960311]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить