Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Оптимизация SQL запроса через VBA и экспорт данных в Excel
 
#1
18.09.2015 15:22:29
Доброго дня всем.
Имеется следующая задача.
Есть база данных SQL где каждый час пополняются параметры (с определенными тегами)
Есть эксель файл, где в ячейках прописаны "Маски" тегов для работы макроса экспорта из базы данных.
в макросе ссылки на эти маски приведены в самом начале. всего 9 масок (данных много и они разные).
не владея углубленно знаниями VBA мы решили задачу "в лоб" и 9 раз обращаемся в базу SQL по каждой маске запрашивая данные.
Вопрос 1 Профи, подскажите пожалуйста пути оптимизации, если поправите код, премного благодарен. чует сердце, что нет в этом рациональности )
вопрос 2 По некоторым маскам (например maskP) значения хранятся ежечасно, в Экселе мне они нужны среднеарифметические за сутки (исключая нулевые значения). можно кусок кода как это делается вообще?
вопрос 3 Как лучше поступить с оформлением таблиц, знаками после запятой в числах. Есть ли вариант их сразу в необходимом формате запрашивать или это долго будет, лучше выгрузить в эксель а потом проводить форматирование?
Спасибо

Код
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
Sub ВыборДанных()
    Dim st As String
    Dim en As String
    Dim mask As String
'задание маски
    ActiveSheet.Select
    Range("B2").Select
    maskP = ActiveCell.Value
    Range("C2").Select
    maskT = ActiveCell.Value
    Range("D2").Select
    maskF = ActiveCell.Value
    Range("E2").Select
    maskDP = ActiveCell.Value
    Range("F2").Select
    mask174P = ActiveCell.Value
    Range("G2").Select
    mask174F = ActiveCell.Value
    Range("H2").Select
    mask174T = ActiveCell.Value
    Range("I2").Select
    mask184P = ActiveCell.Value
    Range("J2").Select
    maskFMET = ActiveCell.Value
    Range("B3").Select
    st = ActiveCell.Value
    Range("B4").Select
    en = ActiveCell.Value
     
    ActiveSheet.Select
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=MainDatabase;Data Source=" _
        , _
        "4;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;" _
        , "Tag with column collation when possible=False"), Destination:=Range("$A$7") _
        ).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "select t.tagname, dbo.rmfconvertsecondstolocal(v.time) time, /*v.msec,*/ v.value, t.tagdescription from maindatabase_online_5.dbo.floatarchive" _
        , _
        " v" & Chr(13) & "" & Chr(10) & "join tag t on (v.tagid=t.tagid)" & Chr(13) & "" & Chr(10) & "where (t.tagname like '" & maskP & "') --tagid = 32593" & Chr(13) & "" & Chr(10) & "and dbo.rmfconvertsecondsto" _
        , _
        "local(v.time) between ('" & st & "') and ('" & en & "')" & Chr(13) & "" & Chr(10) & "order by dbo.rmfconvertsecondstolocal(v.tim" _
        , "e)")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Таблица___MainDatabase1"
        .Refresh BackgroundQuery:=False
    End With
     With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=MainDatabase;Data Source=" _
        , _
        "4;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;" _
        , "Tag with column collation when possible=False"), Destination:=Range("$E$7") _
        ).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "select t.tagname, dbo.rmfconvertsecondstolocal(v.time) time, /*v.msec,*/ v.value, t.tagdescription from maindatabase_online_5.dbo.floatarchive" _
        , _
        " v" & Chr(13) & "" & Chr(10) & "join tag t on (v.tagid=t.tagid)" & Chr(13) & "" & Chr(10) & "where (t.tagname like '" & maskT & "') --tagid = 32593" & Chr(13) & "" & Chr(10) & "and dbo.rmfconvertsecondsto" _
        , _
        "local(v.time) between ('" & st & "') and ('" & en & "')" & Chr(13) & "" & Chr(10) & "order by dbo.rmfconvertsecondstolocal(v.tim" _
        , "e)")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Таблица___MainDatabase2"
        .Refresh BackgroundQuery:=False
    End With
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=MainDatabase;Data Source=" _
        , _
        "4;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;" _
        , "Tag with column collation when possible=False"), Destination:=Range("$I$7") _
        ).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "select t.tagname, dbo.rmfconvertsecondstolocal(v.time) time, /*v.msec,*/ v.value, t.tagdescription from maindatabase_online_5.dbo.floatarchive" _
        , _
        " v" & Chr(13) & "" & Chr(10) & "join tag t on (v.tagid=t.tagid)" & Chr(13) & "" & Chr(10) & "where (t.tagname like '" & maskF & "') --tagid = 32593" & Chr(13) & "" & Chr(10) & "and dbo.rmfconvertsecondsto" _
        , _
        "local(v.time) between ('" & st & "') and ('" & en & "')" & Chr(13) & "" & Chr(10) & "order by dbo.rmfconvertsecondstolocal(v.tim" _
        , "e)")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Таблица___MainDatabase3"
        .Refresh BackgroundQuery:=False
    End With
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=MainDatabase;Data Source=" _
        , _
        "4;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;" _
        , "Tag with column collation when possible=False"), Destination:=Range("$M$7") _
        ).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "select t.tagname, dbo.rmfconvertsecondstolocal(v.time) time, /*v.msec,*/ v.value, t.tagdescription from maindatabase_online_5.dbo.floatarchive" _
        , _
        " v" & Chr(13) & "" & Chr(10) & "join tag t on (v.tagid=t.tagid)" & Chr(13) & "" & Chr(10) & "where (t.tagname like '" & maskDP & "') --tagid = 32593" & Chr(13) & "" & Chr(10) & "and dbo.rmfconvertsecondsto" _
        , _
        "local(v.time) between ('" & st & "') and ('" & en & "')" & Chr(13) & "" & Chr(10) & "order by dbo.rmfconvertsecondstolocal(v.tim" _
        , "e)")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Таблица___MainDatabase4"
        .Refresh BackgroundQuery:=False
    End With
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=MainDatabase;Data Source=" _
        , _
        "4;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;" _
        , "Tag with column collation when possible=False"), Destination:=Range("$Q$7") _
        ).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "select t.tagname, dbo.rmfconvertsecondstolocal(v.time) time, /*v.msec,*/ v.value, t.tagdescription from maindatabase_online_5.dbo.floatarchive" _
        , _
        " v" & Chr(13) & "" & Chr(10) & "join tag t on (v.tagid=t.tagid)" & Chr(13) & "" & Chr(10) & "where (t.tagname like '" & mask174P & "') --tagid = 32593" & Chr(13) & "" & Chr(10) & "and dbo.rmfconvertsecondsto" _
        , _
        "local(v.time) between ('" & st & "') and ('" & en & "')" & Chr(13) & "" & Chr(10) & "order by dbo.rmfconvertsecondstolocal(v.tim" _
        , "e)")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Таблица___MainDatabase5"
        .Refresh BackgroundQuery:=False
    End With
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=MainDatabase;Data Source=" _
        , _
        "4;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;" _
        , "Tag with column collation when possible=False"), Destination:=Range("$U$7") _
        ).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "select t.tagname, dbo.rmfconvertsecondstolocal(v.time) time, /*v.msec,*/ v.value, t.tagdescription from maindatabase_online_5.dbo.floatarchive" _
        , _
        " v" & Chr(13) & "" & Chr(10) & "join tag t on (v.tagid=t.tagid)" & Chr(13) & "" & Chr(10) & "where (t.tagname like '" & mask174F & "') --tagid = 32593" & Chr(13) & "" & Chr(10) & "and dbo.rmfconvertsecondsto" _
        , _
        "local(v.time) between ('" & st & "') and ('" & en & "')" & Chr(13) & "" & Chr(10) & "order by dbo.rmfconvertsecondstolocal(v.tim" _
        , "e)")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Таблица___MainDatabase6"
        .Refresh BackgroundQuery:=False
    End With
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=MainDatabase;Data Source=" _
        , _
        "4;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;" _
        , "Tag with column collation when possible=False"), Destination:=Range("$Y$7") _
        ).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "select t.tagname, dbo.rmfconvertsecondstolocal(v.time) time, /*v.msec,*/ v.value, t.tagdescription from maindatabase_online_5.dbo.floatarchive" _
        , _
        " v" & Chr(13) & "" & Chr(10) & "join tag t on (v.tagid=t.tagid)" & Chr(13) & "" & Chr(10) & "where (t.tagname like '" & mask174T & "') --tagid = 32593" & Chr(13) & "" & Chr(10) & "and dbo.rmfconvertsecondsto" _
        , _
        "local(v.time) between ('" & st & "') and ('" & en & "')" & Chr(13) & "" & Chr(10) & "order by dbo.rmfconvertsecondstolocal(v.tim" _
        , "e)")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Таблица___MainDatabase7"
        .Refresh BackgroundQuery:=False
    End With
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=MainDatabase;Data Source=" _
        , _
        "4;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;" _
        , "Tag with column collation when possible=False"), Destination:=Range("$AC$7") _
        ).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "select t.tagname, dbo.rmfconvertsecondstolocal(v.time) time, /*v.msec,*/ v.value, t.tagdescription from maindatabase_online_5.dbo.floatarchive" _
        , _
        " v" & Chr(13) & "" & Chr(10) & "join tag t on (v.tagid=t.tagid)" & Chr(13) & "" & Chr(10) & "where (t.tagname like '" & mask184P & "') --tagid = 32593" & Chr(13) & "" & Chr(10) & "and dbo.rmfconvertsecondsto" _
        , _
        "local(v.time) between ('" & st & "') and ('" & en & "')" & Chr(13) & "" & Chr(10) & "order by dbo.rmfconvertsecondstolocal(v.tim" _
        , "e)")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Таблица___MainDatabase8"
        .Refresh BackgroundQuery:=False
    End With
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=sa;Initial Catalog=MainDatabase;Data Source=" _
        , _
        "4;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;" _
        , "Tag with column collation when possible=False"), Destination:=Range("$AG$7") _
        ).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "select t.tagname, dbo.rmfconvertsecondstolocal(v.time) time, /*v.msec,*/ v.value, t.tagdescription from maindatabase_online_5.dbo.floatarchive" _
        , _
        " v" & Chr(13) & "" & Chr(10) & "join tag t on (v.tagid=t.tagid)" & Chr(13) & "" & Chr(10) & "where (t.tagname like '" & maskFMET & "') --tagid = 32593" & Chr(13) & "" & Chr(10) & "and dbo.rmfconvertsecondsto" _
        , _
        "local(v.time) between ('" & st & "') and ('" & en & "')" & Chr(13) & "" & Chr(10) & "order by dbo.rmfconvertsecondstolocal(v.tim" _
        , "e)")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Таблица__MainDatabase9"
        .Refresh BackgroundQuery:=False
    End With
    End Sub
 
 
 
