Creating custom three dimensional aggregation

Question

I am struggling to create custom three dimensional aggregation for use in workshop application charts.

I tried to calculate trs = trs_time/(ntrs_time + trs_time) over time for the use in workshop chart. Below is the code that I used to do it. In the next step I tried to do similar thing but to segment trs over time by machine_number - plcId (commented segmentBy code line) the problem is that with my poor understanding of Type Script I am struggling to calculate trs for three dimensional aggregation (accessing sums of trs_time and ntrs_time and dividing them the correct way).

export class MyFunctions {

    @Function()
    public async trs_example(data: ObjectSet<PerformanceProduction>): Promise<TwoDimensionalAggregation<IRange<Timestamp>, Double>> {
        const sum_trs = await data
            .filter(col => col.type.exactMatch("trs_h"))        
            .groupBy(col => col.reportingDate.byDays())
            // .segmentBy(col => col.plcId.topValues())
            .sum(col => col.time);
        const sum_ntrs = await data
            .filter(col => col.type.exactMatch("ntrs_time"))        
            .groupBy(col => col.reportingDate.byDays())
            // .segmentBy(col => col.plcId.topValues())
            .sum(col => col.time);
        let n = sum_trs['buckets'];
        const m = sum_ntrs['buckets'];
    
        n.forEach((num1, index) => {
            const num2 = m[index];
            let calc = (num1['value']/(num2['value']+num1['value']));
            n[index]['value'] = calc;
            });
        console.log(n)
        return {'buckets': n}
    }

I need a way of accessing sum_trs and sum_ntrs for the same time_range and plc_id.

Is this something that you know how to write in SQL, I am not really sure what the desired outcome is/what exactly you aren't able to do at the moment, if so could you post the SQL that would be helpful — aturc, Mar 01 '23 at 14:20
Hi 25th_kania I don't understand what you are trying to achieve here, could you elaborate on your example? If I'm not mistaken when you uncomment your segmentBy, the buckets should get an extra nested property for plcId that you can loop inside your already existing `n.forEach` loop — fmsf, Mar 01 '23 at 17:55
@fmsf yeah I wanted to do exactly that - loop over that extra nested property — 25th_kania, Mar 02 '23 at 15:52

score 1 · Answer 1 · answered Mar 02 '23 at 14:01

I managed to access it with another loop as indicated in one of the comments, I tried it before but I forgot to call to value attribute...

@Function()
public async trs_by_ref(data: ObjectSet<PerformanceProduction>): Promise<ThreeDimensionalAggregation<IRange<Timestamp>, string>> {
    const sum_trs = await data
        .filter(col => col.type.exactMatch("trs_h"))        
        .groupBy(col => col.reportingDate.byDays())
        .segmentBy(col => col.plcId.topValues())
        .sum(col => col.time);
    const sum_ntrs = await data
        .filter(col => Filters.or(
            Filters.and(col.type.exactMatch("ntrs_time")),
            Filters.and(col.type.exactMatch("slowdown_h"))
        )) 
        .groupBy(col => col.reportingDate.byDays())
        .segmentBy(col => col.plcId.topValues())
        .sum(col => col.time);
    let n = sum_trs.buckets;
    const m = sum_ntrs.buckets;

    n.forEach((num1, index) => {
        const curr = num1.value
        const num2 = m[index].value;
        curr.forEach((num11, index11) => {
            const num22 = num2[index11]
            let calc = num11['value']/(num22['value'] + num11['value']);
            cur[index11]['value'] = calc
            console.log(curr)
        })
        console.log(n)
    });
    return {'buckets': n}

Creating custom three dimensional aggregation

1 Answers1