The 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
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
Extensions to the ANSI SQL standard to include complex data analysis were introduced as an amendment to the 1999 SQL standard. SAP Sybase IQ SQL enhancements support these extensions.
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, SAP Sybase IQ builds OLAP features into the database itself, making deployment and integration with other database features, such as stored procedures, easy and seamless.