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

Откуда:
Сообщений: 189
Добрый день. У меня такая проблема. Мне нужно создать ХП которая бы создавала временную таблицу заполняла бы ее и проставляла нули для тех дат которых нет. Я написала такую процедуру для vba
Public Function FnVremennajtablica()          
Dim strSql As String
Dim datek As Date
Dim gCnn As Object
Set gCnn = CurrentProject.Connection
Dim Rst As Recordset
Dim Rst1 As Recordset
Dim ACon As Connection
Dim www As Recordset
Set ACon = Application.CurrentProject.Connection
Set Rst = CreateObject("ADODB.Recordset")
Set Rst1 = CreateObject("ADODB.Recordset")
Set ACon = Application.CurrentProject.Connection
Set www = CreateObject("ADODB.Recordset")
    www.Open sSQL_SU, ACon, adOpenKeyset, adLockOptimistic
Dim SQL As String
SQL = "drop table #t"
DoCmd.RunSQL SQL
   strSql = "create table #t(ID int IDENTITY(1,1) NOT NULL, ddate datetime, fp1 decimal(8,2) ,fp2 decimal(8,2),ft1 decimal(5,1),ft2 decimal(5,1),fg1 decimal(8,2),fg2 decimal(8,2))"
 gCnn.Execute (strSql)
Rst.Open "Select * From #t", ACon, adOpenKeyset, adLockOptimistic
Rst1.Open "Select * From qrPriborOtvodarh  Order By dDate ", ACon, adOpenKeyset, adLockOptimistic
datek = Rst1!dDate

Do Until Rst1.EOF
 If Rst1!dDate Like datek Then
        Rst.AddNew
        Rst!dDate = Rst1!dDate
        Rst!fP1 = Rst1!fP1
        Rst!fP2 = Rst1!fP2
        Rst!ft1 = Rst1!ft1
        Rst!fT2 = Rst1!fT2
        Rst!fG1 = Rst1!fG1
        Rst!fG2 = Rst1!fG2
        Rst!fQ1 = Rst1!fQ1
        Rst!fQ2 = Rst1!fQ2

  End If
  If Rst1!dDate Like datek = False Then
        Rst.AddNew
        Rst!dDate = datek
        Rst!fP1 = 0
        Rst!fP2 = 0
        Rst!ft1 = 0
        Rst!fT2 = 0
        Rst!fG1 = 0
        Rst!fG2 = 0
        Rst!fQ1 = 0
        Rst!fQ2 = 0

  End If
      Rst.Update
    If Rst1!dDate Like datek Then
        Rst1.MoveNext
    End If
    datek = DateAdd("h", 1, datek)
  Loop
 Rst1.Close
 Rst.Close
   End Function
теперь мне нужно записать то же самое для ХП я начала писать вот
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[tabvre] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
		CREATE TABLE #grahTable1 (
		Id INT IDENTITY(1,1) NOT NULL,
        ddate datetime, 
        fp1 decimal(8,2) ,
		fp2 decimal(8,2),
		ft1 decimal(5,1),
		ft2 decimal(5,1),
		fg1 decimal(8,2),
		fg2 decimal(8,2));
INSERT INTO #grahTable1 (ddate,fp1,fp2,ft1,ft2,fg1,fg2) 
select ddate,fp1,fp2,ft1,ft2,fg1,fg2
from qrPriborOtvodArh
END
А дальше не знаю как.буду очень благодарна за помощь
21 ноя 11, 09:13    [11629444]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8880
Суть задачи огласите, пожалуйста. Ипо в код я не въехал, кроме того, что там вложенный цикл на заполнение одной таблицы по другой.
ПыСы - А зачем создавать временную таблицу в процедуре, если по закрытию коннекта она (временная таблица) умрёт? И никому кроме как процедурам, вызванным позже, в этом же коннекте, доступна не будет... Это точно так задумано?
21 ноя 11, 09:25    [11629485]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
Ken@t
Member

Откуда: 大地
Сообщений: 3265
max2000,

Во первых , локальная временная таблица созданная в ХП не будет "видная" ( будет уничтожена ) после выхода из ХП.
Во вторых , можно создать и глобальную временную таблицу ##.
В третьих , прочитайте овременных таблицах - области видимости, время жизни.

В четвёртых это можно сделать одним батчем

А вот с логикой вашей функции - не понял.

к таблицам указывайте схемы, и со звёздачками - завязывайте.
21 ноя 11, 09:32    [11629509]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
max2000
Member

Откуда:
Сообщений: 189
SIMPLicity_,
суть такая:нужно из запроса переписать данные во временную таблицу с проверкой дат.т.е берется первая дата в запросе qrPriborOtvodarh приравнивается к datek и записывается строка дальше к этой datek прибавляется час и проверяется следующая строка из qrPriborOtvodarh равна datek если да то записывается если нет то вставляется datek а все остальные столбцы записываются равными 0 и так далее.Если коротко, то переписываются полностью данные и если нет какого то часа то он вставляется с нулевыми столбцами. Далее на основе этой временной таблицы я хотела строить график.или это не возможно?
21 ноя 11, 09:34    [11629514]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
Ken@t
Member

Откуда: 大地
Сообщений: 3265
max2000,

табличку времени неполохо иметь

declare @s table
(
  d datetime
  ,f int
)

INSERT @s (d,f)
SELECT '20111121', 1 UNION 
SELECT '20111121 1:0', 1 UNION 
SELECT '20111121 2:0', 1 UNION 
SELECT '20111121 3:0', 1 UNION 
SELECT '20111121 5:0', 1 UNION 
SELECT '20111121 9:0', 1 


SELECT 
 DATEADD ( hour, number , '20111121' ) D
 ,ISNULL(f,0) 
FROM master.dbo.spt_values
 left join @s s on ( s.d = DATEADD ( hour, number , '20111121' ) )
where type ='P'  and number BETWEEN 0 AND 23 

Вот на день.
21 ноя 11, 09:51    [11629583]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
max2000
Member

Откуда:
Сообщений: 189
Ken@t,
для чего она?
21 ноя 11, 09:53    [11629589]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
aleks2
Guest
max2000
SIMPLicity_,
суть такая:нужно из запроса переписать данные во временную таблицу с проверкой дат.т.е берется первая дата в запросе qrPriborOtvodarh приравнивается к datek и записывается строка дальше к этой datek прибавляется час и проверяется следующая строка из qrPriborOtvodarh равна datek если да то записывается если нет то вставляется datek а все остальные столбцы записываются равными 0 и так далее.Если коротко, то переписываются полностью данные и если нет какого то часа то он вставляется с нулевыми столбцами. Далее на основе этой временной таблицы я хотела строить график.или это не возможно?


1. Ну дык, не майтесь херней - заполните свою времянку в процедуре и верните рекордсет.
2. Фсе одно клиент работает НЕ с таблицей, а с рекордсетом.
21 ноя 11, 09:54    [11629596]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8880
aleks2
max2000
SIMPLicity_,
суть такая:нужно из запроса переписать данные во временную таблицу с проверкой дат.т.е берется первая дата в запросе qrPriborOtvodarh приравнивается к datek и записывается строка дальше к этой datek прибавляется час и проверяется следующая строка из qrPriborOtvodarh равна datek если да то записывается если нет то вставляется datek а все остальные столбцы записываются равными 0 и так далее.Если коротко, то переписываются полностью данные и если нет какого то часа то он вставляется с нулевыми столбцами. Далее на основе этой временной таблицы я хотела строить график.или это не возможно?


1. Ну дык, не майтесь херней - заполните свою времянку в процедуре и верните рекордсет.
2. Фсе одно клиент работает НЕ с таблицей, а с рекордсетом.

Ээээпс... А нафига вообще ХП тут? Может можно просто выполнить селект и всё?

2 max2000 На таких данных что должно попасть в график?
declare @qrPriborOtvodArh table (id int identity (1,1) ,
                                 ddate datetime,
                                 fp1 decimal(8,2) ,
                                 fp2 decimal(8,2),
                                 ft1 decimal(5,1),
                                 ft2 decimal(5,1),
                                 fg1 decimal(8,2),
                                 fg2 decimal(8,2),
                                 AnotherFields nvarchar(1000));
insert into @qrPriborOtvodArh (ddate,fp1,fp2,ft1,ft2,fg1,fg2,AnotherFields)
select '20111111 01:00:00',  - 0.5, 20.3, 75.1, 35.2, 0,0, 'Во дворе и дома 1'
union all
select '20111111 01:20:10',  - 0.7, 20.0, 75.4, 35.8, 0,0, ''
union all
select '20111111 02:00:00',  - 0.8, 20.1, 75.5, 36.1, 0,0, 'Чуток похолодало'
union all
select '20111111 03:00:00',  - 1.1, 20.0, 76.1, 36.7, 0,0, 'Ыщо похолодало'
union all
select '20111111 03:45:20',  - 1.6, 19.8, 76.2, 37.2, 0,0, 'Чота опять похолодало'
union all
select '20111111 04:00:00',  - 1.7, 19.7, 76.3, 38.2, 0,0, 'Дома дубняк'
union all
select '20111111 06:00:00',  - 1.9, 22.4, 76.4, 35.1, 0,0, 'Включился обогреватель. Зашибись!'
select * from @qrPriborOtvodArh order by ddate

id ddate fp1 fp2 ft1 ft2 fg1 fg2 AnotherFields
1 2011-11-11 01:00:00.000 -0.50 20.30 75.1 35.2 0.00 0.00 Во дворе и дома 1
2 2011-11-11 01:20:10.000 -0.70 20.00 75.4 35.8 0.00 0.00
3 2011-11-11 02:00:00.000 -0.80 20.10 75.5 36.1 0.00 0.00 Чуток похолодало
4 2011-11-11 03:00:00.000 -1.10 20.00 76.1 36.7 0.00 0.00 Ыщо похолодало
5 2011-11-11 03:45:20.000 -1.60 19.80 76.2 37.2 0.00 0.00 Чота опять похолодало
6 2011-11-11 04:00:00.000 -1.70 19.70 76.3 38.2 0.00 0.00 Дома дубняк
7 2011-11-11 06:00:00.000 -1.90 22.40 76.4 35.1 0.00 0.00 Включился обогреватель. Зашибись!
21 ноя 11, 10:00    [11629617]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
max2000
Member

Откуда:
Сообщений: 189
[quot aleks2]
max2000
SIMPLicity_,
1. Ну дык, не майтесь херней - заполните свою времянку в процедуре и верните рекордсет.
2. Фсе одно клиент работает НЕ с таблицей, а с рекордсетом.

1.процедура слишком медленно работает(пол минуты это много). я думала что через хп будет быстрее
2.может быть как то можно переписать мою процедуру под sql?
21 ноя 11, 10:00    [11629618]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
max2000
Member

Откуда:
Сообщений: 189
SIMPLicity_,
чесно говоря не понимаю что вы предлагаете. график строится по всем столбцам ось x это дата а на y все показания (fp1 fp2 ft1 ft2 fg1 fg2)
21 ноя 11, 10:08    [11629646]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
Ken@t
Member

Откуда: 大地
Сообщений: 3265
max2000
SIMPLicity_,
чесно говоря не понимаю что вы предлагаете. график строится по всем столбцам ось x это дата а на y все показания (fp1 fp2 ft1 ft2 fg1 fg2)

вот для этого и нужна таблица времени, к котрой заджойните ваши данные
21 ноя 11, 10:09    [11629655]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5126
max2000,
ИМХО значительно продуктивнее будет если вы приведёте пример исходных данных (строк 5) и что должно получиться...
21 ноя 11, 10:24    [11629725]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8880
max2000
SIMPLicity_,
суть такая:нужно из запроса переписать данные во временную таблицу с проверкой дат.т.е берется первая дата в запросе qrPriborOtvodarh приравнивается к datek и записывается строка дальше к этой datek прибавляется час и проверяется следующая строка из qrPriborOtvodarh равна datek если да то записывается если нет то вставляется datek а все остальные столбцы записываются равными 0 и так далее.Если коротко, то переписываются полностью данные и если нет какого то часа то он вставляется с нулевыми столбцами. Далее на основе этой временной таблицы я хотела строить график.или это не возможно?

