OLAP support

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 the extensions.

These analytic functions, which offer the ability to perform complex data analysis within a single SQL statement, are sometimes referred to as OLAP, or On Line Analytical Processing, whose functions include:

• GROUP BY clause extensions – CUBE and ROLLUP

• Analytical functions:

  • Simple aggregates – AVG, COUNT, MAX, MIN, and SUM, STDDEV and VARIANCE

    All simple aggregates, except the Grouping() function, can be used with a windowed function.

  • Window functions:

    • Windowing aggregates – AVG, COUNT, MAX, MIN, and SUM

    • Ranking functions – RANK, DENSE_RANK, PERCENT_RANK

    • 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, LN, EXP, POWER, SQRT, and FLOOR