Generic Function | Netezza | SQL Server 2000 |
First | #N/A | #N/A |
GeoMean | EXP(AVG(LN(#0))) | #N/A |
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) | #N/A |
Stdev | STDDEV(#0#< #*#>) | STDEV(#0#< #*#>) |
StdevP | STDDEV_POP(#0#< #*#>) | STDEVP(#0#< #*#>) |
Var | VARIANCE(#0#< #*#>) | VAR(#0#< #*#>) |
VarP | VAR_POP(#0#< #*#>) | VARP(#0#< #*#>) |
CurrentDate | CURRENT_DATE | GETDATE() |
CurrentDateTime | NOW() | GETDATE() |
CurrentTime | CURRENT_TIME | GETDATE() |
DayOfMonth | EXTRACT(DAY FROM #0) | DATEPART(DAY, #0) |
DayOfWeek | TO_NUMBER(TO_CHAR(#0, 'D'),'9') | DATEPART(WEEKDAY, #0) |
DayOfYear | EXTRACT(DOY FROM #0) | DATEPART(DAYOFYEAR, #0) |
Hour | EXTRACT(HOUR FROM #0) | DATEPART(Hour, #0) |
MilliSecond | EXTRACT(MILLISECOND FROM #0) | DATEPART(Millisecond, #0) |
Minute | EXTRACT(MINUTE FROM #0) | DATEPART(Minute, #0) |
Month | EXTRACT(MONTH FROM #0) | DATEPART(MONTH, #0) |
Quarter | TO_NUMBER(TO_CHAR(#0, 'Q'),'9') | DATEPART(QUARTER, #0) |
Second | EXTRACT(SECOND FROM #0) | DATEPART(Second, #0) |
Week | TO_NUMBER(TO_CHAR(#0, 'WW') ,'99') | DATEPART(WEEK, #0) |
Year | EXTRACT(YEAR FROM #0) | DATEPART(YEAR, #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=#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 | 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 |
Abs | ABS(#0) | ABS(#0) |
Acos | ACOS(#0) | ACOS(#0) |
Acosh | LN(#0+SQRT(#0-1)*SQRT(#0+1)) | #N/A |
Asin | ASIN(#0) | ASIN(#0) |
Asinh | LN(#0+SQRT(POW(#0, 2)+1)) | #N/A |
Atan | ATAN(#0) | ATAN(#0) |
Atan2 | ATAN2(#0, #1) | ATN2(#0, #1) |
Atanh | ((LN(1+#0)-LN(1-#0))/2) | #N/A |
Ceiling | CEIL(#0) | CEILING(#0) |
Cos | COS(#0) | COS(#0) |
Cosh | ((EXP(#0)+EXP(#0*(-1)))/2) | #N/A |
Degrees | DEGREES(#0) | DEGREES(#0) |
Exp | EXP(#0) | EXP(#0) |
Factorial | #N/A | #N/A |
Floor | FLOOR(#0) | FLOOR(#0) |
Int | TRUNC(#0, 0) | #N/A |
Ln | LN(#0) | LOG(#0) |
Log | NUMERIC_LOG(#1, #0)"/> | #N/A |
Log10 | LOG(#0) | LOG10(#0) |
Mod | MOD(#0, #1) | #N/A |
Power | POW(#0, #1) | POWER(#0, #1) |
Quotient | TRUNC(#0/#1, 0) | #N/A |
Radians | RADIANS(#0) | RADIANS(#0) |
Randbetween | ((#1-#0)*RANDOM()+#0) | #N/A |
Round | ROUND(#0) | ROUND(#0, 0) |
Round2 | ROUND(#0, #1) | ROUND(#0, #1) |
Sin | SIN(#0) | SIN(#0) |
Sinh | ((EXP(#0) - EXP(#0*(-1)))/2) | #N/A |
Sqrt | SQRT(#0) | SQRT(#0) |
Tan | TAN(#0) | TAN(#0) |
Tanh | ((EXP(#0)-EXP(#0*(-1)))/(EXP(#0)+EXP(#0*(-1)))) | #N/A |
Trunc | TRUNC(#0, 0) | #N/A |
IsNotNull | #N/A | #N/A |
IsNull | #N/A | #N/A |
NullToZero | COALESCE(#0, 0) | ISNULL(#0, 0) |
ZeroToNull | NULLIF(#0, 0) | NULLIF(#0, 0) |
FirstInRange | first_value(#0) over(#1) | #N/A |
LastInRange | last_value(#0) over(#1) | #N/A |
MovingAvg | avg(#0) over(#1) | #N/A |
MovingCount | count(#0) over(#1) | #N/A |
MovingMax | max(#0) over(#1) | #N/A |
MovingMin | min(#0) over(#1) | #N/A |
MovingStdev | stddev(#0) over(#1) | #N/A |
MovingStdevP | STDDEV_POP(#0) OVER(#1) | #N/A |
MovingSum | sum(#0) over(#1) | #N/A |
RunningAvg | avg(#0) over(#1) | #N/A |
RunningCount | count(#0) over(#1) | #N/A |
RunningMax | max(#0) over(#1) | #N/A |
RunningMin | min(#0) over(#1) | #N/A |
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) | #N/A |
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)))))) | #N/A |
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)))) | #N/A |
Fisher | (LN((1+#0)/(1-#0))/2) | #N/A |
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))) | #N/A |
InverseFisher | ((EXP(2*#0)-1)/(EXP(2*#0)+1)) | #N/A |
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)))) | #N/A |
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))))) | #N/A |
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) | #N/A |
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))) | #N/A |
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)))) | #N/A |
Standardize | ((#0-#1)/#2) | #N/A |
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)))))) | #N/A |
Concat | (#0#< || #*#>) | (#0#< + #*#>) |
ConcatBlank | (#0#< || ' ' || #*#>) | (#0#< + ' ' + #*#>) |
InitCap | INITCAP(#0) | (UPPER(SUBSTRING(#0, 1, 1)) + LOWER(SUBSTRING(#0, 2, 100))) |
LeftStr | SUBSTR(#0, 1, #1) | LEFT(#0, #1) |
Length | LENGTH(#0) | LEN(#0) |
Lower | LOWER(#0) | LOWER(#0) |
LTrim | LTRIM(#0) | LTRIM(#0) |
Position | POSITION(#0 IN #1) | CHARINDEX(#0, #1) |
RightStr | SUBSTR(#0, (LENGTH(#0) - #1 + 1)) | RIGHT(#0, #1) |
RTrim | RTRIM(#0) | RTRIM(#0) |
SubStr | SUBSTRING(#0, #1, #2) | SUBSTRING(#0, #1, #2) |
Trim | TRIM(BOTH ' ' FROM #0) | LTRIM(RTRIM(#0)) |
Upper | UPPER(#0) | #N/A |
Least | #N/A | #N/A |
0 comments: