特定の値が出現したときの時間差分を表示したい

日本語で質問 | Q&A
, , , ,
1

■やりたいこと
以下のデータをQuickSightに渡しています。
各項目で1と-1の時間差分を1の行に表示したいです。

■渡しているデータ
・各項目には1,-1,0,(null)の4種類が出現します。
・1と-1は必ず交互に出現します。（ただし、1と-1の間に0や(null)は出現します。）

時間  項目A   項目B   項目C  
13:06   1      1       1  
13:10  -1      0     (null)    
14:00 (null)  -1       0   
14:21   1      1      -1     
15:01  -1     -1       0

■実現したいQuickSightの表

時間  項目A   項目B   項目C   項目A時間   項目B時間   項目C時間
13:06   1      1       1      00:04:00    00:54:00    01:15:00
13:10  -1      0     (null)       -           -           -
14:00 (null)  -1       0          -           -           -
14:21   1      1      -1      00:50:00    00:50:00        -
15:01  -1     -1       0          -           -           -

■補足
現時点では、妥協案として以下を使用しています。
以下の計算フィールドを作成し、フィルターで1,-1のみを表示した表に計算フィールドを値として持たせています。
この方法には以下の欠点がありました。
・-1の行にも時間が入ってしまう。
・単純に上下行の差分を見ているため、1項目のみの表であれば正しい時間になるが、複数項目の表の場合は正しい時間にならないケースがある。
・時間をhh:mm:ss表記にできるのか不明。

dateDiff(
    min(time),
    lag(max(time),[time DESC],1),
    "SS"
)

実現できる方法がありましたらご教示お願いいたします。

2

@hiroki

ご質問いただき、ありがとうございます。

そうですね、1と−1の間にくる0や(null)に規則性がないと、提示いただいたように、フィルターを使用して0や(null)を除外し、lag()関数で差分を取得する以外は、思いつかないですね。

そうなると、確かにいくつか問題が発生します。

・−1の行にも時間が入ってしまうのは、ifelse()を使用して、dateDiff計算を1の場合だけ実施すれば解消します。

ifelse(min(itemA)=1,
    dateDiff(
        min(time),
        lag(max(time),[time DESC],1),
        "MI"
    ),
    null)

・hh:mm:ss表記は、DemoCentralのダッシュボード：Calculation - Duration Time Format (HH:MM:SS)で提供されており、その分析画面に入っていただくと、concat()とfloor()を使用した計算フィールドを参照することができ、以下のように変換をすることはできます。

image
image2386×572 60.4 KB

・フィルターが行に適用されてしまうため、１項目のみの表であれば問題ないですが、確かに複数項目の表の場合は実現できないので、項目単位にテーブルを作成するしかなく、ご提示いただいたような表の実現は厳しいですね。

1 Like
3

ご回答ありがとうございます。

100%実現できないまでも、次のアクションのヒントになりましたので、
いただいた回答を参考にさせていただき、現段階で実現できる案を模索したいと思います。

1 Like