返回列表 上一主題 發帖

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

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

請指點迷津:

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

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

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

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

回復 1# Scott090


    尚待大俠高手伸出援手

感恩

TOP

感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限,  一天1元的贊助,人人有能力.

TOP

回復 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

回復 4# Scott090

參考一下
    http://www.1keydata.com/tw/sql/sqlalias.html
感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限,  一天1元的贊助,人人有能力.

TOP

回復 5# GBKEE


    謝謝大師熱心地繼續指引

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

有結論再報告大師

TOP

回復 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

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


  15. Sub ExcelSQL()

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

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

TOP

回復 7# Scott090
我使用2003版
  1. Option Explicit
  2. Sub ExcelSQL()
  3.     Dim SQL As String, TimeIntervalStr As Double
  4.     Dim j%, r%
  5.     Dim cnn As ADODB.Connection
  6.     Dim rs As ADODB.Recordset
  7.     Dim ws As Worksheet
  8.     Set ws = ThisWorkbook.Sheets(1)
  9.     Set cnn = New ADODB.Connection
  10.     With cnn
  11.             '.Provider = "Microsoft.ACE.OLEDB.12.0"
  12.           '    .ConnectionString = "Extended Properties= Excel 12.0;" _
  13.                 & "Data Source=" & ThisWorkbook.FullName
  14.         '建議不要 Source=" & ThisWorkbook.FullName 在這資料庫活頁簿中執行此巨集,很耗記憶體
  15.         '**********************************************************
  16.         '2003版 引用:microsoft activex data objects 2.x library
  17.         .Provider = "microsoft.jet.oledb.4.0"
  18.         .ConnectionString = "Extended Properties= Excel 8.0;" _
  19.         & "Data Source=D:\VBA SQL.xls"
  20.         '建議不要 Source=" & ThisWorkbook.FullName 在這資料庫活頁簿中執行此巨集,很耗記憶體
  21.         .Open
  22.     End With
  23.     Set rs = New ADODB.Recordset
  24.     SQL = "select * from [ct$] "
  25.     rs.Open SQL, cnn
  26.     With ws
  27.         .[A1].CopyFromRecordset rs
  28.          TimeIntervalStr = .[A1]   '取得第一個時間
  29.         .UsedRange.Clear
  30.     End With
  31.     Do
  32.         Set rs = New ADODB.Recordset
  33.         SQL = "SELECT CTcode, avg(test.Volts) as Volts平均,avg(test.Hz) as Hz平均 from [CT$] " & _
  34.             " as test where 日期時間 >=" & TimeIntervalStr & " AND 日期時間 <" & CDbl(DateAdd("n", 10, TimeIntervalStr)) & "  Group by CTcode "
  35.         rs.Open SQL, cnn, adOpenStatic, adLockReadOnly
  36.         If rs.RecordCount Then   '讀取紀錄
  37.             With ws
  38.                 If .UsedRange.Count = 1 Then
  39.                     .Cells(1) = "日期 時間"
  40.                     For j = 0 To rs.Fields.Count - 1
  41.                         .Cells(1, j + 2) = rs.Fields(j).Name
  42.                     Next
  43.                 End If
  44.                 r = .Cells(.Rows.Count, 2).End(xlUp).Row
  45.                 .Range("B" & r + 1).CopyFromRecordset rs
  46.                 r = .Cells(.Rows.Count, 2).End(xlUp).Row
  47.                 With .Range("A" & Rows.Count).End(xlUp).Offset(1)
  48.                     .Resize(r - .Row + 1) = DateAdd("n", 0, TimeIntervalStr) & vbLf & DateAdd("n", 10, TimeIntervalStr)
  49.                 End With
  50.             End With
  51.         End If
  52.         TimeIntervalStr = CDbl(DateAdd("n", 10, TimeIntervalStr)) '下一個10分鐘
  53.     Loop Until rs.RecordCount = 0  '無紀錄
  54.     rs.Close
  55.     cnn.Close
  56.     Set rs = Nothing
  57.     Set cnn = Nothing
  58. End Sub
複製代碼
感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限,  一天1元的贊助,人人有能力.

TOP

回復 8# GBKEE

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

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

TOP

回復 9# Scott090
如ThisWorkbook是資料庫,光是載入就很佔記憶體.
感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限,  一天1元的贊助,人人有能力.

TOP

        靜思自在 : 我們要做好社會的環保,也要做好內心的環保。
返回列表 上一主題