Extensions to the ANSI SQL standard to include complex data analysis were introduced as an amendment to the 1999 SQL standard. Sybase IQ added portions of these SQL enhancements provides additional comprehensive support for the extensions.
These analytic functions, which offer the ability to perform complex data analysis within a single SQL statement, are facilitated by a category of software technology named online analytical processing (OLAP). Its functions are shown in the following list:
GROUP BY clause extensions – CUBE and ROLLUP
Analytical functions:
Simple aggregates – AVG, COUNT, MAX, MIN, and SUM, STDDEV and VARIANCE
You can use simple aggregate functions, except Grouping(), with an
OLAP windowed function.
Window functions:
Windowing aggregates – AVG, COUNT, MAX, MIN, and SUM
Ranking functions – RANK, DENSE_RANK, PERCENT_RANK, and NTILE
Statistical functions – STDDEV, STDDEV_SAMP, STDDEV_POP, VARIANCE, VAR_POP, VAR_SAMP, REGR_AVGX, REGR_AVGY, REGR_COUNT, REGR_INTERCEPT, REGR_R2, REGR_SLOPE, REGR_SXX, REGR_SXY, REGR_SYY, CORR, COVAR_POP, COVAR_SAMP, CUME_DIST, EXP_WEIGHTED_AVG, and WEIGHTED_AVG.
Distribution functions – PERCENTILE_CONT and PERCENTILE_DISC
Numeric functions – WIDTH_BUCKET, CEIL, and LN, EXP, POWER, SQRT, and FLOOR
Some database products provide a separate OLAP module that requires you to move data from the database into the OLAP module before analyzing it. By contrast, Sybase IQ builds OLAP features into the database itself, making deployment and integration with other database features, such as stored procedures, easy and seamless.
