Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
はじめまして、電通国際情報サービスの星加と言います。いつもお世話になっております。
実はロードスクリプトで変数を使ってシーケンスナンバーを入れたいのですが、LOAD文の中でLETが使えないようなので困っています。
オリジナルのファイルはこのようなもので、あらかじめoperation_time順になっています。
operation_time, USER_NAME
09:00,Mr A
09:01,Mr A
09:03,Mr A
10:00,Mr B
10:30,Mr B
11:00,Mr B
15:00,Mr C
15:10,Mr C
15:15,Mr C
17:00,Mr A
これに対してPCの利用者が切り替わった事を判定して、Numberを与えていきたいのです。
operation_time, USER_NAME,Number
09:00,Mr A,1
09:01,Mr A,1
09:03,Mr A,1
10:00,Mr B,2
10:30,Mr B,2
11:00,Mr B,2
15:00,Mr C,3
15:10,Mr C,3
15:15,Mr C,3
17:00,Mr A,4
この時、時系列で同じ人が使っている場合は、上記のように同じ数値にしたく、人が切り替わった時にカウントアップをしたいのです。
単純に名前で Group By すると、最下部のAさんも1になってしまいます。ここはCさんの次なので4にしたいのです。
すごく簡単な事で恐縮なんですが、どなたかご教示くださいませんでしょうか? 宜しくお願いいたします。
PS 本当にやりたいことは
start,end,USER_NAME
09:00,09:03,Mr A
10:00,11:00,Mr B
15:00,15:15,Mr C
17:00,17:00,Mr A
といったテーブルを作成した上で、他のログとインターバルマッチするテーブルを準備したいだけなんですが、その手前で上記赤色の再度現れたAさんのテーブルを作るのが、どうもうまくいかなくて・・・・ 何か良い関数などあるのでしょうか?
kiwaseaqkさんのアドバイスを基に集計キーを作成し、次にMinとMaxにて集計しなおすとPSに書いてあった表ができるようですがいかがでしょうか。
//Script
Tmp:
LOAD operation_time,
USER_NAME,
if(Previous(USER_NAME) = USER_NAME,numsum(peek('col_num') , 0),numsum(peek('col_num') , 1)) as col_num
FROM user.csv (txt, codepage is 932, embedded labels, delimiter is ',', msq);
LOAD Time(min(operation_time)) as start, Time(max(operation_time)) as end, USER_NAME
Resident Tmp
Group By USER_NAME, col_num
Order By col_num;
(ロード結果)
start,end,USER_NAME
09:00,09:03,Mr A
10:00,11:00,Mr B
15:00,15:15,Mr C
17:00,17:00,Mr A
ダミーでも良いのでサンプルデータはありますか。
こんにちは、オーリック 岩瀬と申します。
自分は列に連番を振りたい場合は、ロードスクリプトに以下のような記述していますよ。
if ( Previous(col_abc) = col_abc, numsum( peek('col_num') , 1) as col_num
本当にやりたいことの回答になっていないですが、キーブレークする連番を振るのであれば
上記で可能だと思います。
tigerさん、ありがとうございます。ただ通信データでして、20億件あるので、サンプルデータの作成がちょっと手つかずで、すみません。
岩瀬さんもありがとうございます。
ちなみに numsum( peek('col_num') , 1) の部分、実際には どういう動きをするのか教えていただけませんでしょうか?
私がべたべたに書くと、下記のように膨大になってしまいます・・・・・涙
要約としては
6行目 RowNoで番号を振る
20行目 利用IPが前の行と変わっていたら、その行のRowNo()を入れておく、でなければ0をセット
そうすると
例) 10000600000 みたいな列ができる
それを今度は、何回もループしながら
2回目のループ 11000660000
3回目のループ 11100666000
4回目のループ 11110666600
5回目のループ 11111666660
6回目のループ 11111666666
みたいに、順次、前の行を違っていたら、前の数字を0の上に上書きする処理をして
すべての0がなくなるまでループさせて、終了
こういう汚いロジックになりました・・・・
もっとスパッと出来るロジックはないものでしょうか?
ループさせなくても、処理した直前の行の情報が判定できればいいのですが・・・・・
---------------------------------------------------------------------------------------------------------------------------------------
1 Directory;
2 DHCP:
3 LOAD Distinct
4
5 'ALL' as ALL, // テーブル全体を定義したいから
6 RowNo() as No, // 確認用
7
8 MACアドレス,
9
10 月 &'/'&if(len(日)=1,0&日,日) &' ' &時刻 As DHCP_月日時刻,
11
12 // サーバ名,
13 MSG,
14 // on,
15 利用IP,
16 // to,
17 機器利用者,
18 ホスト名,
19
20 if(利用IP<>Previous(利用IP), RowNo(),0) as IP切り替わりポインタ
21
22 FROM
23 削除可_DHCP.qvd(qvd); // ここでは絶対にWhereを付けてはいけない!!!!!!!! Previous関数を使っているから!!!!!! ★★★★★★★★
24
25 Store DHCP into Z:\削除可_DHCP2.qvd(qvd);
26 //DROP Tables DHCP;
27
28
29 TRACE ------------------------;
30 TRACE ループ処理に入ります;
31 TRACE ------------------------;
32
33
34 LET v回数 = 1; // 下記のDO LOOPの初回を認識したいため、1をセット
35
36
//******************************************************************************* ループ処理 開始
37 DO;
38
39 QUALIFY *;
40 Directory;
41 別名:
42 LOAD
43 ALL,
44 No,
45 MACアドレス,
46 DHCP_月日時刻,
47 MSG,
48 利用IP,
49 機器利用者,
50 ホスト名,
51 IP切り替わりポインタ
52 Resident DHCP;
53 UNQUALIFY *;
54
55 DROP Table DHCP;
56
57 Directory;
58 DHCP:
59 LOAD
60 別名.ALL as ALL,
61 別名.No as No,
62 別名.MACアドレス as MACアドレス,
63 別名.DHCP_月日時刻 as DHCP_月日時刻,
64 別名.MSG as MSG,
65 別名.利用IP as 利用IP,
66 別名.機器利用者 as 機器利用者,
67 別名.ホスト名 as ホスト名,
68
69 if(別名.IP切り替わりポインタ=0,Previous(別名.IP切り替わりポインタ),別名.IP切り替わりポインタ) as IP切り替わりポインタ
70
71 Resident 別名 Order By 別名.No; // Order By は常駐LOADでしか使えない
72
73 if $(v回数) >= 2 then // 2回目以降の場合は、チェックテーブルを削除する。 1回目には存在しないため何もしない
74 DROP Table チェック;
75 end if
76
77
78 チェック: // ユーザが切り替わった直後のみ数値が入るので、まだループ処理が必要な場合は、min(ユーザ切り替わりポインタ)がゼロになる
79 LOAD
80 ALL,
81 min(IP切り替わりポインタ) as ゼロはまだ有るか
82 Resident DHCP
83 Group By ALL;
84
85
86 LET v回数 = v回数 + 1; // 処理回数をカウント
87
88 TRACE ------------------------;
89 TRACE 処理回数 = $(v回数);
90 TRACE ------------------------;
91
92 // store DHCP into Z:\削除可_DHCP準備.qvd(qvd);
93 // drop Table DHCP;
94 drop Table 別名;
95
96 LOOP while peek('ゼロはまだ有るか')=0; // ゼロはまだあるか?のテーブルに、まだ0があればLOOPし、再処理する。全部処理すると1行目(1ユーザ目)が最小値になるのでLOOPを終了する
97
//******************************************************************************* ループ処理 終了
98
99
100
101 store DHCP into Z:\削除可_DHCP準備.qvd(qvd);
102 Drop Table DHCP;
103
104 TRACE ------------------------;
105 TRACE ループ処理完了しました;
106 TRACE ------------------------;
107
108 //************* あらためてロード
109
110 Directory;
111 DHCP:
112 LOAD
113 ALL,
114 No,
115 MACアドレス,
116 DHCP_月日時刻,
117 MSG,
118 利用IP,
119 機器利用者,
120 ホスト名,
121 IP切り替わりポインタ
122 FROM
123 Z:\削除可_DHCP準備.qvd(qvd);
124
125
126 store DHCP into 削除可_DHCP準備2.qvd(qvd);
127 Drop Table DHCP;
128
129
130 TRACE ------------------------;
131 TRACE DHCP(IP)開始終了テーブル作成;
132 TRACE ------------------------;
133
134
135 Directory;
136 DHCP:
137 LOAD
138 MinString(DHCP_月日時刻) As MIN_DHCP_月日時刻,
139 maxString(DHCP_月日時刻) As MAX_DHCP_月日時刻,
140 MSG,
141 MACアドレス,
142 利用IP,
143 機器利用者,
144 ホスト名
145 FROM
146 削除可_DHCP準備2.qvd
147 (qvd)
148 Group by MACアドレス,MSG,利用IP,機器利用者,ホスト名
149 ;
kiwaseaqkさんの方法とほぼ同じですが、
Previous関数を使用すれば良いのでは?
Previous(USERNAME)で前の行の結果を取って、前の行と一致しなければPrevious(NUMBER) +1というような形であればいけるのでは。
tigerさん、お世話になります。
20行目および69行目がそれにあたるのですが
問題なのは、下記なんです。
A行で 処理した結果を入れた(※ 例えば、0だったところを、前行の6に変更)行を 連続して今度は
A+1行の処理の時に さっきのA行をPreviousで取り出しても、処理した結果(6)ではなく、オリジナルの結果(0)が返ってくるので、仕方なくループさせまくってます・・・・・
Prevoiusって、あくまでLOADしたオリジナルしか取り出さないようなのです・・・
私の理解が変なのかな・・・ でもきっと3行程度でこのロジックが書けると思うんですよね・・・QVってすごくかしこいから・・・・
kiwaseaqkさんのアドバイスを基に集計キーを作成し、次にMinとMaxにて集計しなおすとPSに書いてあった表ができるようですがいかがでしょうか。
//Script
Tmp:
LOAD operation_time,
USER_NAME,
if(Previous(USER_NAME) = USER_NAME,numsum(peek('col_num') , 0),numsum(peek('col_num') , 1)) as col_num
FROM user.csv (txt, codepage is 932, embedded labels, delimiter is ',', msq);
LOAD Time(min(operation_time)) as start, Time(max(operation_time)) as end, USER_NAME
Resident Tmp
Group By USER_NAME, col_num
Order By col_num;
(ロード結果)
start,end,USER_NAME
09:00,09:03,Mr A
10:00,11:00,Mr B
15:00,15:15,Mr C
17:00,17:00,Mr A
すごく単純化して
A
----
1
0
0
0
0
6
0
0
0
を
A
---
1
1
1
1
1
6
6
6
6
にしたい場合って、みなさんは、どう書きますか???
私のやり方(ループしまくり方式)で本番DBを処理してるんですが、処理時間があまりにかかってしまい困っております
う!!!すごい!すごい!!
御面倒をおかけしますが、
if(Previous(USER_NAME) = USER_NAME,numsum(peek('col_num') , 0),numsum(peek('col_num') , 1)) as col_num
の
numsum(peek('col_num') , 0)
numsum(peek('col_num') , 1)
の意味を教えて頂けませんか?
peekとnumsum、どちらも詳しくなくって、マニュアルを読んでも、今一つで・・・・
こんにちは、岩瀬です。
peekは、レコード(行)のフィールド(列)を参照するものです。
どのレコードのどのフィールドというように指定して値を取得します。
peek("col_num")とすると1件前のレコードのcol_numというフィールド(列)の値を返してくれます。
書式は、peek("",行)で行を指定しないと前のレコードになります。
numsumは単純にパラメータすべてを合算するものです。
よって、
numsum(peek('col_num') , 0)は、前のレコードのcol_numの値を返します。
numsum(peek('col_num') , 1)は、前のレコードのcol_numの値に1加算したものを返します。