Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Aggregate MEDIAN, STDEV not work with ROLLUP #816

Closed
brunodenis opened this issue Feb 3, 2017 · 7 comments
Closed

Aggregate MEDIAN, STDEV not work with ROLLUP #816

brunodenis opened this issue Feb 3, 2017 · 7 comments

Comments

@brunodenis
Copy link

error s.sort is not a function

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

@mathiasrw
Copy link
Member

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

@agershun
Copy link
Member

agershun commented Feb 5, 2017 via email

@brunodenis
Copy link
Author

brunodenis commented Feb 6, 2017

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
Copy link
Author

Hi,
Any update on this ?

@brunodenis
Copy link
Author

brunodenis commented Mar 24, 2017

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
Copy link
Member

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
Copy link
Author

brunodenis commented Mar 27, 2017

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;
			}
		}
	};
};

@mathiasrw mathiasrw reopened this Mar 27, 2017
@mathiasrw mathiasrw self-assigned this Mar 27, 2017
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants