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

Код
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
 
для ср. арифмет. по значению используйте функцию avg(field_name)
пример запроса
Код
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
 
спасибо за помощь.
я просто не стал файл выкладывать, думая, что все равно запрос на мой сервер идет (опознавательные символы которого я поудалял) и не надеялся что кто то будет запускать на расчет макрос.  
 
Код
.ListObjects("Таблица1").Resize (Range(Cells(5, 1), Cells(ub + 5, 4))) 'Изменить размер под новый массив
протестировал предложенный вариант. 1 круг прогоняет и останавливается на строке
в чем может быть дело ? не подскажете?
Изменено: MEP - 21.09.2015 08:36:25
 
Доброе время суток
Можно по детальнее? Вы хотите объединить все запросы по маскам в одну таблицу на листе? На вскидку, у вас запросы отличаются только параметрами, не проще ли тогда создать хранимую процедуру и обращаясь к ней задавать параметры для получения результата? Да и, учитывая что у вас сервер БД, может стоти создать параметрезованное соединение с базой, которое будет обновляться при изменении ячеек-масок .
 
сразу поясню что я не программист, терминологию понимаю частично) так же изьясняюсь)))
была задача вытащить один параметр по маске из базы данных..далее аппетиты выросли и параметров стало 9 штук, но все они привязаны к единому времени (т.е.например каждые 10 секунд регистрируется 9 значений).
надо было с оптимизировать исходный код. подзадачей является то, что каждые 10 секунд - это слишком много значений на листе. и встал вопрос как вывести среднесуточные показания из базы.. вот как то так)
 
MEP, вопросы, собственно не о программировании - никто не требует от вас этого. А вот правильно поставить задачу, чтобы было понятно тем, кто не посвящён в структуру ваших данных, подобно как вы не посвящены в программирование.
Вопрос. У вас maskP maskT maskT maskF maskDP mask174P mask174F mask174T mask184P maskFMET - это теги, в терминах фильтра Excel вы отбираете только те данные, строки которых в столбце t.tagname начинатются/заканчиваются/содержат текстовые значения меток (детали, какой из видов отбора, видимо находится в содержимом ячеек). Тогда, что означает такая конструкция?
Код
(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 и т. д.?
 
в составленном Dima S примере он прямо в яблочко попал со структурой самого Эксель листа, пример которого к сожалению я сразу не выложил (не думал что он настолько понадобится). т.е. сами маски заданы в ячейках листа. так же там задан диапазон вывода по дате-времени. - это входные данные.

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

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

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

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

P. S. У Dima S выводится только одна таблица со всеми записями, удовлетворяющими маскам тегов.
 
MEP, добрый день. А можно попросить Вас приложить еще кусок базы (естественно, а таблице Excel :)), из которой Вы выбираете данные и на основе этой базы построить конечный вид Вашей "хотелки"?
Изменено: Smiley - 21.09.2015 11:57:21
Учусь программировать :)
 
) да, согласен с Вами. правильно сформулировать задачу - это наверно полдела. кусок базы не могу к сожалению выложить, не дадут мне ее выгрузить.
что ж. и на том спасибо. по крайней мере из диалога я для себя много чего нового вынес, почитал книжки еще. если получится добить вопрос, то выложу тот пример который будет работать для решения моей задачи.
спасибо
 
MEP, ну так Вы же ее можете придумать на несуществующих данных :) Я вот, к примеру, просто не могу понять что именно требуется, а хотелось бы именно понять сам алгоритм.
Изменено: Smiley - 21.09.2015 12:28:14
Учусь программировать :)
 
я по простоте душевной в данном случае надеялся на универсальное решение )  
 
мда.. пропустил я присвоение значения переменной ub ))
но ув. Андрей VG правильно вам подсказал)
в файле исправил немного строку запроса - с REGEXP вам лучше не связываться  (не потому что плохая, а потому что посложнее и маски придется другие писать)  - вернул LIKE

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

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

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

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


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

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