# Querying collections in SQL-like style

version 6.0.0-alpha-2

GINQ (Groovy-Integrated Query) lets you query in-memory collections using familiar SQL-like syntax. It also works with parsed XML, JSON, YAML, and other formats that produce collections.

The heart of GINQ is its query-like expressions, which can be very simple:

    from n in [0, 1, 2]
    select n

Or involve multiple clauses:

    from s in sales
    where s.amount > 50
    groupby s.customer as customer into g
    having g.count() > 1
    orderby g.customer
    select g.customer, g.count() as items, g.sum(s -> s.amount) as total

A GINQ expression can include the following clauses:

- `from` — the data source (required). Accepts any `Iterable`, `Stream`, array, or GINQ result set.

- `join` — combines data from additional sources (`join`, `leftjoin`, `rightjoin`, `fulljoin`, `crossjoin`), matched using an `on` condition.

- `where` — filters rows before grouping, as `s.amount > 50` does above.

- `groupby` — groups rows by one or more expressions. The `into` clause binds the group to a variable (like `g` above) that supports aggregate methods such as `count()`, `sum()`, `min()`, `max()`, `avg()`, and `toList()`. Named keys (via `as`) can be accessed as properties, e.g. `g.customer`.

- `having` — filters groups after aggregation, as `g.count() > 1` does above.

- `orderby` — sorts results in ascending (`in asc`, the default) or descending (`in desc`) order, with `nullslast`/`nullsfirst` control.

- `limit` — restricts output to a given size, with optional offset for pagination.

- `select` — the projection (required). Defines the output columns, with optional `as` aliases.

- `union`, `unionall`, `intersect`, `minus` — set operations that combine two complete `from…​select` expressions.

GINQ also supports window functions and nested subqueries — see Advanced Topics for details.

How it works: Under the covers GINQ transforms the query into calls within a fluent API. For example, the above query is transformed into:

    from(sales)
        .where(s -> s.amount > 50)
        .groupByInto(s -> s.customer, g -> g.count() > 1)
        .orderBy(new Order(g -> g.key, true))
        .select((g, q) -> Tuple.tuple(g.key, g.count(), g.sum(s -> s.amount)))

You normally never see this transformed code, but if you are debugging or looking at stacktraces, don’t be surprised to see fluent API calls instead of an exact match to the terms in the SQL-like syntax.

## 1. Basics

This section covers how to embed GINQ in your code and walks through each clause: data sources, projection, filtering, joining, grouping with aggregation, sorting, and pagination.

### 1.1. Integration

Other code in your scripts and classes may use the same names as the SQL-like syntax, such as `from`, `select`, `where`, etc. To avoid conflicts, GINQ expressions must be wrapped in a `GQ` block, which serves as a marker for the GINQ parser to recognize and transform the SQL-like syntax into the underlying fluent API calls.

A GINQ expression is wrapped in a `GQ` block and returns a lazy `Queryable` result:

    def result = GQ {
        /* GINQ SQL-LIKE EXPRESSION */
    }
    def stream = result.stream() // get the stream from GINQ result
    def list = result.toList() // get the list from GINQ result

Since the result is a standard `Queryable`/`Stream`, it composes naturally with other Groovy and JDK library calls:

    import java.util.stream.Collectors

    def numbers = [0, 1, 2]
    assert '0#1#2' == GQ {
        from n in numbers
        select n
    }.stream()
        .map(e -> String.valueOf(e))
        .collect(Collectors.joining('#'))

Two other markers, `GQL` and `@GQ`, are also supported. `GQL` is a shorthand that returns a `List` directly (equivalent to `GQ {…​}.toList()`):

    def list = GQL {
        /* GINQ SQL-LIKE EXPRESSION */
    }

GINQ can also be used as a method annotation — see <<`@GQ` Annotation>> in Advanced Topics:

    @GQ
    def queryMethod() {
        /* GINQ SQL-LIKE EXPRESSION */
    }

### 1.2. Data Source

The data source for GINQ could be specified by `from` clause, which is equivalent to SQL’s `FROM`. Currently GINQ supports `Iterable`, `Stream`, array and GINQ result set as its data source:

#### 1.2.1. `Iterable` Data Source

    from n in [1, 2, 3] select n

#### 1.2.2. `Stream` Data Source

    from n in [1, 2, 3].stream() select n

#### 1.2.3. Array Data Source

    from n in new int[] {1, 2, 3} select n

#### 1.2.4. GINQ Result Set Data Source

    def vt = GQ {from m in [1, 2, 3] select m}
    assert [1, 2, 3] == GQ {
        from n in vt select n
    }.toList()

### 1.3. Projection

The column names could be renamed with `as` clause:

    def result = GQ {
        from n in [1, 2, 3]
        select Math.pow(n, 2) as powerOfN
    }
    assert [[1, 1], [4, 4], [9, 9]] == result.stream().map(r -> [r[0], r.powerOfN]).toList()

| The renamed column could be referenced by its new name, e.g. `r.powerOfN`. Also, it could be referenced by its index, e.g. `r[0]`

    assert [[1, 1], [2, 4], [3, 9]] == GQ {
        from v in (
            from n in [1, 2, 3]
            select n, Math.pow(n, 2) as powerOfN
        )
        select v.n, v.powerOfN
    }.toList()

| `select P1, P2, …​, Pn` is a simplified syntax of `select new NamedRecord(P1, P2, …​, Pn)` when and only when `n` >= 2. Also, `NamedRecord` instance will be created if `as` clause is used. The values stored in the `NamedRecord` could be referenced by their names.

