AVA SQL

AVA Database follows SQL '92 standard. In addition, AVA's unique SQL adds new features that facilitate high speed analytics and data mining.

Analytics Functions

Mathematical Functions

Modulus ( % )

Take the modulus of the two numbers:

ava> select date, sec_id, ol: open % low from a
TABLE NAME: a | NUMBER OF RECORDS: 3,252,871
---------------------------------------------------
      date |     sec_id |         ol |
---------- | ---------- | ---------- |
2000/10/16 |   ss600008 |       0.00 |
2000/10/16 |   ss600018 |       0.00 |
2000/10/16 |   ss600130 |       1.00 |
0.036 seconds elapsed.

Power ( ^ )

Take the power of ( a real number ):

ava> select date, sec_id, o: open ^ 2 from a
TABLE NAME: a | NUMBER OF RECORDS: 3,252,871
---------------------------------------------------
      date |     sec_id |          o |
---------- | ---------- | ---------- |
2000/10/16 |   ss600008 |     416.16 |
2000/10/16 |   ss600018 |     406.43 |
2000/10/16 |   ss600130 |     788.49 |
0.081 seconds elapsed.

Factorial ( factorial )

Take factorial of an integer:

ava> select date, sec_id, f: factorial(5) * open  from a
TABLE NAME: a | NUMBER OF RECORDS: 3,252,871
---------------------------------------------------
      date |     sec_id |          f |
---------- | ---------- | ---------- |
2000/10/16 |   ss600008 |   2,448.00 |
2000/10/16 |   ss600018 |   2,419.20 |
2000/10/16 |   ss600130 |   3,369.60 |
0.02 seconds elapsed.

Other mathematical functions

1. ABS(): Absolute Value
2. EXP(): Exponential, e ( 2.72 ) to a power ( real number )
3. SIGN(): Sign of a number
4. LOG(): Log of e (2.72)
5. LOG10(): LOG of 10
6. SIN(): SIN
7. COS(): COS
8. TAN(): Tangent
9. ASIN(): Arc Sin
10. ACOS(): Arc Cos
11. ATAN(): Arc Tangent

Statistical Functions

SUM

Aggregate function. Sum over a group ( option )

ava> select s: sum(volume) from a group by sec_id
TABLE NAME: a | NUMBER OF RECORDS: 1,718
---------------------------------------------------
    sec_id |          s |
---------- | ---------- |
  ss000001 |  407718300 |
  ss600000 |  424768664 |
  ss600001 |  481888440 |
0.29 seconds elapsed.

AVG

Aggregate function. Average over a group ( option )

ava> select s: avg(close) from a group by sec_id
TABLE NAME: a | NUMBER OF RECORDS: 1,718
---------------------------------------------------
    sec_id |          s |
---------- | ---------- |
  ss000001 |   2,214.42 |
  ss600000 |      17.70 |
  ss600001 |       5.31 |
0.288 seconds elapsed.

STD

Aggregate function. Standard Deviation over a group ( option )

ava> select s: std(close) from a group by sec_id
TABLE NAME: a | NUMBER OF RECORDS: 1,718
---------------------------------------------------
    sec_id |          s |
---------- | ---------- |
  ss000001 |   1,034.01 |
  ss600000 |      11.83 |
  ss600001 |       1.53 |
0.291 seconds elapsed.

MIN

Aggregate function. Minimum value over a group ( option )

ava> select s: min(close) from a group by sec_id
TABLE NAME: a | NUMBER OF RECORDS: 1,718
---------------------------------------------------
    sec_id |          s |
---------- | ---------- |
  ss000001 |   1,011.50 |
  ss600000 |       6.48 |
  ss600001 |       2.72 |
0.292 seconds elapsed.

MAX

Aggregate function. Maximum value over a group ( option )

ava> select s: max(close) from a group by sec_id
TABLE NAME: a | NUMBER OF RECORDS: 1,718
---------------------------------------------------
    sec_id |          s |
---------- | ---------- |
  ss000001 |   6,092.06 |
  ss600000 |      61.59 |
  ss600001 |      10.92 |
0.293 seconds elapsed.

SQRT

Other Functions COUNT FIRST LAST WSUM WAVG WSTD WMIN WMAX WCOUNT CUMSUM CUMPROD EMA RANK XRANK