| Generic
Function |
Netezza |
Teradata |
| First |
#N/A |
#N/A |
| GeoMean |
EXP(AVG(LN(#0))) |
EXP(AVG(LN(#0))) |
| Greatest |
#N/A |
#N/A |
| Last |
#N/A |
#N/A |
| 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 (-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_SAMP(#0#<
#*#>) |
| StdevP |
STDDEV_POP(#0#<
#*#>) |
STDDEV_POP(#0#<
#*#>) |
| Var |
VARIANCE(#0#<
#*#>) |
VAR_SAMP(#0#<
#*#>) |
| VarP |
VAR_POP(#0#<
#*#>) |
VAR_POP(#0#<
#*#>) |
| CurrentDate |
CURRENT_DATE |
CURRENT_DATE |
| CurrentDateTime |
NOW() |
CURRENT_TIMESTAMP |
| CurrentTime |
CURRENT_TIME |
CURRENT_TIME |
| DayOfMonth |
EXTRACT(DAY
FROM #0) |
EXTRACT(DAY
FROM #0) |
| DayOfWeek |
TO_NUMBER(TO_CHAR(#0,
'D'),'9') |
#N/A |
| DayOfYear |
EXTRACT(DOY
FROM #0) |
cast(cast(#0
as date) - cast(cast(EXTRACT(YEAR FROM #0) - 1 as char(4))||'-12-31' as date)
as int) |
| Hour |
EXTRACT(HOUR
FROM #0) |
EXTRACT(HOUR
FROM #0) |
| MilliSecond |
EXTRACT(MILLISECOND
FROM #0) |
substring(cast(extract(second
from #0) as char(10)) from 4 for 3) |
| Minute |
EXTRACT(MINUTE
FROM #0) |
EXTRACT(MINUTE
FROM #0) |
| Month |
EXTRACT(MONTH
FROM #0) |
EXTRACT(MONTH
FROM #0) |
| Quarter |
TO_NUMBER(TO_CHAR(#0,
'Q'),'9') |
((EXTRACT(MONTH
FROM #0) - 1)/3) + 1 |
| Second |
EXTRACT(SECOND
FROM #0) |
substring(cast(extract(second
from #0) as char(10)) from 1 for 2) |
| Week |
TO_NUMBER(TO_CHAR(#0,
'WW') ,'99') |
#N/A |
| Year |
EXTRACT(YEAR
FROM #0) |
EXTRACT(YEAR
FROM #0) |
| 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 between #1 and #2) then cast((1.0*(#0- #1)/#3) +1 AS INTEGER) 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 |
Case
when (#1=#2 and #0=#1) then #3 when (#0=#2) then #3 when (#0 between #1 and
#2) then CAST(1.0*(#0- #1)/(#2- #1)*#3+1 AS INTEGER) else 0 end |
| Abs |
ABS(#0) |
ABS(#0) |
| Acos |
ACOS(#0) |
ACOS(#0) |
| Acosh |
LN(#0+SQRT(#0-1)*SQRT(#0+1)) |
ACOSH(#0) |
| Asin |
ASIN(#0) |
ASIN(#0) |
| Asinh |
LN(#0+SQRT(POW(#0,
2)+1)) |
ASINH(#0) |
| Atan |
ATAN(#0) |
ATAN(#0) |
| Atan2 |
ATAN2(#0,
#1) |
ATAN2(#1,
#0) |
| Atanh |
((LN(1+#0)-LN(1-#0))/2) |
ATANH(#0) |
| Ceiling |
CEIL(#0) |
(CASE
WHEN (ABS(#0-CAST(#0 AS INTEGER))=0.0) THEN CAST(#0 AS INTEGER) ELSE
CAST(#0+1 AS INTEGER) END) |
| Cos |
COS(#0) |
#N/A |
| Cosh |
((EXP(#0)+EXP(#0*(-1)))/2) |
COSH(#0) |
| Degrees |
DEGREES(#0) |
(ASIN(1)**(-1)*#0*90) |
| Exp |
EXP(#0) |
EXP(#0) |
| Factorial |
#N/A |
#N/A |
| Floor |
FLOOR(#0) |
(CASE
WHEN (#0>=0) THEN CAST(#0 AS INTEGER) ELSE CAST((#0-1) AS INTEGER) END) |
| Int |
TRUNC(#0,
0) |
CAST(#0
AS INTEGER) |
| Ln |
LN(#0) |
LN(#0) |
| Log |
NUMERIC_LOG(#1,
#0)"/> |
(LN(#0)/LN(#1)) |
| Log10 |
LOG(#0) |
LOG(#0) |
| Mod |
MOD(#0,
#1) |
(#0
MOD #1) |
| Power |
POW(#0,
#1) |
#0
** #1 |
| Quotient |
TRUNC(#0/#1,
0) |
CAST((#0/#1)
AS INTEGER) |
| Radians |
RADIANS(#0) |
(ASIN(1)*#0/90) |
| Randbetween |
((#1-#0)*RANDOM()+#0) |
#N/A |
| Round |
ROUND(#0) |
(CASE
WHEN (ABS(#0-CAST(#0 AS INTEGER))<0.5) THEN CAST(#0 AS INTEGER) ELSE
CAST(#0+1 AS INTEGER) END) |
| Round2 |
ROUND(#0,
#1) |
#N/A |
| Sin |
SIN(#0) |
#N/A |
| 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) |
CAST(#0
AS INTEGER) |
| IsNotNull |
#N/A |
#N/A |
| IsNull |
#N/A |
#N/A |
| NullToZero |
COALESCE(#0,
0) |
ZEROIFNULL(#0) |
| ZeroToNull |
NULLIF(#0,
0) |
NULLIFZERO(#0) |
| FirstInRange |
first_value(#0)
over(#1) |
#N/A |
| LastInRange |
last_value(#0)
over(#1) |
#N/A |
| MovingAvg |
avg(#0)
over(#1) |
avg(#0)
over(#1) |
| MovingCount |
count(#0)
over(#1) |
SUM(#0-#0+1)
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) |
(CASE
WHEN SUM(#0 - #0 +1) OVER(#1)=1 THEN 0 ELSE SQRT((SUM(#0*#0)
OVER(#1)-((SUM(#0) OVER(#1)*SUM(#0) OVER(#1))/(SUM(#0-#0+1)
OVER(#1))))/(SUM(#0-#0+1) OVER(#1)-1)) END) |
| MovingStdevP |
STDDEV_POP(#0)
OVER(#1) |
#N/A |
| MovingSum |
sum(#0)
over(#1) |
sum(#0)
over(#1) |
| RunningAvg |
avg(#0)
over(#1) |
#N/A |
| RunningCount |
count(#0)
over(#1) |
#N/A |
| RunningMax |
max(#0)
over(#1) |
max(#0)
over(#1) |
| RunningMin |
min(#0)
over(#1) |
min(#0)
over(#1) |
| RunningStdev |
stddev(#0)
over(#1) |
#N/A |
| RunningStdevP |
STDDEV_POP(#0)
OVER(#1) |
#N/A |
| RunningSum |
sum(#0)
over(#1) |
#N/A |
| Rank |
#0#,#<partition
by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0) |
#0#,#<partition
by#>#<#, #*#>#|rank () over(#1#2#<,#*#> order by #0) |
| 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)))) |
KURTOSIS(#0#<
#*#>) |
| 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))) |
SKEW(#0#<
#*#>) |
| 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)*SU |
| Concat |
(#0#<
|| #*#>) |
(#0#<
|| #*#>) |
| ConcatBlank |
(#0#<
|| ' ' || #*#>) |
(#0#<
|| ' ' || #*#>) |
| InitCap |
INITCAP(#0) |
(UPPER(SUBSTRING(#0
FROM 1 FOR 1))||LOWER(SUBSTRING(#0 FROM 2))) |
| LeftStr |
SUBSTR(#0,
1, #1) |
SUBSTR(#0,
1, #1) |
| Length |
LENGTH(#0) |
CHAR_LENGTH(#0) |
| Lower |
LOWER(#0) |
LOWER(#0) |
| LTrim |
LTRIM(#0) |
TRIM(LEADING
' ' FROM #0) |
| Position |
POSITION(#0
IN #1) |
INDEX(#0,
#1) |
| RightStr |
SUBSTR(#0,
(LENGTH(#0) - #1 + 1)) |
SUBSTR(#0,
(CHAR_LENGTH(#0) - #1 + 1)) |
| RTrim |
RTRIM(#0) |
TRIM(TRAILING
' ' FROM #0) |
| SubStr |
SUBSTRING(#0,
#1, #2) |
SUBSTRING(#0
FROM #1 FOR #2) |
| Trim |
TRIM(BOTH
' ' FROM #0) |
TRIM(BOTH
' ' FROM #0) |
| Upper |
UPPER(#0) |
#N/A |
| Least |
#N/A |
#N/A |