Construct new objects as column values:

    @groovy.transform.EqualsAndHashCode
    class Person {
        String name
        Person(String name) {
            this.name = name
        }
    }
    def persons = [new Person('Daniel'), new Person('Paul'), new Person('Eric')]
    assert persons == GQ {
        from n in ['Daniel', 'Paul', 'Eric']
        select new Person(n)
    }.toList()

#### 1.3.1. Distinct

`distinct` is equivalent to SQL’s `DISTINCT`

    def result = GQ {
        from n in [1, 2, 2, 3, 3, 3]
        select distinct(n)
    }
    assert [1, 2, 3] == result.toList()

    def result = GQ {
        from n in [1, 2, 2, 3, 3, 3]
        select distinct(n, n + 1)
    }
    assert [[1, 2], [2, 3], [3, 4]] == result.toList()

### 1.4. Filtering

`where` is equivalent to SQL’s `WHERE`

    from n in [0, 1, 2, 3, 4, 5]
    where n > 0 && n <= 3
    select n * 2

#### 1.4.1. In

    from n in [0, 1, 2]
    where n in [1, 2]
    select n

    from n in [0, 1, 2]
    where n in (
        from m in [1, 2]
        select m
    )
    select n

    import static groovy.lang.Tuple.tuple
    assert [0, 1] == GQ {
        from n in [0, 1, 2]
        where tuple(n, n + 1) in (
            from m in [1, 2]
            select m - 1, m
        )
        select n
    }.toList()

#### 1.4.2. Not In

    from n in [0, 1, 2]
    where n !in [1, 2]
    select n

    from n in [0, 1, 2]
    where n !in (
        from m in [1, 2]
        select m
    )
    select n

    import static groovy.lang.Tuple.tuple
    assert [2] == GQ {
        from n in [0, 1, 2]
        where tuple(n, n + 1) !in (
            from m in [1, 2]
            select m - 1, m
        )
        select n
    }.toList()

#### 1.4.3. Exists

    from n in [1, 2, 3]
    where (
        from m in [2, 3]
        where m == n
        select m
    ).exists()
    select n

#### 1.4.4. Not Exists

    from n in [1, 2, 3]
    where !(
        from m in [2, 3]
        where m == n
        select m
    ).exists()
    select n

### 1.5. Joining

More data sources for GINQ could be specified by join clauses.

    from n1 in [1, 2, 3]
    join n2 in [1, 3] on n1 == n2
    select n1, n2

| `join` is preferred over `innerjoin` and `innerhashjoin` as it has better readability, and it is smart enough to choose the correct concrete join(i.e. `innerjoin` or `innerhashjoin`) by its `on` clause.

    from n1 in [1, 2, 3]
    innerjoin n2 in [1, 3] on n1 == n2
    select n1, n2

    from n1 in [1, 2, 3]
    leftjoin n2 in [2, 3, 4] on n1 == n2
    select n1, n2

    from n1 in [2, 3, 4]
    rightjoin n2 in [1, 2, 3] on n1 == n2
    select n1, n2

    from n1 in [1, 2, 3]
    fulljoin n2 in [2, 3, 4] on n1 == n2
    select n1, n2

    from n1 in [1, 2, 3]
    crossjoin n2 in [3, 4, 5]
    select n1, n2

hash join is especially efficient when data sources contain lots of objects

    from n1 in [1, 2, 3]
    innerhashjoin n2 in [1, 3] on n1 == n2
    select n1, n2

    from n1 in [1, 2, 3]
    lefthashjoin n2 in [2, 3, 4] on n1 == n2
    select n1, n2

    from n1 in [2, 3, 4]
    righthashjoin n2 in [1, 2, 3] on n1 == n2
    select n1, n2

    from n1 in [1, 2, 3]
    fullhashjoin n2 in [2, 3, 4] on n1 == n2
    select n1, n2

| Only binary expressions(`==`, `&&`) are allowed in the `on` clause of hash join

### 1.6. Grouping

`groupby` is equivalent to SQL’s `GROUP BY`, and `having` is equivalent to SQL’s `HAVING`. The `into` clause binds the grouped result to a named variable, enabling direct method calls for key access and aggregates. The variable is a `GroupResult` which extends `Queryable`, so all aggregate methods (`count()`, `sum()`, `toList()`, etc.) are available as real method calls:

    assert [[1, 2], [3, 2], [6, 3]] == GQ {
        from n in [1, 1, 3, 3, 6, 6, 6]
        groupby n into g
        select g.key, g.count()
    }.toList()

The group variable supports all `Queryable` aggregate methods:

    assert [[1, 2], [3, 6], [6, 18]] == GQ {
        from n in [1, 1, 3, 3, 6, 6, 6]
        groupby n into g
        select g.key, g.sum(n -> n)
    }.toList()

For multi-key grouping, individual keys can be named with `as` and accessed as properties or via subscript:

    def result = GQ {
        from e in employees
        groupby e.dept as department, e.role as role into g
        select g.department, g.role, g.count()
    }.toList()

Subscript access (`g['department']`) and explicit `g.get('department')` are also supported.

`having` works with `into` — the group variable can be used directly in the condition:

    assert [[6, 3]] == GQ {
        from n in [1, 1, 3, 3, 6, 6, 6]
        groupby n into g
        having g.count() > 2
        select g.key, g.count()
    }.toList()

| The `where` clause after `groupby…​into` is reserved for future use; use `having` for now.

#### 1.6.1. Aggregate Functions

GINQ provides some built-in aggregate functions:

Function | Argument Type(s) | Return Type | Description
--- | --- | --- | ---
count() |  | java.lang.Long | number of rows, similar to `count(*)` in SQL
count(expression) | any | java.lang.Long | number of rows for which the value of expression is not `null`
min(expression) | java.lang.Comparable | same as argument type | minimum value of expression across all non-null values
max(expression) | java.lang.Comparable | same as argument type | maximum value of expression across all non-null values
sum(expression) | java.lang.Number | java.math.BigDecimal | sum of expression across all non-null values
avg(expression) | java.lang.Number | java.math.BigDecimal | the average (arithmetic mean) of all non-null values
list(expression) | any | java.util.List | the aggregated list of all non-null values
median(expression) | java.lang.Number | java.math.BigDecimal | value such that the number of non-null values above and below it is the same ("middle" value, not necessarily same as average or mean)
stdev(expression) | java.lang.Number | java.math.BigDecimal | the statistical standard deviation of all non-null values
stdevp(expression) | java.lang.Number | java.math.BigDecimal | the statistical standard deviation for the population for all non-null values
var(expression) | java.lang.Number | java.math.BigDecimal | the statistical variance of all non-null values
varp(expression) | java.lang.Number | java.math.BigDecimal | the statistical variance for the population for all non-null values
agg(expression) | any | any | customizes the aggregation logic in expression and returns single value

    from n in [1, 1, 3, 3, 6, 6, 6]
    groupby n
    select n, count()

    from s in ['a', 'b', 'cd', 'ef']
    groupby s.size() as length
    select length, min(s)

    from s in ['a', 'b', 'cd', 'ef']
    groupby s.size() as length
    select length, max(s)

    from n in [1, 1, 3, 3, 6, 6, 6]
    groupby n
    select n, sum(n)

    from n in [1, 1, 3, 3, 6, 6, 6]
    groupby n
    select n, avg(n)

    from n in [1, 1, 3, 3, 6, 6, 6]
    groupby n
    select n, median(n)

    assert [['A', ['APPLE', 'APRICOT']],
            ['B', ['BANANA']],
            ['C', ['CANTALOUPE']]] == GQL {
        from fruit in ['Apple', 'Apricot', 'Banana', 'Cantaloupe']
        groupby fruit[0] as firstChar
        select firstChar, list(fruit.toUpperCase()) as fruit_list
    }

    def persons = [new Person('Linda', 100, 'Female'),
                   new Person('Daniel', 135, 'Male'),
                   new Person('David', 122, 'Male')]
    assert [['Male', ['Daniel', 'David']], ['Female', ['Linda']]] == GQL {
        from p in persons
        groupby p.gender
        select p.gender, list(p.name)
    }

    from n in [1, 1, 3, 3, 6, 6, 6]
    groupby n
    select n, agg(_g.stream().map(r -> r.n).reduce(BigDecimal.ZERO, BigDecimal::add))

| `_g` is an implicit variable for `agg` aggregate function, it represents the grouped `Queryable` object and its record(e.g. `r`) could reference the data source by alias(e.g. `n`)

    from fruit in ['Apple', 'Apricot', 'Banana', 'Cantaloupe']
    groupby fruit.substring(0, 1) as firstChar
    select firstChar, agg(_g.stream().map(r -> r.fruit).toList()) as fruit_list

Also, we could apply the aggregate functions for the whole GINQ result, i.e. no `groupby` clause is needed:

    assert [3] == GQ {
        from n in [1, 2, 3]
        select max(n)
    }.toList()

    assert [[1, 3, 2, 2, 6, 3, 3, 6]] == GQ {
        from n in [1, 2, 3]
        select min(n), max(n), avg(n), median(n), sum(n), count(n), count(),
                agg(_g.stream().map(r -> r.n).reduce(BigDecimal.ZERO, BigDecimal::add))
    }.toList()

    assert [0.816496580927726] == GQ {
        from n in [1, 2, 3]
        select stdev(n)
    }.toList()

    assert [1] == GQ {
        from n in [1, 2, 3]
        select stdevp(n)
    }.toList()

    assert [0.6666666666666667] == GQ {
        from n in [1, 2, 3]
        select var(n)
    }.toList()

    assert [1] == GQ {
        from n in [1, 2, 3]
        select varp(n)
    }.toList()

### 1.7. Sorting

`orderby` is equivalent to SQL’s `ORDER BY`

    from n in [1, 5, 2, 6]
    orderby n
    select n

| `in asc` is optional when sorting `in` ascending order

    from n in [1, 5, 2, 6]
    orderby n in asc
    select n

    from n in [1, 5, 2, 6]
    orderby n in desc
    select n

    from s in ['a', 'b', 'ef', 'cd']
    orderby s.length() in desc, s in asc
    select s

    from s in ['a', 'b', 'ef', 'cd']
    orderby s.length() in desc, s
    select s

    from n in [1, null, 5, null, 2, 6]
    orderby n in asc(nullslast)
    select n

| `nullslast` is equivalent to SQL’s `NULLS LAST` and applied by default. `nullsfirst` is equivalent to SQL’s `NULLS FIRST`.

    from n in [1, null, 5, null, 2, 6]
    orderby n in asc(nullsfirst)
    select n

    from n in [1, null, 5, null, 2, 6]
    orderby n in desc(nullslast)
    select n

    from n in [1, null, 5, null, 2, 6]
    orderby n in desc(nullsfirst)
    select n

### 1.8. Pagination

`limit` is similar to the `limit` clause of MySQL, which could specify the `offset`(first argument) and `size`(second argument) for paginating, or just specify the only one argument as `size`

    from n in [1, 2, 3, 4, 5]
    limit 3
    select n

    from n in [1, 2, 3, 4, 5]
    limit 1, 3
    select n

### 1.9. Set Operations

Set operations combine the results of two complete GINQ expressions.

`union` returns distinct rows from both queries (duplicates removed):

    assert [1, 2, 3, 4, 5] == GQ {
        from n in [1, 2, 3]
        select n
        union
        from m in [3, 4, 5]
        select m
    }.toList()

