series_shapes_fl()

De functie series_shapes_fl() is een door de gebruiker gedefinieerde functie (UDF) die positieve/negatieve trend of een sprong in een reeks detecteert. Deze functie gebruikt een tabel met meerdere tijdreeksen (dynamische numerieke matrix) en berekent trend- en sprongscores voor elke reeks. De uitvoer is een woordenlijst (dynamisch) met de scores.

Syntax

T | extend series_shapes_fl(, y_seriesGeavanceerde)

Meer informatie over syntaxisconventies.

Parameters

Naam	Type	Vereist	Beschrijving
y_series	dynamic	✔️	Een matrixcel met numerieke waarden.
Geavanceerde	bool		De standaardwaarde is false. Stel in op true om extra berekende parameters uit te voeren.

Functiedefinitie

U kunt de functie definiëren door de code in te sluiten als een door een query gedefinieerde functie of door deze als volgt te maken als een opgeslagen functie in uw database:

Query's gedefinieerd

Definieer de functie met behulp van de volgende let-instructie. Er zijn geen machtigingen vereist.

Belangrijk

Een let-instructie kan niet zelfstandig worden uitgevoerd. Deze moet worden gevolgd door een tabellaire expressie-instructie. Zie Voorbeeld als u een werkend voorbeeld van series_shapes_fl()wilt uitvoeren.

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.

Opgeslagen

Definieer de opgeslagen functie eenmaal met behulp van de volgende .create function. Machtigingen voor databasegebruikers zijn vereist.

Belangrijk

U moet deze code uitvoeren om de functie te maken voordat u de functie kunt gebruiken, zoals wordt weergegeven in het voorbeeld.

.create-or-alter function with (folder = "Packages\\Series", docstring = "Series detector for positive/negative trend or step. Returns a dynamic with trend and jump scores")
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
}

Voorbeeld

Query's gedefinieerd

Als u een door een query gedefinieerde functie wilt gebruiken, roept u deze aan na de definitie van de ingesloten functie.

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)

Opgeslagen

Belangrijk

Als u dit voorbeeld wilt uitvoeren, moet u eerst de functiedefinitiecode uitvoeren om de functie op te slaan.

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)

Uitvoer

De respectieve trend en sprongscores:

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
        }