500 万件を超えるレコードを含む大規模な CSV データ ファイルがあります。これには、開始日時と終了日時が含まれます。
データの例を次に示します。
2019-08-06 16:07:25,2019-08-06 16:07:42
2019-08-06 17:21:42,2019-08-06 17:21:59
2019-08-06 15:43:03,2019-08-06 15:43:20
2019-08-06 13:48:13,2019-08-06 13:48:30
2019-08-06 16:18:56,2019-08-06 16:19:13
2019-08-06 14:34:10,2019-08-06 14:34:27
2019-08-06 16:59:47,2019-08-06 17:00:04
2019-08-06 16:14:57,2019-08-06 16:15:14
2019-08-06 13:04:38,2019-08-06 13:04:55
2019-08-06 16:09:28,2019-08-06 16:09:45
私の目標は、アクティブな同時接続が最も多くなる時間帯を特定するデータを視覚化できるようにすることです。理想的には、観察できる時間間隔が狭いほど、より良い結果が得られます。データの日付は 2 か月以内です。
この問題に対処するために使用できるアプローチを提案できる人はいますか?
Python を使用してレコードごとにファイル全体をループし、同時接続の数を特定しようとしました。数千のレコードを使用して小規模なテストを行ったときはうまくいきました。しかし、500 万件を超えるレコードがあるファイルの場合、この方法はうまく機能しません。500 万レコードすべてを 500 万回ずつループするには、しばらく時間がかかります。
------------------------
ここでは、トランザクションの並列度を示すためにデータを変換する方法という中心的な問題のみを扱います。
このアイデアを説明するために (Oracle) SQL を使用します。
これがあなたのデータであると仮定しましょう
select * from tab;
START_DT END_DT
------------------- -------------------
06.08.2019 16:07:25 06.08.2019 16:07:42
06.08.2019 16:07:30 06.08.2019 16:07:35
06.08.2019 16:07:33 06.08.2019 16:07:50
06.08.2019 16:07:50 06.08.2019 16:07:55
最初のステップでは、各行を 2 つの部分に分割します (UNION ALL を使用)
最初の部分はトランザクションの開始を識別し、アクティブなトランザクションの数を 1 増やします。
2 番目の部分は、トランザクションの終了を識別し、アクティブなトランザクションの数を 1 つ減らします。
タイムスタンプとして、トランザクションの開始日または終了日のいずれかを使用します。
select start_dt trans_dt ,1 trans_cnt from tab union all
select end_dt trans_dt ,-1 trans_cnt from tab
order by 1;
TRANS_DT TRANS_CNT
------------------- ----------
06.08.2019 16:07:25 1
06.08.2019 16:07:30 1
06.08.2019 16:07:33 1
06.08.2019 16:07:35 -1
06.08.2019 16:07:42 -1
06.08.2019 16:07:50 1
06.08.2019 16:07:50 -1
06.08.2019 16:07:55 -1
このように日付を準備したら、トランザクション数を累積するだけで済みます。これは、トランザクションのタイムスタンプに基づいて順序付けされたアナリティクスの集計関数 SUM によって行われます。詳細については後ほど説明します。
最後に、複数のトランザクションが同じタイムスタンプで開始または終了するケースを統合します。GROUP BY と MAX 並列度を使用します。
クエリ全体
with trans as (
select start_dt trans_dt ,1 trans_cnt from tab union all
select end_dt trans_dt ,-1 trans_cnt from tab),
trans_cum as (
select trans_dt,trans_cnt,
sum(trans_cnt) over (order by trans_dt, trans_cnt) parallel_trans_cnt
from trans)
select trans_dt, max(parallel_trans_cnt) parallel_trans_cnt
from trans_cum
group by trans_dt
order by 1;
結果
TRANS_DT PARALLEL_TRANS_CNT
------------------- ------------------
06.08.2019 16:07:25 1
06.08.2019 16:07:30 2
06.08.2019 16:07:33 3
06.08.2019 16:07:35 2
06.08.2019 16:07:42 1
06.08.2019 16:07:50 1
06.08.2019 16:07:55 0
このデータは、通常の X、Y プロットを使用して簡単に視覚化できます。
あるトランザクションが次の開始と同じ秒内に終了することに注意する必要があります。
この 2 番目の時点では 2 つのトランザクションがアクティブでしたか、それとも 1 つだけでしたか?
上記のクエリは後者の場合を想定しています。前者の場合を考慮したい場合は、単純に AC を調整してください。への累積ロジック
sum(trans_cnt) over (order by trans_dt, trans_cnt DESC) parallel_trans_cnt
同じ 1 秒以内に、最初に開始トランザクション (並列度が増加します) を検討し、次に終了トランザクションを検討します。
このロジックは任意のプログラミング言語で実装できますが、SQL は 1) 非常に簡潔である、2) メイン メモリの制限がない、3) すぐに使用できる並列処理があることに注意してください。