`unionall` returns all rows from both queries (duplicates preserved):

    assert [1, 2, 3, 3, 4, 5] == GQ {
        from n in [1, 2, 3]
        select n
        unionall
        from m in [3, 4, 5]
        select m
    }.toList()

`intersect` returns only rows that appear in both queries:

    assert [3, 4] == GQ {
        from n in [1, 2, 3, 4]
        select n
        intersect
        from m in [3, 4, 5, 6]
        select m
    }.toList()

`minus` returns rows from the first query that do not appear in the second:

    assert [1, 2] == GQ {
        from n in [1, 2, 3, 4]
        select n
        minus
        from m in [3, 4, 5, 6]
        select m
    }.toList()

Each side of a set operation is a complete `from…​select` expression and can include `where`, `groupby`, `orderby`, `limit`, and any other clauses.

| The set operation keyword must appear on its own line. Set operations are also available as methods on any `Queryable` result: `q1.union(q2)`, `q1.intersect(q2)`, `q1.minus(q2)`.

## 2. Common Recipes

Recipes for everyday tasks: row numbering, list comprehensions, querying JSON, updating collections, and workarounds for SQL features not yet in GINQ.

### 2.1. Row Number

`_rn` is the implicit variable representing row number for each record in the result set. It starts with `0`

    from n in [1, 2, 3]
    select _rn, n

### 2.2. List Comprehension

List comprehension is an elegant way to define and create lists based on existing lists:

    assert [4, 16, 36, 64, 100] == GQ {from n in 1..<11 where n % 2 == 0 select n ** 2}.toList()

    assert [4, 16, 36, 64, 100] == GQ {from n in 1..<11 where n % 2 == 0 select n ** 2} as List

    assert [4, 16, 36, 64, 100] == GQL {from n in 1..<11 where n % 2 == 0 select n ** 2}

| `GQL {…​}` is the abbreviation of `GQ {…​}.toList()`

GINQ could be used as list comprehension in the loops directly:

    def result = []
    for (def x : GQ {from n in 1..<11 where n % 2 == 0 select n ** 2}) {
        result << x
    }
    assert [4, 16, 36, 64, 100] == result

### 2.3. Query JSON

    def json = new JsonSlurper().parseText('''
        {
            "fruits": [
                {"name": "Orange", "price": 11},
                {"name": "Apple", "price": 6},
                {"name": "Banana", "price": 4},
                {"name": "Mango", "price": 28},
                {"name": "Durian", "price": 32}
            ]
        }
    ''')

    assert [['Mango', 28], ['Orange', 11], ['Apple', 6], ['Banana', 4]] == GQ {
        from f in json.fruits
        where f.price < 30
        orderby f.price in desc
        select f.name, f.price
    }.toList()

### 2.4. Query & Update

This is like `update` statement in SQL

    import groovy.transform.*
    @TupleConstructor
    @EqualsAndHashCode
    @ToString
    class Person {
        String name
        String nickname
    }

    def linda = new Person('Linda', null)
    def david = new Person('David', null)
    def persons = [new Person('Daniel', 'ShanFengXiaoZi'), linda, david]
    def result = GQ {
        from p in persons
        where p.nickname == null
        select p
    }.stream()
        .peek(p -> { p.nickname = 'Unknown' }) // update `nickname`
        .toList()

    def expected = [new Person('Linda', 'Unknown'), new Person('David', 'Unknown')]
    assert expected == result
    assert ['Unknown', 'Unknown'] == [linda, david]*.nickname // ensure the original objects are updated

### 2.5. Alternative for `with` clause

GINQ does not support `with` clause for now, but we could define a temporary variable to workaround:

    def v = GQ { from n in [1, 2, 3] where n < 3 select n }
    def result = GQ {
        from n in v
        where n > 1
        select n
    }
    assert [2] == result.toList()

### 2.6. Alternative for `case-when`

`case-when` of SQL could be replaced with switch expression:

    assert ['a', 'b', 'c', 'c'] == GQ {
        from n in [1, 2, 3, 4]
        select switch (n) {
            case 1 -> 'a'
            case 2 -> 'b'
            default -> 'c'
        }
    }.toList()

## 3. Advanced Topics

This section covers window functions, nested subqueries, the classic (pre-`into`) groupby style, the `@GQ` annotation for declaring GINQ methods, parallel querying, and options for customizing and optimizing GINQ. It also includes the full syntax reference.

### 3.1. Window Functions

Window can be defined by `partitionby`, `orderby`, `rows` and `range`:

    over(
        [partitionby <expression> (, <expression>)*]
        [orderby <expression> (, <expression>)*
           [rows <lower>, <upper> | range <lower>, <upper>]]
    )

- `0` used as bound of `rows` and `range` clause is equivalent to SQL’s `CURRENT ROW`, and negative means `PRECEDING`, positive means `FOLLOWING`

- `null` used as the lower bound of `rows` and `range` clause is equivalent to SQL’s `UNBOUNDED PRECEDING`

- `null` used as the upper bound of `rows` and `range` clause is equivalent to SQL’s `UNBOUNDED FOLLOWING`

Also, GINQ provides some built-in window functions:

