Pinot Query Language Examples

Examples

The Pinot Query Language is very similar to standard SQL:

SELECT COUNT(*) FROM mytable

Usual aggregation functions, grouping and ordering are supported:

SELECT MIN(foo), MAX(foo), SUM(foo), AVG(foo) FROM mytable
GROUP BY bar, baz TOP 50

Filtering is as well:

SELECT COUNT(*) FROM mytable WHERE foo = '-1' AND bar BETWEEN 1 AND 20 OR
(baz < 42 AND quux IN ('hello', 'goodbye') AND quuux NOT IN (42, 69))

Selection also works:

SELECT foo, bar WHERE quux < 5 ORDER BY foo LIMIT 50

PQL documentation

SELECT

The select statement is as follows

SELECT <outputColumn> (, outputColumn)*
  FROM <tableName>
  (WHERE ... | GROUP BY ... | ORDER BY ... | TOP ... | LIMIT ...)*

outputColumn can be * to project all columns, a list of columns (foo, bar, baz) and aggregation functions (min(foo), max(bar), avg(baz)). Supported aggregations are min, max, sum, count, distinctCount and avg.

WHERE

Supported predicates are comparisons with a constant using the standard SQL operators (=, <, <=, >, >=, <>) , range comparisons using BETWEEN (foo BETWEEN 42 AND 69), set membership (foo IN (1, 2, 4, 8)) and exclusion (foo NOT IN (1, 2, 4, 8)).

GROUP BY, ORDER BY

The GROUP BY and ORDER BY clauses respectively group aggregation results and order results by a list of columns.

TOP

The TOP n clause causes the n largest results to be returned. If not specified, the top 10 groups are returned when an aggregation result is computed.

LIMIT

The LIMIT n clause causes the selection results to contain at most n results.