series_shapes_fl()
関数 series_shapes_fl()
は、肯定/負の傾向を検出したり、系列にジャンプしたりする ユーザー定義関数 (UDF) です。 この関数は、複数の時系列 (動的数値配列) を含むテーブルを受け取り、各系列の傾向とジャンプ スコアを計算します。 出力は、スコアを含むディクショナリ (動的) です。
構文
T | extend series_shapes_fl(
,
y_series詳細)
構文規則について詳しく知る。
パラメーター
名前 | 型 | 必須 | 説明 |
---|---|---|---|
y_series | dynamic |
✔️ | 数値の配列セル。 |
詳細 | bool |
既定では、 false です。 を に true 設定すると、追加の計算パラメーターが出力されます。 |
関数の定義
関数を定義するには、次のように、コードをクエリ定義関数として埋め込むか、データベースに格納されている関数として作成します。
次の let ステートメントを使用して関数を定義します。 権限は必要ありません。
重要
let ステートメントは単独では実行できません。 その後に 表形式の式ステートメントを記述する必要があります。 の動作例を実行するには、「例series_shapes_fl()
」を参照してください。
let series_shapes_fl=(series:dynamic, advanced:bool=false)
{
let n = array_length(series);
// calculate normal dynamic range between 10th and 90th percentiles
let xs = array_sort_asc(series);
let low_idx = tolong(n*0.1);
let high_idx = tolong(n*0.9);
let low_pct = todouble(xs[low_idx]);
let high_pct = todouble(xs[high_idx]);
let norm_range = high_pct-low_pct;
// trend score
let lf = series_fit_line_dynamic(series);
let slope = todouble(lf.slope);
let rsquare = todouble(lf.rsquare);
let rel_slope = abs(n*slope/norm_range);
let sign_slope = iff(slope >= 0.0, 1.0, -1.0);
let norm_slope = sign_slope*rel_slope/(rel_slope+0.1); // map rel_slope from [-Inf, +Inf] to [-1, 1]; 0.1 is a clibration constant
let trend_score = norm_slope*rsquare;
// jump score
let lf2=series_fit_2lines_dynamic(series);
let lslope = todouble(lf2.left.slope);
let rslope = todouble(lf2.right.slope);
let rsquare2 = todouble(lf2.rsquare);
let split_idx = tolong(lf2.split_idx);
let last_left = todouble(lf2.left.interception)+lslope*split_idx;
let first_right = todouble(lf2.right.interception)+rslope;
let jump = first_right-last_left;
let rel_jump = abs(jump/norm_range);
let sign_jump = iff(first_right >= last_left, 1.0, -1.0);
let norm_jump = sign_jump*rel_jump/(rel_jump+0.1); // map rel_jump from [-Inf, +Inf] to [-1, 1]; 0.1 is a clibration constant
let jump_score1 = norm_jump*rsquare2;
// filter for jumps that are not close to the series edges and the right slope has the same direction
let norm_rslope = abs(rslope/norm_range);
let jump_score = iff((sign_jump*rslope >= 0.0 or norm_rslope < 0.02) and split_idx between((0.1*n)..(0.9*n)), jump_score1, 0.0);
let res = iff(advanced, bag_pack("n", n, "low_pct", low_pct, "high_pct", high_pct, "norm_range", norm_range, "slope", slope, "rsquare", rsquare, "rel_slope", rel_slope, "norm_slope", norm_slope,
"trend_score", trend_score, "split_idx", split_idx, "jump", jump, "rsquare2", rsquare2, "last_left", last_left, "first_right", first_right, "rel_jump", rel_jump,
"lslope", lslope, "rslope", rslope, "norm_rslope", norm_rslope, "norm_jump", norm_jump, "jump_score", jump_score)
, bag_pack("trend_score", trend_score, "jump_score", jump_score));
res
};
// Write your query to use the function here.
例
クエリ定義関数を使用するには、埋め込み関数定義の後にそれを呼び出します。
let series_shapes_fl=(series:dynamic, advanced:bool=false)
{
let n = array_length(series);
// calculate normal dynamic range between 10th and 90th percentiles
let xs = array_sort_asc(series);
let low_idx = tolong(n*0.1);
let high_idx = tolong(n*0.9);
let low_pct = todouble(xs[low_idx]);
let high_pct = todouble(xs[high_idx]);
let norm_range = high_pct-low_pct;
// trend score
let lf = series_fit_line_dynamic(series);
let slope = todouble(lf.slope);
let rsquare = todouble(lf.rsquare);
let rel_slope = abs(n*slope/norm_range);
let sign_slope = iff(slope >= 0.0, 1.0, -1.0);
let norm_slope = sign_slope*rel_slope/(rel_slope+0.1); // map rel_slope from [-Inf, +Inf] to [-1, 1]; 0.1 is a clibration constant
let trend_score = norm_slope*rsquare;
// jump score
let lf2=series_fit_2lines_dynamic(series);
let lslope = todouble(lf2.left.slope);
let rslope = todouble(lf2.right.slope);
let rsquare2 = todouble(lf2.rsquare);
let split_idx = tolong(lf2.split_idx);
let last_left = todouble(lf2.left.interception)+lslope*split_idx;
let first_right = todouble(lf2.right.interception)+rslope;
let jump = first_right-last_left;
let rel_jump = abs(jump/norm_range);
let sign_jump = iff(first_right >= last_left, 1.0, -1.0);
let norm_jump = sign_jump*rel_jump/(rel_jump+0.1); // map rel_jump from [-Inf, +Inf] to [-1, 1]; 0.1 is a clibration constant
let jump_score1 = norm_jump*rsquare2;
// filter for jumps that are not close to the series edges and the right slope has the same direction
let norm_rslope = abs(rslope/norm_range);
let jump_score = iff((sign_jump*rslope >= 0.0 or norm_rslope < 0.02) and split_idx between((0.1*n)..(0.9*n)), jump_score1, 0.0);
let res = iff(advanced, bag_pack("n", n, "low_pct", low_pct, "high_pct", high_pct, "norm_range", norm_range, "slope", slope, "rsquare", rsquare, "rel_slope", rel_slope, "norm_slope", norm_slope,
"trend_score", trend_score, "split_idx", split_idx, "jump", jump, "rsquare2", rsquare2, "last_left", last_left, "first_right", first_right, "rel_jump", rel_jump,
"lslope", lslope, "rslope", rslope, "norm_rslope", norm_rslope, "norm_jump", norm_jump, "jump_score", jump_score)
, bag_pack("trend_score", trend_score, "jump_score", jump_score));
res
};
let ts_len = 100;
let noise_pct = 2;
let noise_gain = 3;
union
(print tsid=1 | extend y = array_concat(repeat(20, ts_len/2), repeat(150, ts_len/2))),
(print tsid=2 | extend y = array_concat(repeat(0, ts_len*3/4), repeat(-50, ts_len/4))),
(print tsid=3 | extend y = range(40, 139, 1)),
(print tsid=4 | extend y = range(-20, -109, -1))
| extend x = range(1, array_length(y), 1)
//
| extend shapes = series_shapes_fl(y)
| order by tsid asc
| fork (take 4) (project tsid, shapes)
| render timechart with(series=tsid, xcolumn=x, ycolumns=y)
出力
それぞれの傾向とジャンプ スコア:
tsid shapes
1 {
"trend_score": 0.703199714530169,
"jump_score": 0.90909090909090906
}
2 {
"trend_score": -0.51663751343174869,
"jump_score": -0.90909090909090906
}
3 {
"trend_score": 0.92592592592592582,
"jump_score": 0.0
}
4 {
"trend_score": -0.92592592592592582,
"jump_score": 0.0
}
フィードバック
https://aka.ms/ContentUserFeedback」を参照してください。
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「フィードバックの送信と表示