#2
19.09.2015 01:00:57
для ср. арифмет. по значению используйте функцию avg(field_name)
пример запроса
Код
?
1
2
3
4
select name, coment, avg(summa)
from my_table
where my_field = cryterii
group by name, coment
остальное смотрите в файле (который кстати вы должни были потрудиться создать, а не я)
перед выполнением запроса в модуле sql_zaprosy в строке "sCon2 = ..." надо указать свои параметры подключения к БД.
P.S.
Цитата
Есть ли вариант их сразу в необходимом формате запрашивать
Есть вариант - Round(avg(summa),2)
Не ленитесь использовать поиск - сейчас можно найти практически что угодно.
Изменено: Dima S - 19.09.2015 01:06:03
 
 
 
#3
19.09.2015 05:31:42
спасибо за помощь.
я просто не стал файл выкладывать, думая, что все равно запрос на мой сервер идет (опознавательные символы которого я поудалял) и не надеялся что кто то будет запускать на расчет макрос.  
 
 
 
#4
21.09.2015 08:35:40
Код
?
1
.ListObjects("Таблица1").Resize (Range(Cells(5, 1), Cells(ub + 5, 4))) 'Изменить размер под новый массив
протестировал предложенный вариант. 1 круг прогоняет и останавливается на строке
в чем может быть дело ? не подскажете?
Изменено: MEP - 21.09.2015 08:36:25
 
 
 
#5
21.09.2015 08:52:33
Доброе время суток
Можно по детальнее? Вы хотите объединить все запросы по маскам в одну таблицу на листе? На вскидку, у вас запросы отличаются только параметрами, не проще ли тогда создать хранимую процедуру и обращаясь к ней задавать параметры для получения результата? Да и, учитывая что у вас сервер БД, может стоти создать параметрезованное соединение с базой, которое будет обновляться при изменении ячеек-масок .
 
 
 
#6
21.09.2015 09:09:20
сразу поясню что я не программист, терминологию понимаю частично) так же изьясняюсь)))
была задача вытащить один параметр по маске из базы данных..далее аппетиты выросли и параметров стало 9 штук, но все они привязаны к единому времени (т.е.например каждые 10 секунд регистрируется 9 значений).
надо было с оптимизировать исходный код. подзадачей является то, что каждые 10 секунд - это слишком много значений на листе. и встал вопрос как вывести среднесуточные показания из базы.. вот как то так)
 
 
 
#7
21.09.2015 09:32:03
MEP, вопросы, собственно не о программировании - никто не требует от вас этого. А вот правильно поставить задачу, чтобы было понятно тем, кто не посвящён в структуру ваших данных, подобно как вы не посвящены в программирование.
Вопрос. У вас maskP maskT maskT maskF maskDP mask174P mask174F mask174T mask184P maskFMET - это теги, в терминах фильтра Excel вы отбираете только те данные, строки которых в столбце t.tagname начинатются/заканчиваются/содержат текстовые значения меток (детали, какой из видов отбора, видимо находится в содержимом ячеек). Тогда, что означает такая конструкция?
Код
?
1
(t.tagname like '" & maskFMET & "') --tagid = 32593"

Первая часть (t.tagname like '" & maskFMET & "') логическое значение (да преобразуемая к целому типу), а это --tagid? Что собственно фильтруется?
Второе, нужно ли объединять все результаты по maskP maskT maskT maskF maskDP mask174P mask174F mask174T mask184P maskFMET в одну таблицу или как и у вас их должно быть 9 штук.

Далее, на вывод сейчас идут поля
t.tagname, dbo.rmfconvertsecondstolocal(v.time) time, v.value, t.tagdescription
какие из них поля группировки, а какие собственно результаты группировки и какие функции вы предполагаете использовать для этих результатов: Avg, Sum, Count и т. д.?
 
 
 
#8
21.09.2015 09:46:37
в составленном Dima S примере он прямо в яблочко попал со структурой самого Эксель листа, пример которого к сожалению я сразу не выложил (не думал что он настолько понадобится). т.е. сами маски заданы в ячейках листа. так же там задан диапазон вывода по дате-времени. - это входные данные.

из выводных полей t.tagname, dbo.rmfconvertsecondstolocal(v.time) time, v.value, t.tagdescription только v.value должна усредняться за сутки, поле dbo.rmfconvertsecondstolocal(v.time) time представлено ДД.ММ.ГГГГ ЧЧ:ММ:СС -в таком формате.

таблицы (каждая состоит из 4х колонок) выводятся каждая рядом друг с другом (т.е. параллельно на листе), всего соответсвенно 9 таблиц.

про вопрос --tagid = 32593 я честно говоря невкурсе)
 
 
 
#9
21.09.2015 09:47:38
Цитата
MEP написал: 1.ListObjects("Таблица1").Resize (Range(Cells(5, 1), Cells(ub + 5, 4))
кстати в этой строке выяснил почему ошибка. он не понимает что за параметр "ub" прибавляется
 
 
 
#10
21.09.2015 10:38:51
Цитата
dbo.rmfconvertsecondstolocal(v.time) time представлено ДД.ММ.ГГГГ ЧЧ:ММ:СС -в таком формате.
И как вы себе представляете группировку по этому полю? И за сутки - какие заданные, например, в некоторой ячейке? Вычисляемое поле dbo.rmfconvertsecondstolocal(v.time) можно рассматривать как данные даты и времени наблюдений?
 
 
 
#11
21.09.2015 10:47:53
MEP, вы не могли бы всё таки приложить пример данных исходных таблиц в Excel, чтобы понять их структуру и связи, а то так долго можно переписываться. Я не столь догадлив как Dima S, увы.
 
 
 
#12
21.09.2015 10:54:24
dbo.rmfconvertsecondstolocal(v.time) - это дата и время, потому что запись на сервере ведется в таком формате (в формате юникс тайм)
по осреднению я пока представляю процесс так что от 00.00 часов предыдущих суток до 00.00 последующих суток берется среднеарифметическое.
я понимаю что задача по осреднению непростая, возможно придется потом инструментами экселя осреднять (типа сводной таблицы) на выгружаемый массив.

мне бы с переменной ub разобраться сейчас. из-за нее ошибка вываливается
 
 
 
#13
21.09.2015 11:06:05
прилепил пример конечного вида. то что мы хотим сделать.
спасибо за помощь
 
 
 
#14
21.09.2015 11:18:32
Не, я пас. Вы меня не понимаете, я вас. Как у вас на MS SQL Server отработали регулярные выражения (разве что кто-то функцию подогнал)? ub - просто не объявленная переменная, по видимому, подразумевалось UBound(arr) вместо неё.
Успехов.

P. S. У Dima S выводится только одна таблица со всеми записями, удовлетворяющими маскам тегов.
 
 
 
#15
21.09.2015 11:54:52
MEP, добрый день. А можно попросить Вас приложить еще кусок базы (естественно, а таблице Excel :)), из которой Вы выбираете данные и на основе этой базы построить конечный вид Вашей "хотелки"?
Изменено: Smiley - 21.09.2015 11:57:21
Учусь программировать :)
 
 
 
