Microsoft Access 掲示板

イベント型データの並び替え

24 コメント

views

4 フォロー

下記のような手順でクエリを作成したらいいでしょう。

まずは EVENT と E_Cond でグループ化した連番を作成する。
方法としては下記を参考にしてください。

グループ毎連番を自動入力する関数 - hatena chips

上記ではクエリで生成する方法と、VBAでテーブルに書き込む方法(関数)を紹介しています。
クエリの方法は重い処理になりがちなのでデータ数が多い場合は、VBAの方法をお勧めします。

連番が生成出来たら、それから、
E_Cond="START"を抽出するクエリを作成する。→ Q_START
E_Cond="END"を抽出するクエリを作成する。→ Q_END

Q_START と Q_END を含むクエリを作成して、EVENT、E_Cond、連番で結合する。

あとは、必要なフィールドを表示させて、Datediff("s", Q_START.E_Time, Q_END.E_Time) で経過時間(秒)を計算すればいいでしょう。

hatena様、

hatena様のサイトはいつも参考にさせていただいております。ご紹介いただいた記事を参考にやってみます。ご回答ありがとうございました。

速度のほうはさっぱりわからないのですが、クエリくみくみするのは好きなので一例。

SELECT D.*, DateDiff("s", D.START, D.END) as DURATION
  FROM ( SELECT s1.EVENT, s1.E_Time as START
              , ( SELECT TOP 1 e1.E_Time
                    FROM 表1 e1
                   WHERE e1.EVENT = s1.EVENT
                     and e1.E_Time > s1.E_Time
                     and e1.E_Cond = "END" ) as END
           FROM 表1 s1
          WHERE s1.E_Cond="START"
     ) D
 ORDER BY D.START, D.EVENT

考え方としては、STARTだけの抽出結果(Sテーブル)に対し、
「同じEVENT、より遅いTime、CondがEND」の条件で最初にHITする抽出結果(Eテーブル)を
くっつけるというものです。

朱色様、

なるほどです。これもやってみます。

こういったデータの並び替えって、需要が結構ありそうだと思っていたんですが、以外に少ないみたいですね。

ありがとうございました。

約100万レコードある表1形式のテーブルを使って、朱色様に教えていただいたクエリを実行してみました。結果が表示されるまでに約38秒かかりました。この時間は遅いのでしょうか。それとも相応なのでしょうか。hatena様の方法はまだ試しておりませんので、比較はできておりません．．．．．

クエリでやると件数が増えると指数関数的に重くなりますので、そんなもんだと思います。
EVENT と E_Time にインデックスを設定する改善するかも知れません。

最初に紹介した連番入力する方法を提示しておきます。（ちょっと修正してます。）

まず、テーブル「表１」に数値型のフィールドを追加します。フィールド名は「Seq」とします。

次に、リンク先から、「グループ毎の連番を入力する関数」をコピーして標準モジュールに貼り付けます。

表1のデータを更新したときに、下記のコードを実行します。入力フォームの更新後処理にでも実行するといいでしょう。

    SetSequenceNumber "Seq", "表1", "Event,E_Cond", "E_Time"

下記のようなクロス集計クエリを作成します。名前は「Q1」とします。

TRANSFORM First(E_Time)
SELECT EVENT, Seq
FROM 表1
GROUP BY EVENT, Seq
PIVOT E_Cond;

このクエリからさらに下記のクエリを作成します。

SELECT EVENT, START, END, DateDiff("s",START,END) AS DURATION
FROM Q1
ORDER BY START, EVENT;

クエリ２つ、使ってますが、サブクエリを使って一つに纏めることもできます。

クエリはかなり高速になると思います。

100万レコードだと連番入力のコードの実行に時間がかかるかも知れません。その場合は、更新したレコードのEVENTのみを抽出して連番入力するようにするといいでしょう。

表１の入力フォームの更新後処理で実行するとして、下記のようなコードになります。

    SetSequenceNumber "Seq", "表1", "Event,E_Cond", "E_Time", "EVENT='" & Me.EVENT & "'"

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
　
< 実行結果 >

手段	EVENT数 15,000	EVENT数 700
クロス集計クエリ	14.8秒	10.4秒
選択クエリ(多段)	42.1秒	36.8秒
集計クエリ	6.2秒	6.0秒
自己結合(内部結合)	20.4秒	7分22秒
自己結合(外部結合)	36.7秒	12分48秒
スカラサブクエリ	計測断念	計測断念	※ 20分経過しても結果が表示されず
定義域集計関数	計測断念	計測断念	※ 20分間Accessが固まったまま

