Generic
Function |
Netezza |
Oracle |
| First |
#N/A |
Min(#0)
Keep (dense_rank First order by #1) |
| GeoMean |
EXP(AVG(LN(#0))) |
EXP(AVG(LN(#0))) |
| Greatest |
#N/A |
GREATEST(#0#<,
#*#>) |
| Last |
#N/A |
Max(#0)
Keep (dense_rank Last order by #1) |
| Product |
(CASE
WHEN SUM(CASE WHEN #0=0 THEN 1 ELSE 0 END) > 0 THEN 0 ELSE POW(-1,SUM(CASE
WHEN #0<0 THEN 1 ELSE 0 END))*EXP(SUM(LN(ABS(CASE WHEN #0<>0 THEN #0
ELSE NULL END)))) END) |
(CASE
WHEN SUM(CASE WHEN #0=0 THEN 1 ELSE 0 END) > 0 THEN 0 ELSE
POWER(-1,SUM(CASE WHEN #0<0 THEN 1 ELSE 0 END))*EXP(SUM(LN(ABS(CASE WHEN
#0<>0 THEN #0 ELSE NULL END)))) END) |
| Stdev |
STDDEV(#0#<
#*#>) |
STDDEV(#0#<
#*#>) |
| StdevP |
STDDEV_POP(#0#<
#*#>) |
STDDEV_POP(#0#<
#*#>) |
| Var |
VARIANCE(#0#<
#*#>) |
VARIANCE(#0#<
#*#>) |
| VarP |
VAR_POP(#0#<
#*#>) |
VAR_POP(#0#<
#*#>) |
| CurrentDate |
CURRENT_DATE |
CURRENT_DATE |
| CurrentDateTime |
NOW() |
CURRENT_TIMESTAMP |
| CurrentTime |
CURRENT_TIME |
CURRENT_TIMESTAMP |
| DayOfMonth |
EXTRACT(DAY
FROM #0) |
TO_NUMBER(TO_CHAR(#0,
'DD')) |
| DayOfWeek |
TO_NUMBER(TO_CHAR(#0,
'D'),'9') |
TO_NUMBER(TO_CHAR(#0,
'D')) |
| DayOfYear |
EXTRACT(DOY
FROM #0) |
TO_NUMBER(TO_CHAR(#0,
'DDD')) |
| Hour |
EXTRACT(HOUR
FROM #0) |
TO_NUMBER(TO_CHAR(#0,
'HH')) |
| MilliSecond |
EXTRACT(MILLISECOND
FROM #0) |
0 |
| Minute |
EXTRACT(MINUTE
FROM #0) |
TO_NUMBER(TO_CHAR(#0,
'MI')) |
| Month |
EXTRACT(MONTH
FROM #0) |
TO_NUMBER(TO_CHAR(#0,
'MM')) |
| Quarter |
TO_NUMBER(TO_CHAR(#0,
'Q'),'9') |
TO_NUMBER(TO_CHAR(#0,
'Q')) |
| Second |
EXTRACT(SECOND
FROM #0) |
TO_NUMBER(TO_CHAR(#0,
'SS')) |
| Week |
TO_NUMBER(TO_CHAR(#0,
'WW') ,'99') |
TO_NUMBER(TO_CHAR(#0,
'WW')) |
| Year |
EXTRACT(YEAR
FROM #0) |
TO_NUMBER(TO_CHAR(#0,
'YYYY')) |
| Banding |
Case
when (#0=#2 and #2=#1+#3*floor((#2-#1)/#3) ) then floor(1.0*(#0-#1)/#3) when
(#0 between #1 and #2) then floor(1.0*(#0-#1)/#3) +1 else 0 end |
Case
when (#0=#2 and #2=#1+#3*floor((#2- #1)/#3) ) then floor(1.0*(#0- #1)/#3)
when (#0 between #1 and #2) then floor(1.0*(#0- #1)/#3) +1 else 0 end |
| BandingC |
Case
when (#1=#2 and #0=#1) then #3 when (#0=#2) then #3 when (#0 between #1 and
#2) then floor(1.0*(#3*(#0-#1)/(#2-#1)+1)) else 0 end |
Decode(sign(#0-#1)+sign(#0-#2),
1, #3, 0, decode(#1, #2, 1, floor(1.0*(#0-#1)/(#2-#1)*#3+1)), -1, 1, 0) |
| Abs |
ABS(#0) |
ABS(#0) |
| Acos |
ACOS(#0) |
ACOS(#0) |
| Acosh |
LN(#0+SQRT(#0-1)*SQRT(#0+1)) |
LN(#0+SQRT(#0-1)*SQRT(#0+1)) |
| Asin |
ASIN(#0) |
ASIN(#0) |
| Asinh |
LN(#0+SQRT(POW(#0,
2)+1)) |
LN(#0+SQRT(POWER(#0,2)+1)) |
| Atan |
ATAN(#0) |
ATAN(#0) |
| Atan2 |
ATAN2(#0,
#1) |
ATAN2(#0,
#1) |
| Atanh |
((LN(1+#0)-LN(1-#0))/2) |
((LN(1+#0)-LN(1-#0))/2) |
| Ceiling |
CEIL(#0) |
CEIL(#0) |
| Cos |
COS(#0) |
COS(#0) |
| Cosh |
((EXP(#0)+EXP(#0*(-1)))/2) |
COSH(#0) |
| Degrees |
DEGREES(#0) |
(#0*180/(ASIN(1)*2)) |
| Exp |
EXP(#0) |
EXP(#0) |
| Factorial |
#N/A |
#N/A |
| Floor |
FLOOR(#0) |
FLOOR(#0) |
| Int |
TRUNC(#0,
0) |
TRUNC(#0,
0) |
| Ln |
LN(#0) |
LN(#0) |
| Log |
NUMERIC_LOG(#1,
#0)"/> |
LOG(#1,
#0)/> |
| Log10 |
LOG(#0) |
LOG(10,
#0) |
| Mod |
MOD(#0,
#1) |
MOD(#0,
#1) |
| Power |
POW(#0,
#1) |
POWER(#0,
#1) |
| Quotient |
TRUNC(#0/#1,
0) |
TRUNC(#0/#1,
0) |
| Radians |
RADIANS(#0) |
(#0*ASIN(1)*2/180) |
| Randbetween |
((#1-#0)*RANDOM()+#0) |
#N/A |
| Round |
ROUND(#0) |
ROUND(#0) |
| Round2 |
ROUND(#0,
#1) |
ROUND(#0,
#1) |
| Sin |
SIN(#0) |
SIN(#0) |
| Sinh |
((EXP(#0)
- EXP(#0*(-1)))/2) |
SINH(#0) |
| Sqrt |
SQRT(#0) |
SQRT(#0) |
| Tan |
TAN(#0) |
TAN(#0) |
| Tanh |
((EXP(#0)-EXP(#0*(-1)))/(EXP(#0)+EXP(#0*(-1)))) |
TANH(#0) |
| Trunc |
TRUNC(#0,
0) |
TRUNC(#0) |
| IsNotNull |
#N/A |
#N/A |
| IsNull |
#N/A |
#N/A |
| NullToZero |
COALESCE(#0,
0) |
NVL(#0,
0) |
| ZeroToNull |
NULLIF(#0,
0) |
(CASE
WHEN #0=0 THEN NULL ELSE #0 END) |
| FirstInRange |
first_value(#0)
over(#1) |
first_value(#0)
over(#1) |
| LastInRange |
last_value(#0)
over(#1) |
last_value(#0)
over(#1) |
| MovingAvg |
avg(#0)
over(#1) |
avg(#0)
over(#1) |
| MovingCount |
count(#0)
over(#1) |
count(#0)
over(#1) |
| MovingMax |
max(#0)
over(#1) |
max(#0)
over(#1) |
| MovingMin |
min(#0)
over(#1) |
min(#0)
over(#1) |
| MovingStdev |
stddev(#0)
over(#1) |
stddev(#0)
over(#1) |
| MovingStdevP |
STDDEV_POP(#0)
OVER(#1) |
STDDEV_POP(#0)
OVER(#1) |
| MovingSum |
sum(#0)
over(#1) |
sum(#0)
over(#1) |
| RunningAvg |
avg(#0)
over(#1) |
avg(#0)
over(#1) |
| RunningCount |
count(#0)
over(#1) |
count(#0)
over(#1) |
| RunningMax |
max(#0)
over(#1) |
max(#0)
over(#1) |
| RunningMin |
min(#0)
over(#1) |
min(#0)
over(#1) |
| RunningStdev |
stddev(#0)
over(#1) |
stddev(#0)
over(#1) |
| RunningStdevP |
STDDEV_POP(#0)
OVER(#1) |
STDDEV_POP(#0)
OVER(#1) |
| RunningSum |
sum(#0)
over(#1) |
sum(#0)
over(#1) |
| Rank |
#0#,#<partition
by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0) |
#0#,#<partition
by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0 nulls
last) |
| Correlation |
(((1/SUM(#0-#0+#1-#1+1))*(SUM(#0*#1)-(AVG(#0)*SUM(#1))-(SUM(#0)*AVG(#1))+(AVG(#0)*AVG(#1)*SUM(#0-#0+#1-#1+1))))/(SQRT(SUM(#0*#0)/SUM(#0-#0+1)-((SUM(#0)/SUM(#0-#0+1))*(SUM(#0)/SUM(#0-#0+1))))*SQRT(SUM(#1*#1)/SUM(#1-#1+1)-((SUM(#1)/SUM(#1-#1+1))*(SUM(#1)/SUM(#1-#1+1)))))) |
CORR(#0,
#1) |
| Covariance |
((1/SUM(#0-#0+#1-#1+1))*(SUM(#0*#1)-(AVG(#0)*SUM(#1))-(SUM(#0)*AVG(#1))+(AVG(#0)*AVG(#1)*SUM(#0-#0+#1-#1+1)))) |
COVAR_POP(#0,
#1) |
| Fisher |
(LN((1+#0)/(1-#0))/2) |
(LN((1+#0)/(1-#0))/2) |
| Intercept |
(AVG(#0)-((((SUM(#1-#1+#0-#0+1)*SUM(#1*#0))-(SUM(#1)*SUM(#0)))/((SUM(#1-#1+#0-#0+1)*SUM(#1*#1))-(SUM(#1)*SUM(#1))))*AVG(#1))) |
REGR_INTERCEPT(#0,
#1) |
| InverseFisher |
((EXP(2*#0)-1)/(EXP(2*#0)+1)) |
((EXP(2*#0)-1)/(EXP(2*#0)+1)) |
| Kurtosis |
((((SUM(#0-#0+1)*(SUM(#0-#0+1)+1))/((SUM(#0-#0+1)-1)*(SUM(#0-#0+1)-2)*(SUM(#0-#0+1)-3)))*((SUM(POW(#0,4)))-(4*SUM(POW(#0,3))*AVG(#0))+(6*SUM(POW(#0,2))*POW(AVG(#0),2))-(4*SUM(#0)*POW(AVG(#0),3))+(SUM(#0-#0+1)*POW(AVG(#0),4)))/(POW(STDDEV(#0),4)))-((3*(SUM(#0-#0+1)-1)*(SUM(#0-#0+1)-1))/((SUM(#0-#0+1)-2)*(SUM(#0-#0+1)-3)))) |
((((SUM(#0-#0+1)*(SUM(#0-#0+1)+1))/((SUM(#0-#0+1)-1)*(SUM(#0-#0+1)-2)*(SUM(#0-#0+1)-3)))*((SUM(POWER(#0,4)))-(4*SUM(POWER(#0,3))*AVG(#0))+(6*SUM(POWER(#0,2))*POWER(AVG(#0),2))-(4*SUM(#0)*POWER(AVG(#0),3))+(SUM(#0-#0+1)*POWER(AVG(#0),4)))/(POWER(STDDEV(#0),4)))-((3*(SUM(#0-#0+1)-1)*(SUM(#0-#0+1)-1))/((SUM(#0-#0+1)-2)*(SUM(#0-#0+1)-3)))) |
| Pearson |
(((SUM(#0-#0+#1-#1+1)*SUM(#0*#1))-(SUM(#0)*SUM(#1)))/SQRT(((SUM(#0-#0+#1-#1+1)*SUM(#0*#0))-(SUM(#0)*SUM(#0)))*((SUM(#0-#0+#1-#1+1)*SUM(#1*#1))-(SUM(#1)*SUM(#1))))) |
(((SUM(#0-#0+#1-#1+1)*SUM(#0*#1))-(SUM(#0)*SUM(#1)))/SQRT(((SUM(#0-#0+#1-#1+1)*SUM(#0*#0))-(SUM(#0)*SUM(#0)))*((SUM(#0-#0+#1-#1+1)*SUM(#1*#1))-(SUM(#1)*SUM(#1))))) |
| RSquare |
POW((((SUM(#0-#0+#1-#1+1)*SUM(#0*#1))-(SUM(#0)*SUM(#1)))/SQRT(((SUM(#0-#0+#1-#1+1)*SUM(#0*#0))-(SUM(#0)*SUM(#0)))*((SUM(#0-#0+#1-#1+1)*SUM(#1*#1))-(SUM(#1)*SUM(#1))))),2) |
REGR_R2(#0,
#1) |
| Skew |
((SUM(#0-#0+1)/((SUM(#0-#0+1)-1)*(SUM(#0-#0+1)-2)))*((SUM(POW(#0,3)))+(3*SUM(#0)*POW(AVG(#0),2))-(3*SUM(POW(#0,2))*AVG(#0))-(SUM(#0-#0+1)*POW(AVG(#0),3)))/(POW((CASE
WHEN SUM(#0-#0+1)=1 THEN 0 ELSE
SQRT((SUM(#0*#0)-((SUM(#0)*SUM(#0))/(SUM(#0-#0+1))))/(SUM(#0-#0+1)-1))
END),3))) |
((SUM(#0-#0+1)/((SUM(#0-#0+1)-1)*(SUM(#0-#0+1)-2)))*((SUM(POWER(#0,3)))+(3*SUM(#0)*POWER(AVG(#0),2))-(3*SUM(POWER(#0,2))*AVG(#0))-(SUM(#0-#0+1)*POWER(AVG(#0),3)))/(POWER(STDDEV(#0),3))) |
| Slope |
(((SUM(#1-#1+#0-#0+1)*SUM(#1*#0))-(SUM(#1)*SUM(#0)))/((SUM(#1-#1+#0-#0+1)*SUM(#1*#1))-(SUM(#1)*SUM(#1)))) |
REGR_SLOPE(#0,
#1) |
| Standardize |
((#0-#1)/#2) |
((#0-#1)/#2) |
| SteYX |
SQRT((1/(SUM(#1-#1+#0-#0+1)*(SUM(#1-#1+#0-#0+1)-2)))*((SUM(#1-#1+#0-#0+1)*SUM(#0*#0))-(SUM(#0)*SUM(#0))-((((SUM(#1-#1+#0-#0+1)*SUM(#1*#0))-(SUM(#1)*SUM(#0)))*((SUM(#1-#1+#0-#0+1)*SUM(#1*#0))-(SUM(#1)*SUM(#0))))/((SUM(#1-#1+#0-#0+1)*SUM(#1*#1))-(SUM(#1)*SUM(#1)))))) |
SQRT((1/(SUM(#1-#1+#0-#0+1)*(SUM(#1-#1+#0-#0+1)-2)))*((SUM(#1-#1+#0-#0+1)*SUM(#0*#0))-(SUM(#0)*SUM(#0))-((((SUM(#1-#1+#0-#0+1)*SUM(#1*#0))-(SUM(#1)*SUM(#0)))*((SUM(#1-#1+#0-#0+1)*SUM(#1*#0))-(SUM(#1)*SUM(#0))))/((SUM(#1-#1+#0-#0+1)*SUM(#1*#1))-(SUM(#1)*SUM(#1)))))) |
| Concat |
(#0#<
|| #*#>) |
(#0#<
|| #*#>) |
| ConcatBlank |
(#0#<
|| ' ' || #*#>) |
(#0#<
|| ' ' || #*#>) |
| InitCap |
INITCAP(#0) |
INITCAP(#0) |
| LeftStr |
SUBSTR(#0,
1, #1) |
SUBSTR(#0,
1, #1) |
| Length |
LENGTH(#0) |
LENGTH(#0) |
| Lower |
LOWER(#0) |
LOWER(#0) |
| LTrim |
LTRIM(#0) |
LTRIM(#0) |
| Position |
POSITION(#0
IN #1) |
INSTR(#0,
#1) |
| RightStr |
SUBSTR(#0,
(LENGTH(#0) - #1 + 1)) |
SUBSTR(#0,
(LENGTH(#0) - #1 + 1)) |
| RTrim |
RTRIM(#0) |
RTRIM(#0) |
| SubStr |
SUBSTRING(#0,
#1, #2) |
SUBSTR(#0,
#1, #2) |
| Trim |
TRIM(BOTH
' ' FROM #0) |
TRIM(#0) |
| Upper |
UPPER(#0) |
#N/A |
| Least |
#N/A |
LEAST(#0#<,
#*#>) |
0 comments: