類似 SQL 的集合查詢

Groovy 的 groovy-ginq 模組提供集合的較高層次抽象。它可以對物件的記憶體內集合執行類似 SQL 的查詢。此外，由於 XML、JSON、YAML 等可以解析成集合，因此也可以查詢這些資料。由於 GORM 和 jOOQ 足以支援查詢資料庫，因此我們將先介紹集合。

1. GINQ 又稱 Groovy 整合式查詢

GINQ 是使用類似 SQL 語法的查詢 DSL，其結構如下

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>])*
[] 表示相關子句為選用，* 表示零次或多次，+ 表示一次或多次。此外，GINQ 的子句順序很重要，因此子句順序應維持如上結構

如我們所見，最簡單的 GINQ 由 from 子句和 select 子句組成，如下所示

from n in [0, 1, 2]
select n
GINQ 中只有一個 from 子句是必要的。此外，GINQ 透過 from 和相關的關聯支援多個資料來源。

作為 DSL，GINQ 應包覆在以下區塊中才能執行

GQ { /* GINQ CODE */ }

例如，

def numbers = [0, 1, 2]
assert [0, 1, 2] == GQ {
    from n in numbers
    select n
}.toList()
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('#'))

強烈建議使用 def 定義 GINQ 執行結果的變數，它是一個延遲的 Queryable 執行個體。

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
目前，當啟用 STC 時，GINQ 無法正常運作。

此外，GINQ 可以寫在標記為 @GQ 的方法中

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

例如，

  • 使用 @GQ 注解將 ginq 方法標記為 GINQ 方法

@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()

  • 將結果類型指定為 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 支援許多結果類型，例如 List、Set、Collection、Iterable、Iterator、java.util.stream.Stream 和陣列類型。

  • 啟用平行查詢

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()

1.1. GINQ 語法

1.1.1. 資料來源

GINQ 的資料來源可以由 from 子句指定，它等於 SQL 的 FROM。目前，GINQ 支援 Iterable、Stream、陣列和 GINQ 結果集作為其資料來源

Iterable 資料來源
from n in [1, 2, 3] select n
Stream 資料來源
from n in [1, 2, 3].stream() select n
陣列資料來源
from n in new int[] {1, 2, 3} select n
GINQ 結果集資料來源
def vt = GQ {from m in [1, 2, 3] select m}
assert [1, 2, 3] == GQ {
    from n in vt select n
}.toList()

1.1.2. 投影

欄位名稱可以用 as 子句重新命名

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()
重新命名的欄位可以用其新名稱參照，例如 r.powerOfN。此外，也可以用其索引參照，例如 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 是 select new NamedRecord(P1, P2, …​, Pn) 的簡化語法，僅當 n >= 2 時才成立。此外，如果使用 as 子句，則會建立 NamedRecord 執行個體。儲存在 NamedRecord 中的值可以用其名稱參照。

建構新的物件作為欄位值

@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()
相異

distinct 等同於 SQL 的 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.1.3. 篩選

where 等同於 SQL 的 WHERE

from n in [0, 1, 2, 3, 4, 5]
where n > 0 && n <= 3
select n * 2
在內
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()
不在內
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()
存在
from n in [1, 2, 3]
where (
    from m in [2, 3]
    where m == n
    select m
).exists()
select n
不存在
from n in [1, 2, 3]
where !(
    from m in [2, 3]
    where m == n
    select m
).exists()
select n

1.1.4. 聯結

可以透過聯結子句為 GINQ 指定更多資料來源。

from n1 in [1, 2, 3]
join n2 in [1, 3] on n1 == n2
select n1, n2
join 優於 innerjoin 和 innerhashjoin，因為它具有更好的可讀性，而且它足夠聰明，可以透過其 on 子句選擇正確的具體聯結（即 innerjoin 或 innerhashjoin）。 
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

當資料來源包含大量物件時，雜湊聯結特別有效率

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
雜湊聯結的 on 子句中僅允許二元表達式（==、&&）

1.1.5. 分組

groupby 等同於 SQL 的 GROUP BY，而 having 等同於 SQL 的 HAVING

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()

群組欄位可以用 as 子句重新命名

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)
聚合函數

GINQ 提供了一些內建的聚合函數

函數 引數類型 傳回類型 說明

count()

java.lang.Long

列數，類似於 SQL 中的 count(*)

count(expression)

any

java.lang.Long

expression 值不為 null 的列數

min(expression)

java.lang.Comparable

與參數類型相同

所有非 null 值中 expression 的最小值

max(expression)

java.lang.Comparable

與參數類型相同

所有非 null 值中 expression 的最大值

sum(expression)

java.lang.Number

java.math.BigDecimal

所有非 null 值中 expression 的總和

avg(expression)

java.lang.Number

java.math.BigDecimal

所有非 null 值的平均值（算術平均值）

list(expression)

any

java.util.List

所有非 null 值的聚合清單

median(expression)

java.lang.Number

java.math.BigDecimal

值，其上方和下方的非 null 值數量相同（「中間」值，不一定是平均值或平均數）

stdev(expression)

java.lang.Number

java.math.BigDecimal

所有非 null 值的統計標準差

stdevp(expression)

java.lang.Number

java.math.BigDecimal

所有非 null 值的母體統計標準差

var(expression)

java.lang.Number

java.math.BigDecimal

所有非 null 值的統計變異數

varp(expression)

java.lang.Number

java.math.BigDecimal

所有非 null 值的母體統計變異數

agg(expression)

any

any

自訂 expression 中的聚合邏輯，並傳回單一值

 
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 是 agg 聚合函數的隱含變數，它代表已分組的 Queryable 物件，其記錄（例如 r）可透過別名（例如 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

此外，我們可以對整個 GINQ 結果套用聚合函數，亦即不需要 groupby 子句

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.1.6. 排序

orderby 等同於 SQL 的 ORDER BY

from n in [1, 5, 2, 6]
orderby n
select n
以遞增順序排序時，in asc 為選用項目 
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 等同於 SQL 的 NULLS LAST，且預設套用。nullsfirst 等同於 SQL 的 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.1.7. 分頁

limit 類似於 MySQL 的 limit 子句，可以指定分頁的 offset（第一個參數）和 size（第二個參數），或僅指定一個參數作為 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.1.8. 巢狀 GINQ

from 子句中的巢狀 GINQ
from v in (
    from n in [1, 2, 3]
    select n
)
select v
where 子句中的巢狀 GINQ
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
select 子句中的巢狀 GINQ
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()
建議使用 limit 1 來限制子查詢結果的數量，因為如果返回多於一個值，將會擲出 TooManyValuesException

我們可以使用 as 子句來命名子查詢結果

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()

1.1.9. 視窗函數

視窗可以透過 partitionby、orderby、rows 和 range 來定義

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

  • 0 用作 rows 和 range 子句的界限等同於 SQL 的 CURRENT ROW，負數表示 PRECEDING，正數表示 FOLLOWING

  • null 用作 rows 和 range 子句的下界限等同於 SQL 的 UNBOUNDED PRECEDING

  • null 用作 rows 和 range 子句的上界限等同於 SQL 的 UNBOUNDED FOLLOWING

此外，GINQ 提供了一些內建的視窗函數

函數 引數類型 傳回類型 說明

rowNumber()

java.lang.Long

目前列在其分區中的列號，從 0 開始計算

rank()

java.lang.Long

目前列的排名（有間隔）

denseRank()

java.lang.Long

目前列的排名（無間隔）

percentRank()

java.math.BigDecimal

目前列的相對排名：(排名 - 1) / (總列數 - 1)

cumeDist()

java.math.BigDecimal

目前列的相對排名：(目前列之前或同等的列數) / (總列數)

ntile(expression)

java.lang.Long

java.lang.Long

區間索引，範圍從 0 到 expression - 1，盡可能平均地將分區劃分

lead(expression [, offset [, default]])

any [, java.lang.Long [, 與 expression 類型相同]]

與 expression 類型相同

傳回在分區中目前列之後 offset 列的 expression 評估值；如果沒有這樣的列，則傳回 default（必須與 expression 類型相同）。offset 和 default 都會根據目前列進行評估。如果省略，offset 預設為 1，default 預設為 null

lag(expression [, offset [, default]])

any [, java.lang.Long [, 與 expression 類型相同]]

與 expression 類型相同

傳回在分區中目前列之前 offset 列所評估的 expression；如果沒有該列，則傳回 default（必須與 expression 相同類型）。offset 和 default 都會相對於目前列進行評估。如果省略，offset 預設為 1，default 預設為 null

firstValue(expression)

any

與 expression 相同類型

傳回在視窗框架第一列所評估的 expression

