Board logo

標題: 分組前三名語法 [打印本頁]
作者: joey0415    時間: 2014-1-15 15:48     標題: 分組前三名語法

小弟有一個sqlite資料庫，想要分組取前三名，例如某股票、某券商，最大量前三名，但該資料庫並不支援分組取三名的語法，可能是小弟功力太差
想透過excel vba 來取出值，不知道有什麼方式會比較適合，如果方便，請指點一下程式碼

表上前四欄可以透過sql查詢，將資料放進陣列中，如

arr = cN.Execute(sql).GetRows

附加檔案只有數十筆，其實單日就要近三十萬筆，若正確查出以1000多檔股票加上前三名，查詢總數會有約5000筆左右

若是樞鈕分析的方法也行

謝謝
[attach]17262[/attach]

[attach]17263[/attach]
作者: stillfish00    時間: 2014-1-16 10:38

回復 1# joey0415
sql分組語法，試看看
  1. SELECT a.日期,a.股票,a.券商,a.數量, COUNT(*) AS 排名
  2. FROM 資料表 as a
  3. INNER JOIN 資料表 as b
  4. ON (a.股票=b.股票) AND (a.數量<=b.數量)
  5. GROUP BY a.日期,a.股票,a.券商,a.數量
  6. HAVING COUNT(*) <=3
  7. ORDER BY a.股票,COUNT(*)
複製代碼
不過三十萬筆.....，我不知這樣的語法效率是不是足夠 = =
作者: GBKEE    時間: 2014-1-16 11:31

回復 1# joey0415

試試看
  1. Option Explicit
  2. Sub Ex()
  3.     Dim AR(), E As Variant, Rng As Range
  4.     With Sheets("Sheet1")
  5.         .Range("A1").CurrentRegion.Sort Key1:=.Range("B2"), Order1:=xlAscending, Key2:=.Range( _
  6.         "A2"), Order2:=xlAscending, Key3:=.Range("D2"), Order3:=xlDescending, Header:=xlYes
  7.         '.Range("A1").CurrentRegion.Sort"資料的排序
  8.         .UsedRange.Columns(2).AdvancedFilter xlFilterCopy, , .Cells(1, .Columns.Count), True
  9.         'UsedRange.Columns(2)的進階篩選->  不重複的股票
  10.         
  11.         '**三十萬筆資料 這兩行會慢一點
  12.         
  13.         AR = Application.Transpose(.Cells(1, .Columns.Count).CurrentRegion.Offset(1))
  14.         '取得股票置入一維陣列中
  15.         ReDim Preserve AR(1 To UBound(AR) - 1)          '刪除最後一筆的(空白)資料
  16.         .Cells(1, .Columns.Count).CurrentRegion.Clear   '清除
  17.         .Range("F1").CurrentRegion.Offset(1).Clear      '清除顯示前三筆資料的區域
  18.         For Each E In AR
  19.             Set Rng = .Range("B:B").Find(E, LookAT:=xlWhole)  '找每一股票的第一個位置
  20.             .Range(.Cells(Rng.Row, "A"), .Cells(Rng.Row + 2, "D")).Copy .Cells(.Rows.Count, "F").End(xlUp).Offset(1)
  21.         Next
  22.     End With
  23. End Sub
複製代碼
作者: joey0415    時間: 2014-1-16 12:26

回復 2# stillfish00

感謝大大提供的方法，這樣的串接法，在小筆數時，應該還行，如果是30萬x30萬這樣串就不太好！

目前是利用sql讀出30萬筆，用vba以迴圈的方式，如果是前三名的就保留，從30萬筆中找出來放進excel中，這樣不用幾秒

只是個人迴圈方式寫的不漂亮…
目前看來在資料庫中有分組後取排名值功能的
oracle
sql server
postgresql
本來想轉過去用，不過postgresql連接vba
我還不行
所以目前還先這樣用
作者: joey0415    時間: 2014-1-16 13:22

本帖最後由 joey0415 於 2014-1-16 13:24 編輯

回復 3# GBKEE

超版大您好

您的方法可以用
可以再請教一點！
sql查詢出來的資料是一個二維陣列陣列中第一維定義Recordset的欄位（資料行），第二維定義Recordset的資料列。如圖
[attach]17270[/attach]

請用有方法可以將那些資料以您的方法或迴圈的方式，將分組前三名的資料放進另一個新的一維陣列中嗎？
我想查詢在放在陣列，篩選也在陣列，最後直接在寫進資料庫應該會比貼在cells上會快才對？

欄位如下
日期，股票代碼，買量，賣量，淨買賣

以下是我寫的，可能會有些問題，超版的沒有問題！
謝謝
  1.     Worksheets("sheet2").Activate
  2. '    r = 2 '計算總列數
  3.     a = 1 '取值後總列數
  4.     t = 1 '取前三名
  5.     sname = arr(1, r)
  6.     For r = LBound(arr, 2) To UBound(arr, 2)
  7.         If arr(1, r) = sname Then
  8.                 If t <= 3 Then
  9.                 Cells(a, 5) = arr(0, r)
  10.                 Cells(a, 6) = arr(1, r)
  11.                 Cells(a, 7) = arr(5, r)
  12.                 t = t + 1
  13.                 a = a + 1
  14.             End If
  15.         
  16.         Else
  17.             sname = arr(1, r)
  18.             
  19.                 Cells(a, 5) = arr(0, r)
  20.                 Cells(a, 6) = arr(1, r)
  21.                 Cells(a, 7) = arr(5, r)
  22.                 a = a + 1
  23.                 t = 2 '因為填過一次，所以設為2
  24.         
  25.         End If
  26.     r = r + 1
  27.         
  28.     Next
複製代碼
作者: stillfish00    時間: 2014-1-16 14:09

回復 4# joey0415
樞紐分析的結果， 至於1103因為第四名和第三名數量都相同，所以都會顯示
只是我版本不同(excel2010) ，若存為xls檔會有相容性問題所以不上傳檔案了。
[attach]17271[/attach]
作者: joey0415    時間: 2014-1-16 14:50

回復 6# stillfish00
感謝分享，我試試看
應該可以錄成巨集
作者: joey0415    時間: 2014-1-16 15:01

回復 3# GBKEE


超版請教一下

Set Rng = .Range("B:B").Find(E, LookAT:=xlWhole)  '找每一股票的第一個位置

看不出哪一句是找第一個位置

用錄製的也有LookAT:=xlWhole
以股票代碼為例1101有好幾個，為什麼會跳過呢？

謝謝
作者: GBKEE    時間: 2014-1-16 16:55

本帖最後由 GBKEE 於 2014-1-16 17:26 編輯

回復 8# joey0415
  1. Set Rng = .Range("B:B").Find(E, LookAT:=xlWhole)  '找每一股票的第一個位置
複製代碼
已限制在Range("B:B")中,第一次搜尋當然會從第一個(1101)找起
LookAT:=xlWhole :與儲存格的內容完全相同 是 1101 不是 A1101B
作者: joey0415    時間: 2014-1-16 20:30

本帖最後由 joey0415 於 2014-1-16 20:33 編輯

回復 9# GBKEE

謝謝超版，大概懂了，小弟是以F8的方式，加上即時運算視窗與區域變數視窗觀察看出來的

請問版大，版大常常使用WITH來寫程式，之後都加上一個  .    就行，可是有些時候，加點後，VBA會跑出一些相關的程式碼讓你選取，有些時候不會，這樣要怎麼樣來除錯呢？

版大的文章也常用 .End(xlUp).Offset(1)之類的定位找某一格或關鍵的上一個或下一格，不知道哪有相關的說明會清楚一些

小弟買了很多VBA的書，但有時候還是很希望能有類似按一個點就有相關的程式碼可以選用，不知道要怎麼寫或設定才會一直出現呢？

謝謝

[attach]17273[/attach]
作者: GBKEE    時間: 2014-1-16 21:23

回復 10# joey0415
版大的文章也常用 .End(xlUp).Offset(1)之類的定位找某一格或關鍵的上一個或下一格，不知道哪有相關的說明會清楚一些
看這裡
  1. Option Explicit
  2. Sub Ex()
  3.     Dim 物件 As Worksheet
  4.     Set 物件 = Sheets("Sheet1")    '將Sheets("Sheet1")指定到物件變數
  5.     With 物件.Range("a1:f1")
  6.         .Font.Size = 15
  7.         '會出現 .屬性,方法
  8.     End With
  9.     With Sheet1.Range("a1:f1")  'Sheet1 是專案中的物件(工作表物件的CodeName)
  10.     'With Sheets("Sheet1").Range("a1:f1")  '不會出現  .屬性,方法
  11.         
  12.         .Font.Size = 15
  13.         '會出現 .屬性,方法
  14.     End With
  15. End Sub
複製代碼
作者: joey0415    時間: 2014-1-17 15:42

回復 3# GBKEE

請問超版，陣列中有沒有類似Find(E, LookAT:=xlWhole)，把找到的資料放進另一個新的陣列中呢？

謝謝
  1.         For Each E In AR
  2.             Set Rng = .Range("B:B").Find(E, LookAT:=xlWhole)  '找每一股票的第一個位置
  3.             .Range(.Cells(Rng.Row, "A"), .Cells(Rng.Row + 2, "D")).Copy .Cells(.Rows.Count, "F").End(xlUp).Offset(1)
複製代碼
作者: GBKEE    時間: 2014-1-17 15:52

回復 12# joey0415

    vba-名單比對相符合回寫資料 參考參考



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)