< サンプルデータ追加モジュール >
`js
Sub add_record()

    Const 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

シンプルに、新しいクエリを作成して、下記フィールドを考えてみました。
EVENT
E_Time
E_Cond・・・抽出条件:“START”
式1(表2のENDを導出):CDate(DMin(“E_Time”,”表1”,”EVENT=‘“ & [EVENT] & “‘ and E_Time>#” & [E_Time] & “#”))
式2(表2のDURATIONを導出):DateDiff(“n”,[E_Time],[式1])

　もしお試し頂けたらこれ幸いです。成否の結果と100万レコードの処理時間を教えてくれると嬉しいなぁ。

Mayu様のサンプルデータ追加モジュールを活用して、上記、りんごのクエリを実行中、、、
応答なし、、、応答なし、、、30min経過、一部、描画、、、応答なし、、、計測断念。
お試ししたら、ダメなやつでした、ごめんなさい。

計算量という考え方があります

表１のデータを表２の形にするということは、STARTのデータに対しENDのデータを見つけることになりますが、シンプルに考えると100万のうち半数のSTARTのデータそれぞれに対し、同様の50万のデータがENDの候補になっていて、その中から最適な一つのデータを見つけることになるので50万x50万のデータのチェックをすることになります。データ数をnとすれば

n² x a

のチェック回数が必要であることがわかります
nは2乗されます（²が環境依存文字のため念のため）
aはSTARTとENDのデータがそれぞれ元のデータnの半数なので2分の1を2回掛けたり、インデックスが適切に設定されていれば比較処理も半分で済んだりするといったようなチェック回数に関係する係数です（増える係数が掛かることもあるでしょう）

1回のチェック時間がどの程度かは置いておいて、このようなデータ処理を組むと、データ数が10倍になれば処理時間が100倍になります

結果が表示されるまでに約38秒かかりました。この時間は遅いのでしょうか。それとも相応なのでしょうか。

38秒自体が早いか遅いかは感覚によるところですが、データを複数回チェックしなくていいような仕組みを組めば飛躍的に処理時間が短くなることは期待できます

既にいくつかの手法が出ていますが、データのチェックが1回で済むような複数の処理を組み合わせることにより計算量の式が

n x a

となるような処理を作ることができます。この形の処理であればデータ数が10倍になっても処理時間は10倍で済むような処理となるので実時間（38秒）がどうであれ、試した手法は遅い手法だと言えます

こういったデータの並び替えって、需要が結構ありそうだと思っていたんですが、以外に少ないみたいですね。

シンプルな処理は理論の時点で遅く、データの特徴を掴んだ特殊な処理を入れれば高速化が見込めるというものなので、需要はあっても特殊な処理が必要（コピペで動かない）ではずばりの情報は少ないでしょうね

hirotonの感覚からすれば100万件のデータで38秒は「十分に早い」と思います。問題なのは表１の形式のデータが正式なデータとして使われ、表２の形式が欲しいとなったとき、その都度38秒が発生することでしょう

それなら表２の形式のデータを正式なモノにすればよくないですか？一度データを作ってしまえば次からは集計処理がないので表示も一瞬ですよ

「シンプルな処理を考える時間＋38秒」と「複雑な処理を考える時間＋一瞬の処理」、一度しかやらなくていい処理ならどちらがいいかの選択も臨機応変です

こういったデータの並び替えって、需要が結構ありそうだと思っていたんですが、以外に少ないみたいですね。

改めてですが、テーブル構造をしっかり設計するとそもそも需要がないんですよね

>> 13
テーブルのEVENTとE_Timeに複合主キーを設定して、再度チャレンジしてみたら、だいたい5秒で処理出来てしまったのだが、うーん、これはありなのだろうか？

テーブルのEVENTとE_Timeに複合主キーを設定して、
再度チャレンジしてみたら、だいたい5秒で処理出来てしまったのだが、
うーん、これはありなのだろうか？

ありでしょう。
実際かなりの高速化が見込めるでしょうし、素晴らしいチューニングだと思います。

私がインデックスや主キーの設定を施さなかったのは
フィールドのグルーピングや抽出条件に指定するフィールドの順番が
回答者によって異なっていたため、
特定の手法だけベンチマークに有利な影響を及ぼす設定では
アルゴリズムの比較にならない、という理由です。

ただ、処理時間が本当に5秒へ縮小されたかというと...疑問符が付きます。
というのも
定義域集計関数を使ったクエリの場合、データシートビューの表示において
「目視できている」部分のレコードしか各行の演算が済んでいない
ということがあります。

つまり、
「クエリのデータをデータシートビューで閲覧可能、且つUIの操作も可能」
という状態であっても
全行の処理が終わっているとは限らないんです。
　

SELECT EVENT
     , E_Time
     , CDate(
           DMin( "E_Time", "表1"
               , "EVENT = '" & [EVENT] & "' And E_Time > #" & [E_Time] & "#"
           )
       ) As E_END
     , DateDiff( 's', E_Time, E_END ) As DURATION
FROM 表1
WHERE E_Cond = 'START' ;

　
この SQL に名前を付け、クエリとして保存した後に
クエリを右クリックして表示されるコンテキストメニューから
テキストファイルにでもデータをエクスポートしてみることをお薦めします。

クエリを開くために要した時間は数秒程度と短いのに
データのエクスポートを実施した途端、
ファイル書き出しのオーバーヘッドが多少はあるにしろ
異常なくらい長い時間を要する

ということが実感できると思います。

mayuさん、ベンチマーク実験ありがとうございました。非常に示唆に富む興味深い実験ですね。
あと、PC性能すごいですね。当方の環境でテストしたら数倍時間かかりました(;^_^A

100万件のデータとなると連番を入力しておいても結構時間がかかるようです。
そこでテーブルに連番を入力するのではなく StartTime フィールドを追加して、"END"レコードの方に対応する"START"の時刻を格納するという方法にしてみました。

表１に日付/時刻型のフィールドを追加して、名前を StartTime とします
下記のVBAを実行します。

Public Sub SetStarTime()
    Const strSQL = _
          "SELECT * FROM 表1 ORDER BY EVENT, E_Time, E_Cond DESC;"

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

    Do Until rs.EOF
        Dim stime As Date
        If rs!E_Cond = "START" Then
            stime = rs!E_Time
        Else
            rs.Edit
            rs!StartTime = stime
            rs.Update
        End If
        rs.MoveNext
    Loop

    rs.Close

    MsgBox "StarTime入力完了"
End Sub

クエリは下記のようにシンプルになります。

SELECT
    EVENT,
    StartTime,
    E_Time,
    DateDiff('s', StartTime, E_Time) AS DURATION
FROM
    表1
WHERE
    E_Cond = "END";

E_Condにインデックスを貼っておいたらほぼ一瞬で表示されました。

StartTime フィールドを追加して、
"END"レコードの方に対応する"START"の時刻を格納するという方法にしてみました。

発想の柔軟さ、パフォーマンスへの配慮、さすがhatenaさんです。

標準SQLのウィンドウ関数 LAG が利用可能な SQLServer だと
以下のSQLで hatenaさんと同等のロジックになりそうですね。

SELECT EVENT
     , starttime
     , endtime
     , datediff( second, starttime, endtime ) DURATION
FROM
(
    SELECT EVENT
         , E_Time endtime
         , E_Cond
         -- OVER句における E_Cond DESC の指定は不要かも
         , LAG( E_Time, 1 )
               OVER ( PARTITION BY EVENT
                      ORDER BY     E_Time, E_Cond DESC ) starttime
    FROM 表1
) 
VBA_PART
WHERE E_Cond = 'END' ;

些細なことですけどENDをチェックする形にするとENDがあればSTARTがあることが保証される（はず）なのでIf判定がおよそ半分減らせますね（処理時間の差がわからない程度の差ですが）

VBAで連番振ってSQLって方式はどうせVBA使うならこれやればいいじゃないなのでACCESS上の話である以上ナンセンスなんですよね

実は対抗としては>> 14でりんごさんが試されている通り元のテーブルに手を入れてあげればn²オーダーに見えるクエリでも爆速で動くという手法だと思います。主キーインデックスのないテーブルに主キーインデックスを設定してスカラサブクエリで表示するという手法でもVBAだけと遜色ない速度がでます

結局のところテーブル設計をしっかりやりましょうという話です

改めて考えてみたんですがSTARTの判定はよくないですね
ENDのレコードの存在が保証されない場合データが漏れます
ENDのレコードの存在が保証されているなら奇数偶数でSTART/ENDがきれいに出現するのでIF判定が必要ないです

>> 16
　早速、試してみたところ、時間がかかりすぎると、とても実感できました。続けて、テーブル作成クエリやSQLの悪あがきをやってみたけれど、力尽きました。
　ビギナーズラック達成かと舞い上がったのですが、なかなか難しいですね。
　返信、遅くなりましたが、このようなアドバイスはとても助かります。ありがとう。

>> 18
丁寧に返信いただいてありがとうございます。お礼嬉しいです。

今回はデータ件数という「壁」があっただけで
りんごさんは、HHさんの希望に沿った回答をちゃんと提供できています。
長時間のチャレンジ、お疲れ様でした。

皆様、ベンチマークなども行っていただき本当にありがとうございました。自分には難しくて理解できなかった部分もありましたので、テーブル設計も含めて、皆様から提示いただいたクエリやVBAをしっかり勉強したいと思います。ありがとうございました。HH

すでにCLOSEしてしまっていますが、どうしても気になり、mayu様の標準SQLを試してみました。割と古いPCでも約4秒ですべての結果が表示されました。ご報告まで。

Database: SQL Server 2019 Developer Edition
OS: Windows10 Pro 64 bit
Processor: Intel(R) Core(TM) i5-3470 3.20GHz
Installed RAM: 16.0 GB
Storage drive: 500 GB SSD

タグリスト

VBA (386) Office2016 (294) フォーム (242) Office2021 or 2019 (224) クエリ SQL (220) テーブル (139) レポート (128) Office2013 (57) Office2010 (50) Microsoft365 (47)

Markdown テーブル生成

Accessのテーブルやクエリのデータを貼り付ける場合は下記で、Markdown書式のテーブルに変換して貼り付けてください。

Markdown Tables generator

Microsoft Access 掲示板

イベント型データの並び替え

最新トピック

タグリスト

Markdown テーブル生成