CREATE FACT TABLE inventory
( item int,
price float,
rating float,
state int,
country int
) DISTRIBUTE BY HASH(country);
-- User Defined Aggregate Function invocation, 'price' would be treated as the
-- input column references, '1' would be inferred as an integer at runtime, the
-- ALL is the default keyword to the price column; and the TAX('0.095') would be
-- played as the key-value style argument.
SELECT
MEDIAN(
ON(price, 1)
TAX('0.095'))
FROM inventory
GROUP BY country;
-- Old-style user defined aggregate function invocation.
SELECT MEDIAN(price, 1, 0.095) FROM inventory GROUP BY country;
-- User Defined Aggregate Function invocation, 'price' would be treated as the
-- input column references, '1' would be inferred as an integer at runtime, the
-- DISTINCT would apply to price column; and the K('10') would be
-- played as the key-value style argument.
SELECT
TOP_Kth(
DISTINCT
ON(price, 1)
K('10'))
FROM inventory
GROUP BY country;
-- Old-style user defined aggregate function invocation.
SELECT TOP_Kth(DISTINCT price, 1, 10) FROM inventory GROUP BY country;
-- User Defined Scalar Function invocation, 'price', 'state' and 'country' would be
-- treated as the input column references; and the TAX('0.095') would be played as
-- the key-value style argument.
SELECT
item,
FINAL_PRICE(
ON(price, state, country)
TAX('0.095'))
FROM inventory;
-- Old-style user defined scalar function invocation.
SELECT item, FINAL_PRICE(price, state, country, 0.095) FROM inventory;