MicroStrategy Function Name | Impala 1.x | Impala 2.x |
CoalesceFunction |
| COALESCE(#0, 0) |
StdevPFunction |
| stddev_pop(#0#< #*#>) |
BetweenOperator | #0 BETWEEN #1 and #2 | #0 BETWEEN #1 and #2 |
ProductFunction |
| (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) |
StdevFunction |
| STDDEV(#0#< #*#>) |
VarFunction |
| VARIANCE(#0#< #*#>) |
GeoMeanFunction | EXP(AVG(LN(#0))) | EXP(AVG(LN(#0))) |
NotLikeOperator |
| #0 NOT LIKE '#1' |
IsNullFunction |
| #0 IS NULL |
IsNotNullFunction |
| #0 IS NOT NULL |
NotInOperator | not (#0 in (#1#<, #*#>))#/#0 = #1#<#,#0=#*#>#|#<(#>#0#< and #*#>#<)#>#|not #<(#>#0#< or #*#>#<)#> | not (#0 in (#1#<, #*#>))#/#0 = #1#<#,#0=#*#>#|#<(#>#0#< and #*#>#<)#>#|not #<(#>#0#< or #*#>#<)#> |
GreatestFunction | MAX(#0) | MAX(#0) |
LeastFunction | MIN(#0) | MIN(#0) |
FirstFunction | MIN(#0) | MIN(#0) |
LastFunction | MAX(#0) | MAX(#0) |
IFOperator | (Case when #0 then #1 else #2 end) | (Case when #0 then #1 else #2 end) |
VarPFunction |
| VARIANCE_POP(#0#< #*#>) |
MicroStrategy Function Name | Impala 1.x | Impala 2.x |
DaysBetweenFunction | DATEDIFF(#1,#0) | DATEDIFF(#1,#0) |
YearEndDateFunction |
| DATE_SUB(ADD_MONTHS(#0, (13 - MONTH(#0))), DAY(#0)) |
CurrentDateFunction | TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP())) | TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP())) |
DayOfMonthFunction | DAYOFMONTH(#0) | DAYOFMONTH(#0) |
DayOfWeekFunction |
| DAYOFWEEK(#0) |
DayOfYearFunction |
| DAYOFYEAR(#0) |
WeekFunction | WEEKOFYEAR(#0) | WEEKOFYEAR(#0) |
MonthFunction | MONTH(#0) | MONTH(#0) |
YearFunction | YEAR(#0) | YEAR(#0) |
CurrentDateTimeFunction | FROM_UNIXTIME(UNIX_TIMESTAMP()) | FROM_UNIXTIME(UNIX_TIMESTAMP()) |
CurrentTimeFunction | FROM_UNIXTIME(UNIX_TIMESTAMP()) | FROM_UNIXTIME(UNIX_TIMESTAMP()) |
HourFunction | HOUR(#0) | HOUR(#0) |
MinuteFunction | MINUTE(#0) | MINUTE(#0) |
SecondFunction | SECOND(#0) | SECOND(#0) |
MilliSecondFunction |
| EXTRACT(MILLISECOND FROM #0) |
MonthStartDateFunction |
| DATE_ADD(#0,1-DAYOFMONTH(#0)) |
YearStartDateFunction |
| ADD_MONTHS(date_add(date_sub(#0 ,DAY(#0) ), 1), 1 - Month(#0)) |
AddMonthsFunction |
| ADD_MONTHS(#0, #1) |
MonthsBetweenFunction |
| (12*(YEAR(#1) - YEAR(#0)) + MONTH(#1) - MONTH(#0) - (CASE WHEN DAY(#0) > DAY(#1) THEN 1 ELSE 0 END)) |
AddDaysFunction | DATE_ADD(#0,#1) | DATE_ADD(#0,#1) |
MonthEndDateFunction |
| DATE_ADD((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1)))) |
DateFunction | TO_DATE(#0) | TO_DATE(#0) |
MicroStrategy Function Name | Impala 1.x | Impala 2.x |
RunningSumFunction |
| sum(#0) over(#1) |
RunningAvgFunction |
| avg(#0) over(#1) |
MovingAvgFunction |
| avg(#0) over(#1) |
MovingSumFunction |
| sum(#0) over(#1) |
MovingCountFunction |
| count(#0) over(#1) |
RunningMaxFunction |
| max(#0) over(#1) |
RunningMinFunction |
| min(#0) over(#1) |
RunningCountFunction |
| count(#0) over(#1) |
FirstInRangeFunction |
| first_value(#0) over(#1) |
LastInRangeFunction |
| last_value(#0) over(#1) |
OLAPSumFunction |
| sum(#0#< #*#>) over ([#P] [#O] [#W]) |
OLAPAvgFunction |
| avg(#0#< #*#>) over ([#P] [#O] [#W]) |
OLAPCountFunction |
| count(#0#< #*#>) over ([#P] [#O] [#W]) |
LagFunction |
| lag(#0#<, #*#>) over ([#P] [#O]) |
LeadFunction |
| lead(#0#<, #*#>) over ([#P] [#O]) |
OLAPRankFunction |
| rank() over ([#P] [#O]) |
MicroStrategy Function Name | Impala 1.x | Impala 2.x |
Round2Function | ROUND(#0, #1) | ROUND(#0, #1) |
RankFunction |
| #0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0) |
AbsFunction | ABS(#0) | ABS(#0) |
AcosFunction | ACOS(#0) | ACOS(#0) |
AcoshFunction | LN(#0+SQRT(#0-1)*SQRT(#0+1)) | LN(#0+SQRT(#0-1)*SQRT(#0+1)) |
AsinFunction | ASIN(#0) | ASIN(#0) |
AtanFunction | ATAN(#0) | ATAN(#0) |
AtanhFunction | ((LN(1+#0)-LN(1- #0))/2) | ((LN(1+#0)-LN(1- #0))/2) |
CeilingFunction | CEIL(#0) | CEIL(#0) |
CosFunction | COS(#0) | COS(#0) |
CoshFunction | ((EXP(#0)-EXP(#0*(-1)))/2) | ((EXP(#0)-EXP(#0*(-1)))/2) |
DegreesFunction | DEGREES(#0) | DEGREES(#0) |
ExpFunction | EXP(#0) | EXP(#0) |
FloorFunction | FLOOR(#0) | FLOOR(#0) |
IntFunction |
| FLOOR(#0) |
LnFunction | LN(#0) | LN(#0) |
LogFunction | LOG(#1, #0) | LOG(#1, #0) |
Log10Function | LOG10(#0) | LOG10(#0) |
PowerFunction | POWER(#0, #1) | POWER(#0, #1) |
QuotientFunction | CAST(#0/#1 AS INT) | CAST(#0/#1 AS INT) |
RadiansFunction | RADIANS(#0) | RADIANS(#0) |
RandbetweenFunction |
| ((#1- #0)*RAND()+#0) |
RoundFunction | ROUND(#0) | ROUND(#0) |
SinFunction | SIN(#0) | SIN(#0) |
SinhFunction | ((EXP(#0)+EXP(#0*(-1)))/2) | ((EXP(#0)+EXP(#0*(-1)))/2) |
SqrtFunction | SQRT(#0) | SQRT(#0) |
TanFunction | TAN(#0) | TAN(#0) |
TanhFunction | (-1+(2/(1+EXP(-2*#0)))) | (-1+(2/(1+EXP(-2*#0)))) |
TruncFunction | CAST(#0 AS INT) | CAST(#0 AS INT) |
AsinhFunction | LN(#0+SQRT(POW(#0, 2)+1)) | LN(#0+SQRT(POW(#0, 2)+1)) |
ModFunction | SIGN(#0)*PMOD(ABS(#0), #1) | SIGN(#0)*PMOD(ABS(#0), #1) |
MicroStrategy Function Name | Impala 1.x | Impala 2.x |
StandardizeFunction | case when (#2 > 0) then (#0 - #1)/(#2) else NULL end | case when (#2 > 0) then (#0 - #1)/(#2) else NULL end |
