unique

info

This is part of our beta expression language AQL. Learn more. Request beta.

Definition

Return a new table with all unique combination of values in the specified dimensions. It is similar to group but without the need to specify a source table. The difference is that group will only return combination of values that exist with respect to the source table, while unique will choose the source table with the most number of rows.

Syntax

unique(dimension, dimension, ...)

Examples

unique(orders.id, products.id) // -> Table(orders.id, products.id)

unique(orders.id, products.id, customers.id) // -> Table(orders.id, products.id, customers.id)

Input

• dimension (repeatable): A fully-qualified reference to a dimension. The output table will have one row for each unique combination of values in the specified dimensions.

Output

A new table with one row for each unique combination of values in the specified dimensions.

Sample Usages

Unique orders.id and products.id

unique(orders.id, products.id)

Open in AMQL playground

Result

SQL

SELECT
  "orders"."id" AS "orders->id",
  "products"."id" AS "products->id"
FROM
  "demo"."order_items" "order_items"
  LEFT JOIN "demo"."orders" "orders" ON "order_items"."order_id" = "orders"."id"
  LEFT JOIN "demo"."products" "products" ON "order_items"."product_id" = "products"."id"
GROUP BY
  1,
  2

Using unique with select just like group:

Total product by product category

unique(products.name) | select(products.name, count(products.id))

Open in AMQL playground

Result

SQL

SELECT
  "products"."name" AS "products->name",
  COUNT("products"."id") AS "count_products->id"
FROM
  "demo"."products" "products"
GROUP BY
  1