![(please configure the [header_logo] section in trac.ini)](/chrome/site/your_project_logo.png){kind=logo}
Array functions and methods, Aggregate functions
Array functions, methods, and operators
| Function | HTSQL | SQL | Javascript | Python |
| Array constructor | array(x,y,...) | ARRAY[x, y, ...] | [x, y, ...], new Array(x, y, ...) | [x, y, ...] |
| Array length | a.length() | ARRAY_UPPER(a, 1) | a.length | len(a) |
| The k-th item | a[k] | a[k+1] | a[k] | a[k] |
| Extract a subarray (left, right) | a[l:r] | a[l+1:r] | a.slice(l[, r]) | a[l:r] |
| Concatenation | a1+a2 | a1 || a2 | a1.concat(a2, ...) | a1 + a2 |
Signatures:
- htsql:array(Any x, Any y, ...) -> Array[Any]
- Array[Any] . htsql:length() -> Number
- htsql:operator:"[.]"(Array[Any] a, Number k) -> Any
- htsql:operator:"[.:.]"(Array[Any] a, Number l, Number r) -> Array[Any]
- htsql:operator:"[.:]"(Array[Any] a, Number l) -> Array[Any]
- htsql:operator:"[:.]"(Array[Any] a, Number r) -> Array[Any]
- htsql:operator:"[:]"(Array[Any] a) -> Array[Any]
- htsql:operator:"+"(Array[Any] a, Array[Any] b) -> Array[Any]
Aggregate functions
| Function | HTSQL | SQL | Javascript | Python |
| Check if there exists a row satisfying a condition | exists(p) | EXISTS(SELECT 1 WHERE p) | -- | -- |
| Check if all rows satisfy a condition | every(p) | NOT EXISTS(SELECT 1 WHERE NOT p) | -- | -- |
| Count the number of rows satisfying a condition | count(p) | (SELECT COUNT(*) WHERE p) | -- | -- |
| Count the number of rows in an aspect | a.count() | (SELECT COUNT(*) FROM a) | -- | -- |
| Find the sum of the elements | sum(x) | (SELECT SUM(x)) | -- | -- |
| Find the average (sum/count) of the elements | avg(x) | (SELECT AVG(x)) | -- | -- |
| Find the minimal element | min(x) | (SELECT MIN(x)) | -- | -- |
| Find the maximal element | max(x) | (SELECT MAX(x)) | -- | -- |
| Generate array of the elements | array(x) | ARRAY(SELECT x ORDER BY x) | -- | -- |
| Take the first element | first(x) | (SELECT x ORDER BY x LIMIT 1) | -- | -- |
Signatures:
- htsql:exists(Boolean | Any p) -> Boolean(is_nullable=False)
- htsql:every(Boolean | Any p) -> Boolean(is_nullable=False)
- htsql:count(Boolean | Any p) -> Number(is_integer=True)
- Tuple . htsql:count() -> Number(is_integer=True)
- htsql:sum(Number x) -> Number
- htsql:avg(Number x) -> Number
- htsql:min(Number x) -> Number
- htsql:max(Number x) -> Number
- htsql:array(Any x) -> Array[Any]
- htsql:first(Any x) -> Any
The functions exists(), every(), and count() cast their argument into Boolean using the function htsql:boolean() if the given argument is not Boolean already.
The function array() sorts its elements.
