Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Оптимизация SQL запроса через VBA и экспорт данных в Excel
 
#1
18.09.2015 15:22:29
Доброго дня всем.
Имеется следующая задача.
Есть база данных 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
 
 
 
#2
19.09.2015 01:00:57
для ср. арифмет. по значению используйте функцию 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
 
 
 
#3
19.09.2015 05:31:42
спасибо за помощь.
я просто не стал файл выкладывать, думая, что все равно запрос на мой сервер идет (опознавательные символы которого я поудалял) и не надеялся что кто то будет запускать на расчет макрос.  
 
 
 
#4
21.09.2015 08:35:40
Код
.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 начинатются/заканчиваются/содержат текстовые значения меток (детали, какой из видов отбора, видимо находится в содержимом ячеек). Тогда, что означает такая конструкция?
Код
(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 - это комментарий, который не влияет на запрос.
и да - макрос выводит все записи подходящие хотя бы под одну из этих масок в общую таблицу.
в принципе можно сделать и в разные для каждой маски, но я бы не советовал - больше геморроя при обработке плюс возможны дубликаты записей, удовлетворяющих несколько масок.

дальше - что касается среднего значения за сутки:
для этого в строке
Код
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
 
 
 
#20
22.09.2015 03:27:05
Доброе время суток
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 как умная таблица. Это же можно сделать и с управлением параметрами через значения ячеек.
 
 
 
#21
23.09.2015 06:58:40
спасибо за то что не бросаете наедине со своими скупыми познаниями)


в новом коде в этой строке ошибка type mismatch.какой тип надо присвоить переменной ub вначале? или дело не в этом.
Код
.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
Читают тему
Наверх