Alasql: Aggregate MEDIAN, STDEV not work with ROLLUP

[brunodenis]

error s.sort is not a function

SELECT MEDIAN(value) from ? GROUP BY ROLLUP(column)

[mathiasrw]

Thank you for taking the time to let us know :)

[agershun]

You need to rewrite this to:

SELECT MEDIAN([value]) from ? GROUP BY ROLLUP([column])

2017-02-05 16:04 GMT+03:00 Mathias Rangel Wulff [e-mail]:

Thank you for taking the time to let us know :)

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/agershun/alasql/issues/816#issuecomment-277518527,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAnP4B_X6_f8orr4M0lC0NWl8G3nJ686ks5rZcjngaJpZM4L2RQG
.

[brunodenis]

Thanks for your answer.

both methods share the same error message, see below.
var data = [
{DEX:"A",WEEK:"S1",IND1:5,IND2:90},
{DEX:"A",WEEK:"S1",IND1:10,IND2:90},
{DEX:"A",WEEK:"S2",IND1:7,IND2:60},
{DEX:"B",WEEK:"S1",IND1:10,IND2:41},
{DEX:"B",WEEK:"S2",IND1:5,IND2:11}
];

var ancestors = 'SELECT DEX,MEDIAN(IND1) FROM ? GROUP BY ROLLUP(DEX)';
var ancestors = 'SELECT DEX,MEDIAN([IND1]) FROM ? GROUP BY ROLLUP([DEX])';

alasql.promise(ancestors,[data])
.then(function(data){
console.log(data);
}).catch(function(err){
console.log('Error:', err);
});

Error: s.sort is not a function

[brunodenis]

Hi,
Any update on this ?

[brunodenis]

Please find correct MEDIAN aggregate that works with ROLLUP

alasql.aggr.MEDIAN = function(v,s,stage){
    if(stage == 1) {
      return [v];
    } else if(stage ==2) {
      s.push(v);
      return s;
    } else {
        if(!s.length) {
        return s;
      } else {
        var r = s.sort();
        var p = (r.length+1)/2;
        if(Number.isInteger(p)) {
            return r[p-1]; 
        } else {
            return (r[Math.floor(p-1)] + r[Math.ceil(p-1)])/2;
        }
      }
    };
  };

[mathiasrw]

Awesome!

I can see you are counting the null values too - any inputs on why its better than not counting them?

Code optimized slightly and committed to development branch for next release

[brunodenis]

nulls shall not be counted

alasql.aggr.MEDIAN = function(v,s,stage){                                                                               
    if(stage == 2) {
        if(v === null) {
            return s;
        } else {
            s.push(v);
            return s;
        }
    } else if(stage == 1) {
        if(v === null) {
            return [];
        } else {
            return [v];
        }
    } else {
        if(!s.length) {
            return s;
        } else {
            var r = s.sort();
            var p = (r.length+1)/2;
            if(Number.isInteger(p)) {
                return r[p-1]; 
            } else {
                return (r[Math.floor(p-1)] + r[Math.ceil(p-1)])/2;
            }
        }
    };
};