<< Click to Display Table of Contents >> 当前位置:  附录  DATA MART数据源支持的函数 复制链接

Yonghong DATA MART数据源请参考数据准备>数据源>SQL数据源>DATA MART数据源部分，本章将介绍此数据源支持的SQL语法。

1.Select，Group By，Order By

  SELECT * from test_cloud.clqry

  SELECT Market_Size, Sales from test_cloud.clqry where Market_Size = 'Major Market'

  SELECT Market, Sum(Sales) as Sum_Sales from test_cloud.clqry group by Market

  SELECT Market, Sales from test_cloud.clqry order by Sales DESC

2.Distinct

  select distinct MARKET, MARKET_SIZE from "cloud.clqry"

3.Alias

  select MARKET as mk, ID+PROFIT as num from "cloud.clqry"

4.Join，Left Join，Right Join，Full Join

  select aa.datadata,bb.sssss,"bb.date",bb.列1 from "GDriver/query/data1data2.cmqry" aa

  inner join "GDriver/query/data1data2.cmqry"  bb

  on "aa.datadata" = "bb.列1"

5.Union

  select  B.ID from "GDriver/query/cloud1.clqry" B

  union

  select A.ID from "GDriver/query/cloud2.clqry" A

6.Where

•Null

  select ID from "cloud.clqry"  where PROFIT is null

•Like

  SELECT Market from test_cloud.clqry where Market like '%a%'

•Subquery, not, in

  SELECT domestic_price as M_ID , global_price FROM CustomOilQry.cqry where M_ID NOT IN (SELECT ID as S_ID FROM DerbyCoffeeQrySQL.sqry WHERE S_ID>3)

•Between

  SELECT ID from test_cloud.clqry where ID between 2 and 10

•ContainsIn

  select distinct MARKET from "cloud.clqry"  where MARKET containsin ('C', 'E')

•"=", "!=", "<>", ">", ">=", "<", "<="

  select PROFIT from "cloud.clqry"  where PROFIT = -380

•and, or

  select MARKET, ID from "cloud.clqry"  where (MARKET='East' or ID<100) and (MARKET='West' or ID>4000)

7. Case When

select (case MARKET when 'West' then 0 when 'Central' then 1 else 2 end) as c from cloud.clqry

select (case when MARKET = 'West' then 0 when MARKET = 'Central' then 1 else 2 end) as c from cloud.clqry

8.子查询

•Subquery in where

  SELECT Id_O as M_ID FROM Orders.eqry WHERE M_ID = (SELECT domestic_price FROM CustomOilQry.cqry WHERE domestic_price=3)

•Subquery in from

  select p, s from (select PRODUCT as p, Sum(SALES) as s from "cloud.clqry" group by p)v where s < 50000

9.Limit

  select MARKET, MARKET_SIZE, ID from "cloud.clqry" order by MARKET, MARKET_SIZE limit 3

10.注释

•--

  select PRODUCT as p, Sum(SALES) as s

  from "cloud.clqry"

  --where PRODUCT = ?{p}

  group by p

•/** **/

  select PRODUCT as p, Sum(SALES) as s

  from "cloud.clqry"

  /**where PRODUCT = ?{p}**/

  group by p

11.参数

  select * from "cloud.clqry"  where 1=1 <id>and ID < ?{id}</id><state> and STATE like ?{state}</state>

  select * from "?{@c}loud.clqry"

12.聚合函数

Max

  SELECT Max(Sales) as Max_Sales from test_cloud.clqry

  SELECT Product, Max(Sales) as Max_Sales from test_cloud.clqry group by Product

Min

  SELECT Product, Min(Sales) as Min_Sales from test_cloud.clqry group by Product

Sum

  SELECT Sum(Sales) as Sum_Sales from test_cloud.clqry

Count

  SELECT Count(Sales) as Count_Sales from test_cloud.clqry

Count Distinct

  SELECT Count(Distinct Market) as DistinctCount_Market from test_cloud.clqry

DistinctCount    

  SELECT Product, DistinctCount(Sales) as DistinctCount_Market from test_cloud.clqry group by Product

Avg

  SELECT Product, Avg(Sales) as Avg_Sales from test_cloud.clqry group by Product

Correlation

  SELECT Product, Correlation(Sales, Profit) as Correlation_Sales_Profit from test_cloud.clqry group by Product

Covariance

  SELECT Product, Covariance(Sales, Profit) as Covariance_Sales_Profit from test_cloud.clqry group by Product

Median

  SELECT Product, Median(Sales) as Median_Sales from test_cloud.clqry group by Product

PopulationStandardDeviation

  SELECT Product, PopulationStandardDeviation(Sales) as PopulationStandardDeviation_Sales from test_cloud.clqry group by Product

PopulationVariance

  SELECT Product, PopulationVariance(Sales) as PopulationVariance_Sales from test_cloud.clqry group by Product

Product

  SELECT Product, Product(Sales) as Product_Sales from test_cloud.clqry where ID <= 100 group by Product

PthPercentile

  SELECT Product, PthPercentile(Sales, 20) as PthPercentile_Sales from test_cloud.clqry group by Product

Quartile

  SELECT Product, Quartile(Sales, 1) as Quartile_Sales from test_cloud.clqry group by Product

Range

  SELECT Product, Min(Sales) as Min_Sales, Max(Sales) as Max_Sales, Range(Sales) as Range_Sales from test_cloud.clqry group by Product

StandardDeviation  

  SELECT Product, StandardDeviation(Sales) as StandardDeviation_Sales from test_cloud.clqry group by Product

StandardError  

  SELECT Product,StandardError(Sales) as StandardError_Sales from test_cloud.clqry group by Product

SumSQ  

  SELECT Product,SumSQ(Sales) as SumSQ_Sales from test_cloud.clqry group by Product

SumWT  

  SELECT Product,SumWT(Sales,Profit) as SumWT_Sales_Profit from test_cloud.clqry group by Product

Variance  

  SELECT Product, Variance(Sales) as Variance_Sales from test_cloud.clqry group by Product

WeightAVG

  SELECT Product, WeightAVG(Sales, Profit) as WeightAvg_Sales_Profit from test_cloud.clqry group by Product

Mode  

  SELECT Product, Mode_(Sales) as Mode_Sales from test_cloud.clqry group by Product

13.非聚合函数

isNumber(Object val)：判断给定的参数是不是数字。

isDate(Object val)：判断给定的参数是不是日期。

isNull(Object val)：检测对象是否为空。

position(经度:int, 纬度:int)：把经度和纬度转化为一个long来存储，可以用来做GIS inrect和inCircle的输入参数。

formatDate(Object val, String pattern)：把日期按照指定的格式转化为字符串。

parseDate(Object val, String pattern)：把字符串按照指定的格式转化为日期。

dateAdd(Object date, String field, int interval)：按照指定的Field修改日期，合理的field参数是”year”, “quarter”, “month”, “weekofyear”,     “dayofyear”, ”dayofmonth”, “dayofweek”, “hour”, “minute”, “second”。

dateGap(Object date1, Object date2, String field)：计算日期按照指定的Field间隔，合理的field参数是”year”, “quarter”, “month”, “weekofyear”,     “dayofyear”, ”dayofmonth”, “dayofweek”, “hour”, “minute”, “second”。

datePart(Object date, String field)：返回日期指定的Field值，合理的field参数是”year”, “quarter”, “month”, “weekofyear”,     “dayofyear”, ”dayofmonth”, “dayofweek”, “hour”, “minute”, “second”。

trim (String str)：去掉字符串前后的空格。

substring(String str， Integer idx, Object end))：返回某个指定位置的字符串的子集。

formatNumber (double num, String pattern)：把数字按照指定的格式转化为字符串。

indexOf (String src, String key, Object fromIdx)：在制定的字符串中查找子串的位置。

sqr (Object val)：返回平方。

sqrt (Object val)：返回平方根。

abs(Object val)：返回绝对值。

toString(Object val)：将任意对象转换为字符串。

日期函数

返回日期列：Year, Quarter, month, week, day, hour, minute, second

SELECT "Date", year("Date"), quarter("Date"), month("Date"), week("Date"), day("Date"), hour("Date"), minute("Date"), second("Date") from test_cloud.clqry

返回数值列：quarterpart, monthpart, weekpart, daypart, hourpart, minutepart, secondpart

SELECT "Date", quarterpart("Date"), monthpart("Date"), weekpart("Date"), daypart("Date"), hourpart("Date"), minutepart("Date"),  secondpart("Date") from test_cloud.clqry

GIS函数：incircle, inrect

InCircle用来返回指定的经纬度，半径（单位是米）以内满足条件的点。

SELECT longitude,latitude from "gis/gis_cloud.clqry" where pos1 incircle(6.0, 5.1, 1000000) order by longitude, latitude asc

InRect用来返回指定中心点，宽和高（单位是米）以内满足条件的点。

SELECT longitude,latitude from "gis/gis_cloud.clqry" where pos1 inrect(-15, 4.1, 2226388, 2226388) order by longitude, latitude asc

14.Excel中的各种计算函数

14.1数学函数

abs(double)

acos(double)

acosh(double)

asin(double)

asinh(double)

atan(double)

atan2(double, double)

atanh(double)

ceiling(double, double)

combin(double, double)

cos(double)

cosh(double)

degrees(double)

even(double)

exp(double)

fact(double)

factdouble(double)

floor(double, double)

gcd(Object)

integer(double)

lcm(Object)

ln(double)

log(double, double)

log10(double)

mathProduct(Object)

mathSum(Object)

mathSumsq(Object)

mdeterm(Object)

mod(double, double)

mround(double, double)

multinomial(Object)

odd(double)

pi()

power(double, double)

quotient(double, double)

