返回列表 上一主題 發帖

[發問] VBA SQL 取得時間間隔 的其他欄位之平均值

Scott090

帖子
523 
主題
56 
精華
0 
積分
601 
點名
61  
作業系統
win 10 
軟體版本
[] 
閱讀權限
50 
性別
男 
註冊時間
2013-3-19 
最後登錄
2025-3-14 

2024年全勤獎章2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章論壇榮譽勳章論壇贊助勳章2015羊年全勤獎章
1# 跳轉到 » 倒序看帖
打印
tT
發表於 2015-7-26 08:14 | 顯示全部帖子

[發問] VBA SQL 取得時間間隔 的其他欄位之平均值

請指點迷津：

下面的 SQL 字串 結構無法得到目的； 資料如附件(原檔案資料甚大，只截取少量)
  1. Option Explicit

  3. '目的： 每間隔 10 分鐘、每一個 CTcode 做 欄位 Volts,Hz 的平均值，
  4. '       並依已間隔之時間序及CTcode 序呈現結果

  6. Sub ExcelSQL()
  7.     Dim SQL As String, TimeIntervalStr$
  8.     Dim j%, r%
  9.     Dim cnn As ADODB.Connection
  10.     Dim rs As ADODB.Recordset
  11.     Dim ws As Worksheet
  12.     Dim StartTime#
  13.     Set ws = Sheets("Temp")
  14.    
  15.     StartTime = Timer
  16.         Set cnn = New ADODB.Connection
  17.         With cnn
  18.             .Provider = "Microsoft.ACE.OLEDB.12.0"
  19.             .ConnectionString = "Extended Properties= Excel 12.0;" _
  20.                 & "Data Source=" & ThisWorkbook.FullName
  21.             .Open
  22.         End With
  23.         Set rs = New ADODB.Recordset

  25. ''        SQL = " select * from [CT$] where [日期時間] between #2015/6/1# and #2015/6/2#"
  26.         
  27.         TimeIntervalStr = " DateAdd(" + """n""" + ",10,[日期時間] )"        '每間隔10分鐘
  28.         
  29.         SQL = "SELECT" + TimeIntervalStr + " as [DateTime],avg(Volts) as avgVolt,AVg(Hz) as avgHz " & _
  30.             "from [CT$] " & _
  31.             "where [日期時間] between #2015/06/01# and #2015/06/02# " & _
  32.             "Group by [DateTime],CTcode " & _
  33.             "Order by [DateTime]"
  34.         
  35.         Debug.Print SQL
  36.         rs.Open SQL, cnn
  37.         
  38.     With ws
  39.         .Cells.Clear
  40.         For j = 0 To rs.Fields.Count - 1
  41.             .Cells(1, j + 1) = rs.Fields(j).Name
  42.         Next
  43.         r = .Cells(.Rows.Count, 1).End(xlUp).Row
  44.         .Range("A" & r + 1).CopyFromRecordset rs
  45.     End With
  46.     rs.Close
  47.     cnn.Close
  48.     Set rs = Nothing
  49.     Set cnn = Nothing
  50.     Debug.Print Timer - StartTime
  51. End Sub
複製代碼
CT.rar (431.9 KB)
收藏 分享

Scott090

帖子
523 
主題
56 
精華
0 
積分
601 
點名
61  
作業系統
win 10 
軟體版本
[] 
閱讀權限
50 
性別
男 
註冊時間
2013-3-19 
最後登錄
2025-3-14 

2024年全勤獎章2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章論壇榮譽勳章論壇贊助勳章2015羊年全勤獎章
2#
發表於 2015-7-28 16:48 | 顯示全部帖子
回復 1# Scott090


    尚待大俠高手伸出援手

感恩

TOP

Scott090

帖子
523 
主題
56 
精華
0 
積分
601 
點名
61  
作業系統
win 10 
軟體版本
[] 
閱讀權限
50 
性別
男 
註冊時間
2013-3-19 
最後登錄
2025-3-14 

2024年全勤獎章2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章論壇榮譽勳章論壇贊助勳章2015羊年全勤獎章
3#
發表於 2015-7-29 23:01 | 顯示全部帖子
回復 3# GBKEE

感謝指引
從 1# 的程式碼 :
            SQL = " select * from [CT$] where [日期時間] between #2015/6/1# and #2015/6/2#"
的實驗，基本的ADO連結設定沒有問題；
問題是  '每間隔10分鐘要取其其他欄位的平均值 這樣的 資料擷取 的 SQL 字串結構要如何做，才能達到目的。
以下是在 1# 內的問題所在。
想過 用子查詢，但未能構思其邏輯。
        TimeIntervalStr = " DateAdd(" + """n""" + ",10,[日期時間] )"        '每間隔10分鐘        
        SQL = "SELECT" + TimeIntervalStr + " as [DateTime],avg(Volts) as avgVolt,AVg(Hz) as avgHz " & _
            "from [CT$] " & _
            "where [日期時間] between #2015/06/01# and #2015/06/02# " & _
            "Group by [DateTime],CTcode " & _
            "Order by [DateTime]"

尚請繼續協助

TOP

Scott090

帖子
523 
主題
56 
精華
0 
積分
601 
點名
61  
作業系統
win 10 
軟體版本
[] 
閱讀權限
50 
性別
男 
註冊時間
2013-3-19 
最後登錄
2025-3-14 

2024年全勤獎章2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章論壇榮譽勳章論壇贊助勳章2015羊年全勤獎章
4#
發表於 2015-7-30 06:48 | 顯示全部帖子
回復 5# GBKEE


    謝謝大師熱心地繼續指引

繼續研究中，有可能的方向是先用一 SQL 處理 [日期時間]分別成 "10"分鐘間隔的組，
再用另一SQL 整理出其他的平均值

有結論再報告大師

TOP

Scott090

帖子
523 
主題
56 
精華
0 
積分
601 
點名
61  
作業系統
win 10 
軟體版本
[] 
閱讀權限
50 
性別
男 
註冊時間
2013-3-19 
最後登錄
2025-3-14 

2024年全勤獎章2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章論壇榮譽勳章論壇贊助勳章2015羊年全勤獎章
5#
發表於 2015-8-9 15:01 | 顯示全部帖子
回復 5# GBKEE


    如下的 ADO recordset 方式可以達到目的：
1.先處理時間欄位使其可成為分組的新欄位
2.以時間組處理分組的數值欄位平均值
但還是期望用一個 SQL 字串完成目的
  1. '目的： 每間隔 10 分鐘、每一個 CTcode 做 欄位 Volts,Hz 的平均值，
  2. '       並依已間隔之時間序及CTcode 序呈現結果
  3. Option Explicit
  4. Option Base 1
  5. Option Private Module

  7. 'Definitions for ADODB SQL
  8.     Public cnn As ADODB.Connection
  9.     Public rs As ADODB.Recordset
  10.     Public SQL As String, FileName$
  11.     Public ws1 As Worksheet
  12.     Public ws2 As Worksheet
  13.     Public TimeInterval#
  14.     Public Time0#
  15.     Public StartTime$, EndTime$, TimeStr$


  18. Sub ExcelSQL()

  20.     Dim i&, j%
  21.     Set ws1 = Sheets("CT")
  22.     Set ws2 = Sheets("Temp")
  23.     ws2.Cells.Clear
  24.    
  25.     FileName = ThisWorkbook.FullName
  26.    
  27.     ws1.Select
  28.     StartTime = Format(Cells(2, 1), "yyyy/mm/dd hh:mm")
  29.     EndTime = Format(Cells([A1].CurrentRegion.Rows.Count, 1), "yyyy/mm/dd hh:mm")
  30.     TimeInterval = 10   '間隔 10 分鐘
  31.    
  32.     Time0 = Timer
  33.    
  34. '=== 賦予時間間格的新欄位 =============
  35.     Set cnn = New ADODB.Connection
  36.         cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
  37.         cnn.ConnectionString = "Data Source=" & FileName & ";Extended Properties=""Excel 12.0 xml;HDR=Yes;"""
  38.         cnn.Open
  39.             
  40.     SQL = "Select format( iif(minute([日期時間]) mod " & TimeInterval & " = 0,[日期時間], " & _
  41.                 "DateAdd(""n"", " & TimeInterval & "-minute([日期時間]) mod " & TimeInterval & _
  42.                 " ,[日期時間])),""yyyy/mm/dd HH:mm"") " & _
  43.     " From [" & ws1.Name & "$]" & _
  44.     " where [日期時間] >=  #" & StartTime & "#  " & _
  45.     " order by [日期時間]"
  46.    
  47.     Set rs = cnn.Execute(SQL)
  48.    
  49.     With ws1
  50.         j = .[A1].End(xlToRight).Column
  51.         .Cells(1, j + 1) = "DateTime1"
  52.         .Cells(2, j + 1).CopyFromRecordset rs
  53.     End With
  54.     rs.Close
  55.     cnn.Close
  56.     Set rs = Nothing
  57.     Set cnn = Nothing

  59. '=== 處理數值欄位的平均 ===========================
  60.     Set cnn = New ADODB.Connection
  61.         cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
  62.         cnn.ConnectionString = "Data Source=" & FileName & ";Extended Properties=""Excel 12.0 xml;HDR=Yes;"""
  63.         cnn.Open
  64.     SQL = "SELECT format([DateTime1],""yyyy/mm/dd HH:mm"") as [DateTime], CTCode, " & _
  65.                 "AVG([Volts]) as [avgVolt], AVG([Hz]) as [avgHz], AVG([kW]) as [avgkW] " & _
  66.         " From [" & ws1.Name & "$] " & _
  67.         " Where [日期時間] >=  #" & StartTime & "# and [DateTime1] <= #" & EndTime & "# " & _
  68.         " Group by [DateTime1],CTCode " & _
  69.         " Order by [DateTime1] ,CTCode"
  70.     Debug.Print SQL
  71.    
  72.     Set rs = cnn.Execute(SQL)
  73.    
  74.     With ws2
  75.         For i = 0 To rs.Fields.Count - 1
  76.             .Cells(1, i + 1) = rs.Fields(i).Name
  77.         Next
  78.         .Cells(2, 1).CopyFromRecordset rs
  79.     End With
  80.    
  81.     ws1.Select
  82.     Cells(1, ws1.UsedRange.Columns.Count).EntireColumn.Clear
  83.    
  84.     rs.Close
  85.     cnn.Close
  86.     Set rs = Nothing
  87.     Set cnn = Nothing
  88. '    Debug.Print Timer - Time0
  89. End Sub
複製代碼

TOP

Scott090

帖子
523 
主題
56 
精華
0 
積分
601 
點名
61  
作業系統
win 10 
軟體版本
[] 
閱讀權限
50 
性別
男 
註冊時間
2013-3-19 
最後登錄
2025-3-14 

2024年全勤獎章2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章論壇榮譽勳章論壇贊助勳章2015羊年全勤獎章
6#
發表於 2015-8-12 23:43 | 顯示全部帖子
回復 8# GBKEE

謝謝超版的指導
試執行的時間約 40秒
學習到：
1.日期時間用 Double處理
2.每一時間間格做一次的 SQL 查詢

有一點請教
    "'建議不要 Source=" & ThisWorkbook.FullName 在這資料庫活頁簿中執行此巨集,很耗記憶體"
用甚麼方法能減少記憶體的使用?
請明示指導

TOP

Scott090

帖子
523 
主題
56 
精華
0 
積分
601 
點名
61  
作業系統
win 10 
軟體版本
[] 
閱讀權限
50 
性別
男 
註冊時間
2013-3-19 
最後登錄
2025-3-14 

2024年全勤獎章2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章論壇榮譽勳章論壇贊助勳章2015羊年全勤獎章
7#
發表於 2015-8-13 22:59 | 顯示全部帖子
回復 10# GBKEE

謝謝超版
用你指導的方法不用 ThisworkBook.fullname，跑一次是約 11秒快了許多

另外請教，Excel 資料庫要新增一欄位，用了 Alter Table [CT$] add NewColumn char(10) 得到運算無效的信息
ADO 不支援 Alter TABLE 嗎?
不曉得問題在哪裡，敬請指導
  1. Sub ExcelAddColumn()
  2.     Dim SQL As String
  3.     Dim j%, r%
  4.     Dim cnn As ADODB.Connection
  5.     Dim rs As ADODB.Recordset
  6.     Dim ws As Worksheet
  7.    
  8.     Time0 = Timer
  9.     Set ws = ThisWorkbook.Sheets(1)
  10.     Set cnn = New ADODB.Connection
  11.    
  12.     With cnn
  13.             .Provider = "Microsoft.ACE.OLEDB.12.0"
  14.               .ConnectionString = "Extended Properties= Excel 12.0;" _
  15.                 & "Data Source=D:\VBA SQL.xlsm"
  16.         '建議不要 Source=" & ThisWorkbook.FullName 在這資料庫活頁簿中執行此巨集,很耗記憶體
  17.         '**********************************************************
  18. '        '2003版 引用：microsoft activex data objects 2.x library
  19. '        .Provider = "microsoft.jet.oledb.4.0"
  20. '        .ConnectionString = "Extended Properties= Excel 8.0;" _
  21. '        & "Data Source=D:\VBA SQL.xls"
  22. '        '建議不要 Source=" & ThisWorkbook.FullName 在這資料庫活頁簿中執行此巨集,很耗記憶體
  23.         .Open
  24.     End With
  25.    
  26.     SQL = "Alter Table [CT$] ADD NewColumn char(10)"   '要增加一欄位名稱
  27.    
  28. '' 出現"無效的運算" 錯誤
  29.     cnn.Execute SQL
  30.    
  31.     Set rs = New ADODB.Recordset
  32.    
  33.     SQL = "select * from [CT$] "
  34.     rs.Open SQL, cnn
  35.     Debug.Print rs.Fields.Count
  36.    
  37. End Sub
複製代碼

TOP

Scott090

帖子
523 
主題
56 
精華
0 
積分
601 
點名
61  
作業系統
win 10 
軟體版本
[] 
閱讀權限
50 
性別
男 
註冊時間
2013-3-19 
最後登錄
2025-3-14 

2024年全勤獎章2023年全勤獎章2022虎年全勤獎章2021牛年全勤獎章2020鼠年全勤獎章2019 豬年全勤獎章2018 狗年全勤獎章2017 雞年全勤獎章2016猴年全勤獎章論壇榮譽勳章論壇贊助勳章2015羊年全勤獎章
8#
發表於 2015-8-23 10:55 | 顯示全部帖子
回復 8# GBKEE


    以一個SQL敘述完成需求終於可以達成如下，供參考； 謝謝
  1. Sub ExcelSQL2()

  3.     Dim i&, j%
  4.     Dim cnn As ADODB.Connection
  5.     Dim rs As ADODB.Recordset
  6.     Dim SQL As String, FileName$
  7.     Dim ws1 As Worksheet
  8.     Dim ws2 As Worksheet
  9.     Dim TimeInterval#
  10.     Dim Time0#
  11.     Dim StartTime, EndTime, TimeStr$
  12.    
  13.     Set ws1 = Sheets("CT")
  14.     Set ws2 = Sheets("Temp")
  15.     ws2.Cells.Clear
  16.    
  17.     FileName = ThisWorkbook.FullName
  18.    
  19.     ws1.Select
  20. '    StartTime = #1/1/2015 12:00:01 AM#
  21. '    EndTime = #12/31/2030#
  22. '    StartTime = CDbl(StartTime)
  23. '    EndTime = CDbl(EndTime)
  24.     StartTime = "2015/01/01 00:00:01"
  25.    
  26.     TimeInterval = 10   '間隔 10 分鐘
  27.    
  28. '    Time0 = Timer
  29.     TimeStr = " format( iif(MINUTE([日期時間]) mod " & TimeInterval & " = 0,[日期時間], " & _
  30.                 "DateAdd(""n"", " & TimeInterval & "-MINUTE([日期時間]) mod " & TimeInterval & _
  31.                 " ,[日期時間])),""yyyy/mm/dd hh:mm"") "
  32.                
  33. '=== 處理數值欄位的每 10分鐘平均 ===========================
  34.     Set cnn = New ADODB.Connection
  35.         cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
  36.         cnn.ConnectionString = "Data Source=" & FileName & ";Extended Properties=""Excel 12.0 xml;HDR=Yes;"""
  37.         cnn.Open
  38.    
  39.       SQL = "SELECT " + TimeStr + " as [DateTime], CTcode, " & _
  40.                 "AVG([Volts]) as [avgVolt], AVG([Hz]) as [avgHz] " & _
  41.         " From [" & ws1.Name & "$] " & _
  42.         " Where [日期時間] >= #" & StartTime & "# " & _
  43.         " Group by " + TimeStr + " ,CTcode " & _
  44.         " Order by " + TimeStr + " ,CTcode"
  45.         
  46.     Set rs = cnn.Execute(SQL)
  47.    
  48.     With ws2
  49.         For i = 0 To rs.Fields.Count - 1
  50.             .Cells(1, i + 1) = rs.Fields(i).Name
  51.         Next
  52.         .Cells(2, 1).CopyFromRecordset rs
  53.     End With
  54.       
  55.     rs.Close
  56.     cnn.Close
  57.     Set rs = Nothing
  58.     Set cnn = Nothing
  59. '    Debug.Print Timer - Time0
  60. End Sub
複製代碼

TOP

        靜思自在 : 【時間無法遮擋】怕時間消逝，花了許多心血，想盡各式方法要遮擋時間，結果是：浪費了更多時間，且一無所成！
返回列表 上一主題