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 in previous releases. Sybase IQ 12.7, however, contains comprehensive support for these 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 On Line Analytical Processing (OLAP) whose functions include GROUP BY clause extensions and analytical functions as 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
All simple aggregate functions, except Grouping(), can
be used 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_SAMP, and VAR_POP
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.
