Мастера DELPHI (документация, исходники, скачать)

Основная («Начинающим»)/ Базы / WinAPI / Компоненты / Сети / Media / Игры / Corba и COM / KOL / FreePascal / .Net / Прочее / RSDN.ORG

« предыдущая ветвь | форум | следующая ветвь »

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

Страницы: 1 2 >
said46 © (2003-09-17 13:10:00)

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

дата значение
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 ...

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

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

Krey (2003-09-19 12:15:00) [1]

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

ZrenBy (2003-09-19 11:20:00) [2]

>>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

Sandman25 (2003-09-19 10:19:00) [3]

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

said46 (2003-09-19 09:32:00) [4]

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

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

Krey (2003-09-19 00:46:00) [5]

Жалко что все-таки одним запросом без 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-18 12:39:00) [6]

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

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

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

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

Sandman25 (2003-09-18 12:25:00) [7]

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

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

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

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

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

said46 (2003-09-18 12:20:00) [8]

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

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

Sandman25 (2003-09-18 12:16:00) [9]

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

said46 (2003-09-18 12:08:00) [10]

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

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 11:59:00) [11]

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

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

said46 (2003-09-18 11:51:00) [12]

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

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

и попробуй.

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

Sandman25 (2003-09-18 11:39:00) [13]

А так?
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

Sandman25 (2003-09-18 11:36:00) [14]

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

said46 (2003-09-18 11:35:00) [15]

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

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:32:00) [16]

Так не пойдет?
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:24:00) [17]

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

Sandman25 (2003-09-18 10:36:00) [18]

Попробуйте объединить запросы по 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 10:10:00) [19]

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

Кусочек хранимой процедуры:
-- Автоматический режим левая сторона 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-17 15:18:00) [20]

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

Страницы: 1 2 >

Наверх

	« предыдущая ветвь \| форум \| следующая ветвь »