Difference between revisions of "AVA SQL"

From avawiki
Jump to: navigation, search
(Created page with "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== Mathemat...")
 
Line 2: Line 2:
  
 
==Analytics Functions==
 
==Analytics Functions==
−
Mathematical Functions
 
−
ID_MODULUS        %
 
−
ID_XOR            ||
 
−
ID_POW            ^
 
−
ID_FACTORIAL      !
 
−
ID_ABS            ABS()
 
−
ID_EXP            EXP()
 
−
ID_SIGN          SIGN()
 
−
ID_SQRT          SQRT()
 
−
ID_LOG            LOG()
 
−
ID_LOG10          LOG10()
 
−
ID_SIN            SIN()
 
−
ID_COS            COS()
 
−
ID_TAN            TAN()
 
−
ID_ASIN          ASIN()
 
−
ID_ACOS          ACOS()
 
−
ID_ATAN          ATAN()
 
−
ID_FFACTORIAL    FACTORIAL()
 
  
−
Statistical 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.
  
−
ID_SUM           
+
====Power ( ^ )====
−
ID_AVG           
+
Take the power of ( a real number ):
−
ID_SDV           
+
 
−
ID_MIN           
+
ava> select date, sec_id, o: open ^ 2 from a
−
ID_MAX           
+
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 ====
 +
 
 +
# '''ABS():''' Absolute Value
 +
# '''EXP():''' Exponential, e ( 2.72 ) to a power ( real number )
 +
# '''SIGN():''' Sign of a number
 +
# '''LOG():''' Log of e (2.72)
 +
# '''LOG10():''' LOG of 10
 +
# '''SIN():''' SIN
 +
# '''COS():''' COS
 +
# '''TAN():''' Tangent
 +
# '''ASIN():''' Arc Sin
 +
# '''ACOS():''' Arc Cos
 +
# '''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
 
Other Functions
−
ID_COUNT          
+
COUNT          
−
ID_FIRST          
+
FIRST          
−
ID_LAST            
+
LAST            
−
ID_WSUM            
+
WSUM            
−
ID_WAVG            
+
WAVG            
−
ID_WSTD            
+
WSTD            
−
ID_WMIN            
+
WMIN            
−
ID_WMAX            
+
WMAX            
−
ID_WCOUNT          
+
WCOUNT          
−
ID_CUMSUM          
+
CUMSUM          
−
ID_CUMPROD        
+
CUMPROD        
−
ID_EMA            
+
EMA            
−
ID_RANK            
+
RANK            
−
ID_XRANK          
+
XRANK          
  
  

Revision as of 15:31, 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.

Contents

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


Table Join Functions

Data Conversion Functions

Retrieved from "http://avainformatics.com/avawiki/index.php?title=AVA_SQL&oldid=27"