Microsoft Access 掲示板
イベント型データの並び替え / 9
24
コメント
views
4
フォロー
9
mayu
2021/06/20 (日) 09:08:51
ef559@a99f1
hatenaさんが回答なさっている{ クロス集計 + 選択クエリ }による
多段クエリのほうが可読性が良く、内容の理解は容易なのですが
SQL の読み書きに対し、特に苦手意識が無いのでしたら
SetSequenceNumber関数で Seq フィールドに連番を付与した後、
表のスキャンが一度だけになる集計クエリを作ればいいでしょう。
SELECT EVENT
, Max( IIf( E_Cond = 'START', E_Time ) ) As E_START
, Max( IIf( E_Cond = 'END', E_Time ) ) As E_END
, DateDiff( 's'
, Max( IIf( E_Cond = 'START', E_Time ) )
, Max( IIf( E_Cond = 'END', E_Time ) )
) As DURATION
FROM 表1
GROUP BY EVENT
, Seq
ORDER BY Max( IIf( E_Cond = 'START', E_Time ) )
, EVENT ;
また、朱色さんと似たロジックになりますが
自己結合を用いた記述方法もご紹介します。
(
ENDが存在しないイベントも表示する場合は LEFT JOIN
ENDが存在しないイベントが表示不要の場合は INNER JOIN
というように
表示件数とパフォーマンスを自身で調整することが可能です
)
SELECT x.EVENT
, x.E_Time As E_START
, Min( y.E_Time ) As E_END
, DateDiff( 's', x.E_Time, Min( y.E_Time ) ) As DURATION
FROM 表1 x
INNER JOIN 表1 y
ON
(
x.E_Cond = 'START' AND
y.E_Cond = 'END' AND
x.EVENT = y.EVENT AND
x.E_Time < y.E_Time
)
GROUP BY x.EVENT
, x.E_Time
ORDER BY x.E_Time
, x.EVENT ;
こういったデータの並び替えって、
需要が結構ありそうだと思っていたんですが、以外に少ないみたいですね。
前後への行参照は、定番と言っていいほど需要はあるのですけど
hatenaさんが仰っているように、SQL では重い処理になります。
朱色さんが記述したスカラサブクエリ、私が後半で記述した自己結合ともに「重い」SQLです。
Oracle や SQLServer などの本格的なデータベースでは
SQL文中に row_number, rag, lead といった分析関数を駆使して高速化できるのですが
残念ながら、Accessには分析関数が実装されていません。
したがって、SQL のボトルネックを VBA で補い、
SQL と組み合わせるという Access ならではのテクニックを用いて高速化したのが
hatenaさんの回答になります。
通報 ...
100万件のデータに対して、クエリ( SQL )の実行を10回繰り返し
最終行のデータが表示されるまでの平均所要時間を計測してみました。
( EVENT, E_Time, E_Cond, Seq 各列にインデックスや主キーの設定は無し )
< ベンチマーク実施環境 >
バージョン: Access 2019 (32bit)
OS: Windows 10 Pro 64bit
CPU: Intel Core i9-8950HK 2.90GHz
メモリ： 64 GB
ディスク： Samsung SSD 970 Pro
< 実行結果 >
`jsConst TOTAL_REC As Long = 1000000
Const EVENT_CHAR As Long = 3
Const MAX_DURATION As Long = 120
Const TABLE_NAME As String = "表1"
With CurrentDb
If (DCount("*", "MSysObjects", "[Name] = '" & TABLE_NAME & "'") = 0) Then
Dim strDDL As String
strDDL = "CREATE TABLE " & TABLENAME & " " & vbNewLine
& "( " & vbNewLine
& " EVENT VARCHAR(50) NOT NULL " & vbNewLine
& " , ETime DATETIME NOT NULL " & vbNewLine
& " , ECond VARCHAR(20) NOT NULL " & vbNewLine
& " , Seq INT " & vbNewLine _
& ");"
.Execute Query:=strDDL, Options:=dbFailOnError
Application.RefreshDatabaseWindow
End If
.Execute Query:="DELETE FROM " & TABLE_NAME & ";", Options:=dbFailOnError
Dim ts As Date
ts = DateSerial(Year(Date), 1, 1) + TimeSerial(0, 0, 1)
Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
With .OpenRecordset(TABLE_NAME, dbOpenTable)
Dim i As Long
Dim j As Long
Dim e_name As String
e_name = Space$(EVENT_CHAR)
For i = 1 To TOTAL_REC
If (i Mod 2 = 1) Then
Randomize
For j = 1 To EVENT_CHAR
Mid(e_name, j, 1) = Chr$(Int((90 - 65 + 1) Rnd) + 65)
Next j
dic(e_name) = dic(e_name) + 1
End If
ts = DateAdd("s", Int(MAX_DURATION Rnd + 1), ts)
.AddNew
.Fields("EVENT").Value = e_name
.Fields("E_Time").Value = ts
.Fields("E_Cond").Value = IIf(i Mod 2 = 1, "START", "END")
.Fields("Seq").Value = CLng(dic(e_name))
.Update
Next i
.Close
End With
Set dic = Nothing
.Close
End With
MsgBox Prompt:="データ作成完了", Buttons:=vbInformation, Title:="実行結果"
End Sub