CDと本の管理システムをExcelで作成する(7.検索結果(配列)をSQLで並び替え)

前回でレーベンシュタイン距離を付与することが出来ました

www.excellovers.com

今回はレーベンシュタイン距離の値に応じ検索結果を並び替えたいと思います。

ソートアルゴリズム？いえSQLです

検索結果はユーザーフォームに表示される直前に多段階配列(ジャグ配列)に取り込まれています。

であるならば通常はバブルソートなどのソートアルゴリズムを使用して並び替えるところですが、今回はちょっと違う方法で並び替えたいと思います。

配列をIn Memory Databaseに取り込んでSQLで並び替え

これです。

無理やりです。

使ってみたいんです、In Memory Database。

では早速やってみます

前回コード

Private Sub CommandButton1_Click()
     Dim myDBHandle As Long
     
     SQLite3Initialize
    SQLite3OpenV2 "C:\XXX\CDBookManager.db3", myDBHandle, _
          SQLITE_OPEN_READONLY, ""
     
     Dim mySQL As String
     Dim myStmtHandle As Long
     
     mySQL = "select タイトル from CDBookManager"
     SQLite3PrepareV2 myDBHandle, mySQL, myStmtHandle
     
     Dim ReturnValue As Variant
     Dim ColCount As Long
     Dim i As Long
     Dim n As Long
     Dim myArray() As Variant
     Dim myVar As Variant
     Dim colType As Long
     Dim colValue As Variant
     
     ReturnValue = SQLite3Step(myStmtHandle)
     ColCount = SQLite3ColumnCount(myStmtHandle)
     ReDim myArray(ColCount)
     ReDim myVar(n)
     
     Do Until ReturnValue = SQLITE_DONE
          For i = 0 To ColCount - 1
               colType = SQLite3ColumnType(myStmtHandle, i)
               colValue = ColumnValue(myStmtHandle, i, colType)
               myArray(i) = colValue
          Next i

          myArray(ColCount) = Levenshtein3(TextBox_Title.Value, myArray(0))
          myVar(n) = myArray
          n = n + 1
          ReDim Preserve myVar(n)
          ReturnValue = SQLite3Step(myStmtHandle)
     Loop
     ReDim Preserve myVar(n - 1)
     SQLite3Finalize myStmtHandle
     SQLite3Close myDBHandle
     
'---ここに次に紹介するコードを挿入--------------------------------------------

     ListBox_Result.List = WorksheetFunction.Transpose(WorksheetFunction.Transpose(myVar))
        
     
End Sub

SQLでデータの並び替え

     Dim Titles() As Variant
     
'①
     SQLite3OpenV2 "Sort.db3", _
          myDBHandle, SQLITE_OPEN_READWRITE + SQLITE_OPEN_MEMORY, ""
     SQLite3PrepareV2 myDBHandle, _
          "create table sortData(Title text,Levenshtein integer)", _
          myStmtHandle
     SQLite3Step myStmtHandle
     SQLite3Finalize myStmtHandle
     
     Titles = WorksheetFunction.Transpose(WorksheetFunction.Transpose(myVar))
'②
     For i = LBound(Titles) To UBound(Titles)
          mySQL = "insert into sortData values(""" & Titles(i, 1) & """," & Titles(i, 2) & ")"
          SQLite3PrepareV2 myDBHandle, mySQL, myStmtHandle
          SQLite3Step myStmtHandle
          SQLite3Finalize myStmtHandle
     Next i
     
'③
     mySQL = "select * from sortData order by levenshtein desc"
     SQLite3PrepareV2 myDBHandle, mySQL, myStmtHandle
     ColCount = SQLite3ColumnCount(myStmtHandle)
     n = 0
     ReDim myArray(ColCount - 1)
     ReDim myVar(n)
 
'④
     ReturnValue = ""
     Do Until ReturnValue = SQLITE_DONE
          For i = 0 To ColCount - 1
               colType = SQLite3ColumnType(myStmtHandle, i)
'⑤            
               If colType <> 5 Then
                    colValue = ColumnValue(myStmtHandle, i, colType)
                    myArray(i) = colValue
               End If
          Next i
'⑥
          If colType <> 5 Then
               myVar(n) = myArray
               n = n + 1
               ReDim Preserve myVar(n)
          End If
          ReturnValue = SQLite3Step(myStmtHandle)
     Loop
     ReDim Preserve myVar(n - 1)
     SQLite3Finalize myStmtHandle
     SQLite3Close myDBHandle

①でインメモリーデータベースを作成。

インメモリーデータベースが出来上がった後は普通にデータベースにテーブル作るかのようにSQL文でテーブルを作成。

②で配列のデータを一旦インメモリーデータベースにインポート。

③でレーベンシュタイン距離の降順でデータを並び替えた後に全抽出。

④で並び替えたデータを再び配列へ取込み。

⑤⑥の処理はIn Memory Databaseを作成すると作成したテーブルの全列１行目にNullが入力されているので、それを省くための処理。
※多分Nullが入力されていると思うんだけど、いかんせんメモリ上のデータで確認しにくい。

これで無事に検索結果をレーベンシュタイン距離に応じて並び替えることが出来ました。

まとめ

今回でタイトルを曖昧検索(≒レーベンシュタイン距離)を行うことが出来るようになりました。

当初の狙い通り普通の曖昧検索では出てこないであろう項目が上位に表示されていて、面白い出会いを演出してくれています。

次回はタイトルだけではなく別の項目で検索ができるようにユーザーフォームを修正していきたいと思います。