Простите, но условия построения графика у Вас что называется "Arse over tits" (что по-русски означает "через жопу") ибо (выжирнил и одчеркнул даже): "если нет то вставляется datek а все остальные столбцы записываются равными 0 " - это просто писец. Даже если у Вас прибор снимает показания СТРОГО до миллисекунды ровно в начале часа, то при пропадании значения за один единственный раз (например, в 7-00), вы получаете красивую "галку" на графике.
Суть моего вопроса заключалась в том, что в приведённых мною "какбэданных" есть моменты с пропуском времени и моменты с двумя показаниями за часовой интервал. Чо вот с ними делать....

Если всё строго и хорошо - выкидывайте нах процедуру и воспользуйтесь запросом Ken@t. Единственное, что логин (под которым Ваша программа подключается к серверу), должен иметь права на чтение из базы master. Ну да судя по всему, у Вас это будет работать...
21 ноя 11, 11:09    [11629976]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
max2000
Member

Откуда:
Сообщений: 189
вот например у меня такая таблица.
id ddate fp1 fp2 ft1 ft2 fg1 fg2
1 2011-11-11 01:00:00 1 2 7 3 5 7
2 2011-11-11 02:00:00 4 5 4 5 0 8
3 2011-11-11 04:00:00 4 7 4 5 4 9
4 2011-11-11 05:00:00 9 6 7 9 3 744
5 2011-11-11 06:00:00 6 1 2 3 2 7


а во временной должна получится вот такая
id ddate fp1 fp2 ft1 ft2 fg1 fg2
1 2011-11-11 01:00:00 1 2 7 3 5 7
2 2011-11-11 02:00:00 4 5 4 5 0 8
3 2011-11-11 03:00:00 0 0 0 0 0 0
4 2011-11-11 04:00:00 4 7 4 5 4 9
5 2011-11-11 05:00:00 9 6 7 9 3 744
6 2011-11-11 06:00:00 6 1 2 3 2 7
21 ноя 11, 11:10    [11629979]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8880
Используя вариант Ken@t:

declare @qrPriborOtvodArh table (id int identity (1,1) ,
                                 ddate datetime,
                                 fp1 decimal(8,2) ,
                                 fp2 decimal(8,2),
                                 ft1 decimal(5,1),
                                 ft2 decimal(5,1),
                                 fg1 decimal(8,2),
                                 fg2 decimal(8,2),
                                 AnotherFields nvarchar(1000));
insert into @qrPriborOtvodArh (ddate,fp1,fp2,ft1,ft2,fg1,fg2,AnotherFields)
select '20111111 01:00:00',  - 0.5, 20.3, 75.1, 35.2, 0,0, 'Во дворе и дома 1'
union all
select '20111111 01:20:10',  - 0.7, 20.0, 75.4, 35.8, 0,0, ''
union all
select '20111111 02:00:00',  - 0.8, 20.1, 75.5, 36.1, 0,0, 'Чуток похолодало'
union all
select '20111111 03:00:00',  - 1.1, 20.0, 76.1, 36.7, 0,0, 'Ыщо похолодало'
union all
select '20111111 03:45:20',  - 1.6, 19.8, 76.2, 37.2, 0,0, 'Чота опять похолодало'
union all
select '20111111 04:00:00',  - 1.7, 19.7, 76.3, 38.2, 0,0, 'Дома дубняк'
union all
select '20111111 06:00:00',  - 1.9, 22.4, 76.4, 35.1, 0,0, 'Включился обогреватель. Зашибись!'

SELECT 
 DATEADD ( hour, number , '20111111' ) D
 ,ISNULL(fp1,0) fp1, ISNULL(fp2,0) fp2, ISNULL(ft1,0) ft1, ISNULL(ft2,0) ft2, ISNULL(fg1,0) fg1, ISNULL(fg2,0) fg2
FROM master.dbo.spt_values
 left join @qrPriborOtvodArh s on ( s.ddate = DATEADD ( hour, number , '20111111' ) )
where type ='P'  and number BETWEEN 0 AND 23 and DATEADD ( hour, number , '20111111' ) between (select min(ddate) from  @qrPriborOtvodArh) and (select max(ddate) from  @qrPriborOtvodArh)



D fp1 fp2 ft1 ft2 fg1 fg2
2011-11-11 01:00:00.000 -0.50 20.30 75.1 35.2 0.00 0.00
2011-11-11 02:00:00.000 -0.80 20.10 75.5 36.1 0.00 0.00
2011-11-11 03:00:00.000 -1.10 20.00 76.1 36.7 0.00 0.00
2011-11-11 04:00:00.000 -1.70 19.70 76.3 38.2 0.00 0.00
2011-11-11 05:00:00.000 0.00 0.00 0.0 0.0 0.00 0.00
2011-11-11 06:00:00.000 -1.90 22.40 76.4 35.1 0.00 0.00
21 ноя 11, 11:24    [11630097]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
max2000
Member

Откуда:
Сообщений: 189
[quot SIMPLicity_]
max2000
SIMPLicity_,
Суть моего вопроса заключалась в том, что в приведённых мною "какбэданных" есть моменты с пропуском времени и моменты с двумя показаниями за часовой интервал. Чо вот с ними делать....

Если всё строго и хорошо - выкидывайте нах процедуру и воспользуйтесь запросом Ken@t. Единственное, что логин (под которым Ваша программа подключается к серверу), должен иметь права на чтение из базы master. Ну да судя по всему, у Вас это будет работать...

что делать когда время пропущенное я уже написала "столбцы записываются равными 0" а два показания за час не могут быть!
1.так мне и не понятен запрос Ken@t
2.зачем мне бд master?
21 ноя 11, 11:25    [11630102]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8880
Первая часть запроса - это создание и заполнение "как бы вашей таблицы с данными". Табличная переменная используется исключительно для удобства работы.
Смысл запроса - сначала формируется "виртуальная таблица" временных дат с интервалом в час за определённый день (например, 11 - 11 - 2011 ): час утра , два часа утр, три ....

Потом она связывается с вашей таблицей и из неё (вашей таблицы) выводятся строки, у которых временной интервал совпал с тем, что был в поле d (час утра , два часа утр, три ....)... Там где данных за какой-то момент (час ровно, без минутов и секундов) нет, значения преобразуются в 0 (функция isnull).

Плюс вывод ограничивается максимальным и миимальным временем в таблице исходных данных.

PS Всё равно это всё херня и бред.... ИМБО.
21 ноя 11, 11:33    [11630159]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
max2000
Member

Откуда:
Сообщений: 189
SIMPLicity_,
так не пойдет так как данные хранятся у меня за несколько лет и все даты описывать.....
21 ноя 11, 11:41    [11630228]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8880
max2000
SIMPLicity_,
так не пойдет так как данные хранятся у меня за несколько лет и все даты описывать.....


версия MSSQL-сервера какая? (select @@version)
MSSQL-2000 (как вариант - MSDE,- однох....нно)
MSSQL-2005/2008/2008 R2 или старше (как вариант - в редакции Express)
21 ноя 11, 11:45    [11630271]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
max2000
Member

Откуда:
Сообщений: 189
SIMPLicity_,
2005
21 ноя 11, 11:49    [11630316]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
Glory
Member

Откуда:
Сообщений: 104751
max2000
так не пойдет так как данные хранятся у меня за несколько лет и все даты описывать.....

И как вы думаете, сколько займет вспомогательная таблица со "всеми датами" ?
21 ноя 11, 12:16    [11630601]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
max2000
Member

Откуда:
Сообщений: 189
Glory
max2000
так не пойдет так как данные хранятся у меня за несколько лет и все даты описывать.....

И как вы думаете, сколько займет вспомогательная таблица со "всеми датами" ?

в смысле сколько займет? места? времени заполнения?
21 ноя 11, 12:21    [11630661]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
Glory
Member

Откуда:
Сообщений: 104751
max2000
места? времени заполнения?

И того и другого
21 ноя 11, 12:24    [11630684]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
max2000
Member

Откуда:
Сообщений: 189
Glory,
на данный момент примерно миллион строк.да и описывать все даты это не выход
21 ноя 11, 12:37    [11630812]     Ответить | Цитировать Сообщить модератору
 Re: заполнение временных таблиц  [new]
Glory
Member

Откуда:
Сообщений: 104751
max2000
на данный момент примерно миллион строк.

Откуда у вас миллион _разных_ дат ?

max2000
и описывать все даты это не выход

Конечно не выход. Это вход.
21 ноя 11, 12:43    [11630891]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить