apache-ignite

1
// Licensed to the Apache Software Foundation (ASF) under one or more
2
// contributor license agreements.  See the NOTICE file distributed with
3
// this work for additional information regarding copyright ownership.
4
// The ASF licenses this file to You under the Apache License, Version 2.0
5
// (the "License"); you may not use this file except in compliance with
6
// the License.  You may obtain a copy of the License at
7
//
8
// http://www.apache.org/licenses/LICENSE-2.0
9
//
10
// Unless required by applicable law or agreed to in writing, software
11
// distributed under the License is distributed on an "AS IS" BASIS,
12
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13
// See the License for the specific language governing permissions and
14
// limitations under the License.
15
= Aggregate Functions
16

17
== AVG
18

19

20
[source,sql]
21
----
22
AVG ([DISTINCT] expression)
23
----
24

25
The average (mean) value. If no rows are selected, the result is `NULL`. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter.
26

27
=== Parameters
28

29
- `DISTINCT` - optional keyword. If presents, will average the unique values.
30

31

32
=== Examples
33
Calculating average players' age:
34

35

36
[source,sql]
37
----
38
SELECT AVG(age) "AverageAge" FROM Players;
39
----
40

41

42
== BIT_AND
43

44

45
[source,sql]
46
----
47
BIT_AND (expression)
48
----
49

50
The bitwise AND of all non-null values. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements.
51

52
A logical AND operation is performed on each pair of corresponding bits of two binary expressions of equal length.
53

54
In each pair, it returns 1 if the first bit is 1 AND the second bit is 1. Else, it returns 0.
55

56

57
== BIT_OR
58

59

60
[source,sql]
61
----
62
BIT_OR (expression)
63
----
64

65
The bitwise OR of all non-null values. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements.
66

67
A logical OR operation is performed on each pair of corresponding bits of two binary expressions of equal length.
68

69
In each pair, the result is 1 if the first bit is 1 OR the second bit is 1 OR both bits are 1, and otherwise the result is 0.
70

71
////
72
== BOOL_AND
73

74
[source,sql]
75
----
76
BOOL_AND (boolean)
77
----
78

79
Returns true if all expressions are true. If no entries are selected, the result is NULL. Aggregates are only allowed in select statements.
80

81
=== Example
82

83
[source,sql]
84
----
85
SELECT item, BOOL_AND(price > 10) FROM Items GROUP BY item;
86
----
87

88
== BOOL_OR
89

90
[source,sql]
91
----
92
BOOL_AND  (boolean)
93
----
94

95
Returns true if any expression is true. If no entries​ are selected, the result is NULL. Aggregates are only allowed in select statements.
96

97
=== Example
98

99
[source,sql]
100
----
101
SELECT BOOL_OR(CITY LIKE 'W%') FROM Users;
102
----
103
////
104

105
== COUNT
106

107
[source,sql]
108
----
109
COUNT (* | [DISTINCT] expression)
110
----
111

112
The count of all entries or of the non-null values. This method returns a long. If no entries are selected, the result is 0. Aggregates are only allowed in select statements.
113

114
=== Example
115
Calculate the number of players in every city:
116

117
[source,sql]
118
----
119
SELECT city_id, COUNT(*) FROM Players GROUP BY city_id;
120
----
121

122
== GROUP_CONCAT
123

