Difference between revisions of "AVA SQL"

Revision as of 15:52, 14 May 2013

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

Some AVA SQL conventions

Keyword cases

Keywords are case-insensitive. Both the low case or upper case, or mixed case will point to the same keyword. However, the column names and table names are case-sensitive.

Column assignment

A column can be created from an SQL update. There are two ways to assign a resulting column to a named column:

SQL convention

The "AS" clause to assign the results to a column.

ava> update low * 123.1 as new_low from a
0.024 seconds elapsed.

Non-SQL convention

A shortcut to create a column is by the syntax: "COLUMN_NAME: ...":

ava> update new_low: low * 123.1 from a
0.029 seconds elapsed.

Both will generate the same result:

ava> select * from a
TABLE NAME: a | NUMBER OF RECORDS: 3,252,871
---------------------------------------------------
      date |     sec_id |       open |       high |        low |      close |     volume |  adj_close |    new_low |
---------- | ---------- | ---------- | ---------- | ---------- | ---------- | ---------- | ---------- | ---------- |
2000/10/16 |   ss600008 |      20.40 |      20.58 |      20.18 |      20.58 |     797800 |      16.87 |   2,484.16 |
2000/10/16 |   ss600018 |      20.16 |      20.50 |      20.15 |      20.20 |     346400 |      17.60 |   2,480.46 |
2000/10/16 |   ss600130 |      28.08 |      28.29 |      27.16 |      27.23 |     438300 |      24.89 |   3,343.40 |
0.001 seconds elapsed.

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.

COUNT FIRST LAST

Other Functions WSUM WAVG WSTD WMIN WMAX WCOUNT

RANK XRANK

CUMSUM CUMPROD EMA

Table Join Functions

Data Conversion Functions