lastValue(expression)

any

與 expression 相同類型

傳回在視窗框架最後一列所評估的 expression

nthValue(expression, n)

任何，java.lang.Long

與 expression 相同類型

傳回在視窗框架第 nth 列所評估的 expression

count()

java.lang.Long

列數，類似於 SQL 中的 count(*)

count(expression)

any

java.lang.Long

expression 值不為 null 的列數

min(expression)

java.lang.Comparable

與參數類型相同

所有非 null 值中 expression 的最小值

max(expression)

java.lang.Comparable

與參數類型相同

所有非 null 值中 expression 的最大值

sum(expression)

java.lang.Number

java.math.BigDecimal

所有非 null 值中 expression 的總和

avg(expression)

java.lang.Number

java.math.BigDecimal

所有非 null 值的平均值（算術平均值）

median(expression)

java.lang.Number

java.math.BigDecimal

值，其上方和下方的非 null 值數量相同（「中間」值，不一定是平均值或平均數）

stdev(expression)

java.lang.Number

java.math.BigDecimal

所有非 null 值的統計標準差

stdevp(expression)

java.lang.Number

java.math.BigDecimal

所有非 null 值的母體統計標準差

var(expression)

java.lang.Number

java.math.BigDecimal

所有非 null 值的統計變異數

varp(expression)

java.lang.Number

java.math.BigDecimal

所有非 null 值的母體統計變異數

agg(expression)

any

any

INCUBATING：自訂 expression 中的彙總邏輯，並傳回單一值
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()
視窗函數周圍需要加上括號。
rank, denseRank, percentRank, cumeDist 和 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()
lead 和 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()

除了預設的 offset 1 之外，還可以指定其他 offset

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()

當 offset 指定的索引超出視窗時，可以傳回預設值，例如 '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()
firstValue, lastValue 和 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()
min, max, count, sum, avg, median, stdev, stdevp, var ,varp 和 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()

1.2. GINQ 提示

1.2.1. 列號碼

_rn 是表示結果集中每筆記錄的列號碼的隱含變數。從 0 開始

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

1.2.2. 清單推導

清單推導是一種根據現有清單定義和建立清單的優雅方式

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 {…​} 是 GQ {…​}.toList() 的縮寫

GINQ 可直接用於迴圈中的清單理解

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

1.2.3. 查詢和更新

這類似於 SQL 中的 update 陳述式

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

1.2.4. with 子句的替代方案

GINQ 目前不支援 with 子句，但我們可以定義暫時變數來解決問題

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()

1.2.5. case-when 的替代方案

SQL 的 case-when 可以用 switch 表達式取代

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()

1.2.6. 查詢 JSON

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

def expected = [['Mongo', 29], ['Orange', 11], ['Apple', 6], ['Banana', 4]]
assert expected == GQ {
    from f in json.fruits
    where f.price < 32
    orderby f.price in desc
    select f.name, f.price
}.toList()

1.2.7. 平行查詢

查詢大型資料來源時，平行查詢特別有效率。它在預設情況下已停用，但我們可以手動啟用它

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()

由於平行查詢將使用共用執行緒池，因此以下程式碼可以在所有 GINQ 陳述式執行完成後釋放資源，並且它將等待執行緒的所有工作完成。

GQ {
    shutdown
}
一旦發出 shutdown，平行查詢就無法再運作了。

以下程式碼等同於上述程式碼，換句話說，immediate 是可選的

GQ {
    shutdown immediate
}

在不等待工作完成的情況下關閉

GQ {
    shutdown abort
}

1.2.8. 自訂 GINQ

對於進階使用者，您可以透過指定自己的目標程式碼產生器來自訂 GINQ 行為。例如，我們可以指定合格類別名稱 org.apache.groovy.ginq.provider.collection.GinqAstWalker 作為目標程式碼產生器，以產生用於查詢集合的 GINQ 方法呼叫，這是 GINQ 的預設行為

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

1.2.9. 最佳化 GINQ

GINQ 最佳化器在預設情況下已啟用，以提升效能。它將轉換 GINQ AST 以達成更好的執行計畫。我們可以手動停用它

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()

1.3. GINQ 範例

1.3.1. 產生乘法表

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

1.3.2. 更多範例

連結：最新的 GINQ 範例

上述連結中的一些範例需要最新的 Groovy SNAPSHOT 版本才能執行。