Google Git
Sign in
apache / groovy / aef68f078d955c98fe4056c4852e9c8334fde326 / . / subprojects / groovy-ginq / src / spec / doc / ginq-userguide.adoc
blob: e505ae1853c35086405d6a0289ce92f1534f7ca3 [file] [log] [blame]
//////////////////////////////////////////
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements. See the NOTICE file
distributed with this work for additional information
regarding copyright ownership. The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied. See the License for the
specific language governing permissions and limitations
under the License.
//////////////////////////////////////////
= Querying collections in SQL-like style
Groovy's `groovy-ginq` module provides a higher-level abstraction over collections.
It could perform queries against in-memory collections of objects in SQL-like style.
Also, querying XML, JSON, YAML, etc. could also be supported because they can be parsed into collections.
As GORM and jOOQ are powerful enough to support querying DB, we will cover collections first.
== GINQ a.k.a. Groovy-Integrated Query
GINQ is a DSL for querying with SQL-like syntax, which consists of the following structure:
```sql
GQ, i.e. abbreviation for GINQ
|__ 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>])*
| |__ [having]
| |__ <condition> ((&& | ||) <condition>)*
|__ [orderby]
| |__ <expression> [in (asc|desc)] (, <expression> [in (asc|desc)])*
|__ [limit]
| |__ [<offset>,] <size>
|__ select
|__ <expression> [as <alias>] (, <expression> [as <alias>])*
```
[NOTE]
`[]` means the related clause is optional, `*` means zero or more times, and `+` means one or more times. Also, the clauses of GINQ are order sensitive,
so the order of clauses should be kept as the above structure
As we could see, the simplest GINQ consists of a `from` clause and a `select` clause, which looks like:
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_simplest,indent=0]
----
[NOTE]
__ONLY ONE__ `from` clause is required in GINQ. Also, GINQ supports multiple data sources through `from` and the related joins.
As a DSL, GINQ should be wrapped with the following block to be executed:
```groovy
GQ { /* GINQ CODE */ }
```
For example,
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_execution_01,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_execution_02,indent=0]
----
And it is strongly recommended to use `def` to define the variable for the result of GINQ execution,
which is a `Queryable` instance that is lazy.
```groovy
def result = GQ {
/* GINQ CODE */
}
def stream = result.stream() // get the stream from GINQ result
def list = result.toList() // get the list from GINQ result
```
[WARNING]
Currently GINQ can not work well when STC is enabled.
Also, GINQ could be written in a method marked with `@GQ`:
```groovy
@GQ
def someGinqMethod() {
/* GINQ CODE */
}
```
For example,
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_method_01,indent=0]
----
Specify the return type as `List`:
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_method_03,indent=0]
----
Enable parallel querying:
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_method_02,indent=0]
----
=== GINQ Syntax
==== 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:
===== `Iterable` Data Source
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_datasource_03,indent=0]
----
===== `Stream` Data Source
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_datasource_01,indent=0]
----
===== Array Data Source
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_datasource_02,indent=0]
----
===== GINQ Result Set Data Source
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_datasource_04,indent=0]
----
==== Projection
The column names could be renamed with `as` clause:
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_projection_03,indent=0]
----
[NOTE]
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]`
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_projection_01,indent=0]
----
[NOTE]
`select P1, P2, ..., Pn` is a simplifed 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:
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_projection_02,indent=0]
----
===== Distinct
`distinct` is equivalent to SQL's `DISTINCT`
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_distinct_1,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_distinct_2,indent=0]
----
==== Filtering
`where` is equivalent to SQL's `WHERE`
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_filtering_01,indent=0]
----
===== In
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_filtering_08,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_filtering_07,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_filtering_09,indent=0]
----
===== Not In
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_filtering_05,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_filtering_06,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_filtering_10,indent=0]
----
===== Exists
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_filtering_02,indent=0]
----
===== Not Exists
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_filtering_03,indent=0]
----
==== Joining
More data sources for GINQ could be specified by join clauses.
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_joining_10,indent=0]
----
[NOTE]
`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.
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_joining_01,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_joining_02,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_joining_03,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_joining_04,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_joining_05,indent=0]
----
hash join is especially efficient when data sources contain lots of objects
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_joining_06,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_joining_07,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_joining_08,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_joining_09,indent=0]
----
[NOTE]
Only binary expressions(`==`, `&&`) are allowed in the `on` clause of hash join
==== Grouping
`groupby` is equivalent to SQL's `GROUP BY`, and `having` is equivalent to SQL's `HAVING`
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_grouping_01,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_grouping_02,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_grouping_10,indent=0]
----
The group columns could be renamed with `as` clause:
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_grouping_08,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_grouping_09,indent=0]
----
===== 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
|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
|===
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_grouping_07,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_grouping_06,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_grouping_05,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_grouping_04,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_grouping_11,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_grouping_12,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_grouping_03,indent=0]
----
[NOTE]
`_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`)
Also, we could apply the aggregate functions for the whole GINQ result, i.e. no `groupby` clause is needed:
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_aggfunction_02,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_aggfunction_01,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_aggfunction_03,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_aggfunction_04,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_aggfunction_05,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_aggfunction_06,indent=0]
----
==== Sorting
`orderby` is equivalent to SQL's `ORDER BY`
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_sorting_01,indent=0]
----
[NOTE]
`in asc` is optional when sorting `in` ascending order
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_sorting_03,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_sorting_02,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_sorting_04,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_sorting_05,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_sorting_06,indent=0]
----
[NOTE]
`nullslast` is equivalent to SQL's `NULLS LAST` and applied by default.
`nullsfirst` is equivalent to SQL's `NULLS FIRST`.
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_sorting_07,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_sorting_08,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_sorting_09,indent=0]
----
==== 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`
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_pagination_01,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_pagination_02,indent=0]
----
==== Nested GINQ
===== Nested GINQ in `from` clause
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_nested_01,indent=0]
----
===== Nested GINQ in `where` clause
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_nested_02,indent=0]
----
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_filtering_04,indent=0]
----
===== Nested GINQ in `select` clause
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_nested_03,indent=0]
----
[NOTE]
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
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_nested_04,indent=0]
----
==== Window Functions
Window can be defined by `partitionby`, `orderby`, `rows` and `range`:
```sql
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
|customizes the aggregation logic in _expression_ and returns single value
|===
===== `rowNumber`
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_24,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_34,indent=0]
----
[NOTE]
The parentheses around the window function is required.
===== `rank`, `denseRank`, `percentRank`, `cumeDist` and `ntile`
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_25,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_41,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_39,indent=0]
----
===== `lead` and `lag`
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_02,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_04,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_07,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_08,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_01,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_03,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_05,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_06,indent=0]
----
The offset can be specified other than the default offset `1`:
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_20,indent=0]
----
The default value can be returned when the index specified by offset is out of window, e.g. `'NONE'`:
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_21,indent=0]
----
===== `firstValue`, `lastValue` and `nthValue`
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_09,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_10,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_13,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_16,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_17,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_18,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_19,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_12,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_14,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_15,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_11,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_40,indent=0]
----
===== `min`, `max`, `count`, `sum`, `avg`, `median`, `stdev`, `stdevp`, `var` ,`varp` and `agg`
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_22,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_23,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_26,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_27,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_28,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_33,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_31,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_32,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_29,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_30,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_35,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_36,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_37,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_38,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_winfunction_42,indent=0]
----
=== GINQ Tips
==== Row Number
`_rn` is the implicit variable representing row number for each record in the result set. It starts with `0`
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_tips_05,indent=0]
----
==== List Comprehension
List comprehension is an elegant way to define and create lists based on existing lists:
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_tips_01,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_tips_02,indent=0]
----
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_tips_06,indent=0]
----
[NOTE]
`GQL {...}` is the abbreviation of `GQ {...}.toList()`
GINQ could be used as list comprehension in the loops directly:
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_tips_03,indent=0]
----
==== Query & Update
This is like `update` statement in SQL
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_tips_07,indent=0]
----
==== Alternative for `with` clause
GINQ does not support `with` clause for now, but we could define a temporary variable to workaround:
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_tips_12,indent=0]
----
==== Alternative for `case-when`
`case-when` of SQL could be replaced with switch expression:
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_tips_13,indent=0]
----
==== Query JSON
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_tips_04,indent=0]
----
==== Parallel Querying
Parallel querying is especially efficient when querying big data sources. It is disabled by default, but we could enable it by hand:
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_tips_08,indent=0]
----
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.
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_tips_09,indent=0]
----
[WARNING]
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:
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_tips_10,indent=0]
----
Shutdown without waiting tasks to complete:
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_tips_11,indent=0]
----
==== Customize GINQ
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:
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_customize_01,indent=0]
----
==== 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:
[source, groovy]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_optimize_01,indent=0]
----
=== GINQ Examples
==== Generate Multiplication Table
[source, sql]
----
include::../test/org/apache/groovy/ginq/GinqTest.groovy[tags=ginq_examples_01,indent=0]
----
==== More examples
link: the latest https://github.com/apache/groovy/blob/master/subprojects/groovy-ginq/src/spec/test/org/apache/groovy/ginq/GinqTest.groovy[GINQ examples]
[NOTE]
Some examples in the above link require the latest SNAPSHOT version of Groovy to run.