radians(double)

rand()

randbetween(int, int)

round(double, int)

rounddown(double, int)

roundup(double, int)

seriessum(double, double, double, Object)

sign(double)

sin(double)

sinh(double)

sqr(double)

sqrt(double)

sqrtpi(double)

subtotal(int, Object)

sumif(Object, String, Object)

sumproduct(Object)

sumx2my2(Object, Object)

sumx2py2(Object, Object)

sumxmy2(Object, Object)

tan(double)

tanh(double)

trunc(double, int)

14.2日期函数

date(int, int, int)

datevalue(Object)

day(Object)

dayofyear(Object)

days360(Object, Object, Object)

edate(Object, int)

eomonth(Object, int)

hour(Object)

minute(Object)

month(Object)

monthname(Object)

networkdays(Object, Object, Object)

now()

quarter(Object)

second(Object)

time(int, int, int)

timevalue(Object)

today()

weekday(Object, Object)

weekdayname(Object)

weeknum(Object, Object)

workday(Object, int, Object)

year(Object)

yearfrac(Object, Object, Object)

14.3金融函数

accrint(Object, Object, Object, double, double, double, double)

accrintm(Object, Object, double, double, double)

amordegrc(double, Object, Object, double, int, double, int)

amorlinc(double, Object, Object, double, int, double, int)

coupdaybs(Object, Object, double, int)

coupdays(Object, Object, double, double)

coupdaysnc(Object, Object, double, double)

coupncd(Object, Object, double, double)

coupnum(Object, Object, double, double)

couppcd(Object, Object, double, double)

cumipmt(double, int, double, double, double, int)

cumprinc(double, int, double, double, double, int)

ddb(double, double, double, double, double)

disc(Object, Object, double, double, double)

duration(Object, Object, double, double, double, double)

effect(double, double)

financialDb(double, double, double, double, double)

fv(double, int, double, double, int)

fvschedule(double, Object)

intrate(Object, Object, double, double, double)

ipmt(double, int, int, double, double, int)

ispmt(double, int, int, double)

mduration(Object, Object, double, double, double, double)

mirr(Object, double, double)

nominal(double, double)

nper(double, double, double, double, int)

npv(double, Object)

pmt(double, int, double, double, int)

ppmt(double, int, int, double, double, int)

price(Object, Object, double, double, double, double, int)

pricedisc(Object, Object, double, double, double)

pricemat(Object, Object, Object, double, double, double)

pv(double, int, double, double, int)

received(Object, Object, double, double, double)

sln(double, double, int)

syd(double, double, double, double)

tbilleq(Object, Object, double)

tbillprice(Object, Object, double)

tbillyield(Object, Object, double)

vdb(double, double, double, double, double, double, boolean)

xnpv(double, Object, Object)

yielddisc(Object, Object, double, double, double)

yieldmat(Object, Object, Object, double, double, double)

14.4逻辑函数

and(Object, Object, Object, Object)

iif(Object, Object, Object)

not(Object)

or(Object, Object, Object, Object)

14.5统计函数

avedev(Object)

average(Object)

averagea(Object)

averageif(Object, String, Object)

binomdist(double, double, double, boolean)

correl(Object, Object)

counta(Object)

countblank(Object)

countdistinct(Object)

countif(Object, String)

countn(Object)

covariance(Object, Object)

devsq(Object)

expondist(double, double, boolean)

fisher(double)

fisherinv(double)

forecast(double, Object, Object)

frequency(Object, Object)

geomean(Object)

harmean(Object)

hypgeomdist(double, double, double, double, boolean)

intercept(Object, Object)

kurt(Object)

large(Object, int)

maxa(Object)

mina(Object)

negbinomdist(double, double, double)

pearson(Object, Object)

percentile(Object, double)

percentrank(Object, double, double)

permut(double, double)

poisson(double, double, boolean)

prob(Object, Object, double, double)

rank(double, Object, double)

rsq(Object, Object)

skew(Object)

slope(Object, Object)

small(Object, int)

standardize(double, double, double)

statisticCount(Object)

statisticMax(Object)

statisticMedian(Object)

statisticMin(Object)

statisticMode(Object)

statisticQuartile(Object, double)

stdev(Object)

stdeva(Object)

stdevp(Object)

stdevpa(Object)

steyx(Object, Object)

trimmean(Object, double)

vara(Object)

varn(Object)

varp(Object)

varpa(Object)

weibull(double, double, double, boolean)

weightedavg(Object, Object)

14.6文本函数

character(int)

code(String)

concatenate(Object)

currency(double, int)

dollar(double, int)

exact(String, String)

find(String, String, int)

fixed(double, int, boolean)

left(String, int)

len(String)

lower(String)

mid(String, int, int)

proper(String)

replace(String, int, int, String)

rept(String, int)

right(String, int)

search(String, String, int)

substitute(String, String, String, int)

t(String)

text(double, String)

trim(String)

upper(String)