Database Reference
In-Depth Information
Summary
This chapter presented several techniques and examples illustrating how SQL can
be used to perform in-database analytics. A typical SQL query involves joining
several tables, filtering the returned dataset to the desired records with a
WHERE
clause, and specifying the particular columns of interest. SQL provides the set
operations of
UNION
and
UNION ALL
to merge the results of two or more
SELECT
statements or
INTERSECT
to find common record elements. Other SQL queries can
summarize a dataset using aggregate functions such as
COUNT()
and
SUM()
and
the
GROUP BY
clause. Grouping extensions such as the
CUBE
and
ROLLUP
operators
enable the computation of subtotals and grand totals.
Although SQL is most commonly associated with structured data, SQL tables often
contain unstructured data such as comments, descriptions, and other freeform text
content. Regular expressions and related functions can be used in SQL to examine
and restructure such unstructured data for further analysis.
More complex SQL queries can utilize window functions to supply computed values
such as ranks and rolling averages along with an original dataset. In addition to
built-in functions, SQL offers the ability to create user-defined functions. Although
it is possible to process the data within a database and extract the results into an
analytical tool such as R, external libraries such as MADlib can be utilized by SQL to
conduct statistical analyses within a database.