#16
21.09.2015 12:24:01
) да, согласен с Вами. правильно сформулировать задачу - это наверно полдела. кусок базы не могу к сожалению выложить, не дадут мне ее выгрузить.
что ж. и на том спасибо. по крайней мере из диалога я для себя много чего нового вынес, почитал книжки еще. если получится добить вопрос, то выложу тот пример который будет работать для решения моей задачи.
спасибо
 
 
 
#17
21.09.2015 12:27:39
MEP, ну так Вы же ее можете придумать на несуществующих данных :) Я вот, к примеру, просто не могу понять что именно требуется, а хотелось бы именно понять сам алгоритм.
Изменено: Smiley - 21.09.2015 12:28:14
Учусь программировать :)
 
 
 
#18
21.09.2015 12:47:22
я по простоте душевной в данном случае надеялся на универсальное решение )  
 
 
 
#19
22.09.2015 02:23:42
мда.. пропустил я присвоение значения переменной ub ))
но ув. Андрей VG правильно вам подсказал)
в файле исправил немного строку запроса - с REGEXP вам лучше не связываться  (не потому что плохая, а потому что посложнее и маски придется другие писать)  - вернул LIKE

--tagid = 32593 - это комментарий, который не влияет на запрос.
и да - макрос выводит все записи подходящие хотя бы под одну из этих масок в общую таблицу.
в принципе можно сделать и в разные для каждой маски, но я бы не советовал - больше геморроя при обработке плюс возможны дубликаты записей, удовлетворяющих несколько масок.

дальше - что касается среднего значения за сутки:
для этого в строке
Код
?
1
select t.tagname, dbo.rmfconvertsecondstolocal(v.time) time, v.value, t.tagdescription
надо изменить
Код
?
1
select t.tagname, date(dbo.rmfconvertsecondstolocal(v.time)) time, round(avg(v.value),2), t.tagdescription
(сразу ваш пункт 3 выполняется)
а перед
Код
?
1
order by dbo.rmfconvertsecondstolocal(v.time)
нужно добавить
Код
?
1
group by t.tagname, time, t.tagdescription

но при этом надо понимать, что среднее будет в разрезе, указанном в группировке.

в файле группировку не делал - попробуете сами если будет желание.
Изменено: Dima S - 22.09.2015 02:24:24
 
 
 
#20
22.09.2015 03:27:05
Доброе время суток
Dima S, спасибо за
Цитата
--tagid = 32593 - это комментарий, который не влияет на запрос.
что то я "затупил"
Цитата
group by t.tagname
с этим то как раз и сложности - ту же нужно нечто обобщённое, раз отбор идёт по (t.tagname like '" & mask174P & "') - что нибудь
Код
?
1
Case When (t.tagname like '" & mask174P & "') Then 'mask174P' End As TagName
, хотя может я и не прав.
Цитата
MEP написал:
я по простоте душевной в данном случае надеялся на универсальное решение )
Универсальное решение есть SQL-запрос к базе данных (или хранимая процедура). Результат которой отображается на листе Excel как умная таблица. Это же можно сделать и с управлением параметрами через значения ячеек.
 
 
 
#21
23.09.2015 06:58:40
спасибо за то что не бросаете наедине со своими скупыми познаниями)


в новом коде в этой строке ошибка type mismatch.какой тип надо присвоить переменной ub вначале? или дело не в этом.
Код
?
1
.ListObjects("таблица1").Resize (Range(Cells(5, 1), Cells(ub + 5, 4)))
 
 
 
#22
23.09.2015 09:06:45
Доброе время суток
MEP, я уже писал, что ub это
Цитата
подразумевалось UBound(arr)
Цитата
со своими скупыми познаниями
Так умейте пользоваться нашими ;) . А то получается мы вам вопросы. А вы пример исходных строк по 20 с каждой таблицы не можете выложить. Уже бы общими усилиями бы сделали.
 
 
 
#23
23.09.2015 10:05:32
все таблицы с единообразными данными вываливаются в виде:

tagnametime value tagdescript
FIP10_101.06.2014 00:10 5.32 РАЗГР
и так каждые 10 секунд
Изменено: MEP - 23.09.2015 10:08:36
 
 
 
Страницы: 1
Читают тему
Loading...