| 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 |
0 comments: