to_ordered_list


to_ordered_list(PRIMITIVE value [, PRIMITIVE key, const string options]) – Return list of values sorted by value itself or specific key

WITH t as (
SELECT 5 as key, ‘apple’ as value
UNION ALL
SELECT 3 as key, ‘banana’ as value
UNION ALL
SELECT 4 as key, ‘candy’ as value
UNION ALL
SELECT 2 as key, ‘donut’ as value
UNION ALL
SELECT 3 as key, ‘egg’ as value
)
SELECT — expected output
to_ordered_list(value, key, ‘-reverse’), — [apple, candy, (banana, egg | egg, banana), donut] (reverse order)
to_ordered_list(value, key, ‘-k 2’), — [apple, candy] (top-k)
to_ordered_list(value, key, ‘-k 100’), — [apple, candy, (banana, egg | egg, banana), dunut]
to_ordered_list(value, key, ‘-k 2 -reverse’), — [donut, (banana | egg)] (reverse top-k = tail-k)
to_ordered_list(value, key), — [donut, (banana, egg | egg, banana), candy, apple] (natural order)
to_ordered_list(value, key, ‘-k -2’), — [donut, (banana | egg)] (tail-k)
to_ordered_list(value, key, ‘-k -100’), — [donut, (banana, egg | egg, banana), candy, apple]
to_ordered_list(value, key, ‘-k -2 -reverse’), — [apple, candy] (reverse tail-k = top-k)
to_ordered_list(value, ‘-k 2’), — [egg, donut] (alphabetically)
to_ordered_list(key, ‘-k -2 -reverse’), — [5, 4] (top-2 keys)
to_ordered_list(key), — [2, 3, 3, 4, 5] (natural ordered keys)
to_ordered_list(value, key, ‘-k 2 -kv_map’), — {4:”candy”,5:”apple”}
to_ordered_list(value, key, ‘-k 2 -vk_map’) — {“candy”:4,”apple”:5}
FROM
t

Platforms: WhereOS, Spark, Hive
Class: hivemall.tools.list.UDAFToOrderedList

More functions can be added to WhereOS via Python or R bindings or as Java & Scala UDF (user-defined function), UDAF (user-defined aggregation function) and UDTF (user-defined table generating function) extensions. Custom libraries can be added on via Settings-page or installed from WhereOS Store.

Leave a Reply