1 of 14

Aggregate

This section describes how to use AGGREGATE functions in Lenses SQL.

AVG

This page describes the AVG function in Lenses SQL.

AVG(numExpr)

Returns the average of the values in a group. It ignores the null value. It can be used with numeric input only.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

CREATE TABLE order-events 
     (_key string, total int, orderId int) 
FORMAT 
     (string, json) 
PROPERTIES 
    (partitions=1, compacted=false); 

INSERT INTO order-events  
    (_key, name, total, orderId) 
VALUES 
   ("user1",23423,23423

sample code:

USE `kafka`;
SELECT AVG(total) 
FROM orders-events
LIMIT 1000;

Output:

{
  "value": {
    "AVG": "54.540900000000000000"
  }
}

BOTTOMK

This page describes the BOTTOMK function in Lenses SQL.

Returns the last K lowest ranked values. The ranking is based on how many times a value has been seen.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

Sample code:

Output:

COLLECT

This page describe the COLLECT function in Lenses SQL.

COLLECT(expr, maxN)

Returns an array in which each value in the input set is assigned to an element of the array.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

Sample code:

USE `kafka`;
SELECT COLLECT(total, 5) 
FROM orders-events

Output:

{
  "value": {
    "COLLECT": [
      43.63,
      16.93,
      80.47,
      56.86,
      29.12
    ]
  }
}

COLLECT_UNIQUE

This page describes the COLLECT_UNIQUE function in Lenses SQL.

COLLECT_UNIQUE(expr, maxN)

Returns an array of unique values in which each value in the input set is assigned to an element of the array.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

Sample code:

USE `kafka`;
SELECT COLLECT_UNIQUE(total, 5) 
FROM orders-events

Output:

{
  "value": {
    "COLLECT_UNIQUE": [
      56.8,
      25.75,
      88.97,
      19.47,
      99.26
    ]
  }
}

COUNT

This page describes the COUNT function in Lenses SQL.

COUNT(*) AS total

Returns the number of records returned by a query or the records in a group as a result of a GROUP BY statement.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

Sample code:

USE `kafka`;
SELECT COUNT(id) 
FROM orders-events

Output:

{
  "value": {
    "COUNT": 1000
  }
}

FIRST

This page describes the FIRST function in Lenses SQL.

FIRST(expr)

Returns the first item seen in a group.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

Sample code:

USE `kafka`;
SELECT FIRST(id) 
FROM orders-events

Output:

{
  "value": {
    "FIRST": "d4e60554-260d-4f14-873a-9c0352ad9387"
  }
}

LAST

This page describes the LAST function in Lenses SQL.

LAST(expr)

Returns the last item seen in a group.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

Sample code:

USE `kafka`;
SELECT LAST(id) 
FROM orders-events

Output:

{
  "value": {
    "LAST": "9ce6a2c1-104f-49d8-818a-7fb632bb2f29"
  }
}

MAXK

This page describes the MAXK function in Lenses SQL.

MAXK(numExpr, N)

Returns the N largest values of a numExpr.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

Sample code:

USE `kafka`;
SELECT MAXK(total) 
FROM orders-events

Output:

{
  "value": {
    "MAXK": [
      99.92
    ]
  }
}

MAXK_UNIQUE

This page describes the MAXK_UNIQUE function in Lenses SQL.

Returns the N smallest unique values of a numExpr.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

Sample code:

Output:

MINK

This page describes the MINK function in Lenses SQL.

MINK(numExpr, N)

Returns the N smallest values of an numExpr.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

Sample code:

USE `kafka`;
SELECT MINK(total) 
FROM orders-events

Output:

{
  "value": {
    "MINK": [
      10.14
    ]
  }
}

MINK_UNIQUE

This page describes the MINK_UNIQUE function in Lenses SQL.

MINK_UNIQUE(numExpr, N)

Returns the N smallest unique values of a numExpr.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

Sample code:

USE `kafka`;
SELECT MINK_UNIQUE(total) 
FROM orders-events

Output:

{
  "value": {
    "MINK_UNIQUE": [
      10.14
    ]
  }
}

SUM

This page describes the SUM function in Lenses SQL.

SUM(numExpr)

Returns the sum of all the values, in the expression. It can be used with numeric input only. Null values are ignored.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

Sample code:

USE `kafka`;
SELECT SUM(total) 
FROM orders-events

Output:

{
  "value": {
    "SUM": "163053.270000000000000000"
  }
}

TOPK

This page describes the TOPK function in Lenses SQL.

TOPK(numExpr, N)

Returns the K highest ranked values. The ranking is based on how many times a value has been seen.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

Sample code:

USE `kafka`;
SELECT TOPK(id) 
FROM orders-events

Output:

{
  "value": {
    "TOPK": [
      "ffd7c548-06bb-40e9-8aae-558be65c1e70"
    ]
  }
}