Sybase和Oracle+常用函数

1、S ybase相关函数

1.1同期值的计算

SELECT A.ID,A.CALC_MONTH,A.NUM,B.TQ_NUM

FROM (SELECT A.ID,A.CALC_MONTH,NUM FROM) A

LEFT JOIN (SELECT B.ID,B.CALC_MONTH,TQ_NUM FROM) B

ON A.ID=B.ID AND A.CALC_MONTH=(CONVERT(INT,SUBSTR(B.CALC_MONTH,1,4))+1)||SUBSTR （B.CALC_MONTH,5,2)

1.2累计及同期累计值的计算

SUM(Column) OVER(PARTITION BY Column1，Column2，Column3... ORDER BY Column4 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) LJ_ Column –累计值

SUM(TQ_Column) OVER(PARTITION BY Column1，Column2，Column3... ORDER BY Column4 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) TQ_LJ_ Column –同期累计值

1.3TOP排序

DENSE_RANK() OVER(PARTITION BY Column1，Column2 ORDER BY Column3 DESC) TOP_N---排名1.4日期转换格式

Dateformat(日期字段，'yyyymmdd'). 其中日期字段必须为日期类型，否则不能进行转换例如：dateformat(getdate(),’yyyymm’)

1.5返回指定小数点位数。

round(numeric_expr,int_expr) ----把数值表达式圆整到int_expr指定的精度

例如：Round(convert(float,字段类型)，2）

1.6转换函数convert

此函数把值从一种类型改变成另一种类型

convert(datetype [(length)],expression)

例如：select "Advance="+convert(char(12),advance) from titles

1.7大小写转换

upper(char_expr) 把char_expr转换成大写形式

lower(char_expr) 把char_expr转换成小写形式

1.8删除空格

ltrim(char_expr) 删除头空

rtrim(char_expr) 删除尾空

1.9数学函数

abs(numeric_expr) 返回指定值的绝对值

ceiling(numeric_expr) 返回大于或等于指定值的最小整数

exp(float_expr) 给出指定值的指数值

floor(numeric_expr) 返回小于或等于指定值的最大整数

pi() 返回常数3.1415926

power(numeric_expr,power)返回numeric_expr的值给power的幂

rand([int_expr]) 返回0-1之间的随机浮点数，可指定基值

round(numeric_expr,int_expr)把数值表达式圆整到int_expr指定的精度

sign(int_expr) 返回正+1，零0或负-1

sqrt(float_expr) 返回指定值的平方根

1.10日期函数

getdate() 返回当前的系统日期和时间

datename(datepart,date_expr) 以字符串形式返回date_expr指定部分的值,转换成合适的名字datepart(datepart,date_expr ) 作为整数返回date_expr值的指定部分

datediff(datepart,date_expr1,date_expr2) 返回date_expr2-date_expr1,通过指定的datepart度量dateadd(datepart,number,date_expr)返回日期,通过在date_expr上增加指定number的日期部件而产生的

1.11ISNULL函数

isnull函数用指定的值代替查询栏或合计中的空值

例：select avg(isnull(total_order,$0)) from invoices

以下为Sybase函数大全，集中了大部分操作中所用到的函数信息。

(详见：SVN\参考资料\ETL参考资料\Sybase参考资料\Sybase函数大全)

2、O racle相关函数

2.1 Oracle分析函数1

SUM

功能描述：该函数计算组中表达式的累积和。

MIN

功能描述：在一个组中的数据窗口中查找表达式的最小值。

SAMPLE：下面例子中dept_min返回当前行所在部门的最小薪水值

MAX

功能描述：在一个组中的数据窗口中查找表达式的最大值。

AVG

功能描述：用于计算一个组和数据窗口内表达式的平均值。

COUNT

功能描述：对一组内发生的事情进行累积计数，如果指定*或一些非空常数，count将对所有行计数，如果指定一个表达式，count返回表达式非空赋值的计数，当有相同值出现时，这些相等的值都会被纳入被计算的值；可以使用DISTINCT来记录去掉一组中完全相同的数据后出现的行数。

2.2 Oracle分析函数2

RANK：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置同组内跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）DENSE_RANK：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置同组内连续排序，有两个第二名时仍然跟着第三名。

FIRST：从DENSE_RANK返回的集合中取出排在最前面的一个值的行

LAST：从DENSE_RANK返回的集合中取出排在最后面的一个值的行

FIRST_VALUE：返回组中数据窗口的第一个值

LAST_VALUE：返回组中数据窗口的最后一个值。

LAG：可以访问结果集中的其它行而不用进行自连接

LEAD：LEAD与LAG相反，LEAD可以访问组中当前行之后的行

ROW_NUMBER：返回有序组中一行的偏移量，从而可用于按特定标准排序的行号

2.3 Oracle分析函数3

STDDEV ：计算当前行关于组的标准偏离

STDDEV_POP：该函数计算总体标准偏离，并返回总体变量的平方根

STDDEV_SAMP：该函数计算累积样本标准偏离，并返回总体变量的平方根

VAR_POP ：该函数返回非空集合的总体变量（忽略null）

VAR_SAMP ：该函数返回非空集合的样本变量（忽略null）

VARIANCE ：如果表达式中行数为1，则返回0，如果表达式中行数大于1，则返回VAR_SAMP

COVAR_POP ：返回一对表达式的总体协方差

COVAR_SAMP：返回一对表达式的样本协方差

CORR ：返回一对表达式的相关系数

2.4 Oracle分析函数4

CUME_DIST ：计算一行在组中的相对位置

NTILE ：将一个组分为"表达式"的散列表示

PERCENT_RANK：和CUME_DIST（累积分配）函数类似

PERCENTILE_DISC：返回一个与输入的分布百分比值相对应的数据值

PERCENTILE_CONT：返回一个与输入的分布百分比值相对应的数据值

RATIO_TO_REPORT：该函数计算expression/(sum(expression))的值，它给出相对于总数的百分比

REGR_ (Linear Regression) Functions：这些线性回归函数适合最小二乘法回归线，有9个不同的回归函数可使用

-------------------------------------------------------------------------------------------------

2.5 Oracle分析函数的语法：

function_name(arg1,arg2,...) over ( )

说明：

1. partition-clause 数据记录集分组

2. order-by-clause 数据记录集排序

3. windowing clause 功能非常强大、比较复杂，定义分析函数在操作行的集合。有三种开窗方式: range、row、specifying。

--Partition by，按相应的值(manager_id)进行分组统计

--Order by按相应的值(hire_date)进行排序并累计统计

--Partition by Order by首先按相应的值(manager_id,hire_date)排序，并按order by 的值(hire_date)进行累计统计

2.6 Oracle分析函数5(求本期、累计值、最大、最小、平均值)：

昨日值：first_value(ydljfd_v) over (partition by id order by ddate RANGE NUMTODSINTERVAL(1,'day') PRECEDING) ydljfd_v_ldval

上周同期值：first_value(ydljfd_v) over (partition by id order by ddate RANGE NUMTODSINTERVAL(7,'day') PRECEDING) ydljfd_v_lwdval

上月同期值：FIRST_VALUE(ydljfd_v) OVER(PARTITION BY id ORDER BY ddate RANGE NUMTOYMINTERVAL(1,'month') PRECEDING) ydljfd_v_lmdval

FIRST_VALUE(B.SDL) OVER(PARTITION BY B.DEPT_ID ORDER BY TO_DATE(B.CALC_MONTH,'YYYY-MM') RANGE NUMTOYMINTERVAL(1,'YEAR') PRECEDING), --上年同期值

max(ydljfd_v) over (partition by id,trunc(ddate,'mm') order by ddate rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ydljfd_v_cdmmax, --当月最大值

min(ydljfd_v) over (partition by id,trunc(ddate,'mm') order by ddate rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ydljfd_v_cdmmin, --当月最小值

sum(ydljfd_v) over (partition by id,trunc(ddate,'mm') order by ddate rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ydljfd_v_cdmsum, --当月累计值

avg(ydljfd_v) over (partition by id,trunc(ddate,'mm') order by ddate rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ydljfd_v_cdmavg, --当月平均值

max(ydljfd_v) over (partition by id,trunc(ddate,'mm') order by ddate rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ydljfd_v_cammax, --全月最大值

min(ydljfd_v) over (partition by id,trunc(ddate,'mm') order by ddate rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ydljfd_v_cammin, --全月最小值

sum(ydljfd_v) over (partition by id,trunc(ddate,'mm') order by ddate rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ydljfd_v_camsum, --全月累计值

avg(ydljfd_v) over (partition by id,trunc(ddate,'mm') order by ddate rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ydljfd_v_camavg, --全月平均值

max(ydljfd_v) over (partition by id,trunc(ddate,'yyyy') order by ddate rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ydljfd_v_cdymax, --本年最大值

min(ydljfd_v) over (partition by id,trunc(ddate,'yyyy') order by ddate rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ydljfd_v_cdymin, --本年最小值

sum(ydljfd_v) over (partition by id,trunc(ddate,'yyyy') order by ddate rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ydljfd_v_cdysum, --本年累计值

avg(ydljfd_v) over (partition by id,trunc(ddate,'yyyy') order by ddate rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ydljfd_v_cdyavg, --本年平均值

max(ydljfd_v) over (partition by id,trunc(ddate,'yyyy') order by ddate rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

ydljfd_v_caymax, --本年最大值

min(ydljfd_v) over (partition by id,trunc(ddate,'yyyy') order by ddate rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

ydljfd_v_caymin, --本年最小值

sum(ydljfd_v) over (partition by id,trunc(ddate,'yyyy') order by ddate rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

ydljfd_v_caysum, --本年累计值

avg(ydljfd_v) over (partition by id,trunc(ddate,'yyyy') order by ddate rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

ydljfd_v_cayavg, --本年平均值

max(ydljfd_v) over (partition by id order by ddate RANGE BETWEEN (ddate-trunc(add_months(ddate,-1),'mm')) preceding AND (ddate -

last_day(add_months(ddate,-1)))preceding) ydljfd_v_lmamax,--上月全月

最大值

min(ydljfd_v) over (partition by id order by ddate RANGE BETWEEN (ddate-trunc(add_months(ddate,-1),'mm')) preceding AND (ddate -

last_day(add_months(ddate,-1)))preceding) ydljfd_v_lmamin,--上月全月

最小值

sum(ydljfd_v) over (partition by id order by ddate RANGE BETWEEN (ddate-trunc(add_months(ddate,-1),'mm')) preceding AND (ddate -

last_day(add_months(ddate,-1)))preceding) ydljfd_v_lmasum,--上月全月

累计值

avg(ydljfd_v) over (partition by id order by ddate RANGE BETWEEN (ddate-trunc(add_months(ddate,-1),'mm')) preceding AND (ddate -

last_day(add_months(ddate,-1)))preceding) ydljfd_v_lmaavg, --上月全月平均值

max(ydljfd_v) over (partition by id order by ddate RANGE BETWEEN (ddate-trunc(add_months(ddate,-1),'mm')) preceding AND (ddate -

add_months(ddate,-1))preceding) ydljfd_v_lmdmax,--上月同期最大值

min(ydljfd_v) over (partition by id order by ddate RANGE BETWEEN (ddate-trunc(add_months(ddate,-1),'mm')) preceding AND (ddate -

add_months(ddate,-1))preceding) ydljfd_v_lmdmin,--上月同期最小值

sum(ydljfd_v) over (partition by id order by ddate RANGE BETWEEN (ddate-trunc(add_months(ddate,-1),'mm')) preceding AND (ddate -

add_months(ddate,-1))preceding) ydljfd_v_lmdsum,--上月同期累计值

avg(ydljfd_v) over (partition by id order by ddate RANGE BETWEEN (ddate-trunc(add_months(ddate,-1),'mm')) preceding AND (ddate -

add_months(ddate,-1))preceding) ydljfd_v_lmdavg, --上月同期平均值

max(ydljfd_v) over (partition by id order by ddate RANGE BETWEEN (ddate-trunc(add_months(ddate,-12),'mm')) preceding AND (ddate -

add_months(ddate,-12))preceding) ydljfd_v_lymdmax,--去年同期当月最大

值

min(ydljfd_v) over (partition by id order by ddate RANGE BETWEEN (ddate-trunc(add_months(ddate,-12),'mm')) preceding AND (ddate -

add_months(ddate,-12))preceding) ydljfd_v_lymdmin,--去年同期当月最小

值

sum(ydljfd_v) over (partition by id order by ddate RANGE BETWEEN (ddate-trunc(add_months(ddate,-12),'mm')) preceding AND (ddate -

add_months(ddate,-12))preceding) ydljfd_v_lymdsum,--去年同期当月累计

值

avg(ydljfd_v) over (partition by id order by ddate RANGE BETWEEN (ddate-trunc(add_months(ddate,-12),'mm')) preceding AND (ddate -

add_months(ddate,-12))preceding) ydljfd_v_lymdavg --去年同期当月平均值max(ydljfd_v) over (partition by id order by ddate RANGE BETWEEN (ddate-trunc(add_months(ddate,-13),'mm')) preceding AND (ddate -

last_day(add_months(ddate,-13)))preceding) ydljfd_v_lylmamax,--去年同期上月全月最大值

min(ydljfd_v) over (partition by id order by ddate RANGE BETWEEN (ddate-trunc(add_months(ddate,-13),'mm')) preceding AND (ddate -

last_day(add_months(ddate,-13)))preceding) ydljfd_v_lylmamin,--去年同期上月全月最小值

sum(ydljfd_v) over (partition by id order by ddate RANGE BETWEEN (ddate-trunc(add_months(ddate,-13),'mm')) preceding AND (ddate -

last_day(add_months(ddate,-13)))preceding) ydljfd_v_lylmasum,--去年同期上月全月累计值

avg(ydljfd_v) over (partition by id order by ddate RANGE BETWEEN (ddate-trunc(add_months(ddate,-13),'mm')) preceding AND (ddate -

last_day(add_months(ddate,-13)))preceding) ydljfd_v_lylmaavg, --去年同期上月全月平均值

max(ydljfd_v) over (partition by id order by ddate RANGE BETWEEN (ddate-trunc(add_months(ddate,-1),'mm')) preceding AND (ddate -

add_months(ddate,-1))preceding) ydljfd_v_lylmdmax,---去年同期上月最大值

min(ydljfd_v) over (partition by id order by ddate RANGE BETWEEN (ddate-trunc(add_months(ddate,-1),'mm')) preceding AND (ddate -

add_months(ddate,-1))preceding) ydljfd_v_lylmdmin,--去年同期上月最小值

sum(ydljfd_v) over (partition by id order by ddate RANGE BETWEEN (ddate-trunc(add_months(ddate,-1),'mm')) preceding AND (ddate -

add_months(ddate,-1))preceding) ydljfd_v_lylmdsum,--去年同期上月累计值

avg(ydljfd_v) over (partition by id order by ddate RANGE BETWEEN (ddate-trunc(add_months(ddate,-1),'mm')) preceding AND (ddate -

add_months(ddate,-1))preceding) ydljfd_v_lylmdavg --去年同期上月平均值其相关信息如下：(详见：SVN\参考资料\ETL参考资料\Oracle参考资料\分析函数.xls)

D:\学习资料\

Oracle资料\分析函数.

2.7 Oracle分析函数6（oracle.10G函数大全）

(详见：SVN\参考资料\ETL参考资料\Oracle参考资料\ oracle.10G函数大全.chm)