Одним запросом...

← →
said46 (2003-09-17 13:10) [0]

Всем привет. Есть таблица примерно такого вида:

дата значение
01.01.2003 00:00:00 1
01.01.2003 00:01:20 5
01.01.2003 00:03:05 7
...
01.01.2003 01:01:15 5
01.01.2003 01:07:23 2
...
01.01.2003 05:06:00 4
01.01.2003 05:08:09 3
...

С помощью какого запроса можно получить следующее (и можно ли):

период (час) сумма значений за период
с 00:00:00 по 00:01:00 ...
с 00:01:00 по 00:02:00 ...
с 00:02:00 по 00:03:00 ...

т.е. сумму значений за каждый час, этакая "почасовая" раскладка.

Заранее благодарен за ответ.

← →
Sandman25 (2003-09-17 13:15) [1]

Нельзя.
Из пустой таблицы должно получиться
с 00:00:00 по 00:01:00 0
с 00:01:00 по 00:02:00 0
с 00:02:00 по 00:03:00 0
И откуда эти данные брать?

Без цикла с шагом 1 час никак не получится.

← →
Nikolay M. (2003-09-17 13:21) [2]

Хранимой процедурой - на здоровье :)

← →
said46 (2003-09-17 13:22) [3]

Ясна... Я то думал как-нибудь сгруппировать можно :)
Спасибо.

← →
DenK_vrtz (2003-09-17 13:38) [4]

Я уверен, что и одним запросом можно. Только в MSSQL не знаю как, а Oracle без проблем.

← →
Sandman25 (2003-09-17 13:40) [5]

[4] DenK_vrtz © (17.09.03 13:38)

Посмотрите [1] - случай пустой таблицы.

← →
ZrenBy (2003-09-17 14:01) [6]

Тупейший вариант, можно изящней.

select
A,
S = (select sum(...) from ... where datepart(hh,..)>=A and datepart(hh,..)<A+1)
from(
select 0 as A
union select 1
union select 2
union select 3
union select 4
union select 5
union select 6
union select 7
union select 8
union select 9
union select 10
union select 11
union select 12
union select 13
union select 14
union select 15
union select 16
union select 17
union select 18
union select 19
union select 20
union select 21
union select 22
union select 23)H

← →
bushmen (2003-09-17 14:04) [7]

с 00:00:00 по 00:01:00
Во-первых, ты выбираешь поминутно, а не почасово.

← →
said46 (2003-09-17 14:07) [8]

2bushmen:

очепятка

Всем спасибо.

← →
said46 (2003-09-17 14:11) [9]

2ZrenBy:

Я восхищен!!!

← →
ZrenBy (2003-09-17 14:27) [10]

Вдогонку. Вариант без подзапросов.

select
H,
S = isnull(SS,0)
from(
select
H = 4*a.A+b.A
from
(select 0 as A union select 1 union select 2 union select 3 union select 4 union select 5)a,
(select 0 as A union select 1 union select 2 union select 3)b
)H
left outer join
(
select
HH = datepart(hh,[date]),
SS = sum(...)
from ...
group by datepart(hh,[date])
)T on H = HH
order by 1

← →
Delirium (2003-09-17 14:41) [11]

Сначала делашь шаблон - таблицу с одним полем, в котором часы или минуты или дни - как угодно, так называемая временная линейка. На линейку join-ишь свою инфу... вот и все :)

← →
Delirium (2003-09-17 14:44) [12]

Для удобства приведу код функции округляющей до минут, переделай под часы и агрегируй сколько душе угодно

CREATE FUNCTION fn_RoundTime4Min (@input datetime, @trunc int) RETURNS datetime AS BEGIN declare @M numeric(38, 24) set @M=1.000000000000000000000000/24/60 RETURN Convert(datetime, Convert(smalldatetime, Round(Convert(numeric(38, 30), @input)/@M, 0, @trunc)*@M)) END

← →
said46 (2003-09-17 14:52) [13]

Еще раз всем спасибо :)

← →
Sandman25 (2003-09-17 15:18) [14]

Не, так не честно. Это же запрос по другой таблице, в которую записан как раз тот цикл, о котором я писал :)
А если серьезно, то очень красиво. Надо будет запомнить идею.

← →
said46 (2003-09-18 10:10) [15]

Требуется совет.
Не слишком громоздко (имею ввиду большое кол-во селектов)?
Не будет медленно работать?
Нельзя ли как-нибудь чтобы селектов поменьше?

Кусочек хранимой процедуры:
-- Автоматический режим левая сторона 1 смена SELECT substring(convert(varchar(19), dateadd(hh, A, @rep_cur_hour), 108), 1, 5) + " - " + substring(convert(varchar(19), dateadd(hh, A + 1, @rep_cur_hour), 108), 1, 5) AS RepHour, Cement = (SELECT sum(cement) FROM BSUShipment WHERE date_time >= dateadd(hh, A, @rep_cur_hour) AND date_time < dateadd(hh, A + 1, @rep_cur_hour) AND type = 1 AND line < 2 AND status < 3), Sand = (SELECT sum(sand) FROM BSUShipment WHERE date_time >= dateadd(hh, A, @rep_cur_hour) AND date_time < dateadd(hh, A + 1, @rep_cur_hour) AND type = 1 AND line < 2 AND status < 3), Metal = (SELECT sum(metal) FROM BSUShipment WHERE date_time >= dateadd(hh, A, @rep_cur_hour) AND date_time < dateadd(hh, A + 1, @rep_cur_hour) AND type = 1 AND line < 2 AND status < 3), Additive = (SELECT sum(additive) FROM BSUShipment WHERE date_time >= dateadd(hh, A, @rep_cur_hour) AND date_time < dateadd(hh, A + 1, @rep_cur_hour) AND type = 1 AND line < 2 AND status < 3), Water = (SELECT sum(water) FROM BSUShipment WHERE date_time >= dateadd(hh, A, @rep_cur_hour) AND date_time < dateadd(hh, A + 1, @rep_cur_hour) AND type = 1 AND line < 2 AND status < 3), Concrete = (SELECT 0.5*Count(*) FROM BSUShipment WHERE date_time >= dateadd(hh, A, @rep_cur_hour) AND date_time < dateadd(hh, A + 1, @rep_cur_hour) AND type = 1 AND line < 2 AND status < 3) FROM( SELECT 0 AS A UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8)H

← →
Sandman25 (2003-09-18 10:36) [16]

Попробуйте объединить запросы по cement, sand, metal и т.д.
Что-то типа
(SELECT sum(cement), sum(sand), sum(metal) FROM BSUShipment WHERE date_time >= dateadd(hh, A, @rep_cur_hour) AND date_time < dateadd(hh, A + 1, @rep_cur_hour) AND type = 1 AND line < 2 AND status < 3)

← →
said46 (2003-09-18 11:24) [17]

Это-то ясно. Я имел ввиду как это сделать в контексте данного запроса

← →
Sandman25 (2003-09-18 11:32) [18]

Так не пойдет?
SELECT
substring(convert(varchar(19), dateadd(hh, A, @rep_cur_hour), 108), 1, 5) + " - " + substring(convert(varchar(19), dateadd(hh, A + 1, @rep_cur_hour), 108), 1, 5) AS RepHour,
(SELECT sum(cement), sum(sand), sum(metal), sum(additive), sum(water), sum(concrete) FROM BSUShipment WHERE date_time >= dateadd(hh, A, @rep_cur_hour) AND date_time < dateadd(hh, A + 1, @rep_cur_hour) AND type = 1 AND line < 2 AND status < 3),
FROM(
SELECT 0 AS A
UNION SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8)H

← →
said46 (2003-09-18 11:35) [19]

Это самое первое что я попробовал. Результат:

Server: Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

← →
Sandman25 (2003-09-18 11:36) [20]

Понятно. Буду думать...

← →
Sandman25 (2003-09-18 11:39) [21]

А так?
SELECT substring(convert(varchar(19), dateadd(hh, A, @rep_cur_hour), 108), 1, 5) + " - " + substring(convert(varchar(19), dateadd(hh, A + 1, @rep_cur_hour), 108), 1, 5) AS RepHour, sum(cement), sum(sand), sum(metal), sum(additive), sum(water), sum(concrete) FROM BSUShipment, ( SELECT 0 AS A UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8)H WHERE date_time >= dateadd(hh, A, @rep_cur_hour) AND date_time < dateadd(hh, A + 1, @rep_cur_hour) AND type = 1 AND line < 2 AND status < 3) GROUP BY 1

← →
said46 (2003-09-18 11:51) [22]

Это ваще не работает. Для проверки создай табличку

дата значение1 значение2

и попробуй.

Иначе мне много вариантов придется проверить :)

← →
Sandman25 (2003-09-18 11:59) [23]

Я проверить не могу - в моей СУБД не поддерживается select from (select).
Так что Вы уж как-нибудь сами :)

Там в предыдущем select была скобка лишняя в конце, надеюсь, проблема не в этом :)
Копать надо в направлении group by, причем желательно сначала извлекать только cast (A as char(200)) (чтобы быстрее сработало), а потом еще update по этому полю сделать, добиваясь нужного вида сообщения.

← →
said46 (2003-09-18 12:08) [24]

Слушай, работает! Только сделал пару мелких исправлений:

SELECT substring(convert(varchar(19), dateadd(hh, A, @rep_cur_hour), 108), 1, 5) + " - " + substring(convert(varchar(19), dateadd(hh, A + 1, @rep_cur_hour), 108), 1, 5) AS RepHour, sum(cement), sum(sand), sum(metal), sum(additive), sum(water), sum(concrete) FROM BSUShipment, ( SELECT 0 AS A UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8)H WHERE date_time >= dateadd(hh, A, @rep_cur_hour) AND date_time < dateadd(hh, A + 1, @rep_cur_hour) AND type = 1 AND line < 2 AND status < 3 GROUP BY A

← →
Sandman25 (2003-09-18 12:16) [25]

Рад за Вас :)
Если надо будет еще ускорить - см. [23] Sandman25 © (18.09.03 11:59)
А то группировку по полю Varchar нельзя назвать быстрой операцией.

← →
said46 (2003-09-18 12:20) [26]

В этом запросе разве A не smallint, судя хотя бы по типу второго
параметра dateadd(hh, A, @rep_cur_hour)?

И еще. Работать то работает, а смысла не пойму. Что значит, если
таблицы после FROM перечислены через запятую?

← →
Sandman25 (2003-09-18 12:25) [27]

Забудьте про мое "ускорение". Я уже и сам не уверен, что оно будет.

Насчет второго вопроса:
from a, b
where a.id = b.id

это примерно то же самое что

from a left join b on a.id = b.id

Но я не спец во втором синтаксисе, так что могу и ошибаться.

← →
said46 (2003-09-18 12:39) [28]

Ясно. Кстати, протестировал варианты:

1-ый, который я привел в [15]
2-ый, который сочинил Sandman25 и совсем немножечко подправил я

2-ой работает гораздо быстрее, так что не зря я этот вопрос замутил :).

Спасибо за помощь.

← →
Krey (2003-09-19 00:46) [29]

Жалко что все-таки одним запросом без SP и UDF никак не получится. Имеется в виду не такая простая задача: по часам, а например по дням. Писать 365 union"ов на год вряд ли стоит.
Думал часов 5. Получилось выполнить в теле запроса цикл, бегать по записям созданного в теле ОДНОГО запроса курсора, но возникла проблема: записать, данные временной ленейки некуда.
Если возникнет вопрос как в теле запроса организовать цикл, то вот простой ответ:

SELECT a.* FROM OPENROWSET("SQLOLEDB","server";"user";"password","Exec(""
Declare @int int
Set @int=1
while @int<100 Set @int = @int + 1
select @int
"")") AS a

Беда в том что OPENROWSET не позволяет производить изменения где либо. Но зато можно делать все остальное. :)

← →
said46 (2003-09-19 09:32) [30]

Ну уж, на год. Вот на месяц дык легко.

SET @rep_cur_hour = dateadd(day,0,convert(datetime,substring(convert(varchar(19), @BeginTime,120),1,8)+"01 00:00:00")) IF @rep_cur_hour > GetDate() SET @rep_cur_hour = dateadd(mm, -1, @rep_cur_hour) -- левая сторона автоматический режим SELECT substring(convert(varchar(19), dateadd(day, A - 1, @rep_cur_hour), 104), 1, 10), sum(cement) AS cement, sum(sand) AS sand, sum(metal) AS metal, sum(additive) AS additive, sum(water) AS water, 0.5*count(ALL date_time) AS concrete FROM BSUShipment RIGHT JOIN ( SELECT 1 AS A UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 where datepart(mm, dateadd(day, 28, @rep_cur_hour)) = datepart(mm, @rep_cur_hour) UNION SELECT 30 where datepart(mm, dateadd(day, 29, @rep_cur_hour)) = datepart(mm, @rep_cur_hour) UNION SELECT 31 where datepart(mm, dateadd(day, 30, @rep_cur_hour)) = datepart(mm, @rep_cur_hour) )H ON date_time >= dateadd(hh, A - 1, @rep_cur_hour) AND date_time < dateadd(hh, A, @rep_cur_hour) AND type = 1 AND line < 2 AND status < 3 GROUP BY A

← →
Sandman25 (2003-09-19 10:19) [31]

Если на год, то лучше завести постоянную таблицу, в которую записать значения от 1 до 365, и использовать ее вместо select union select.
Кстати, то же самое можно использовать даже для выбора по часам, возможно даже убыстрение.

← →
ZrenBy (2003-09-19 11:20) [32]

>>Krey (19.09.03 00:46) [29]
>>Писать 365 union"ов на год вряд ли стоит.

Стандартный ход:

select 100*a.a+10*b.b+c.c from
(select 0 as a union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)a,
(select 0 as b union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)b,
(select 0 as c union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)c
order by 1

← →
Krey (2003-09-19 12:15) [33]

К сожалению обычно права доступа к серверу БД не позволяют в нем писать хранимые процедуры.

Одним запросом... Найти похожие ветки