Apach Geode OQL 查询

一、概述

Geode 提供了一种类似于 SQL 的查询语言，称为 OQL，允许您访问存储在 Geode 区域中的数据。

由于 Geode 区域是键值存储，其中的值范围可以从简单字节数组到复杂的嵌套对象，因此 Geode 使用基于 OQL（对象查询语言）的查询语法来查询区域数据。OQL 与 SQL 非常相似，但它们有显着的差异。例如，虽然 OQL 不提供 SQL 的所有功能（如聚合），但 OQL 允许您对复杂对象图执行查询、查询对象属性和调用对象方法。

典型的 Geode OQL 查询的语法为：

[IMPORT package]
SELECT [DISTINCT] projectionList
FROM collection1, [collection2, …]
[WHERE clause]
[ORDER BY order_criteria [desc]]

因此，一个简单的 Geode OQL 查询类似于以下内容：

SELECT DISTINCT * FROM /exampleRegion WHERE status = ‘active’

要注意的 Geode 查询的一个重要特征是，默认情况下，Geode 查询的是区域的值，而不是键。若要从区域获取键，必须在查询的区域上使用 keySet 路径表达式。例如/exampleRegion.keySet。

二、查询例子

接下来的查询例子会使用portfolio ID作为键，用下面类定义中的汇总数据作为值：/exampleRegion

class Portfolio implements DataSerializable {
   int ID;
   String type;
   String status;
   Map positions;
}
class Position implements DataSerializable {
   String secId;
   double mktValue;
   double qty;
}

1. 基本的WHERE用例

下面的例子中,status字段是String类型而ID字段是int类型。

1. 查询所有的active portfolios。

   SELECT * FROM /exampleRegion WHERE status = 'active'
   

2. 查询所有status字段以’activ’开头的portfolios。

   SELECT * FROM /exampleRegion p WHERE p.status LIKE 'activ%'
   

3. 查询所有ID字段大于100的portfolios。

   SELECT * from /exampleRegion p WHERE p.ID > 100
   

2. DISTINCT用例

从区域里查询满足条件status='active'的不重复对象。

SELECT DISTINCT * FROM /exampleRegion WHERE status = 'active'

3. 别名和同义词

在查询语句中，可以使用别名定义路径表达式（区域及其对象）。可以在查询中的其他位置使用或引用此别名。

SELECT DISTINCT * FROM /exampleRegion p WHERE p.status = 'active'

SELECT p.ID, p.status FROM /exampleRegion p WHERE p.ID > 0

4. NOT运算符

SELECT DISTINCT * FROM /exampleRegion WHERE NOT (status = 'active') AND ID = 2

SELECT * FROM /exampleRegion WHERE NOT (ID IN SET(1,2))

5. AND和OR运算符

SELECT * FROM /exampleRegion WHERE ID > 4 AND ID < 9

SELECT * FROM /exampleRegion WHERE ID = 0 OR ID = 1

SELECT DISTINCT p.status FROM /exampleRegion p 
WHERE (p.createTime IN SET (10|) OR p.status IN SET ('active')) AND p.ID > 0

6. 使用不等于

SELECT * FROM /exampleRegion portfolio WHERE portfolio.ID <> 2

SELECT * FROM /exampleRegion portfolio WHERE portfolio.ID != 2

7. Projection attribute用例

SELECT p.get('account') FROM /exampleRegion p

8. 查询嵌套集合

以下查询用到了HashMap类型的Positions。

SELECT p, pos FROM /exampleRegion p, p.positions.values pos WHERE pos.secId = 'VMW'

9. 使用LIMIT

SELECT * FROM /exampleRegion p WHERE p.ID > 0 LIMIT 2

10. 使用MAX和MIN

SELECT MIN(ID)
FROM /exampleRegion
WHERE ID > 0

SELECT MAX(ID)
FROM /exampleRegion
WHERE ID > 0 AND status LIKE 'act%'

SELECT MIN(pos.mktValue)
FROM /exampleRegion p, p.positions.values pos
WHERE p.ID = 10

SELECT MAX(p.ID)
FROM /exampleRegion p, p.positions.values pos
WHERE p.ID > 0 OR p.status = 'active' OR pos.secId = 'IBM'

11. 使用AVG

SELECT AVG(ID)
FROM /exampleRegion
WHERE ID > 0

SELECT AVG(ID)
FROM /exampleRegion
WHERE ID > 0 AND status LIKE 'act%'

SELECT AVG(pos.mktValue)
FROM /exampleRegion p, p.positions.values pos
WHERE p.isActive()

SELECT AVG(DISTINCT p.ID)
FROM /exampleRegion p, p.positions.values pos
WHERE p.ID > 0 OR p.status = 'active' OR pos.secId = 'IBM'

12. 使用COUNT

SELECT COUNT(*)
FROM /exampleRegion
WHERE ID > 0

SELECT COUNT(*)
FROM /exampleRegion
WHERE ID > 0 LIMIT 50

SELECT COUNT(*)
FROM /exampleRegion
WHERE ID > 0 AND status LIKE 'act%'

SELECT COUNT(*)
FROM /exampleRegion
WHERE ID IN SET(1,2,3,4,5)

SELECT COUNT(DISTINCT p.status)
FROM /exampleRegion p
WHERE p.ID > 0

SELECT COUNT(*)
FROM /exampleRegion p, p.positions.values pos 
WHERE p.ID > 0 AND pos.secId 'IBM'

SELECT DISTINCT COUNT(*)
FROM /exampleRegion p, p.positions.values pos
WHERE p.ID > 0 OR p.status = 'active' OR pos.secId = 'IBM'

13. 使用SUM

SELECT SUM(ID)
FROM /exampleRegion
WHERE ID > 0

SELECT SUM(ID)
FROM /exampleRegion
WHERE ID > 0 AND status LIKE 'act%'

SELECT SUM(pos.mktValue)
FROM /exampleRegion p, p.positions.values pos
WHERE p.status = 'active'

SELECT SUM(DISTINCT p.ID)
FROM /exampleRegion p, p.positions.values pos
WHERE p.ID > 0 OR p.status = 'active' OR pos.secId = 'IBM'

14. 使用GROUP BY

SELECT p.status, MAX(p.ID) 
FROM /exampleRegion p
WHERE p.ID > 0
GROUP BY p.status

SELECT p.ID, MIN(pos.qty) AS lessQty
FROM /exampleRegion p, p.positions.values pos
WHERE p.ID > 0 AND p.status = 'active'
GROUP BY p.ID
ORDER BY lessQty ASC

SELECT p.ID, MAX(pos.mktValue) AS maxValue
FROM /exampleRegion p, p.positions.values pos
WHERE p.ID > 0 AND p.status = 'active'
GROUP BY p.ID
ORDER BY maxValue DESC

SELECT p.status, AVG(p.ID) 
FROM /exampleRegion p
WHERE p.ID > 0
GROUP BY p.status

SELECT p.ID, pos.secId, AVG(pos.mktValue)
FROM /exampleRegion p, p.positions.values pos
WHERE p.ID > 0 OR p.status = 'active'
GROUP BY p.ID, pos.secId

SELECT p.status, AVG(p.ID) as sm 
FROM /exampleRegion p
WHERE p.ID > 0
GROUP BY p.status
ORDER BY sm DESC

SELECT p.status, COUNT(*) 
FROM /exampleRegion p
WHERE p.ID > 0
GROUP BY p.status

SELECT p.ID, COUNT(pos) AS positionsAmount
FROM /exampleRegion p, p.positions.values pos
WHERE p.ID > 0 OR p.status = 'active'
GROUP BY p.ID
ORDER BY positionsAmount

SELECT p.status, SUM(p.ID) 
FROM /exampleRegion p
WHERE p.ID > 0
GROUP BY p.status

SELECT p.ID, pos.secId, SUM(pos.mktValue)
FROM /exampleRegion p, p.positions.values pos
WHERE p.ID > 0 OR p.status = 'active'
GROUP BY p.ID, pos.secId

SELECT p.status, SUM(p.ID) as sm 
FROM /exampleRegion p
WHERE p.ID > 0
GROUP BY p.status
ORDER BY sm DESC

SELECT p.ID, SUM(pos.mktValue) AS marketValue
FROM /exampleRegion p, p.positions.values pos
WHERE p.isActive()
GROUP BY p.ID
ORDER BY marketValue DESC

15. 使用LIKE

SELECT * FROM /exampleRegion ps WHERE ps.pkid LIKE '_bc'

SELECT * FROM /exampleRegion ps WHERE ps.status LIKE '_b_' OR ps.pkid = '2'

SELECT * FROM /exampleRegion ps WHERE ps.status LIKE '%b%

16. 使用区域键值对

SELECT * FROM /exampleRegion.keys k WHERE k.ID = 1

SELECT entry.value FROM /exampleRegion.entries entry 
WHERE entry.key = '1'

SELECT key, positions FROM /exampleRegion.entrySet, value.positions.values positions 
WHERE positions.mktValue >= 25.00

SELECT DISTINCT entry.value FROM /exampleRegion.entries entry 
WHERE entry.key = '1'

SELECT * FROM /exampleRegion.entries entry WHERE entry.value.ID > 1

SELECT * FROM /exampleRegion.keySet key WHERE key = '1'

SELECT * FROM /exampleRegion.values portfolio 
WHERE portfolio.status = 'active'

17. 嵌套查询

IMPORT "query".Portfolio;
SELECT * FROM /exampleRegion, (SELECT DISTINCT * FROM /exampleRegion p TYPE Portfolio, p.positions 
WHERE value!=null)

SELECT DISTINCT * FROM (SELECT DISTINCT * FROM /exampleRegion portfolios, positions pos) 
WHERE pos.value.secId = 'IBM'

SELECT * FROM /exampleRegion portfolio 
WHERE portfolio.ID IN (SELECT p2.ID FROM /exampleRegion2 p2 WHERE p2.ID > 1)

SELECT DISTINCT * FROM /exampleRegion p, (SELECT DISTINCT pos 
FROM /exampleRegion x, x.positions.values pos WHERE x.ID = p.ID ) AS itrX

18. 查询 FROM 子句表达式的结果

SELECT DISTINCT * FROM (SELECT DISTINCT * FROM /Portfolios ptf, positions pos) p 
WHERE p.get('pos').value.secId = 'IBM'

19. 哈希映射查询

在下列例子中，’version‘是hashmap的键之一。

SELECT * FROM /exampleRegion p WHERE p['version'] = '1.0'

SELECT entry.key, entry.value FROM /exampleRegion.entries entry 
WHERE entry.value['version'] = '100'

20. 嵌套的哈希映射查询

SELECT DISTINCT * FROM /exampleRegion p 
WHERE p.portfolios['key2'] >= 3

21. 获取数组值的查询示例

SELECT * FROM /exampleRegion p WHERE p.names[0] = 'aaa'

SELECT * FROM /exampleRegion p 
WHERE p.collectionHolderMap.get('1').arr[0] = '0'

22. 使用ORDER BY（加上LIMIT）

使用ORDER BY查询时必须加上DISTINCT关键字。

SELECT DISTINCT * FROM /exampleRegion WHERE ID < 101 ORDER BY ID

SELECT DISTINCT * FROM /exampleRegion WHERE ID < 101 ORDER BY ID asc

SELECT DISTINCT * FROM /exampleRegion WHERE ID < 101 ORDER BY ID desc

SELECT DISTINCT key.ID, key.status AS st FROM /exampleRegion.keys key 
WHERE key.status = 'inactive' ORDER BY key.status desc, key.ID LIMIT 1

SELECT DISTINCT * FROM /exampleRegion p ORDER BY p.getP1().secId, p.ID dec, p.ID LIMIT 9

SELECT DISTINCT * FROM /exampleRegion p ORDER BY p.ID, val.secId LIMIT 1

SELECT DISTINCT e.key FROM /exampleRegion.entrySet e 
ORDER BY e.key.ID desc, e.key.pkid desc

SELECT DISTINCT p.names[1] FROM /exampleRegion p ORDER BY p.names[1]

23. 连接查询

SELECT * FROM /exampleRegion portfolio1, /exampleRegion2 portfolio2 
WHERE portfolio1.status = portfolio2.status

SELECT portfolio1.ID, portfolio2.status FROM /exampleRegion portfolio1, /exampleRegion2 portfolio2 
WHERE portfolio1.status = portfolio2.status

SELECT * FROM /exampleRegion portfolio1, portfolio1.positions.values positions1, 
/exampleRegion2 portfolio2, portfolio2.positions.values positions2 WHERE positions1.secId = positions1.secId

SELECT * FROM /exampleRegion portfolio1, portfolio1.positions.values positions1, 
/exampleRegion2 portfolio2, portfolio2.positions.values positions2 WHERE portfolio1.ID = 1 
AND positions1.secId = positions1.secId

SELECT DISTINCT a, b.price FROM /exampleRegoin1 a, /exampleRegion2 b 
WHERE a.price = b.price

24. 使用AS

SELECT * FROM /exampleRegion p, p.positions.values AS pos 
WHERE pos.secId != '1'

25. 使用TRUE

SELECT DISTINCT * FROM /Portfolios WHERE TRUE

26. 使用IN和SET

SELECT * FROM /exampleRegion portfolio 
WHERE portfolio.ID IN SET(1, 2)

SELECT * FROM /exampleRegion portfolio, portfolio.positions.values positions 
WHERE portfolio.Pk IN SET ('1', '2') AND positions.secId = '1'

SELECT * FROM /exampleRegion portfolio, portfolio.positions.values positions 
WHERE portfolio.Pk IN SET ('1', '2') OR positions.secId IN SET ('1', '2', '3')

SELECT * FROM /exampleRegion portfolio, portfolio.positions.values positions 
WHERE portfolio.Pk IN SET ('1', '2') OR positions.secId IN SET ('1', '2', '3') 
AND portfolio.status = 'active'

27. 查询Set集合

在下面的查询中，sp是Set类型。

SELECT * FROM /exampleRegion WHERE sp = set('20', '21', '22')

如果 Set（sp） 仅包含“20”和“21”，则查询的计算结果将为 false。该查询将比较这两个集，并查找两个集中是否都出现了这些元素。

对于其他集合类型，如 list（sp 的类型为 List），查询可以按如下方式编写：

SELECT * FROM /exampleRegion 
WHERE sp.containsAll(set('20', '21', '22'))

28. 在对象上调用方法

SELECT * FROM /exampleRegion p WHERE p.length > 1

SELECT DISTINCT * FROM /exampleRegion p WHERE p.positions.size >= 2

SELECT DISTINCT * FROM /exampleRegion p WHERE p.positions.isEmpty

SELECT DISTINCT * FROM /exampleRegion p WHERE p.name.startsWith('Bo')

29. 使用查询级调试

若要在查询级别设置调试，请在查询之前添加<trace>关键字。（如果使用的是 IMPORT 语句，请将其包含在 IMPORT 之前）。

<trace>
SELECT * from /exampleRegion, positions.values TYPE myclass

30. 在查询中使用保留字

若要访问与查询语言保留字同名的任何方法、属性或命名对象，请将该名称括在双引号内。

SELECT * FROM /exampleRegion 
WHERE status = 'active' AND "type" = 'XYZ'

SELECT DISTINCT "type" FROM /exampleRegion WHERE status = 'active'

31. 使用IMPORT

如果相同的类名存在于不同的命名空间（包），则需要有一种方法来引用名称相同的不同的类，IMPORT语句则用于为查询的类建立名称范围。

IMPORT package.Position;
SELECT DISTINCT * FROM /exampleRegion, positions.values positions TYPE Position 
WHERE positions.mktValue >= 25.00

32. 使用TYPE

指定对象类型有助于查询引擎以最佳速度处理查询。除了在配置期间指定对象类型（使用键约束和值约束）之外，还可以在查询字符串中显式指定类型。

SELECT DISTINCT * FROM /exampleRegion, positions.values positions TYPE Position 
WHERE positions.mktValue >= 25.00

33. 使用ELEMENT

使用ELEMENT(expr)从一个集合或数组中获取单个元素。如果参数中的集合或数组没有元素则会抛出FunctionDomainException。

ELEMENT(SELECT DISTINCT * FROM /exampleRegion 
WHERE id = 'XYZ-1').status = 'active'