# Presto Functions and Features Added Between 0 189 and 0 205 2018 * [v0.189](/products/customer-data-platform/data-workbench/queries/archives/presto-functions-and-features-added-between-0-189-and-0-205-2018#h1_1013277974) * [v0.190](/products/customer-data-platform/data-workbench/queries/archives/presto-functions-and-features-added-between-0-189-and-0-205-2018#h1__196575608) * [v0.193](/products/customer-data-platform/data-workbench/queries/archives/presto-functions-and-features-added-between-0-189-and-0-205-2018#h1_206708919) * [v0.196](/products/customer-data-platform/data-workbench/queries/archives/presto-functions-and-features-added-between-0-189-and-0-205-2018#h1_966223806) * [v0.197 and v0.198](/products/customer-data-platform/data-workbench/queries/archives/presto-functions-and-features-added-between-0-189-and-0-205-2018#h1__981318810) * [v0.197](/products/customer-data-platform/data-workbench/queries/archives/presto-functions-and-features-added-between-0-189-and-0-205-2018#h1__1762659549) * [v0.199](/products/customer-data-platform/data-workbench/queries/archives/presto-functions-and-features-added-between-0-189-and-0-205-2018#h1_1013277973) * [v0.200](/products/customer-data-platform/data-workbench/queries/archives/presto-functions-and-features-added-between-0-189-and-0-205-2018#h1__1218212512) * [v0.202](/products/customer-data-platform/data-workbench/queries/archives/presto-functions-and-features-added-between-0-189-and-0-205-2018#h1__55413098) # v0.189 ## Add support for dereferencing row fields in lambda expressions The following lambda function which uses a table alias is supported. ```sql select transform(array[1], x -> x + t.a) from (values 1, 2, 3) t(a); => ["2"] ["3"] ["4"] ``` # v0.190 ## Add support for ORDER BY in aggregation functions Some aggregate functions such as `array_agg()` produce different results depending on the order of input values. This ordering can be specified by writing an [ORDER BY Clause](https://trino.io/docs/423/sql/select.html#order-by-clause) within the aggregate function: ```sql array_agg(x ORDER BY y DESC) array_agg(x ORDER BY x, y, z) ``` ## Add support for casting to INTERVAL types Optimized aggregations on INTERVAL types ```sql SELECT CAST(INTERVAL '18000' SECOND AS VARCHAR); =>0 05:00:00.000 ``` ## Add ST_Buffer() geospatial function Returns the geometry that represents all points whose distance from the specified geometry is less than or equal to the specified distance. ```sql SELECT ST_BUFFER(ST_Point(1, 2), 2.0) => POLYGON ((3 2, 2.9957178464772056 2.130806258460286, 2.982889722747619 2.2610523844401027, 2.9615705608064595 2.390180644032256, 2.9318516525781355 2.517638090205041, 2.8938602589902103 2.6428789306063227, 2.8477590650225726 2.765366864730179, 2.7937454830653756 2.8845773804380017, 2.7320508075688767 2.999999999999999, 2.66293922460509 3.1111404660392035, 2.58670668058247 3.217522858017441, 2.5036796149579543 3.318691630200137, 2.414213562373095 3.4142135623730945, 2.3186916302001377 3.5036796149579543, 2.217522858017441 3.58670668058247, 2.1111404660392044 3.66293922460509, 2 3.7320508075688767, 1.8845773804380026 3.7937454830653765, 1.7653668647301795 3.847759065022573, 1.6428789306063232 3.893860258990211, 1.5176380902050415 3.9318516525781364, 1.3901806440322564 3.961570560806461, 1.2610523844401031 3.982889722747621, 1.130806258460286 3.995717846477207, 1 4, 0.8691937415397142 3.9957178464772056, 0.7389476155598972 3.982889722747619, 0.6098193559677438 3.9615705608064595, 0.48236190979495897 3.9318516525781355, 0.3571210693936774 3.8938602589902103, 0.2346331352698211 3.8477590650225726, 0.11542261956199817 3.7937454830653756, 7.7715611723760960e-16 3.7320508075688767, -0.11114046603920369 3.66293922460509, -0.21752285801744065 3.58670668058247, -0.318691630200137 3.5036796149579543, -0.4142135623730945 3.414213562373095, -0.5036796149579543 3.3186916302001377, -0.5867066805824699 3.217522858017441, -0.66293922460509 3.1111404660392044, -0.732050807568877 3, -0.7937454830653763 2.8845773804380026, -0.8477590650225733 2.7653668647301792, -0.8938602589902112 2.642878930606323, -0.9318516525781366 2.5176380902050415, -0.9615705608064609 2.3901806440322564, -0.9828897227476208 2.261052384440103, -0.995717846477207 2.1308062584602863, -1 2, -0.9957178464772056 1.8691937415397142, -0.9828897227476194 1.7389476155598973, -0.9615705608064595 1.6098193559677438, -0.9318516525781353 1.482361909794959, -0.8938602589902103 1.3571210693936773, -0.8477590650225726 1.2346331352698212, -0.7937454830653756 1.1154226195619983, -0.7320508075688765 1.0000000000000009, -0.6629392246050898 0.8888595339607963, -0.5867066805824699 0.7824771419825594, -0.5036796149579543 0.681308369799863, -0.4142135623730947 0.5857864376269055, -0.31869163020013747 0.4963203850420457, -0.2175228580174411 0.4132933194175301, -0.11114046603920436 0.33706077539491, 1.1102230246251565e-16 0.26794919243112303, 0.11542261956199751 0.2062545169346237, 0.23463313526982055 0.15224093497742675, 0.35712106939367694 0.10613974100978885, 0.4823619097949585 0.06814834742186338, 0.6098193559677435 0.03842943919353914, 0.7389476155598969 0.017110277252379236, 0.8691937415397138 0.004282153522793042, 1 0, 1.1308062584602858 0.004282153522794374, 1.2610523844401027 0.017110277252380568, 1.3901806440322562 0.03842943919354047, 1.517638090205041 0.06814834742186471, 1.6428789306063227 0.10613974100978973, 1.7653668647301788 0.1522409349774274, 1.8845773804380017 0.20625451693462438, 1.9999999999999991 0.2679491924311235, 2.1111404660392035 0.3370607753949102, 2.217522858017441 0.4132933194175301, 2.318691630200137 0.4963203850420457, 2.4142135623730945 0.5857864376269053, 2.5036796149579543 0.6813083697998625, 2.58670668058247 0.7824771419825589, 2.66293922460509 0.8888595339607956, 2.7320508075688767 1, 2.7937454830653765 1.1154226195619974, 2.847759065022573 1.2346331352698205, 2.893860258990211 1.3571210693936768, 2.9318516525781364 1.4823619097949585, 2.961570560806461 1.6098193559677436, 2.982889722747621 1.7389476155598969, 2.995717846477207 1.869193741539714, 3 2)) ``` ## approx_distinct()function now supports a standard error in the range of [0.0040625, 0.26000] Returns the approximate number of distinct input values. This function provides an approximation of `count(DISTINCT x)`. Zero is returned if all input values are null. This function should produce a standard error of no more than `e`, which is the standard deviation of the (approximately normal) error distribution over all possible sets. It does not guarantee an upper bound on the error for any specific input set. The current implementation of this function requires that `e` be in the range of `[0.0040625, 0.26000]`. ```sql SELECT APPROX_DISTINCT(x, value) from tbl; ``` # v0.193 ## Add map_zip_with() lambda function Merges the two given maps into a single map by applying `function` to the pair of values with the same key. For keys only presented in one map, NULL will be passed as the value for the missing key. ```sql map_zip_with(map, map, function) → map ``` ```sql SELECT map_zip_with(MAP(ARRAY[1, 2, 3], ARRAY['a', 'b', 'c']), -- {1 -> ad, 2 -> be, 3 -> cf} MAP(ARRAY[1, 2, 3], ARRAY['d', 'e', 'f']), (k, v1, v2) -> concat(v1, v2)); SELECT map_zip_with(MAP(ARRAY['k1', 'k2'], ARRAY[1, 2]), -- {k1 -> ROW(1, null), k2 -> ROW(2, 4), k3 -> ROW(null, 9)} MAP(ARRAY['k2', 'k3'], ARRAY[4, 9]), (k, v1, v2) -> (v1, v2)); SELECT map_zip_with(MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 8, 27]), -- {a -> a1, b -> b4, c -> c9} MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 2, 3]), (k, v1, v2) -> k || CAST(v1/v2 AS VARCHAR)); ``` ## Add normal_cdf() function Returns the cumulative distribution function value for a normal distribution. ```sql Normal_cdf(mean, standard deviation, value) → double ``` # v0.196 ## Add is_json_scalar() function Determine if `json` is a scalar (i.e. a JSON number, a JSON string, `true`,`false` or `null`): ```sql SELECT is_json_scalar('1'); => true SELECT is_json_scalar('[1, 2, 3]'); => false ``` ## Add regexp_replace()function variant that executes a lambda for each replacement Replaces every instance of the substring matched by the regular expression `pattern` in `string` with `replacement`. [Capturing groups](http://docs.oracle.com/javase/8/docs/api/java/util/regex/Pattern.md#cg) can be referenced in `replacement` using `$g` for a numbered group or `${name}` for a named group. A dollar sign (`$`) may be included in the replacement by escaping it with a backslash (`\$`): ```sql SELECT regexp_replace('1a 2b 14m', '(\d+)([ab]) ', '3c$2 '); => '3ca 3cb 14m' ``` # v0.197 and v0.198 ## Add DATE variant for sequence()function with an implicit one-day step increment Generate a sequence of integers from `start` to `stop`, incrementing by `1` if `start` is less than or equal to `stop`,otherwise `-1`. # v0.197 ## Add ST_IsSimple() geospatial function ```sql SELECT ST_IsSimple(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 1 2))')); st_issimple ------------- t (1 row) SELECT ST_IsSimple(ST_GeomFromText('LINESTRING(1 1,2 2,2 3.5,1 3,1 2,2 1)')); st_issimple ------------- f (1 row) ``` # v0.199 ## Add hmac_md5(), hmac_sha1(), hmac_sha256(), and hmac_sha512()functions Computes HMAC with md5 or sha1 or sha256 or sha512 of `binary` with the given `key` ```sql hmac_md5(binary, key) → varbinaryComputes hmac_sha1(binary, key) → varbinaryComputes  hmac_sha256(binary, key) → varbinaryComputes  hmac_sha512(binary, key) → varbinaryComputes ``` ## Add array_sort() function that takes a lambda as a comparator Sorts and returns the `array` based on the given comparator `function`. The comparator will take two nullable arguments representing two nullable elements of the `array`. It returns -1, 0, or 1 as the first nullable element is less than, equal to, or greater than the second nullable element. If the comparator function returns other values (including `NULL`), the query will fail and raise an error ```sql SELECT array_sort(ARRAY [3, 2, 5, 1, 2], (x, y) -> IF(x < y, 1, IF(x = y, 0, -1))); -- [5, 3, 2, 2, 1] SELECT array_sort(ARRAY ['bc', 'ab', 'dc'], (x, y) -> IF(x < y, 1, IF(x = y, 0, -1))); -- ['dc', 'bc', 'ab'] SELECT array_sort(ARRAY [3, 2, null, 5, null, 1, 2], -- sort null first with descending order (x, y) -> CASE WHEN x IS NULL THEN -1 WHEN y IS NULL THEN 1 WHEN x < y THEN 1 WHEN x = y THEN 0 ELSE -1 END); -- [null, null, 5, 3, 2, 2, 1] SELECT array_sort(ARRAY [3, 2, null, 5, null, 1, 2], -- sort null last with descending order (x, y) -> CASE WHEN x IS NULL THEN 1 WHEN y IS NULL THEN -1 WHEN x < y THEN 1 WHEN x = y THEN 0 ELSE -1 END); -- [5, 3, 2, 2, 1, null, null] SELECT array_sort(ARRAY ['a', 'abcd', 'abc'], -- sort by string length (x, y) -> IF(length(x) < length(y), -1, IF(length(x) = length(y), 0, 1))); -- ['a', 'abc', 'abcd'] SELECT array_sort(ARRAY [ARRAY[2, 3, 1], ARRAY[4, 2, 1, 4], ARRAY[1, 2]], -- sort by array length (x, y) -> IF(cardinality(x) < cardinality(y), -1, IF(cardinality(x) = cardinality(y), 0, 1))); -- [[1, 2], [2, 3, 1], [4, 2, 1, 4]] ``` ## Add line_locate_point()geospatial function Returns a float between 0 and 1 representing the location of the closest point on the LineString to the given Point, as a fraction of total 2d line length. Returns `null` if a LineString or a Point is empty or `null`. ```sql SELECT LINE_LOCATE_POINT(GEOMFROMTEXT('LINESTRING(0 0, 5 5, 10 10)'), GEOMFROMTEXT('POINT(5 5)')); ------------- col1 ------------- 0.500 ------------- ``` # v0.200 ## Add ST_GeometryType() geospatial function Returns the type of the geometry. ```sql SELECT ST_GeometryType(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)')); => ST_LineString ``` # v0.202 ## Add multimap_from_entries()function Returns a map created from the given array of entries. ```sql SELECT multimap_from_entries(ARRAY[(1, 'x'), (2, 'y'), (1, 'z')]); -- {1 -> ['x', 'z'], 2 -> ['y']} ``` ## Add bing_tiles_around()geospatial functions Returns a collection of Bing tiles that surround the point specified by the latitude and longitude arguments at a given zoom level. ```sql bing_tiles_around(latitude, longitude, zoom_level) → array ``` ## Add ST_NumGeometries() geospatial functions Returns the number of geometries in the collection. If the geometry is a collection of geometries (e.g., GEOMETRYCOLLECTION or MULTI*), returns the number of geometries, for single geometries returns 1, for empty geometries returns 0. ```sql ST_NumGeometries(Geometry) → bigint ``` ```sql SELECT ST_NumGeometries(ST_GeomFromEWKT('GEOMETRYCOLLECTION(MULTIPOINT(-2 3 , -2 2), LINESTRING(5 5 ,10 10), POLYGON((-7 4.2,-7.1 5,-7.1 4.3,-7 4.2)))')); ``` ## Add ST_GeometryN() geospatial functions Returns the geometry element at a given index (indices start at 1). If the geometry is a collection of geometries (e.g., GEOMETRYCOLLECTION or MULTI*), returns the geometry at a given index. If the given index is less than 1 or greater than the total number of elements in the collection, returns `NULL`.Use :func:`ST_NumGeometries` to find out the total number of elements. Singular geometries (e.g., POINT, LINESTRING, POLYGON), are treated as collections of one element. Empty geometries are treated as empty collections. ```sql SELECT gid, n, ST_GeometryN(the_geom, n) FROM tbl CROSS JOIN generate_series(1,100) n WHERE n <= ST_NumGeometries(the_geom); ``` ## Add ST_ConvexHull() geospatial functions Returns the minimum convex geometry that encloses all input geometries. This function doesn’t support geometry collections. ```sql SELECT t.tbl_type, ST_ConvexHull(ST_Collect(t.the_geom)) As the_geom FROM tbl As t GROUP BY t.tbl_type; ``` ## Add wilson_interval_lower() and wilson_interval_upper() functions Returns the lower bound of the Wilson score interval of a Bernoulli trial process at a confidence specified by the z-score Z. ```sql assertFunction("ST_AsText(ST_EnvelopeAsPts(ST_GeometryFromText('MULTIPOINT (1 2, 2 4, 3 6, 4 8)')))", VARCHAR, "MULTIPOINT ((1 2), (4 8))"); ```