Database Reference
In-Depth Information
Besides creating an array, the values can be concatenated together into one text
string using the
string_agg()
function.
SELECT STRING_AGG(s.sales ORDER BY s.sales) AS ord_sales,
COUNT(*) AS n
FROM sales_by_week s
WHERE s.year = 2014
AND s.week <= 5
ord_sales n
15645391572128158014615823311600769 5
However, in this particular example, it may be useful to separate the values with a
delimiter, such as a comma.
SELECT STRING_AGG(s.sales, ',' ORDER BY s.sales) AS
ord_sales,
COUNT(*) AS n
FROM sales_by_week s
WHERE s.year = 2014
AND s.week <= 5
ord_sales n
1564539,1572128,1580146,1582331,1600769 5
Although the sorted sales appear to be an array, there are no braces around the
output. So the displayed ordered sales are a text string.
11.3.4 MADlib
SQL implementations include many basic analytical and statistical built-in
functions, such as means and variances. As illustrated in this chapter, SQL also
enables the development of user-defined functions and aggregates to provide
additional functionality. Furthermore, SQL databases can utilize an external
library of functions. One such library is known as
MADlib
. The description file [2]
included with the MADlib library download states the following:
MADlib is an open-source library for scalable in-database analytics. It offers
data-parallel implementations of mathematical, statistical, and machine
learning methods for structured and unstructured data.