Function | Argument Type(s) | Return Type | Description
--- | --- | --- | ---
rowNumber() |  | java.lang.Long | number of the current row within its partition, counting from `0`
rank() |  | java.lang.Long | rank of the current row with gaps
denseRank() |  | java.lang.Long | rank of the current row without gaps
percentRank() |  | java.math.BigDecimal | relative rank of the current row: (rank - 1) / (total rows - 1)
cumeDist() |  | java.math.BigDecimal | relative rank of the current row: (number of rows preceding or peer with current row) / (total rows)
ntile(expression) | java.lang.Long | java.lang.Long | bucket index ranging from `0` to `expression - 1`, dividing the partition as equally as possible
lead(expression [, offset [, default]]) | any [, java.lang.Long [, same as expression type]] | same as expression type | returns expression evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return default (which must be of the same type as expression). Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to `1` and default to `null`
lag(expression [, offset [, default]]) | any [, java.lang.Long [, same as expression type]] | same as expression type | returns expression evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default (which must be of the same type as expression). Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to `1` and default to `null`
firstValue(expression) | any | same type as expression | returns expression evaluated at the row that is the first row of the window frame
lastValue(expression) | any | same type as expression | returns expression evaluated at the row that is the last row of the window frame
nthValue(expression, n) | any, java.lang.Long | same type as expression | returns expression evaluated at the row that is the nth row of the window frame
count() |  | java.lang.Long | number of rows, similar to `count(*)` in SQL
count(expression) | any | java.lang.Long | number of rows for which the value of expression is not `null`
min(expression) | java.lang.Comparable | same as argument type | minimum value of expression across all non-null values
max(expression) | java.lang.Comparable | same as argument type | maximum value of expression across all non-null values
sum(expression) | java.lang.Number | java.math.BigDecimal | sum of expression across all non-null values
avg(expression) | java.lang.Number | java.math.BigDecimal | the average (arithmetic mean) of all non-null values
median(expression) | java.lang.Number | java.math.BigDecimal | value such that the number of non-null values above and below it is the same ("middle" value, not necessarily same as average or mean)
stdev(expression) | java.lang.Number | java.math.BigDecimal | the statistical standard deviation of all non-null values
stdevp(expression) | java.lang.Number | java.math.BigDecimal | the statistical standard deviation for the population for all non-null values
var(expression) | java.lang.Number | java.math.BigDecimal | the statistical variance of all non-null values
varp(expression) | java.lang.Number | java.math.BigDecimal | the statistical variance for the population for all non-null values
agg(expression) | any | any | INCUBATING: customizes the aggregation logic in expression and returns single value

#### 3.1.1. `rowNumber`

    assert [[2, 1, 1, 1], [1, 0, 0, 2], [null, 3, 3, 3], [3, 2, 2, 0]] == GQ {
        from n in [2, 1, null, 3]
        select n, (rowNumber() over(orderby n)),
                  (rowNumber() over(orderby n in asc)),
                  (rowNumber() over(orderby n in desc))
    }.toList()

    assert [[1, 0, 1, 2, 3], [2, 1, 2, 1, 2], [null, 3, 0, 3, 0], [3, 2, 3, 0, 1]] == GQ {
        from n in [1, 2, null, 3]
        select n, (rowNumber() over(orderby n in asc(nullslast))),
                  (rowNumber() over(orderby n in asc(nullsfirst))),
                  (rowNumber() over(orderby n in desc(nullslast))),
                  (rowNumber() over(orderby n in desc(nullsfirst)))
    }.toList()

| The parentheses around the window function is required.

#### 3.1.2. `rank`, `denseRank`, `percentRank`, `cumeDist` and `ntile`

    assert [['a', 1, 1], ['b', 2, 2], ['b', 2, 2],
            ['c', 4, 3], ['c', 4, 3], ['d', 6, 4],
            ['e', 7, 5]] == GQ {
        from s in ['a', 'b', 'b', 'c', 'c', 'd', 'e']
        select s,
            (rank() over(orderby s)),
            (denseRank() over(orderby s))
    }.toList()

    assert [[60, 0, 0.4], [60, 0, 0.4], [80, 0.5, 0.8], [80, 0.5, 0.8], [100, 1, 1]] == GQ {
        from n in [60, 60, 80, 80, 100]
        select n,
            (percentRank() over(orderby n)),
            (cumeDist() over(orderby n))
    }.toList()

    assert [[1, 0], [2, 0], [3, 0],
            [4, 1], [5, 1],
            [6, 2], [7, 2],[8, 2],
            [9, 3], [10, 3]] == GQ {
        from n in 1..10
        select n, (ntile(4) over(orderby n))
    }.toList()

#### 3.1.3. `lead` and `lag`

    assert [[2, 3], [1, 2], [3, null]] == GQ {
        from n in [2, 1, 3]
        select n, (lead(n) over(orderby n))
    }.toList()

    assert [[2, 3], [1, 2], [3, null]] == GQ {
        from n in [2, 1, 3]
        select n, (lead(n) over(orderby n in asc))
    }.toList()

    assert [['a', 'bc'], ['ab', null], ['b', 'a'], ['bc', 'ab']] == GQ {
        from s in ['a', 'ab', 'b', 'bc']
        select s, (lead(s) over(orderby s.length(), s in desc))
    }.toList()

    assert [['a', null], ['ab', null], ['b', 'a'], ['bc', 'ab']] == GQ {
        from s in ['a', 'ab', 'b', 'bc']
        select s, (lead(s) over(partitionby s.length() orderby s.length(), s in desc))
    }.toList()

    assert [[2, 1], [1, null], [3, 2]] == GQ {
        from n in [2, 1, 3]
        select n, (lag(n) over(orderby n))
    }.toList()

    assert [[2, 3], [1, 2], [3, null]] == GQ {
        from n in [2, 1, 3]
        select n, (lag(n) over(orderby n in desc))
    }.toList()

    assert [['a', null], ['b', 'a'], ['aa', null], ['bb', 'aa']] == GQ {
        from s in ['a', 'b', 'aa', 'bb']
        select s, (lag(s) over(partitionby s.length() orderby s))
    }.toList()

    assert [[2, 3, 1], [1, 2, null], [3, null, 2]] == GQ {
        from n in [2, 1, 3]
        select n, (lead(n) over(orderby n)), (lag(n) over(orderby n))
    }.toList()

The offset can be specified other than the default offset `1`:

    assert [[2, null, null], [1, 3, null], [3, null, 1]] == GQ {
        from n in [2, 1, 3]
        select n, (lead(n, 2) over(orderby n)), (lag(n, 2) over(orderby n))
    }.toList()

The default value can be returned when the index specified by offset is out of window, e.g. `'NONE'`:

    assert [[2, 'NONE', 'NONE'], [1, 3, 'NONE'], [3, 'NONE', 1]] == GQ {
        from n in [2, 1, 3]
        select n, (lead(n, 2, 'NONE') over(orderby n)), (lag(n, 2, 'NONE') over(orderby n))
    }.toList()

#### 3.1.4. `firstValue`, `lastValue` and `nthValue`

    assert [[2, 1], [1, 1], [3, 2]] == GQ {
        from n in [2, 1, 3]
        select n, (firstValue(n) over(orderby n rows -1, 1))
    }.toList()

    assert [[2, 3], [1, 2], [3, 3]] == GQ {
        from n in [2, 1, 3]
        select n, (lastValue(n) over(orderby n rows -1, 1))
    }.toList()

    assert [[2, 2], [1, 1], [3, 3]] == GQ {
        from n in [2, 1, 3]
        select n, (firstValue(n) over(orderby n rows 0, 1))
    }.toList()

    assert [[2, 1], [1, null], [3, 1]] == GQ {
        from n in [2, 1, 3]
        select n, (firstValue(n) over(orderby n rows -2, -1))
    }.toList()

    assert [[2, 1], [1, null], [3, 2]] == GQ {
        from n in [2, 1, 3]
        select n, (lastValue(n) over(orderby n rows -2, -1))
    }.toList()

    assert [[2, 3], [1, 3], [3, null]] == GQ {
        from n in [2, 1, 3]
        select n, (lastValue(n) over(orderby n rows 1, 2))
    }.toList()

    assert [[2, 3], [1, 2], [3, null]] == GQ {
        from n in [2, 1, 3]
        select n, (firstValue(n) over(orderby n rows 1, 2))
    }.toList()

    assert [[2, 2], [1, 1], [3, 3]] == GQ {
        from n in [2, 1, 3]
        select n, (lastValue(n) over(orderby n rows -1, 0))
    }.toList()

    assert [[2, 1], [1, 1], [3, 1]] == GQ {
        from n in [2, 1, 3]
        select n, (firstValue(n) over(orderby n rows null, 1))
    }.toList()

    assert [[2, 3], [1, 3], [3, 3]] == GQ {
        from n in [2, 1, 3]
        select n, (lastValue(n) over(orderby n rows -1, null))
    }.toList()

    assert [['a', 'a', 'b'], ['aa', 'aa', 'bb'], ['b', 'a', 'b'], ['bb', 'aa', 'bb']] == GQ {
        from s in ['a', 'aa', 'b', 'bb']
        select s, (firstValue(s) over(partitionby s.length() orderby s)),
                (lastValue(s) over(partitionby s.length() orderby s))
    }.toList()

    assert [[1, 1, 2, 3, null], [2, 1, 2, 3, null], [3, 1, 2, 3, null]] == GQ {
        from n in 1..3
        select n, (nthValue(n, 0) over(orderby n)),
                  (nthValue(n, 1) over(orderby n)),
                  (nthValue(n, 2) over(orderby n)),
                  (nthValue(n, 3) over(orderby n))
    }.toList()

#### 3.1.5. `min`, `max`, `count`, `sum`, `avg`, `median`, `stdev`, `stdevp`, `var` ,`varp` and `agg`

    assert [['a', 'a', 'b'], ['b', 'a', 'b'], ['aa', 'aa', 'bb'], ['bb', 'aa', 'bb']] == GQ {
        from s in ['a', 'b', 'aa', 'bb']
        select s, (min(s) over(partitionby s.length())), (max(s) over(partitionby s.length()))
    }.toList()

    assert [[1, 2, 2, 2, 1, 1], [1, 2, 2, 2, 1, 1],
            [2, 2, 2, 4, 2, 2], [2, 2, 2, 4, 2, 2],
            [3, 2, 2, 6, 3, 3], [3, 2, 2, 6, 3, 3]] == GQ {
        from n in [1, 1, 2, 2, 3, 3]
        select n, (count() over(partitionby n)),
                  (count(n) over(partitionby n)),
                  (sum(n) over(partitionby n)),
                  (avg(n) over(partitionby n)),
                  (median(n) over(partitionby n))
    }.toList()

    assert [[2, 6, 3, 1, 3, 4], [1, 6, 3, 1, 3, 4],
            [3, 6, 3, 1, 3, 4], [null, 6, 3, 1, 3, 4]] == GQ {
        from n in [2, 1, 3, null]
        select n, (sum(n) over()),
                  (max(n) over()),
                  (min(n) over()),
                  (count(n) over()),
                  (count() over())
    }.toList()

    assert [[1, 1, 1], [2, 2, 3], [5, 2, 10], [5, 2, 10]] == GQ {
        from n in [1, 2, 5, 5]
        select n, (count() over(orderby n range -2, 0)),
                  (sum(n) over(orderby n range -2, 0))
    }.toList()

    assert [[1, 2, 3], [2, 1, 2], [5, 2, 10], [5, 2, 10]] == GQ {
        from n in [1, 2, 5, 5]
        select n, (count() over(orderby n range 0, 1)),
                  (sum(n) over(orderby n range 0, 1))
    }.toList()

    assert [[1, 2, 3], [2, 2, 3], [5, 2, 10], [5, 2, 10]] == GQ {
        from n in [1, 2, 5, 5]
        select n, (count() over(orderby n range -1, 1)),
                  (sum(n) over(orderby n range -1, 1))
    }.toList()

    assert [[1, 1, 2], [2, 0, 0], [5, 0, 0], [5, 0, 0]] == GQ {
        from n in [1, 2, 5, 5]
        select n, (count() over(orderby n in desc range 1, 2)),
                  (sum(n) over(orderby n in desc range 1, 2))
    }.toList()

    assert [[1, 0, 0], [2, 1, 1], [5, 0, 0], [5, 0, 0]] == GQ {
        from n in [1, 2, 5, 5]
        select n, (count() over(orderby n in desc range -2, -1)),
                  (sum(n) over(orderby n in desc range -2, -1))
    }.toList()

    assert [[1, 3, 12], [2, 2, 10], [5, 0, 0], [5, 0, 0]] == GQ {
        from n in [1, 2, 5, 5]
        select n, (count() over(orderby n range 1, null)),
                  (sum(n) over(orderby n range 1, null))
    }.toList()

    assert [[1, 2, 3], [2, 2, 3], [5, 4, 13], [5, 4, 13]] == GQ {
        from n in [1, 2, 5, 5]
        select n, (count() over(orderby n range null, 1)),
                  (sum(n) over(orderby n range null, 1))
    }.toList()

    assert [[1, 0.816496580927726],
            [2, 0.816496580927726],
            [3, 0.816496580927726]] == GQ {
        from n in [1, 2, 3]
        select n, (stdev(n) over())
    }.toList()

    assert [[1, 1], [2, 1], [3, 1]] == GQ {
        from n in [1, 2, 3]
        select n, (stdevp(n) over())
    }.toList()

    assert [[1, 0.6666666666666667],
            [2, 0.6666666666666667],
            [3, 0.6666666666666667]] == GQ {
        from n in [1, 2, 3]
        select n, (var(n) over())
    }.toList()

    assert [[1, 1], [2, 1], [3, 1]] == GQ {
        from n in [1, 2, 3]
        select n, (varp(n) over())
    }.toList()

    assert [[1, 4], [2, 2], [3, 4]] == GQ {
        from n in [1, 2, 3]
        select n,
               (agg(_g.stream().map(r -> r.n).reduce(BigDecimal.ZERO, BigDecimal::add)) over(partitionby n % 2))
    }.toList()

### 3.2. Nested GINQ

#### 3.2.1. Nested GINQ in `from` clause

    from v in (
        from n in [1, 2, 3]
        select n
    )
    select v

#### 3.2.2. Nested GINQ in `where` clause

    from n in [0, 1, 2]
    where n in (
        from m in [1, 2]
        select m
    )
    select n

    from n in [0, 1, 2]
    where (
        from m in [1, 2]
        where m == n
        select m
    ).exists()
    select n

#### 3.2.3. Nested GINQ in `select` clause

    assert [null, 2, 3] == GQ {
        from n in [1, 2, 3]
        select (
            from m in [2, 3, 4]
            where m == n
            limit 1
            select m
        )
    }.toList()

| It’s recommended to use `limit 1` to restrict the count of sub-query result because `TooManyValuesException` will be thrown if more than one values returned

We could use `as` clause to name the sub-query result

    assert [[1, null], [2, 2], [3, 3]] == GQ {
        from n in [1, 2, 3]
        select n, (
            from m in [2, 3, 4]
            where m == n
            select m
        ) as sqr
    }.toList()

### 3.3. Classic groupby style

GINQ also supports an older style without the `into` keyword which looks simpler for some cases but has some limitations — aggregate functions use a special syntax rather than real method calls, and the group cannot be accessed as a composable collection. Each field in any nonaggregate expression in the `select` clause must be included in the `groupby` clause:

    from n in [1, 1, 3, 3, 6, 6, 6]
    groupby n
    select n, count(n)

    from n in [1, 1, 3, 3, 6, 6, 6]
    groupby n
    having n >= 3
    select n, count(n)

    from n in [1, 1, 3, 3, 6, 6, 6]
    groupby n
    having count() < 3
    select n, count()

The group columns could be renamed with `as` clause:

    from s in ['ab', 'ac', 'bd', 'acd', 'bcd', 'bef']
    groupby s.size() as length, s[0] as firstChar
    select length, firstChar, max(s)

    from s in ['ab', 'ac', 'bd', 'acd', 'bcd', 'bef']
    groupby s.size() as length, s[0] as firstChar
    having length == 3 && firstChar == 'b'
    select length, firstChar, max(s)

### 3.4. Using the Queryable API directly

The `groupByInto` method is also available on the `Queryable` API directly, which can be useful when building queries programmatically:

    def nums = [1, 2, 2, 3, 3, 4, 4, 5]
    def result = from(nums)
        .groupByInto(e -> e, g -> g.count() > 1)
        .select((g, q) -> Tuple.tuple(g.key, g.count()))
        .toList()
    assert [[2, 2], [3, 2], [4, 2]] == result

### 3.5. `@GQ` Annotation

GINQ could be written in a method marked with `@GQ`:

    @GQ
    def someGinqMethod() {
        /* GINQ CODE */
    }

For example,

- Mark the `ginq` method as a GINQ method with `@GQ` annotation:

    @groovy.ginq.transform.GQ
    def ginq(list, b, e) {
        from n in list
        where b < n && n < e
        select n
    }

    assert [3, 4] == ginq([1, 2, 3, 4, 5, 6], 2, 5).toList()

- Specify the result type as `List`:

    import groovy.ginq.transform.GQ

    @GQ(List)
    def ginq(b, e) {
        from n in [1, 2, 3, 4, 5, 6]
        where b < n && n < e
        select n
    }

    assert [3, 4] == ginq(2, 5)

| GINQ supports many result types, e.g. `List`, `Set`, `Collection`, `Iterable`, `Iterator`, `java.util.stream.Stream` and array types.

- Enable parallel querying:

    import groovy.ginq.transform.GQ

    @GQ(parallel=true)
    def ginq(x) {
        from n in [1, 2, 3]
        where n < x
        select n
    }

    assert [1] == ginq(2).toList()

### 3.6. Parallel Querying

Parallel querying is especially efficient when querying big data sources. It is disabled by default, but we could enable it by hand:

    assert [[1, 1], [2, 2], [3, 3]] == GQ(parallel: true) {
        from n1 in 1..1000
        join n2 in 1..10000 on n2 == n1
        where n1 <= 3 && n2 <= 5
        select n1, n2
    }.toList()

As parallel querying will use a shared thread pool, the following code can release resources after all GINQ statements execution are completed, and it will wait util all tasks of threads are completed.

    GQ {
        shutdown
    }

| Once `shutdown` is issued, parallel querying can not work anymore.

The following code is equivalent to the above code, in other words, `immediate` is optional:

    GQ {
        shutdown immediate
    }

Shutdown without waiting tasks to complete:

    GQ {
        shutdown abort
    }

### 3.7. Asynchronous Querying

A GINQ query is an ordinary (blocking) expression, so you can run it asynchronously using Groovy’s `async`/`await` with no GINQ-specific support: wrap the query in `async { …​ }` to launch it, carry on with other work, then `await` the result when you need it.

    def numbers = [1, 2, 3, 4, 5]

    // launch the GINQ query asynchronously
    def query = async {
        GQL {
            from n in numbers
            where n % 2 == 0
            select n
        }
    }

    // ... do other work here ...

    // await the result when it is needed
    def evens = await query
    assert evens == [2, 4]

### 3.8. Customize GINQ

| INCUBATING: the custom code-generator (provider) extension point described here — the `astWalker` option and the `GinqAstVisitor` SPI it selects — is still incubating and may change in a future release, even though the rest of GINQ is stable.

For advanced users, you could customize GINQ behaviour by specifying your own target code generator. For example, we could specify the qualified class name `org.apache.groovy.ginq.provider.collection.GinqAstWalker` as the target code generator to generate GINQ method calls for querying collections, which is the default behaviour of GINQ:

    assert [0, 1, 2] == GQ(astWalker: 'org.apache.groovy.ginq.provider.collection.GinqAstWalker') {
        from n in [0, 1, 2]
        select n
    }.toList()

### 3.9. Optimize GINQ

GINQ optimizer is enabled by default for better performance. It will transform the GINQ AST to achieve better execution plan. We could disable it by hand:

    assert [[2, 2]] == GQ(optimize: false) {
        from n1 in [1, 2, 3]
        join n2 in [1, 2, 3] on n1 == n2
        where n1 > 1 &&  n2 < 3
        select n1, n2
    }.toList()

### 3.10. GINQ Syntax Reference

The full GINQ clause structure for reference:

    GQ, i.e. abbreviation for GINQ
    |__ <query>
    |   |__ from
    |   |   |__ <data_source_alias> in <data_source>
    |   |__ [join/innerjoin/leftjoin/rightjoin/fulljoin/crossjoin]*
    |   |   |__ <data_source_alias> in <data_source>
    |   |   |__ on <condition> ((&& | ||) <condition>)* (NOTE: `crossjoin` does not need `on` clause)
    |   |__ [where]
    |   |   |__ <condition> ((&& | ||) <condition>)*
    |   |__ [groupby]
    |   |   |__ <expression> [as <alias>] (, <expression> [as <alias>])* [into <group_alias>]
    |   |   |__ [having]
    |   |       |__ <condition> ((&& | ||) <condition>)*
    |   |__ [orderby]
    |   |   |__ <expression> [in (asc|desc)] (, <expression> [in (asc|desc)])*
    |   |__ [limit]
    |   |   |__ [<offset>,] <size>
    |   |__ select
    |       |__ <expression> [as <alias>] (, <expression> [as <alias>])*
    |__ [union/unionall/intersect/minus]*
        |__ <query>

| `[]` means the related clause is optional, `*` means zero or more times, and `+` means one or more times. Also, the clauses within each query are order sensitive, so the order of clauses should be kept as the above structure. ONLY ONE `from` clause is required per query. Multiple data sources are supported through `from` and the related joins. Set operations chain left-to-right, so `Q1 union Q2 minus Q3` is evaluated as `(Q1 union Q2) minus Q3`.

### 3.11. Known Limitations

| Currently GINQ can not work well when STC is enabled.

## 4. GINQ Examples

Complete worked examples showing GINQ in action.

### 4.1. Generate Multiplication Table

    from v in (
        from a in 1..9
        join b in 1..9 on a <= b
        select a as f, b as s, "$a * $b = ${a * b}".toString() as r
    )
    groupby v.s
    select max(v.f == 1 ? v.r : '') as v1,
           max(v.f == 2 ? v.r : '') as v2,
           max(v.f == 3 ? v.r : '') as v3,
           max(v.f == 4 ? v.r : '') as v4,
           max(v.f == 5 ? v.r : '') as v5,
           max(v.f == 6 ? v.r : '') as v6,
           max(v.f == 7 ? v.r : '') as v7,
           max(v.f == 8 ? v.r : '') as v8,
           max(v.f == 9 ? v.r : '') as v9

### 4.2. More examples

link: the latest GINQ examples

| Some examples in the above link require the latest SNAPSHOT version of Groovy to run.