124
[source,sql]
125
----
126
GROUP_CONCAT([DISTINCT] expression || [expression || [expression ...]]
127
  [ORDER BY expression [ASC|DESC], [[ORDER BY expression [ASC|DESC]]]
128
  [SEPARATOR expression])
129
----
130

131
Concatenates strings with a separator. The default separator is a ',' (without whitespace). This method returns a string. If no entries are selected, the result is NULL. Aggregates are only allowed in select statements.
132

133
The `expression` can be a concatenation of columns and strings using the `||` operator, for example: `column1 || "=" || column2`.
134

135
=== Parameters
136
- `DISTINCT` - filters the result set for unique sets of expressions.
137
- `expression` - specifies an expression that may be a column name, a result of another function, or a math operation.
138
- `ORDER BY` - orders rows by expression.
139
- `SEPARATOR` - overrides a string separator. By default, the separator character is the comma ','.
140

141
NOTE: The `DISTINCT` and `ORDER BY` expressions inside the GROUP_CONCAT function are only supported if you group the results by the primary or affinity key (i.e. use `GROUP BY`). Moreover, you have to tell Ignite that your data is colocated by specifying the `collocated=true` property in the connection string or by calling `SqlFieldsQuery.setCollocated(true)` if you use the link:{javadoc_base_url}/org/apache/ignite/cache/query/SqlFieldsQuery.html#setCollocated-boolean-[Java API, window=_blank].
142

143

144
=== Example
145
Group all players' names in one row:
146

147

148
[source,sql]
149
----
150
SELECT GROUP_CONCAT(name ORDER BY id SEPARATOR ', ') FROM Players;
151
----
152

153

154
== MAX
155

156
[source,sql]
157
----
158
MAX (expression)
159
----
160

161
Returns the highest value. If no entries are selected, the result is NULL. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter.
162

163

164
=== Parameters
165
- `expression` - may be a column name, a result of another function, or a math operation.
166

167

168
=== Example
169
Return the height of the ​tallest player:
170

171

172
[source,sql]
173
----
174
SELECT MAX(height) FROM Players;
175
----
176

177

178
== MIN
179

180
[source,sql]
181
----
182
MIN (expression)
183
----
184

185
Returns the lowest value. If no entries are selected, the result is NULL. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter.
186

187

188

189
=== Parameters
190
- `expression` - may be a column name, the result of another function, or a math operation.
191

192
=== Example
193
Return the age of the youngest player:
194

195

196
[source,sql]
197
----
198
SELECT MIN(age) FROM Players;
199
----
200

201

202
== SUM
203

204
[source,sql]
205
----
206
SUM ([DISTINCT] expression)
207
----
208

209
Returns the sum of all values. If no entries are selected, the result is NULL. Aggregates are only allowed in select statements. The data type of the returned value depends on the parameter data.
210

211

212
=== Parameters
213
- `DISTINCT` - accumulate unique values only.
214
- `expression` - may be a column name, the result of another function, or a math operation.
215

216
=== Example
217
Get the total number of goals scored by all players:
218

219

220
[source,sql]
221
----
222
SELECT SUM(goal) FROM Players;
223
----
224

225
////
226
this function is not supported
227
== SELECTIVITY
228

229
[source,sql]
230
----
231
SELECTIVITY (expression)
232
----
233
Estimates the selectivity (0-100) of a value. The value is defined as `(100 * distinctCount / rowCount)`. The selectivity of 0 rows is 0 (unknown). Aggregates are only allowed in select statements.
234

235

236
=== Parameters
237
- `expression` - may be a column name.
238

239

240
=== Example
241
Calculate the selectivity of the `first_name` and `second_name` columns:
242

243

244
[source,sql]
245
----
246
SELECT SELECTIVITY(first_name), SELECTIVITY(second_name) FROM Player
247
  WHERE ROWNUM() < 20000;
248
----
249

250

251
== STDDEV_POP
252

253
[source,sql]
254
----
255
STDDEV_POP ([DISTINCT] expression)
256
----
257
Returns the population standard deviation. This method returns a `double`. If no entries are selected, the result is NULL. Aggregates are only allowed in select statements.
258

259

260
=== Parameters
261
- `DISTINCT` - calculate unique value only.
262
- `expression` - may be a column name.
263

264

265
=== Example
266
Calculate the standard deviation for Players' age:
267

268

269
[source,sql]
270
----
271
SELECT STDDEV_POP(age) from Players;
272
----
273

274

275
== STDDEV_SAMP
276

277
[source,sql]
278
----
279
STDDEV_SAMP ([DISTINCT] expression)
280
----
281

282
Calculates the sample standard deviation. This method returns a `double`. If no entries are selected, the result is NULL. Aggregates are only allowed in select statements.
283

284
=== Parameters
285
- `DISTINCT` - calculate unique values only.
286
- `expression` - may be a column name.
287

288

289
=== Example
290
Calculates the sample standard deviation for Players' age:
291

292

293
[source,sql]
294
----
295
SELECT STDDEV_SAMP(age) from Players;
296
----
297

298

299
== VAR_POP
300

301
[source,sql]
302
----
303
VAR_POP ([DISTINCT] expression)
304
----
305

306
Calculates the _population variance_ (square of the population standard deviation). This method returns a `double`. If no entries are selected, the result is NULL. Aggregates are only allowed in select statements.
307

308

309
=== Parameters
310
- `DISTINCT` - calculate unique values only.
311
- `expression` - may be a column name.
312

313

314
=== Example
315
Calculate the variance of Players' age:
316

317

318
[source,sql]
319
----
320
SELECT VAR_POP (age) from Players;
321
----
322

323

324

325
== VAR_SAMP
326

327
[source,sql]
328
----
329
VAR_SAMP ([DISTINCT] expression)
330
----
331

332
Calculates the _sample variance_ (square of the sample standard deviation). This method returns a `double`. If no entries are selected, the result is NULL. Aggregates are only allowed in select statements.
333

334

335
=== Parameters
336
- `DISTINCT` - calculate unique values only.
337
- `expression` - may be a column name.
338

339

340
=== Example
341
Calculate the variance of Players' age:
342

343

344
[source,sql]
345
----
346
SELECT VAR_SAMP(age) FROM Players;
347
----
348
////
349