Explicit
create table ZZEA00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11create table ZZAM00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11 create table ZZAM01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZEA00 pa11
create table ZZEA02 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZAM01 pa11 create table ZZMQ03 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZEA02 pa11
where (pa11.WJXBFS1 <= 5.0)
create table ZZEA04 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ03 pa12
where a11.SUBCAT_ID = pa12.SUBCAT_ID
select pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.WJXBFS1 WJXBFS1,
pa11.WJXBFS2 WJXBFS2
from ZZEA04 pa11,
LU_SUBCATEG a12
where pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZAM00 pa11
where (pa11.WJXBFS1 <= 5.0)
select a11.SUBCAT_ID SUBCAT_ID,
a13.SUBCAT_DESC SUBCAT_DESC,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ01 pa12,
LU_SUBCATEG a13
where a11.SUBCAT_ID = pa12.SUBCAT_ID and
a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT base64(dt_binary)
FROM wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
SELECT c_to_string(dt_binary)
FROM wh2.dt_fact_table1;FunctionSQL PatternAdd(#0#< + #*#>) Average(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) AvgAVG(#0#<#*#>) CountCOUNT(#0#<#*#>) GeoMeanFunctionEXP(AVG(LN(#0))) GreatestFunctionGREATEST(#0#<, #*#>) LeastFunctionLEAST(#0#<, #*#>) MaxMAX(#0#<#*#>) MinMIN(#0#<#*#>) Multiply(#0#< * #*#>) StdevFunctionSTDDEV(#0#< #*#>) SumSUM(#0#<#*#>) VarFunctionVAR(#0#< #*#>) FunctionSQL PatternConcatFunctionCONCAT(#0, #1) LengthFunctionLENGTH(#0)LowerFunctionLOWER(#0) LTrimFunctionLTRIM(#0) PositionFunctionLOCATE(#1, #0) RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2) InitCapFunction(UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) TrimFunctionLTRIM(RTRIM(#0)) RightStrFunctionRIGHT(#0, #1) LeftStrFunctionLEFT(#0, #1) UpperFunctionUPPER(#0) ConcatBlankFunction(#0#< || ' ' || #*#>) FunctionSQL PatternDaysBetweenFunctionDAYS_BETWEEN(#0, #1)YearEndDateFunctionADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0))CurrentDateFunctionNOW()DayOfMonthFunctionDAYOFMONTH(#0)DayOfWeekFunction(CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END)DayOfYearFunctionDAYOFYEAR(#0)WeekFunctionTO_CHAR(#0,'WW')MonthFunctionMONTH(#0)QuarterFunctionTO_CHAR (#0, 'Q')YearFunctionYEAR(#0)CurrentDateTimeFunctionNOW()CurrentTimeFunctionNOW()HourFunctionHOUR(#0)MinuteFunctionMINUTE(#0)SecondFunctionROUND(SECOND(#0))MilliSecondFunctionsubstring(cast(extract(second from #0) as char(10)), 4, 3)MonthStartDateFunctionADD_DAYS(#0,1-DAYOFMONTH(#0))YearStartDateFunctionADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0)))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunction(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))AddDaysFunctionADD_DAYS(#0, #1)MonthEndDateFunctionADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1))))DateFunctionTO_DATE(#0,'YYYY-MM-DD')FunctionSQL PatternLagFunctionlag(#0#<, #*#>) over ([#P] [#O]) LeadFunctionlead(#0#<, #*#>) over ([#P] [#O]) OLAPRankFunctionrank() over ([#P] [#O])FunctionSQL PatternRound2FunctionROUND(#0, #1)RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>Int2FunctionFLOOR(#0)AcosFunctionACOS(#0)AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#1, #0)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEILING(#0)CosFunctionCOS(#0)CoshFunctionCOSH(#0)ExpFunctionEXP(#0)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#1, #0)Log10FunctionLOG(10,#0)PowerFunctionPOWER(#0, #1)QuotientFunction
(CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END)RoundFunctionROUND(#0, 0)SinFunctionSIN(#0)SinhFunctionSINH(#0)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunctionTANH(#0)TruncFunctionCAST(#0 AS INTEGER)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunctionMOD(#0, #1)ItemExampleApplication server versionTomcat 7.0, IIS 7.5, etc.Application server operating systemWindows 2008 R2Operating system bit type64-bitStrategy Web version9.4.1 Hotfix 9Machine physical memory8 GBNumber of CPUs and speedDual core @ 2.4 GhzJava version1.8.0 update 60Java max heap size2048 MOther Java parametersVerbose GC loggingAverage user concurrency on peak times100 | Explicit
create table ZZEA00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11create table ZZAM00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11 create table ZZAM01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZEA00 pa11
create table ZZEA02 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZAM01 pa11 create table ZZMQ03 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZEA02 pa11
where (pa11.WJXBFS1 <= 5.0)
create table ZZEA04 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ03 pa12
where a11.SUBCAT_ID = pa12.SUBCAT_ID
select pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.WJXBFS1 WJXBFS1,
pa11.WJXBFS2 WJXBFS2
from ZZEA04 pa11,
LU_SUBCATEG a12
where pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZAM00 pa11
where (pa11.WJXBFS1 <= 5.0)
select a11.SUBCAT_ID SUBCAT_ID,
a13.SUBCAT_DESC SUBCAT_DESC,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ01 pa12,
LU_SUBCATEG a13
where a11.SUBCAT_ID = pa12.SUBCAT_ID and
a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT base64(dt_binary)
FROM wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
SELECT c_to_string(dt_binary)
FROM wh2.dt_fact_table1;FunctionSQL PatternAdd(#0#< + #*#>) Average(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) AvgAVG(#0#<#*#>) CountCOUNT(#0#<#*#>) GeoMeanFunctionEXP(AVG(LN(#0))) GreatestFunctionGREATEST(#0#<, #*#>) LeastFunctionLEAST(#0#<, #*#>) MaxMAX(#0#<#*#>) MinMIN(#0#<#*#>) Multiply(#0#< * #*#>) StdevFunctionSTDDEV(#0#< #*#>) SumSUM(#0#<#*#>) VarFunctionVAR(#0#< #*#>) FunctionSQL PatternConcatFunctionCONCAT(#0, #1) LengthFunctionLENGTH(#0)LowerFunctionLOWER(#0) LTrimFunctionLTRIM(#0) PositionFunctionLOCATE(#1, #0) RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2) InitCapFunction(UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) TrimFunctionLTRIM(RTRIM(#0)) RightStrFunctionRIGHT(#0, #1) LeftStrFunctionLEFT(#0, #1) UpperFunctionUPPER(#0) ConcatBlankFunction(#0#< || ' ' || #*#>) FunctionSQL PatternDaysBetweenFunctionDAYS_BETWEEN(#0, #1)YearEndDateFunctionADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0))CurrentDateFunctionNOW()DayOfMonthFunctionDAYOFMONTH(#0)DayOfWeekFunction(CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END)DayOfYearFunctionDAYOFYEAR(#0)WeekFunctionTO_CHAR(#0,'WW')MonthFunctionMONTH(#0)QuarterFunctionTO_CHAR (#0, 'Q')YearFunctionYEAR(#0)CurrentDateTimeFunctionNOW()CurrentTimeFunctionNOW()HourFunctionHOUR(#0)MinuteFunctionMINUTE(#0)SecondFunctionROUND(SECOND(#0))MilliSecondFunctionsubstring(cast(extract(second from #0) as char(10)), 4, 3)MonthStartDateFunctionADD_DAYS(#0,1-DAYOFMONTH(#0))YearStartDateFunctionADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0)))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunction(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))AddDaysFunctionADD_DAYS(#0, #1)MonthEndDateFunctionADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1))))DateFunctionTO_DATE(#0,'YYYY-MM-DD')FunctionSQL PatternLagFunctionlag(#0#<, #*#>) over ([#P] [#O]) LeadFunctionlead(#0#<, #*#>) over ([#P] [#O]) OLAPRankFunctionrank() over ([#P] [#O])FunctionSQL PatternRound2FunctionROUND(#0, #1)RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>Int2FunctionFLOOR(#0)AcosFunctionACOS(#0)AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#1, #0)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEILING(#0)CosFunctionCOS(#0)CoshFunctionCOSH(#0)ExpFunctionEXP(#0)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#1, #0)Log10FunctionLOG(10,#0)PowerFunctionPOWER(#0, #1)QuotientFunction
(CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END)RoundFunctionROUND(#0, 0)SinFunctionSIN(#0)SinhFunctionSINH(#0)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunctionTANH(#0)TruncFunctionCAST(#0 AS INTEGER)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunctionMOD(#0, #1)ItemExampleApplication server versionTomcat 7.0, IIS 7.5, etc.Application server operating systemWindows 2008 R2Operating system bit type64-bitStrategy Web version9.4.1 Hotfix 9Machine physical memory8 GBNumber of CPUs and speedDual core @ 2.4 GhzJava version1.8.0 update 60Java max heap size2048 MOther Java parametersVerbose GC loggingAverage user concurrency on peak times100 |
Explicit
create table ZZEA00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11create table ZZAM00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11 create table ZZAM01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZEA00 pa11
create table ZZEA02 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZAM01 pa11 create table ZZMQ03 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZEA02 pa11
where (pa11.WJXBFS1 <= 5.0)
create table ZZEA04 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ03 pa12
where a11.SUBCAT_ID = pa12.SUBCAT_ID
select pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.WJXBFS1 WJXBFS1,
pa11.WJXBFS2 WJXBFS2
from ZZEA04 pa11,
LU_SUBCATEG a12
where pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZAM00 pa11
where (pa11.WJXBFS1 <= 5.0)
select a11.SUBCAT_ID SUBCAT_ID,
a13.SUBCAT_DESC SUBCAT_DESC,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ01 pa12,
LU_SUBCATEG a13
where a11.SUBCAT_ID = pa12.SUBCAT_ID and
a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT base64(dt_binary)
FROM wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
SELECT c_to_string(dt_binary)
FROM wh2.dt_fact_table1;FunctionSQL PatternAdd(#0#< + #*#>) Average(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) AvgAVG(#0#<#*#>) CountCOUNT(#0#<#*#>) GeoMeanFunctionEXP(AVG(LN(#0))) GreatestFunctionGREATEST(#0#<, #*#>) LeastFunctionLEAST(#0#<, #*#>) MaxMAX(#0#<#*#>) MinMIN(#0#<#*#>) Multiply(#0#< * #*#>) StdevFunctionSTDDEV(#0#< #*#>) SumSUM(#0#<#*#>) VarFunctionVAR(#0#< #*#>) FunctionSQL PatternConcatFunctionCONCAT(#0, #1) LengthFunctionLENGTH(#0)LowerFunctionLOWER(#0) LTrimFunctionLTRIM(#0) PositionFunctionLOCATE(#1, #0) RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2) InitCapFunction(UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) TrimFunctionLTRIM(RTRIM(#0)) RightStrFunctionRIGHT(#0, #1) LeftStrFunctionLEFT(#0, #1) UpperFunctionUPPER(#0) ConcatBlankFunction(#0#< || ' ' || #*#>) FunctionSQL PatternDaysBetweenFunctionDAYS_BETWEEN(#0, #1)YearEndDateFunctionADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0))CurrentDateFunctionNOW()DayOfMonthFunctionDAYOFMONTH(#0)DayOfWeekFunction(CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END)DayOfYearFunctionDAYOFYEAR(#0)WeekFunctionTO_CHAR(#0,'WW')MonthFunctionMONTH(#0)QuarterFunctionTO_CHAR (#0, 'Q')YearFunctionYEAR(#0)CurrentDateTimeFunctionNOW()CurrentTimeFunctionNOW()HourFunctionHOUR(#0)MinuteFunctionMINUTE(#0)SecondFunctionROUND(SECOND(#0))MilliSecondFunctionsubstring(cast(extract(second from #0) as char(10)), 4, 3)MonthStartDateFunctionADD_DAYS(#0,1-DAYOFMONTH(#0))YearStartDateFunctionADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0)))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunction(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))AddDaysFunctionADD_DAYS(#0, #1)MonthEndDateFunctionADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1))))DateFunctionTO_DATE(#0,'YYYY-MM-DD')FunctionSQL PatternLagFunctionlag(#0#<, #*#>) over ([#P] [#O]) LeadFunctionlead(#0#<, #*#>) over ([#P] [#O]) OLAPRankFunctionrank() over ([#P] [#O])FunctionSQL PatternRound2FunctionROUND(#0, #1)RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>Int2FunctionFLOOR(#0)AcosFunctionACOS(#0)AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#1, #0)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEILING(#0)CosFunctionCOS(#0)CoshFunctionCOSH(#0)ExpFunctionEXP(#0)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#1, #0)Log10FunctionLOG(10,#0)PowerFunctionPOWER(#0, #1)QuotientFunction
(CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END)RoundFunctionROUND(#0, 0)SinFunctionSIN(#0)SinhFunctionSINH(#0)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunctionTANH(#0)TruncFunctionCAST(#0 AS INTEGER)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunctionMOD(#0, #1)ItemExampleApplication server versionTomcat 7.0, IIS 7.5, etc.Application server operating systemWindows 2008 R2Operating system bit type64-bitStrategy Web version9.4.1 Hotfix 9Machine physical memory8 GBNumber of CPUs and speedDual core @ 2.4 GhzJava version1.8.0 update 60Java max heap size2048 MOther Java parametersVerbose GC loggingAverage user concurrency on peak times100 | Explicit
create table ZZEA00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11create table ZZAM00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11 create table ZZAM01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZEA00 pa11
create table ZZEA02 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZAM01 pa11 create table ZZMQ03 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZEA02 pa11
where (pa11.WJXBFS1 <= 5.0)
create table ZZEA04 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ03 pa12
where a11.SUBCAT_ID = pa12.SUBCAT_ID
select pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.WJXBFS1 WJXBFS1,
pa11.WJXBFS2 WJXBFS2
from ZZEA04 pa11,
LU_SUBCATEG a12
where pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZAM00 pa11
where (pa11.WJXBFS1 <= 5.0)
select a11.SUBCAT_ID SUBCAT_ID,
a13.SUBCAT_DESC SUBCAT_DESC,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ01 pa12,
LU_SUBCATEG a13
where a11.SUBCAT_ID = pa12.SUBCAT_ID and
a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT base64(dt_binary)
FROM wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
SELECT c_to_string(dt_binary)
FROM wh2.dt_fact_table1;FunctionSQL PatternAdd(#0#< + #*#>) Average(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) AvgAVG(#0#<#*#>) CountCOUNT(#0#<#*#>) GeoMeanFunctionEXP(AVG(LN(#0))) GreatestFunctionGREATEST(#0#<, #*#>) LeastFunctionLEAST(#0#<, #*#>) MaxMAX(#0#<#*#>) MinMIN(#0#<#*#>) Multiply(#0#< * #*#>) StdevFunctionSTDDEV(#0#< #*#>) SumSUM(#0#<#*#>) VarFunctionVAR(#0#< #*#>) FunctionSQL PatternConcatFunctionCONCAT(#0, #1) LengthFunctionLENGTH(#0)LowerFunctionLOWER(#0) LTrimFunctionLTRIM(#0) PositionFunctionLOCATE(#1, #0) RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2) InitCapFunction(UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) TrimFunctionLTRIM(RTRIM(#0)) RightStrFunctionRIGHT(#0, #1) LeftStrFunctionLEFT(#0, #1) UpperFunctionUPPER(#0) ConcatBlankFunction(#0#< || ' ' || #*#>) FunctionSQL PatternDaysBetweenFunctionDAYS_BETWEEN(#0, #1)YearEndDateFunctionADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0))CurrentDateFunctionNOW()DayOfMonthFunctionDAYOFMONTH(#0)DayOfWeekFunction(CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END)DayOfYearFunctionDAYOFYEAR(#0)WeekFunctionTO_CHAR(#0,'WW')MonthFunctionMONTH(#0)QuarterFunctionTO_CHAR (#0, 'Q')YearFunctionYEAR(#0)CurrentDateTimeFunctionNOW()CurrentTimeFunctionNOW()HourFunctionHOUR(#0)MinuteFunctionMINUTE(#0)SecondFunctionROUND(SECOND(#0))MilliSecondFunctionsubstring(cast(extract(second from #0) as char(10)), 4, 3)MonthStartDateFunctionADD_DAYS(#0,1-DAYOFMONTH(#0))YearStartDateFunctionADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0)))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunction(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))AddDaysFunctionADD_DAYS(#0, #1)MonthEndDateFunctionADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1))))DateFunctionTO_DATE(#0,'YYYY-MM-DD')FunctionSQL PatternLagFunctionlag(#0#<, #*#>) over ([#P] [#O]) LeadFunctionlead(#0#<, #*#>) over ([#P] [#O]) OLAPRankFunctionrank() over ([#P] [#O])FunctionSQL PatternRound2FunctionROUND(#0, #1)RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>Int2FunctionFLOOR(#0)AcosFunctionACOS(#0)AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#1, #0)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEILING(#0)CosFunctionCOS(#0)CoshFunctionCOSH(#0)ExpFunctionEXP(#0)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#1, #0)Log10FunctionLOG(10,#0)PowerFunctionPOWER(#0, #1)QuotientFunction
(CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END)RoundFunctionROUND(#0, 0)SinFunctionSIN(#0)SinhFunctionSINH(#0)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunctionTANH(#0)TruncFunctionCAST(#0 AS INTEGER)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunctionMOD(#0, #1)ItemExampleApplication server versionTomcat 7.0, IIS 7.5, etc.Application server operating systemWindows 2008 R2Operating system bit type64-bitStrategy Web version9.4.1 Hotfix 9Machine physical memory8 GBNumber of CPUs and speedDual core @ 2.4 GhzJava version1.8.0 update 60Java max heap size2048 MOther Java parametersVerbose GC loggingAverage user concurrency on peak times100 |
Explicit
create table ZZEA00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11create table ZZAM00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11 create table ZZAM01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZEA00 pa11
create table ZZEA02 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZAM01 pa11 create table ZZMQ03 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZEA02 pa11
where (pa11.WJXBFS1 <= 5.0)
create table ZZEA04 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ03 pa12
where a11.SUBCAT_ID = pa12.SUBCAT_ID
select pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.WJXBFS1 WJXBFS1,
pa11.WJXBFS2 WJXBFS2
from ZZEA04 pa11,
LU_SUBCATEG a12
where pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZAM00 pa11
where (pa11.WJXBFS1 <= 5.0)
select a11.SUBCAT_ID SUBCAT_ID,
a13.SUBCAT_DESC SUBCAT_DESC,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ01 pa12,
LU_SUBCATEG a13
where a11.SUBCAT_ID = pa12.SUBCAT_ID and
a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT base64(dt_binary)
FROM wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
SELECT c_to_string(dt_binary)
FROM wh2.dt_fact_table1;FunctionSQL PatternAdd(#0#< + #*#>) Average(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) AvgAVG(#0#<#*#>) CountCOUNT(#0#<#*#>) GeoMeanFunctionEXP(AVG(LN(#0))) GreatestFunctionGREATEST(#0#<, #*#>) LeastFunctionLEAST(#0#<, #*#>) MaxMAX(#0#<#*#>) MinMIN(#0#<#*#>) Multiply(#0#< * #*#>) StdevFunctionSTDDEV(#0#< #*#>) SumSUM(#0#<#*#>) VarFunctionVAR(#0#< #*#>) FunctionSQL PatternConcatFunctionCONCAT(#0, #1) LengthFunctionLENGTH(#0)LowerFunctionLOWER(#0) LTrimFunctionLTRIM(#0) PositionFunctionLOCATE(#1, #0) RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2) InitCapFunction(UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) TrimFunctionLTRIM(RTRIM(#0)) RightStrFunctionRIGHT(#0, #1) LeftStrFunctionLEFT(#0, #1) UpperFunctionUPPER(#0) ConcatBlankFunction(#0#< || ' ' || #*#>) FunctionSQL PatternDaysBetweenFunctionDAYS_BETWEEN(#0, #1)YearEndDateFunctionADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0))CurrentDateFunctionNOW()DayOfMonthFunctionDAYOFMONTH(#0)DayOfWeekFunction(CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END)DayOfYearFunctionDAYOFYEAR(#0)WeekFunctionTO_CHAR(#0,'WW')MonthFunctionMONTH(#0)QuarterFunctionTO_CHAR (#0, 'Q')YearFunctionYEAR(#0)CurrentDateTimeFunctionNOW()CurrentTimeFunctionNOW()HourFunctionHOUR(#0)MinuteFunctionMINUTE(#0)SecondFunctionROUND(SECOND(#0))MilliSecondFunctionsubstring(cast(extract(second from #0) as char(10)), 4, 3)MonthStartDateFunctionADD_DAYS(#0,1-DAYOFMONTH(#0))YearStartDateFunctionADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0)))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunction(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))AddDaysFunctionADD_DAYS(#0, #1)MonthEndDateFunctionADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1))))DateFunctionTO_DATE(#0,'YYYY-MM-DD')FunctionSQL PatternLagFunctionlag(#0#<, #*#>) over ([#P] [#O]) LeadFunctionlead(#0#<, #*#>) over ([#P] [#O]) OLAPRankFunctionrank() over ([#P] [#O])FunctionSQL PatternRound2FunctionROUND(#0, #1)RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>Int2FunctionFLOOR(#0)AcosFunctionACOS(#0)AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#1, #0)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEILING(#0)CosFunctionCOS(#0)CoshFunctionCOSH(#0)ExpFunctionEXP(#0)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#1, #0)Log10FunctionLOG(10,#0)PowerFunctionPOWER(#0, #1)QuotientFunction
(CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END)RoundFunctionROUND(#0, 0)SinFunctionSIN(#0)SinhFunctionSINH(#0)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunctionTANH(#0)TruncFunctionCAST(#0 AS INTEGER)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunctionMOD(#0, #1)ItemExampleApplication server versionTomcat 7.0, IIS 7.5, etc.Application server operating systemWindows 2008 R2Operating system bit type64-bitStrategy Web version9.4.1 Hotfix 9Machine physical memory8 GBNumber of CPUs and speedDual core @ 2.4 GhzJava version1.8.0 update 60Java max heap size2048 MOther Java parametersVerbose GC loggingAverage user concurrency on peak times100 | Explicit
create table ZZEA00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11create table ZZAM00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11 create table ZZAM01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZEA00 pa11
create table ZZEA02 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZAM01 pa11 create table ZZMQ03 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZEA02 pa11
where (pa11.WJXBFS1 <= 5.0)
create table ZZEA04 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ03 pa12
where a11.SUBCAT_ID = pa12.SUBCAT_ID
select pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.WJXBFS1 WJXBFS1,
pa11.WJXBFS2 WJXBFS2
from ZZEA04 pa11,
LU_SUBCATEG a12
where pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZAM00 pa11
where (pa11.WJXBFS1 <= 5.0)
select a11.SUBCAT_ID SUBCAT_ID,
a13.SUBCAT_DESC SUBCAT_DESC,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ01 pa12,
LU_SUBCATEG a13
where a11.SUBCAT_ID = pa12.SUBCAT_ID and
a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT base64(dt_binary)
FROM wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
SELECT c_to_string(dt_binary)
FROM wh2.dt_fact_table1;FunctionSQL PatternAdd(#0#< + #*#>) Average(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) AvgAVG(#0#<#*#>) CountCOUNT(#0#<#*#>) GeoMeanFunctionEXP(AVG(LN(#0))) GreatestFunctionGREATEST(#0#<, #*#>) LeastFunctionLEAST(#0#<, #*#>) MaxMAX(#0#<#*#>) MinMIN(#0#<#*#>) Multiply(#0#< * #*#>) StdevFunctionSTDDEV(#0#< #*#>) SumSUM(#0#<#*#>) VarFunctionVAR(#0#< #*#>) FunctionSQL PatternConcatFunctionCONCAT(#0, #1) LengthFunctionLENGTH(#0)LowerFunctionLOWER(#0) LTrimFunctionLTRIM(#0) PositionFunctionLOCATE(#1, #0) RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2) InitCapFunction(UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) TrimFunctionLTRIM(RTRIM(#0)) RightStrFunctionRIGHT(#0, #1) LeftStrFunctionLEFT(#0, #1) UpperFunctionUPPER(#0) ConcatBlankFunction(#0#< || ' ' || #*#>) FunctionSQL PatternDaysBetweenFunctionDAYS_BETWEEN(#0, #1)YearEndDateFunctionADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0))CurrentDateFunctionNOW()DayOfMonthFunctionDAYOFMONTH(#0)DayOfWeekFunction(CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END)DayOfYearFunctionDAYOFYEAR(#0)WeekFunctionTO_CHAR(#0,'WW')MonthFunctionMONTH(#0)QuarterFunctionTO_CHAR (#0, 'Q')YearFunctionYEAR(#0)CurrentDateTimeFunctionNOW()CurrentTimeFunctionNOW()HourFunctionHOUR(#0)MinuteFunctionMINUTE(#0)SecondFunctionROUND(SECOND(#0))MilliSecondFunctionsubstring(cast(extract(second from #0) as char(10)), 4, 3)MonthStartDateFunctionADD_DAYS(#0,1-DAYOFMONTH(#0))YearStartDateFunctionADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0)))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunction(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))AddDaysFunctionADD_DAYS(#0, #1)MonthEndDateFunctionADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1))))DateFunctionTO_DATE(#0,'YYYY-MM-DD')FunctionSQL PatternLagFunctionlag(#0#<, #*#>) over ([#P] [#O]) LeadFunctionlead(#0#<, #*#>) over ([#P] [#O]) OLAPRankFunctionrank() over ([#P] [#O])FunctionSQL PatternRound2FunctionROUND(#0, #1)RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>Int2FunctionFLOOR(#0)AcosFunctionACOS(#0)AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#1, #0)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEILING(#0)CosFunctionCOS(#0)CoshFunctionCOSH(#0)ExpFunctionEXP(#0)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#1, #0)Log10FunctionLOG(10,#0)PowerFunctionPOWER(#0, #1)QuotientFunction
(CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END)RoundFunctionROUND(#0, 0)SinFunctionSIN(#0)SinhFunctionSINH(#0)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunctionTANH(#0)TruncFunctionCAST(#0 AS INTEGER)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunctionMOD(#0, #1)ItemExampleApplication server versionTomcat 7.0, IIS 7.5, etc.Application server operating systemWindows 2008 R2Operating system bit type64-bitStrategy Web version9.4.1 Hotfix 9Machine physical memory8 GBNumber of CPUs and speedDual core @ 2.4 GhzJava version1.8.0 update 60Java max heap size2048 MOther Java parametersVerbose GC loggingAverage user concurrency on peak times100 |
Explicit
create table ZZEA00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11create table ZZAM00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11 create table ZZAM01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZEA00 pa11
create table ZZEA02 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZAM01 pa11 create table ZZMQ03 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZEA02 pa11
where (pa11.WJXBFS1 <= 5.0)
create table ZZEA04 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ03 pa12
where a11.SUBCAT_ID = pa12.SUBCAT_ID
select pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.WJXBFS1 WJXBFS1,
pa11.WJXBFS2 WJXBFS2
from ZZEA04 pa11,
LU_SUBCATEG a12
where pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZAM00 pa11
where (pa11.WJXBFS1 <= 5.0)
select a11.SUBCAT_ID SUBCAT_ID,
a13.SUBCAT_DESC SUBCAT_DESC,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ01 pa12,
LU_SUBCATEG a13
where a11.SUBCAT_ID = pa12.SUBCAT_ID and
a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT base64(dt_binary)
FROM wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
SELECT c_to_string(dt_binary)
FROM wh2.dt_fact_table1;FunctionSQL PatternAdd(#0#< + #*#>) Average(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) AvgAVG(#0#<#*#>) CountCOUNT(#0#<#*#>) GeoMeanFunctionEXP(AVG(LN(#0))) GreatestFunctionGREATEST(#0#<, #*#>) LeastFunctionLEAST(#0#<, #*#>) MaxMAX(#0#<#*#>) MinMIN(#0#<#*#>) Multiply(#0#< * #*#>) StdevFunctionSTDDEV(#0#< #*#>) SumSUM(#0#<#*#>) VarFunctionVAR(#0#< #*#>) FunctionSQL PatternConcatFunctionCONCAT(#0, #1) LengthFunctionLENGTH(#0)LowerFunctionLOWER(#0) LTrimFunctionLTRIM(#0) PositionFunctionLOCATE(#1, #0) RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2) InitCapFunction(UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) TrimFunctionLTRIM(RTRIM(#0)) RightStrFunctionRIGHT(#0, #1) LeftStrFunctionLEFT(#0, #1) UpperFunctionUPPER(#0) ConcatBlankFunction(#0#< || ' ' || #*#>) FunctionSQL PatternDaysBetweenFunctionDAYS_BETWEEN(#0, #1)YearEndDateFunctionADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0))CurrentDateFunctionNOW()DayOfMonthFunctionDAYOFMONTH(#0)DayOfWeekFunction(CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END)DayOfYearFunctionDAYOFYEAR(#0)WeekFunctionTO_CHAR(#0,'WW')MonthFunctionMONTH(#0)QuarterFunctionTO_CHAR (#0, 'Q')YearFunctionYEAR(#0)CurrentDateTimeFunctionNOW()CurrentTimeFunctionNOW()HourFunctionHOUR(#0)MinuteFunctionMINUTE(#0)SecondFunctionROUND(SECOND(#0))MilliSecondFunctionsubstring(cast(extract(second from #0) as char(10)), 4, 3)MonthStartDateFunctionADD_DAYS(#0,1-DAYOFMONTH(#0))YearStartDateFunctionADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0)))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunction(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))AddDaysFunctionADD_DAYS(#0, #1)MonthEndDateFunctionADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1))))DateFunctionTO_DATE(#0,'YYYY-MM-DD')FunctionSQL PatternLagFunctionlag(#0#<, #*#>) over ([#P] [#O]) LeadFunctionlead(#0#<, #*#>) over ([#P] [#O]) OLAPRankFunctionrank() over ([#P] [#O])FunctionSQL PatternRound2FunctionROUND(#0, #1)RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>Int2FunctionFLOOR(#0)AcosFunctionACOS(#0)AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#1, #0)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEILING(#0)CosFunctionCOS(#0)CoshFunctionCOSH(#0)ExpFunctionEXP(#0)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#1, #0)Log10FunctionLOG(10,#0)PowerFunctionPOWER(#0, #1)QuotientFunction
(CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END)RoundFunctionROUND(#0, 0)SinFunctionSIN(#0)SinhFunctionSINH(#0)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunctionTANH(#0)TruncFunctionCAST(#0 AS INTEGER)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunctionMOD(#0, #1)ItemExampleApplication server versionTomcat 7.0, IIS 7.5, etc.Application server operating systemWindows 2008 R2Operating system bit type64-bitStrategy Web version9.4.1 Hotfix 9Machine physical memory8 GBNumber of CPUs and speedDual core @ 2.4 GhzJava version1.8.0 update 60Java max heap size2048 MOther Java parametersVerbose GC loggingAverage user concurrency on peak times100 | Explicit
create table ZZEA00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11create table ZZAM00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11 create table ZZAM01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZEA00 pa11
create table ZZEA02 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZAM01 pa11 create table ZZMQ03 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZEA02 pa11
where (pa11.WJXBFS1 <= 5.0)
create table ZZEA04 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ03 pa12
where a11.SUBCAT_ID = pa12.SUBCAT_ID
select pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.WJXBFS1 WJXBFS1,
pa11.WJXBFS2 WJXBFS2
from ZZEA04 pa11,
LU_SUBCATEG a12
where pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZAM00 pa11
where (pa11.WJXBFS1 <= 5.0)
select a11.SUBCAT_ID SUBCAT_ID,
a13.SUBCAT_DESC SUBCAT_DESC,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ01 pa12,
LU_SUBCATEG a13
where a11.SUBCAT_ID = pa12.SUBCAT_ID and
a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT base64(dt_binary)
FROM wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
SELECT c_to_string(dt_binary)
FROM wh2.dt_fact_table1;FunctionSQL PatternAdd(#0#< + #*#>) Average(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) AvgAVG(#0#<#*#>) CountCOUNT(#0#<#*#>) GeoMeanFunctionEXP(AVG(LN(#0))) GreatestFunctionGREATEST(#0#<, #*#>) LeastFunctionLEAST(#0#<, #*#>) MaxMAX(#0#<#*#>) MinMIN(#0#<#*#>) Multiply(#0#< * #*#>) StdevFunctionSTDDEV(#0#< #*#>) SumSUM(#0#<#*#>) VarFunctionVAR(#0#< #*#>) FunctionSQL PatternConcatFunctionCONCAT(#0, #1) LengthFunctionLENGTH(#0)LowerFunctionLOWER(#0) LTrimFunctionLTRIM(#0) PositionFunctionLOCATE(#1, #0) RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2) InitCapFunction(UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) TrimFunctionLTRIM(RTRIM(#0)) RightStrFunctionRIGHT(#0, #1) LeftStrFunctionLEFT(#0, #1) UpperFunctionUPPER(#0) ConcatBlankFunction(#0#< || ' ' || #*#>) FunctionSQL PatternDaysBetweenFunctionDAYS_BETWEEN(#0, #1)YearEndDateFunctionADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0))CurrentDateFunctionNOW()DayOfMonthFunctionDAYOFMONTH(#0)DayOfWeekFunction(CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END)DayOfYearFunctionDAYOFYEAR(#0)WeekFunctionTO_CHAR(#0,'WW')MonthFunctionMONTH(#0)QuarterFunctionTO_CHAR (#0, 'Q')YearFunctionYEAR(#0)CurrentDateTimeFunctionNOW()CurrentTimeFunctionNOW()HourFunctionHOUR(#0)MinuteFunctionMINUTE(#0)SecondFunctionROUND(SECOND(#0))MilliSecondFunctionsubstring(cast(extract(second from #0) as char(10)), 4, 3)MonthStartDateFunctionADD_DAYS(#0,1-DAYOFMONTH(#0))YearStartDateFunctionADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0)))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunction(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))AddDaysFunctionADD_DAYS(#0, #1)MonthEndDateFunctionADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1))))DateFunctionTO_DATE(#0,'YYYY-MM-DD')FunctionSQL PatternLagFunctionlag(#0#<, #*#>) over ([#P] [#O]) LeadFunctionlead(#0#<, #*#>) over ([#P] [#O]) OLAPRankFunctionrank() over ([#P] [#O])FunctionSQL PatternRound2FunctionROUND(#0, #1)RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>Int2FunctionFLOOR(#0)AcosFunctionACOS(#0)AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#1, #0)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEILING(#0)CosFunctionCOS(#0)CoshFunctionCOSH(#0)ExpFunctionEXP(#0)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#1, #0)Log10FunctionLOG(10,#0)PowerFunctionPOWER(#0, #1)QuotientFunction
(CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END)RoundFunctionROUND(#0, 0)SinFunctionSIN(#0)SinhFunctionSINH(#0)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunctionTANH(#0)TruncFunctionCAST(#0 AS INTEGER)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunctionMOD(#0, #1)ItemExampleApplication server versionTomcat 7.0, IIS 7.5, etc.Application server operating systemWindows 2008 R2Operating system bit type64-bitStrategy Web version9.4.1 Hotfix 9Machine physical memory8 GBNumber of CPUs and speedDual core @ 2.4 GhzJava version1.8.0 update 60Java max heap size2048 MOther Java parametersVerbose GC loggingAverage user concurrency on peak times100 |
Explicit
create table ZZEA00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11create table ZZAM00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11 create table ZZAM01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZEA00 pa11
create table ZZEA02 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZAM01 pa11 create table ZZMQ03 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZEA02 pa11
where (pa11.WJXBFS1 <= 5.0)
create table ZZEA04 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ03 pa12
where a11.SUBCAT_ID = pa12.SUBCAT_ID
select pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.WJXBFS1 WJXBFS1,
pa11.WJXBFS2 WJXBFS2
from ZZEA04 pa11,
LU_SUBCATEG a12
where pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZAM00 pa11
where (pa11.WJXBFS1 <= 5.0)
select a11.SUBCAT_ID SUBCAT_ID,
a13.SUBCAT_DESC SUBCAT_DESC,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ01 pa12,
LU_SUBCATEG a13
where a11.SUBCAT_ID = pa12.SUBCAT_ID and
a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT base64(dt_binary)
FROM wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
SELECT c_to_string(dt_binary)
FROM wh2.dt_fact_table1;FunctionSQL PatternAdd(#0#< + #*#>) Average(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) AvgAVG(#0#<#*#>) CountCOUNT(#0#<#*#>) GeoMeanFunctionEXP(AVG(LN(#0))) GreatestFunctionGREATEST(#0#<, #*#>) LeastFunctionLEAST(#0#<, #*#>) MaxMAX(#0#<#*#>) MinMIN(#0#<#*#>) Multiply(#0#< * #*#>) StdevFunctionSTDDEV(#0#< #*#>) SumSUM(#0#<#*#>) VarFunctionVAR(#0#< #*#>) FunctionSQL PatternConcatFunctionCONCAT(#0, #1) LengthFunctionLENGTH(#0)LowerFunctionLOWER(#0) LTrimFunctionLTRIM(#0) PositionFunctionLOCATE(#1, #0) RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2) InitCapFunction(UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) TrimFunctionLTRIM(RTRIM(#0)) RightStrFunctionRIGHT(#0, #1) LeftStrFunctionLEFT(#0, #1) UpperFunctionUPPER(#0) ConcatBlankFunction(#0#< || ' ' || #*#>) FunctionSQL PatternDaysBetweenFunctionDAYS_BETWEEN(#0, #1)YearEndDateFunctionADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0))CurrentDateFunctionNOW()DayOfMonthFunctionDAYOFMONTH(#0)DayOfWeekFunction(CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END)DayOfYearFunctionDAYOFYEAR(#0)WeekFunctionTO_CHAR(#0,'WW')MonthFunctionMONTH(#0)QuarterFunctionTO_CHAR (#0, 'Q')YearFunctionYEAR(#0)CurrentDateTimeFunctionNOW()CurrentTimeFunctionNOW()HourFunctionHOUR(#0)MinuteFunctionMINUTE(#0)SecondFunctionROUND(SECOND(#0))MilliSecondFunctionsubstring(cast(extract(second from #0) as char(10)), 4, 3)MonthStartDateFunctionADD_DAYS(#0,1-DAYOFMONTH(#0))YearStartDateFunctionADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0)))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunction(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))AddDaysFunctionADD_DAYS(#0, #1)MonthEndDateFunctionADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1))))DateFunctionTO_DATE(#0,'YYYY-MM-DD')FunctionSQL PatternLagFunctionlag(#0#<, #*#>) over ([#P] [#O]) LeadFunctionlead(#0#<, #*#>) over ([#P] [#O]) OLAPRankFunctionrank() over ([#P] [#O])FunctionSQL PatternRound2FunctionROUND(#0, #1)RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>Int2FunctionFLOOR(#0)AcosFunctionACOS(#0)AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#1, #0)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEILING(#0)CosFunctionCOS(#0)CoshFunctionCOSH(#0)ExpFunctionEXP(#0)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#1, #0)Log10FunctionLOG(10,#0)PowerFunctionPOWER(#0, #1)QuotientFunction
(CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END)RoundFunctionROUND(#0, 0)SinFunctionSIN(#0)SinhFunctionSINH(#0)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunctionTANH(#0)TruncFunctionCAST(#0 AS INTEGER)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunctionMOD(#0, #1)ItemExampleApplication server versionTomcat 7.0, IIS 7.5, etc.Application server operating systemWindows 2008 R2Operating system bit type64-bitStrategy Web version9.4.1 Hotfix 9Machine physical memory8 GBNumber of CPUs and speedDual core @ 2.4 GhzJava version1.8.0 update 60Java max heap size2048 MOther Java parametersVerbose GC loggingAverage user concurrency on peak times100 | Explicit
create table ZZEA00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11create table ZZAM00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11 create table ZZAM01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZEA00 pa11
create table ZZEA02 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZAM01 pa11 create table ZZMQ03 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZEA02 pa11
where (pa11.WJXBFS1 <= 5.0)
create table ZZEA04 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ03 pa12
where a11.SUBCAT_ID = pa12.SUBCAT_ID
select pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.WJXBFS1 WJXBFS1,
pa11.WJXBFS2 WJXBFS2
from ZZEA04 pa11,
LU_SUBCATEG a12
where pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZAM00 pa11
where (pa11.WJXBFS1 <= 5.0)
select a11.SUBCAT_ID SUBCAT_ID,
a13.SUBCAT_DESC SUBCAT_DESC,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ01 pa12,
LU_SUBCATEG a13
where a11.SUBCAT_ID = pa12.SUBCAT_ID and
a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT base64(dt_binary)
FROM wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
SELECT c_to_string(dt_binary)
FROM wh2.dt_fact_table1;FunctionSQL PatternAdd(#0#< + #*#>) Average(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) AvgAVG(#0#<#*#>) CountCOUNT(#0#<#*#>) GeoMeanFunctionEXP(AVG(LN(#0))) GreatestFunctionGREATEST(#0#<, #*#>) LeastFunctionLEAST(#0#<, #*#>) MaxMAX(#0#<#*#>) MinMIN(#0#<#*#>) Multiply(#0#< * #*#>) StdevFunctionSTDDEV(#0#< #*#>) SumSUM(#0#<#*#>) VarFunctionVAR(#0#< #*#>) FunctionSQL PatternConcatFunctionCONCAT(#0, #1) LengthFunctionLENGTH(#0)LowerFunctionLOWER(#0) LTrimFunctionLTRIM(#0) PositionFunctionLOCATE(#1, #0) RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2) InitCapFunction(UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) TrimFunctionLTRIM(RTRIM(#0)) RightStrFunctionRIGHT(#0, #1) LeftStrFunctionLEFT(#0, #1) UpperFunctionUPPER(#0) ConcatBlankFunction(#0#< || ' ' || #*#>) FunctionSQL PatternDaysBetweenFunctionDAYS_BETWEEN(#0, #1)YearEndDateFunctionADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0))CurrentDateFunctionNOW()DayOfMonthFunctionDAYOFMONTH(#0)DayOfWeekFunction(CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END)DayOfYearFunctionDAYOFYEAR(#0)WeekFunctionTO_CHAR(#0,'WW')MonthFunctionMONTH(#0)QuarterFunctionTO_CHAR (#0, 'Q')YearFunctionYEAR(#0)CurrentDateTimeFunctionNOW()CurrentTimeFunctionNOW()HourFunctionHOUR(#0)MinuteFunctionMINUTE(#0)SecondFunctionROUND(SECOND(#0))MilliSecondFunctionsubstring(cast(extract(second from #0) as char(10)), 4, 3)MonthStartDateFunctionADD_DAYS(#0,1-DAYOFMONTH(#0))YearStartDateFunctionADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0)))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunction(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))AddDaysFunctionADD_DAYS(#0, #1)MonthEndDateFunctionADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1))))DateFunctionTO_DATE(#0,'YYYY-MM-DD')FunctionSQL PatternLagFunctionlag(#0#<, #*#>) over ([#P] [#O]) LeadFunctionlead(#0#<, #*#>) over ([#P] [#O]) OLAPRankFunctionrank() over ([#P] [#O])FunctionSQL PatternRound2FunctionROUND(#0, #1)RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>Int2FunctionFLOOR(#0)AcosFunctionACOS(#0)AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#1, #0)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEILING(#0)CosFunctionCOS(#0)CoshFunctionCOSH(#0)ExpFunctionEXP(#0)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#1, #0)Log10FunctionLOG(10,#0)PowerFunctionPOWER(#0, #1)QuotientFunction
(CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END)RoundFunctionROUND(#0, 0)SinFunctionSIN(#0)SinhFunctionSINH(#0)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunctionTANH(#0)TruncFunctionCAST(#0 AS INTEGER)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunctionMOD(#0, #1)ItemExampleApplication server versionTomcat 7.0, IIS 7.5, etc.Application server operating systemWindows 2008 R2Operating system bit type64-bitStrategy Web version9.4.1 Hotfix 9Machine physical memory8 GBNumber of CPUs and speedDual core @ 2.4 GhzJava version1.8.0 update 60Java max heap size2048 MOther Java parametersVerbose GC loggingAverage user concurrency on peak times100 |
Explicit
create table ZZEA00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11create table ZZAM00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11 create table ZZAM01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZEA00 pa11
create table ZZEA02 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZAM01 pa11 create table ZZMQ03 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZEA02 pa11
where (pa11.WJXBFS1 <= 5.0)
create table ZZEA04 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ03 pa12
where a11.SUBCAT_ID = pa12.SUBCAT_ID
select pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.WJXBFS1 WJXBFS1,
pa11.WJXBFS2 WJXBFS2
from ZZEA04 pa11,
LU_SUBCATEG a12
where pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZAM00 pa11
where (pa11.WJXBFS1 <= 5.0)
select a11.SUBCAT_ID SUBCAT_ID,
a13.SUBCAT_DESC SUBCAT_DESC,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ01 pa12,
LU_SUBCATEG a13
where a11.SUBCAT_ID = pa12.SUBCAT_ID and
a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT base64(dt_binary)
FROM wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
SELECT c_to_string(dt_binary)
FROM wh2.dt_fact_table1;FunctionSQL PatternAdd(#0#< + #*#>) Average(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) AvgAVG(#0#<#*#>) CountCOUNT(#0#<#*#>) GeoMeanFunctionEXP(AVG(LN(#0))) GreatestFunctionGREATEST(#0#<, #*#>) LeastFunctionLEAST(#0#<, #*#>) MaxMAX(#0#<#*#>) MinMIN(#0#<#*#>) Multiply(#0#< * #*#>) StdevFunctionSTDDEV(#0#< #*#>) SumSUM(#0#<#*#>) VarFunctionVAR(#0#< #*#>) FunctionSQL PatternConcatFunctionCONCAT(#0, #1) LengthFunctionLENGTH(#0)LowerFunctionLOWER(#0) LTrimFunctionLTRIM(#0) PositionFunctionLOCATE(#1, #0) RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2) InitCapFunction(UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) TrimFunctionLTRIM(RTRIM(#0)) RightStrFunctionRIGHT(#0, #1) LeftStrFunctionLEFT(#0, #1) UpperFunctionUPPER(#0) ConcatBlankFunction(#0#< || ' ' || #*#>) FunctionSQL PatternDaysBetweenFunctionDAYS_BETWEEN(#0, #1)YearEndDateFunctionADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0))CurrentDateFunctionNOW()DayOfMonthFunctionDAYOFMONTH(#0)DayOfWeekFunction(CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END)DayOfYearFunctionDAYOFYEAR(#0)WeekFunctionTO_CHAR(#0,'WW')MonthFunctionMONTH(#0)QuarterFunctionTO_CHAR (#0, 'Q')YearFunctionYEAR(#0)CurrentDateTimeFunctionNOW()CurrentTimeFunctionNOW()HourFunctionHOUR(#0)MinuteFunctionMINUTE(#0)SecondFunctionROUND(SECOND(#0))MilliSecondFunctionsubstring(cast(extract(second from #0) as char(10)), 4, 3)MonthStartDateFunctionADD_DAYS(#0,1-DAYOFMONTH(#0))YearStartDateFunctionADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0)))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunction(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))AddDaysFunctionADD_DAYS(#0, #1)MonthEndDateFunctionADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1))))DateFunctionTO_DATE(#0,'YYYY-MM-DD')FunctionSQL PatternLagFunctionlag(#0#<, #*#>) over ([#P] [#O]) LeadFunctionlead(#0#<, #*#>) over ([#P] [#O]) OLAPRankFunctionrank() over ([#P] [#O])FunctionSQL PatternRound2FunctionROUND(#0, #1)RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>Int2FunctionFLOOR(#0)AcosFunctionACOS(#0)AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#1, #0)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEILING(#0)CosFunctionCOS(#0)CoshFunctionCOSH(#0)ExpFunctionEXP(#0)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#1, #0)Log10FunctionLOG(10,#0)PowerFunctionPOWER(#0, #1)QuotientFunction
(CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END)RoundFunctionROUND(#0, 0)SinFunctionSIN(#0)SinhFunctionSINH(#0)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunctionTANH(#0)TruncFunctionCAST(#0 AS INTEGER)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunctionMOD(#0, #1)ItemExampleApplication server versionTomcat 7.0, IIS 7.5, etc.Application server operating systemWindows 2008 R2Operating system bit type64-bitStrategy Web version9.4.1 Hotfix 9Machine physical memory8 GBNumber of CPUs and speedDual core @ 2.4 GhzJava version1.8.0 update 60Java max heap size2048 MOther Java parametersVerbose GC loggingAverage user concurrency on peak times100 | Explicit
create table ZZEA00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11create table ZZAM00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11 create table ZZAM01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZEA00 pa11
create table ZZEA02 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZAM01 pa11 create table ZZMQ03 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZEA02 pa11
where (pa11.WJXBFS1 <= 5.0)
create table ZZEA04 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ03 pa12
where a11.SUBCAT_ID = pa12.SUBCAT_ID
select pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.WJXBFS1 WJXBFS1,
pa11.WJXBFS2 WJXBFS2
from ZZEA04 pa11,
LU_SUBCATEG a12
where pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZAM00 pa11
where (pa11.WJXBFS1 <= 5.0)
select a11.SUBCAT_ID SUBCAT_ID,
a13.SUBCAT_DESC SUBCAT_DESC,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ01 pa12,
LU_SUBCATEG a13
where a11.SUBCAT_ID = pa12.SUBCAT_ID and
a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT base64(dt_binary)
FROM wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
SELECT c_to_string(dt_binary)
FROM wh2.dt_fact_table1;FunctionSQL PatternAdd(#0#< + #*#>) Average(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) AvgAVG(#0#<#*#>) CountCOUNT(#0#<#*#>) GeoMeanFunctionEXP(AVG(LN(#0))) GreatestFunctionGREATEST(#0#<, #*#>) LeastFunctionLEAST(#0#<, #*#>) MaxMAX(#0#<#*#>) MinMIN(#0#<#*#>) Multiply(#0#< * #*#>) StdevFunctionSTDDEV(#0#< #*#>) SumSUM(#0#<#*#>) VarFunctionVAR(#0#< #*#>) FunctionSQL PatternConcatFunctionCONCAT(#0, #1) LengthFunctionLENGTH(#0)LowerFunctionLOWER(#0) LTrimFunctionLTRIM(#0) PositionFunctionLOCATE(#1, #0) RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2) InitCapFunction(UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) TrimFunctionLTRIM(RTRIM(#0)) RightStrFunctionRIGHT(#0, #1) LeftStrFunctionLEFT(#0, #1) UpperFunctionUPPER(#0) ConcatBlankFunction(#0#< || ' ' || #*#>) FunctionSQL PatternDaysBetweenFunctionDAYS_BETWEEN(#0, #1)YearEndDateFunctionADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0))CurrentDateFunctionNOW()DayOfMonthFunctionDAYOFMONTH(#0)DayOfWeekFunction(CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END)DayOfYearFunctionDAYOFYEAR(#0)WeekFunctionTO_CHAR(#0,'WW')MonthFunctionMONTH(#0)QuarterFunctionTO_CHAR (#0, 'Q')YearFunctionYEAR(#0)CurrentDateTimeFunctionNOW()CurrentTimeFunctionNOW()HourFunctionHOUR(#0)MinuteFunctionMINUTE(#0)SecondFunctionROUND(SECOND(#0))MilliSecondFunctionsubstring(cast(extract(second from #0) as char(10)), 4, 3)MonthStartDateFunctionADD_DAYS(#0,1-DAYOFMONTH(#0))YearStartDateFunctionADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0)))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunction(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))AddDaysFunctionADD_DAYS(#0, #1)MonthEndDateFunctionADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1))))DateFunctionTO_DATE(#0,'YYYY-MM-DD')FunctionSQL PatternLagFunctionlag(#0#<, #*#>) over ([#P] [#O]) LeadFunctionlead(#0#<, #*#>) over ([#P] [#O]) OLAPRankFunctionrank() over ([#P] [#O])FunctionSQL PatternRound2FunctionROUND(#0, #1)RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>Int2FunctionFLOOR(#0)AcosFunctionACOS(#0)AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#1, #0)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEILING(#0)CosFunctionCOS(#0)CoshFunctionCOSH(#0)ExpFunctionEXP(#0)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#1, #0)Log10FunctionLOG(10,#0)PowerFunctionPOWER(#0, #1)QuotientFunction
(CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END)RoundFunctionROUND(#0, 0)SinFunctionSIN(#0)SinhFunctionSINH(#0)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunctionTANH(#0)TruncFunctionCAST(#0 AS INTEGER)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunctionMOD(#0, #1)ItemExampleApplication server versionTomcat 7.0, IIS 7.5, etc.Application server operating systemWindows 2008 R2Operating system bit type64-bitStrategy Web version9.4.1 Hotfix 9Machine physical memory8 GBNumber of CPUs and speedDual core @ 2.4 GhzJava version1.8.0 update 60Java max heap size2048 MOther Java parametersVerbose GC loggingAverage user concurrency on peak times100 |
Explicit
create table ZZEA00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11create table ZZAM00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11 create table ZZAM01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZEA00 pa11
create table ZZEA02 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZAM01 pa11 create table ZZMQ03 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZEA02 pa11
where (pa11.WJXBFS1 <= 5.0)
create table ZZEA04 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ03 pa12
where a11.SUBCAT_ID = pa12.SUBCAT_ID
select pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.WJXBFS1 WJXBFS1,
pa11.WJXBFS2 WJXBFS2
from ZZEA04 pa11,
LU_SUBCATEG a12
where pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZAM00 pa11
where (pa11.WJXBFS1 <= 5.0)
select a11.SUBCAT_ID SUBCAT_ID,
a13.SUBCAT_DESC SUBCAT_DESC,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ01 pa12,
LU_SUBCATEG a13
where a11.SUBCAT_ID = pa12.SUBCAT_ID and
a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT base64(dt_binary)
FROM wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
SELECT c_to_string(dt_binary)
FROM wh2.dt_fact_table1;FunctionSQL PatternAdd(#0#< + #*#>) Average(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) AvgAVG(#0#<#*#>) CountCOUNT(#0#<#*#>) GeoMeanFunctionEXP(AVG(LN(#0))) GreatestFunctionGREATEST(#0#<, #*#>) LeastFunctionLEAST(#0#<, #*#>) MaxMAX(#0#<#*#>) MinMIN(#0#<#*#>) Multiply(#0#< * #*#>) StdevFunctionSTDDEV(#0#< #*#>) SumSUM(#0#<#*#>) VarFunctionVAR(#0#< #*#>) FunctionSQL PatternConcatFunctionCONCAT(#0, #1) LengthFunctionLENGTH(#0)LowerFunctionLOWER(#0) LTrimFunctionLTRIM(#0) PositionFunctionLOCATE(#1, #0) RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2) InitCapFunction(UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) TrimFunctionLTRIM(RTRIM(#0)) RightStrFunctionRIGHT(#0, #1) LeftStrFunctionLEFT(#0, #1) UpperFunctionUPPER(#0) ConcatBlankFunction(#0#< || ' ' || #*#>) FunctionSQL PatternDaysBetweenFunctionDAYS_BETWEEN(#0, #1)YearEndDateFunctionADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0))CurrentDateFunctionNOW()DayOfMonthFunctionDAYOFMONTH(#0)DayOfWeekFunction(CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END)DayOfYearFunctionDAYOFYEAR(#0)WeekFunctionTO_CHAR(#0,'WW')MonthFunctionMONTH(#0)QuarterFunctionTO_CHAR (#0, 'Q')YearFunctionYEAR(#0)CurrentDateTimeFunctionNOW()CurrentTimeFunctionNOW()HourFunctionHOUR(#0)MinuteFunctionMINUTE(#0)SecondFunctionROUND(SECOND(#0))MilliSecondFunctionsubstring(cast(extract(second from #0) as char(10)), 4, 3)MonthStartDateFunctionADD_DAYS(#0,1-DAYOFMONTH(#0))YearStartDateFunctionADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0)))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunction(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))AddDaysFunctionADD_DAYS(#0, #1)MonthEndDateFunctionADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1))))DateFunctionTO_DATE(#0,'YYYY-MM-DD')FunctionSQL PatternLagFunctionlag(#0#<, #*#>) over ([#P] [#O]) LeadFunctionlead(#0#<, #*#>) over ([#P] [#O]) OLAPRankFunctionrank() over ([#P] [#O])FunctionSQL PatternRound2FunctionROUND(#0, #1)RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>Int2FunctionFLOOR(#0)AcosFunctionACOS(#0)AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#1, #0)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEILING(#0)CosFunctionCOS(#0)CoshFunctionCOSH(#0)ExpFunctionEXP(#0)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#1, #0)Log10FunctionLOG(10,#0)PowerFunctionPOWER(#0, #1)QuotientFunction
(CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END)RoundFunctionROUND(#0, 0)SinFunctionSIN(#0)SinhFunctionSINH(#0)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunctionTANH(#0)TruncFunctionCAST(#0 AS INTEGER)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunctionMOD(#0, #1)ItemExampleApplication server versionTomcat 7.0, IIS 7.5, etc.Application server operating systemWindows 2008 R2Operating system bit type64-bitStrategy Web version9.4.1 Hotfix 9Machine physical memory8 GBNumber of CPUs and speedDual core @ 2.4 GhzJava version1.8.0 update 60Java max heap size2048 MOther Java parametersVerbose GC loggingAverage user concurrency on peak times100 | Explicit
create table ZZEA00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11create table ZZAM00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11 create table ZZAM01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZEA00 pa11
create table ZZEA02 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZAM01 pa11 create table ZZMQ03 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZEA02 pa11
where (pa11.WJXBFS1 <= 5.0)
create table ZZEA04 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ03 pa12
where a11.SUBCAT_ID = pa12.SUBCAT_ID
select pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.WJXBFS1 WJXBFS1,
pa11.WJXBFS2 WJXBFS2
from ZZEA04 pa11,
LU_SUBCATEG a12
where pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZAM00 pa11
where (pa11.WJXBFS1 <= 5.0)
select a11.SUBCAT_ID SUBCAT_ID,
a13.SUBCAT_DESC SUBCAT_DESC,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ01 pa12,
LU_SUBCATEG a13
where a11.SUBCAT_ID = pa12.SUBCAT_ID and
a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT base64(dt_binary)
FROM wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
SELECT c_to_string(dt_binary)
FROM wh2.dt_fact_table1;FunctionSQL PatternAdd(#0#< + #*#>) Average(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) AvgAVG(#0#<#*#>) CountCOUNT(#0#<#*#>) GeoMeanFunctionEXP(AVG(LN(#0))) GreatestFunctionGREATEST(#0#<, #*#>) LeastFunctionLEAST(#0#<, #*#>) MaxMAX(#0#<#*#>) MinMIN(#0#<#*#>) Multiply(#0#< * #*#>) StdevFunctionSTDDEV(#0#< #*#>) SumSUM(#0#<#*#>) VarFunctionVAR(#0#< #*#>) FunctionSQL PatternConcatFunctionCONCAT(#0, #1) LengthFunctionLENGTH(#0)LowerFunctionLOWER(#0) LTrimFunctionLTRIM(#0) PositionFunctionLOCATE(#1, #0) RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2) InitCapFunction(UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) TrimFunctionLTRIM(RTRIM(#0)) RightStrFunctionRIGHT(#0, #1) LeftStrFunctionLEFT(#0, #1) UpperFunctionUPPER(#0) ConcatBlankFunction(#0#< || ' ' || #*#>) FunctionSQL PatternDaysBetweenFunctionDAYS_BETWEEN(#0, #1)YearEndDateFunctionADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0))CurrentDateFunctionNOW()DayOfMonthFunctionDAYOFMONTH(#0)DayOfWeekFunction(CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END)DayOfYearFunctionDAYOFYEAR(#0)WeekFunctionTO_CHAR(#0,'WW')MonthFunctionMONTH(#0)QuarterFunctionTO_CHAR (#0, 'Q')YearFunctionYEAR(#0)CurrentDateTimeFunctionNOW()CurrentTimeFunctionNOW()HourFunctionHOUR(#0)MinuteFunctionMINUTE(#0)SecondFunctionROUND(SECOND(#0))MilliSecondFunctionsubstring(cast(extract(second from #0) as char(10)), 4, 3)MonthStartDateFunctionADD_DAYS(#0,1-DAYOFMONTH(#0))YearStartDateFunctionADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0)))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunction(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))AddDaysFunctionADD_DAYS(#0, #1)MonthEndDateFunctionADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1))))DateFunctionTO_DATE(#0,'YYYY-MM-DD')FunctionSQL PatternLagFunctionlag(#0#<, #*#>) over ([#P] [#O]) LeadFunctionlead(#0#<, #*#>) over ([#P] [#O]) OLAPRankFunctionrank() over ([#P] [#O])FunctionSQL PatternRound2FunctionROUND(#0, #1)RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>Int2FunctionFLOOR(#0)AcosFunctionACOS(#0)AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#1, #0)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEILING(#0)CosFunctionCOS(#0)CoshFunctionCOSH(#0)ExpFunctionEXP(#0)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#1, #0)Log10FunctionLOG(10,#0)PowerFunctionPOWER(#0, #1)QuotientFunction
(CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END)RoundFunctionROUND(#0, 0)SinFunctionSIN(#0)SinhFunctionSINH(#0)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunctionTANH(#0)TruncFunctionCAST(#0 AS INTEGER)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunctionMOD(#0, #1)ItemExampleApplication server versionTomcat 7.0, IIS 7.5, etc.Application server operating systemWindows 2008 R2Operating system bit type64-bitStrategy Web version9.4.1 Hotfix 9Machine physical memory8 GBNumber of CPUs and speedDual core @ 2.4 GhzJava version1.8.0 update 60Java max heap size2048 MOther Java parametersVerbose GC loggingAverage user concurrency on peak times100 |
Explicit
create table ZZEA00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11create table ZZAM00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11 create table ZZAM01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZEA00 pa11
create table ZZEA02 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZAM01 pa11 create table ZZMQ03 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZEA02 pa11
where (pa11.WJXBFS1 <= 5.0)
create table ZZEA04 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ03 pa12
where a11.SUBCAT_ID = pa12.SUBCAT_ID
select pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.WJXBFS1 WJXBFS1,
pa11.WJXBFS2 WJXBFS2
from ZZEA04 pa11,
LU_SUBCATEG a12
where pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZAM00 pa11
where (pa11.WJXBFS1 <= 5.0)
select a11.SUBCAT_ID SUBCAT_ID,
a13.SUBCAT_DESC SUBCAT_DESC,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ01 pa12,
LU_SUBCATEG a13
where a11.SUBCAT_ID = pa12.SUBCAT_ID and
a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT base64(dt_binary)
FROM wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
SELECT c_to_string(dt_binary)
FROM wh2.dt_fact_table1;FunctionSQL PatternAdd(#0#< + #*#>) Average(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) AvgAVG(#0#<#*#>) CountCOUNT(#0#<#*#>) GeoMeanFunctionEXP(AVG(LN(#0))) GreatestFunctionGREATEST(#0#<, #*#>) LeastFunctionLEAST(#0#<, #*#>) MaxMAX(#0#<#*#>) MinMIN(#0#<#*#>) Multiply(#0#< * #*#>) StdevFunctionSTDDEV(#0#< #*#>) SumSUM(#0#<#*#>) VarFunctionVAR(#0#< #*#>) FunctionSQL PatternConcatFunctionCONCAT(#0, #1) LengthFunctionLENGTH(#0)LowerFunctionLOWER(#0) LTrimFunctionLTRIM(#0) PositionFunctionLOCATE(#1, #0) RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2) InitCapFunction(UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) TrimFunctionLTRIM(RTRIM(#0)) RightStrFunctionRIGHT(#0, #1) LeftStrFunctionLEFT(#0, #1) UpperFunctionUPPER(#0) ConcatBlankFunction(#0#< || ' ' || #*#>) FunctionSQL PatternDaysBetweenFunctionDAYS_BETWEEN(#0, #1)YearEndDateFunctionADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0))CurrentDateFunctionNOW()DayOfMonthFunctionDAYOFMONTH(#0)DayOfWeekFunction(CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END)DayOfYearFunctionDAYOFYEAR(#0)WeekFunctionTO_CHAR(#0,'WW')MonthFunctionMONTH(#0)QuarterFunctionTO_CHAR (#0, 'Q')YearFunctionYEAR(#0)CurrentDateTimeFunctionNOW()CurrentTimeFunctionNOW()HourFunctionHOUR(#0)MinuteFunctionMINUTE(#0)SecondFunctionROUND(SECOND(#0))MilliSecondFunctionsubstring(cast(extract(second from #0) as char(10)), 4, 3)MonthStartDateFunctionADD_DAYS(#0,1-DAYOFMONTH(#0))YearStartDateFunctionADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0)))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunction(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))AddDaysFunctionADD_DAYS(#0, #1)MonthEndDateFunctionADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1))))DateFunctionTO_DATE(#0,'YYYY-MM-DD')FunctionSQL PatternLagFunctionlag(#0#<, #*#>) over ([#P] [#O]) LeadFunctionlead(#0#<, #*#>) over ([#P] [#O]) OLAPRankFunctionrank() over ([#P] [#O])FunctionSQL PatternRound2FunctionROUND(#0, #1)RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>Int2FunctionFLOOR(#0)AcosFunctionACOS(#0)AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#1, #0)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEILING(#0)CosFunctionCOS(#0)CoshFunctionCOSH(#0)ExpFunctionEXP(#0)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#1, #0)Log10FunctionLOG(10,#0)PowerFunctionPOWER(#0, #1)QuotientFunction
(CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END)RoundFunctionROUND(#0, 0)SinFunctionSIN(#0)SinhFunctionSINH(#0)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunctionTANH(#0)TruncFunctionCAST(#0 AS INTEGER)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunctionMOD(#0, #1)ItemExampleApplication server versionTomcat 7.0, IIS 7.5, etc.Application server operating systemWindows 2008 R2Operating system bit type64-bitStrategy Web version9.4.1 Hotfix 9Machine physical memory8 GBNumber of CPUs and speedDual core @ 2.4 GhzJava version1.8.0 update 60Java max heap size2048 MOther Java parametersVerbose GC loggingAverage user concurrency on peak times100 | Explicit
create table ZZEA00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11create table ZZAM00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11 create table ZZAM01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZEA00 pa11
create table ZZEA02 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZAM01 pa11 create table ZZMQ03 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZEA02 pa11
where (pa11.WJXBFS1 <= 5.0)
create table ZZEA04 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ03 pa12
where a11.SUBCAT_ID = pa12.SUBCAT_ID
select pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.WJXBFS1 WJXBFS1,
pa11.WJXBFS2 WJXBFS2
from ZZEA04 pa11,
LU_SUBCATEG a12
where pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZAM00 pa11
where (pa11.WJXBFS1 <= 5.0)
select a11.SUBCAT_ID SUBCAT_ID,
a13.SUBCAT_DESC SUBCAT_DESC,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ01 pa12,
LU_SUBCATEG a13
where a11.SUBCAT_ID = pa12.SUBCAT_ID and
a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT base64(dt_binary)
FROM wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
SELECT c_to_string(dt_binary)
FROM wh2.dt_fact_table1;FunctionSQL PatternAdd(#0#< + #*#>) Average(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) AvgAVG(#0#<#*#>) CountCOUNT(#0#<#*#>) GeoMeanFunctionEXP(AVG(LN(#0))) GreatestFunctionGREATEST(#0#<, #*#>) LeastFunctionLEAST(#0#<, #*#>) MaxMAX(#0#<#*#>) MinMIN(#0#<#*#>) Multiply(#0#< * #*#>) StdevFunctionSTDDEV(#0#< #*#>) SumSUM(#0#<#*#>) VarFunctionVAR(#0#< #*#>) FunctionSQL PatternConcatFunctionCONCAT(#0, #1) LengthFunctionLENGTH(#0)LowerFunctionLOWER(#0) LTrimFunctionLTRIM(#0) PositionFunctionLOCATE(#1, #0) RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2) InitCapFunction(UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) TrimFunctionLTRIM(RTRIM(#0)) RightStrFunctionRIGHT(#0, #1) LeftStrFunctionLEFT(#0, #1) UpperFunctionUPPER(#0) ConcatBlankFunction(#0#< || ' ' || #*#>) FunctionSQL PatternDaysBetweenFunctionDAYS_BETWEEN(#0, #1)YearEndDateFunctionADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0))CurrentDateFunctionNOW()DayOfMonthFunctionDAYOFMONTH(#0)DayOfWeekFunction(CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END)DayOfYearFunctionDAYOFYEAR(#0)WeekFunctionTO_CHAR(#0,'WW')MonthFunctionMONTH(#0)QuarterFunctionTO_CHAR (#0, 'Q')YearFunctionYEAR(#0)CurrentDateTimeFunctionNOW()CurrentTimeFunctionNOW()HourFunctionHOUR(#0)MinuteFunctionMINUTE(#0)SecondFunctionROUND(SECOND(#0))MilliSecondFunctionsubstring(cast(extract(second from #0) as char(10)), 4, 3)MonthStartDateFunctionADD_DAYS(#0,1-DAYOFMONTH(#0))YearStartDateFunctionADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0)))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunction(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))AddDaysFunctionADD_DAYS(#0, #1)MonthEndDateFunctionADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1))))DateFunctionTO_DATE(#0,'YYYY-MM-DD')FunctionSQL PatternLagFunctionlag(#0#<, #*#>) over ([#P] [#O]) LeadFunctionlead(#0#<, #*#>) over ([#P] [#O]) OLAPRankFunctionrank() over ([#P] [#O])FunctionSQL PatternRound2FunctionROUND(#0, #1)RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>Int2FunctionFLOOR(#0)AcosFunctionACOS(#0)AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#1, #0)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEILING(#0)CosFunctionCOS(#0)CoshFunctionCOSH(#0)ExpFunctionEXP(#0)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#1, #0)Log10FunctionLOG(10,#0)PowerFunctionPOWER(#0, #1)QuotientFunction
(CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END)RoundFunctionROUND(#0, 0)SinFunctionSIN(#0)SinhFunctionSINH(#0)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunctionTANH(#0)TruncFunctionCAST(#0 AS INTEGER)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunctionMOD(#0, #1)ItemExampleApplication server versionTomcat 7.0, IIS 7.5, etc.Application server operating systemWindows 2008 R2Operating system bit type64-bitStrategy Web version9.4.1 Hotfix 9Machine physical memory8 GBNumber of CPUs and speedDual core @ 2.4 GhzJava version1.8.0 update 60Java max heap size2048 MOther Java parametersVerbose GC loggingAverage user concurrency on peak times100 |
Explicit
create table ZZEA00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11create table ZZAM00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11 create table ZZAM01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZEA00 pa11
create table ZZEA02 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZAM01 pa11 create table ZZMQ03 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZEA02 pa11
where (pa11.WJXBFS1 <= 5.0)
create table ZZEA04 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ03 pa12
where a11.SUBCAT_ID = pa12.SUBCAT_ID
select pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.WJXBFS1 WJXBFS1,
pa11.WJXBFS2 WJXBFS2
from ZZEA04 pa11,
LU_SUBCATEG a12
where pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZAM00 pa11
where (pa11.WJXBFS1 <= 5.0)
select a11.SUBCAT_ID SUBCAT_ID,
a13.SUBCAT_DESC SUBCAT_DESC,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ01 pa12,
LU_SUBCATEG a13
where a11.SUBCAT_ID = pa12.SUBCAT_ID and
a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT base64(dt_binary)
FROM wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
SELECT c_to_string(dt_binary)
FROM wh2.dt_fact_table1;FunctionSQL PatternAdd(#0#< + #*#>) Average(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) AvgAVG(#0#<#*#>) CountCOUNT(#0#<#*#>) GeoMeanFunctionEXP(AVG(LN(#0))) GreatestFunctionGREATEST(#0#<, #*#>) LeastFunctionLEAST(#0#<, #*#>) MaxMAX(#0#<#*#>) MinMIN(#0#<#*#>) Multiply(#0#< * #*#>) StdevFunctionSTDDEV(#0#< #*#>) SumSUM(#0#<#*#>) VarFunctionVAR(#0#< #*#>) FunctionSQL PatternConcatFunctionCONCAT(#0, #1) LengthFunctionLENGTH(#0)LowerFunctionLOWER(#0) LTrimFunctionLTRIM(#0) PositionFunctionLOCATE(#1, #0) RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2) InitCapFunction(UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) TrimFunctionLTRIM(RTRIM(#0)) RightStrFunctionRIGHT(#0, #1) LeftStrFunctionLEFT(#0, #1) UpperFunctionUPPER(#0) ConcatBlankFunction(#0#< || ' ' || #*#>) FunctionSQL PatternDaysBetweenFunctionDAYS_BETWEEN(#0, #1)YearEndDateFunctionADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0))CurrentDateFunctionNOW()DayOfMonthFunctionDAYOFMONTH(#0)DayOfWeekFunction(CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END)DayOfYearFunctionDAYOFYEAR(#0)WeekFunctionTO_CHAR(#0,'WW')MonthFunctionMONTH(#0)QuarterFunctionTO_CHAR (#0, 'Q')YearFunctionYEAR(#0)CurrentDateTimeFunctionNOW()CurrentTimeFunctionNOW()HourFunctionHOUR(#0)MinuteFunctionMINUTE(#0)SecondFunctionROUND(SECOND(#0))MilliSecondFunctionsubstring(cast(extract(second from #0) as char(10)), 4, 3)MonthStartDateFunctionADD_DAYS(#0,1-DAYOFMONTH(#0))YearStartDateFunctionADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0)))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunction(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))AddDaysFunctionADD_DAYS(#0, #1)MonthEndDateFunctionADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1))))DateFunctionTO_DATE(#0,'YYYY-MM-DD')FunctionSQL PatternLagFunctionlag(#0#<, #*#>) over ([#P] [#O]) LeadFunctionlead(#0#<, #*#>) over ([#P] [#O]) OLAPRankFunctionrank() over ([#P] [#O])FunctionSQL PatternRound2FunctionROUND(#0, #1)RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>Int2FunctionFLOOR(#0)AcosFunctionACOS(#0)AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#1, #0)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEILING(#0)CosFunctionCOS(#0)CoshFunctionCOSH(#0)ExpFunctionEXP(#0)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#1, #0)Log10FunctionLOG(10,#0)PowerFunctionPOWER(#0, #1)QuotientFunction
(CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END)RoundFunctionROUND(#0, 0)SinFunctionSIN(#0)SinhFunctionSINH(#0)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunctionTANH(#0)TruncFunctionCAST(#0 AS INTEGER)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunctionMOD(#0, #1)ItemExampleApplication server versionTomcat 7.0, IIS 7.5, etc.Application server operating systemWindows 2008 R2Operating system bit type64-bitStrategy Web version9.4.1 Hotfix 9Machine physical memory8 GBNumber of CPUs and speedDual core @ 2.4 GhzJava version1.8.0 update 60Java max heap size2048 MOther Java parametersVerbose GC loggingAverage user concurrency on peak times100 | Explicit
create table ZZEA00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11create table ZZAM00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11 create table ZZAM01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZEA00 pa11
create table ZZEA02 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZAM01 pa11 create table ZZMQ03 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZEA02 pa11
where (pa11.WJXBFS1 <= 5.0)
create table ZZEA04 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ03 pa12
where a11.SUBCAT_ID = pa12.SUBCAT_ID
select pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.WJXBFS1 WJXBFS1,
pa11.WJXBFS2 WJXBFS2
from ZZEA04 pa11,
LU_SUBCATEG a12
where pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZAM00 pa11
where (pa11.WJXBFS1 <= 5.0)
select a11.SUBCAT_ID SUBCAT_ID,
a13.SUBCAT_DESC SUBCAT_DESC,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ01 pa12,
LU_SUBCATEG a13
where a11.SUBCAT_ID = pa12.SUBCAT_ID and
a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT base64(dt_binary)
FROM wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
SELECT c_to_string(dt_binary)
FROM wh2.dt_fact_table1;FunctionSQL PatternAdd(#0#< + #*#>) Average(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) AvgAVG(#0#<#*#>) CountCOUNT(#0#<#*#>) GeoMeanFunctionEXP(AVG(LN(#0))) GreatestFunctionGREATEST(#0#<, #*#>) LeastFunctionLEAST(#0#<, #*#>) MaxMAX(#0#<#*#>) MinMIN(#0#<#*#>) Multiply(#0#< * #*#>) StdevFunctionSTDDEV(#0#< #*#>) SumSUM(#0#<#*#>) VarFunctionVAR(#0#< #*#>) FunctionSQL PatternConcatFunctionCONCAT(#0, #1) LengthFunctionLENGTH(#0)LowerFunctionLOWER(#0) LTrimFunctionLTRIM(#0) PositionFunctionLOCATE(#1, #0) RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2) InitCapFunction(UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) TrimFunctionLTRIM(RTRIM(#0)) RightStrFunctionRIGHT(#0, #1) LeftStrFunctionLEFT(#0, #1) UpperFunctionUPPER(#0) ConcatBlankFunction(#0#< || ' ' || #*#>) FunctionSQL PatternDaysBetweenFunctionDAYS_BETWEEN(#0, #1)YearEndDateFunctionADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0))CurrentDateFunctionNOW()DayOfMonthFunctionDAYOFMONTH(#0)DayOfWeekFunction(CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END)DayOfYearFunctionDAYOFYEAR(#0)WeekFunctionTO_CHAR(#0,'WW')MonthFunctionMONTH(#0)QuarterFunctionTO_CHAR (#0, 'Q')YearFunctionYEAR(#0)CurrentDateTimeFunctionNOW()CurrentTimeFunctionNOW()HourFunctionHOUR(#0)MinuteFunctionMINUTE(#0)SecondFunctionROUND(SECOND(#0))MilliSecondFunctionsubstring(cast(extract(second from #0) as char(10)), 4, 3)MonthStartDateFunctionADD_DAYS(#0,1-DAYOFMONTH(#0))YearStartDateFunctionADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0)))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunction(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))AddDaysFunctionADD_DAYS(#0, #1)MonthEndDateFunctionADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1))))DateFunctionTO_DATE(#0,'YYYY-MM-DD')FunctionSQL PatternLagFunctionlag(#0#<, #*#>) over ([#P] [#O]) LeadFunctionlead(#0#<, #*#>) over ([#P] [#O]) OLAPRankFunctionrank() over ([#P] [#O])FunctionSQL PatternRound2FunctionROUND(#0, #1)RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>Int2FunctionFLOOR(#0)AcosFunctionACOS(#0)AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#1, #0)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEILING(#0)CosFunctionCOS(#0)CoshFunctionCOSH(#0)ExpFunctionEXP(#0)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#1, #0)Log10FunctionLOG(10,#0)PowerFunctionPOWER(#0, #1)QuotientFunction
(CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END)RoundFunctionROUND(#0, 0)SinFunctionSIN(#0)SinhFunctionSINH(#0)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunctionTANH(#0)TruncFunctionCAST(#0 AS INTEGER)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunctionMOD(#0, #1)ItemExampleApplication server versionTomcat 7.0, IIS 7.5, etc.Application server operating systemWindows 2008 R2Operating system bit type64-bitStrategy Web version9.4.1 Hotfix 9Machine physical memory8 GBNumber of CPUs and speedDual core @ 2.4 GhzJava version1.8.0 update 60Java max heap size2048 MOther Java parametersVerbose GC loggingAverage user concurrency on peak times100 |
Explicit
create table ZZEA00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11create table ZZAM00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11 create table ZZAM01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZEA00 pa11
create table ZZEA02 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZAM01 pa11 create table ZZMQ03 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZEA02 pa11
where (pa11.WJXBFS1 <= 5.0)
create table ZZEA04 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ03 pa12
where a11.SUBCAT_ID = pa12.SUBCAT_ID
select pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.WJXBFS1 WJXBFS1,
pa11.WJXBFS2 WJXBFS2
from ZZEA04 pa11,
LU_SUBCATEG a12
where pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZAM00 pa11
where (pa11.WJXBFS1 <= 5.0)
select a11.SUBCAT_ID SUBCAT_ID,
a13.SUBCAT_DESC SUBCAT_DESC,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ01 pa12,
LU_SUBCATEG a13
where a11.SUBCAT_ID = pa12.SUBCAT_ID and
a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT base64(dt_binary)
FROM wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
SELECT c_to_string(dt_binary)
FROM wh2.dt_fact_table1;FunctionSQL PatternAdd(#0#< + #*#>) Average(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) AvgAVG(#0#<#*#>) CountCOUNT(#0#<#*#>) GeoMeanFunctionEXP(AVG(LN(#0))) GreatestFunctionGREATEST(#0#<, #*#>) LeastFunctionLEAST(#0#<, #*#>) MaxMAX(#0#<#*#>) MinMIN(#0#<#*#>) Multiply(#0#< * #*#>) StdevFunctionSTDDEV(#0#< #*#>) SumSUM(#0#<#*#>) VarFunctionVAR(#0#< #*#>) FunctionSQL PatternConcatFunctionCONCAT(#0, #1) LengthFunctionLENGTH(#0)LowerFunctionLOWER(#0) LTrimFunctionLTRIM(#0) PositionFunctionLOCATE(#1, #0) RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2) InitCapFunction(UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) TrimFunctionLTRIM(RTRIM(#0)) RightStrFunctionRIGHT(#0, #1) LeftStrFunctionLEFT(#0, #1) UpperFunctionUPPER(#0) ConcatBlankFunction(#0#< || ' ' || #*#>) FunctionSQL PatternDaysBetweenFunctionDAYS_BETWEEN(#0, #1)YearEndDateFunctionADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0))CurrentDateFunctionNOW()DayOfMonthFunctionDAYOFMONTH(#0)DayOfWeekFunction(CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END)DayOfYearFunctionDAYOFYEAR(#0)WeekFunctionTO_CHAR(#0,'WW')MonthFunctionMONTH(#0)QuarterFunctionTO_CHAR (#0, 'Q')YearFunctionYEAR(#0)CurrentDateTimeFunctionNOW()CurrentTimeFunctionNOW()HourFunctionHOUR(#0)MinuteFunctionMINUTE(#0)SecondFunctionROUND(SECOND(#0))MilliSecondFunctionsubstring(cast(extract(second from #0) as char(10)), 4, 3)MonthStartDateFunctionADD_DAYS(#0,1-DAYOFMONTH(#0))YearStartDateFunctionADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0)))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunction(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))AddDaysFunctionADD_DAYS(#0, #1)MonthEndDateFunctionADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1))))DateFunctionTO_DATE(#0,'YYYY-MM-DD')FunctionSQL PatternLagFunctionlag(#0#<, #*#>) over ([#P] [#O]) LeadFunctionlead(#0#<, #*#>) over ([#P] [#O]) OLAPRankFunctionrank() over ([#P] [#O])FunctionSQL PatternRound2FunctionROUND(#0, #1)RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>Int2FunctionFLOOR(#0)AcosFunctionACOS(#0)AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#1, #0)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEILING(#0)CosFunctionCOS(#0)CoshFunctionCOSH(#0)ExpFunctionEXP(#0)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#1, #0)Log10FunctionLOG(10,#0)PowerFunctionPOWER(#0, #1)QuotientFunction
(CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END)RoundFunctionROUND(#0, 0)SinFunctionSIN(#0)SinhFunctionSINH(#0)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunctionTANH(#0)TruncFunctionCAST(#0 AS INTEGER)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunctionMOD(#0, #1)ItemExampleApplication server versionTomcat 7.0, IIS 7.5, etc.Application server operating systemWindows 2008 R2Operating system bit type64-bitStrategy Web version9.4.1 Hotfix 9Machine physical memory8 GBNumber of CPUs and speedDual core @ 2.4 GhzJava version1.8.0 update 60Java max heap size2048 MOther Java parametersVerbose GC loggingAverage user concurrency on peak times100 | Explicit
create table ZZEA00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11create table ZZAM00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11 create table ZZAM01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZEA00 pa11
create table ZZEA02 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZAM01 pa11 create table ZZMQ03 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZEA02 pa11
where (pa11.WJXBFS1 <= 5.0)
create table ZZEA04 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ03 pa12
where a11.SUBCAT_ID = pa12.SUBCAT_ID
select pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.WJXBFS1 WJXBFS1,
pa11.WJXBFS2 WJXBFS2
from ZZEA04 pa11,
LU_SUBCATEG a12
where pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZAM00 pa11
where (pa11.WJXBFS1 <= 5.0)
select a11.SUBCAT_ID SUBCAT_ID,
a13.SUBCAT_DESC SUBCAT_DESC,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ01 pa12,
LU_SUBCATEG a13
where a11.SUBCAT_ID = pa12.SUBCAT_ID and
a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT base64(dt_binary)
FROM wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
SELECT c_to_string(dt_binary)
FROM wh2.dt_fact_table1;FunctionSQL PatternAdd(#0#< + #*#>) Average(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) AvgAVG(#0#<#*#>) CountCOUNT(#0#<#*#>) GeoMeanFunctionEXP(AVG(LN(#0))) GreatestFunctionGREATEST(#0#<, #*#>) LeastFunctionLEAST(#0#<, #*#>) MaxMAX(#0#<#*#>) MinMIN(#0#<#*#>) Multiply(#0#< * #*#>) StdevFunctionSTDDEV(#0#< #*#>) SumSUM(#0#<#*#>) VarFunctionVAR(#0#< #*#>) FunctionSQL PatternConcatFunctionCONCAT(#0, #1) LengthFunctionLENGTH(#0)LowerFunctionLOWER(#0) LTrimFunctionLTRIM(#0) PositionFunctionLOCATE(#1, #0) RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2) InitCapFunction(UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) TrimFunctionLTRIM(RTRIM(#0)) RightStrFunctionRIGHT(#0, #1) LeftStrFunctionLEFT(#0, #1) UpperFunctionUPPER(#0) ConcatBlankFunction(#0#< || ' ' || #*#>) FunctionSQL PatternDaysBetweenFunctionDAYS_BETWEEN(#0, #1)YearEndDateFunctionADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0))CurrentDateFunctionNOW()DayOfMonthFunctionDAYOFMONTH(#0)DayOfWeekFunction(CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END)DayOfYearFunctionDAYOFYEAR(#0)WeekFunctionTO_CHAR(#0,'WW')MonthFunctionMONTH(#0)QuarterFunctionTO_CHAR (#0, 'Q')YearFunctionYEAR(#0)CurrentDateTimeFunctionNOW()CurrentTimeFunctionNOW()HourFunctionHOUR(#0)MinuteFunctionMINUTE(#0)SecondFunctionROUND(SECOND(#0))MilliSecondFunctionsubstring(cast(extract(second from #0) as char(10)), 4, 3)MonthStartDateFunctionADD_DAYS(#0,1-DAYOFMONTH(#0))YearStartDateFunctionADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0)))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunction(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))AddDaysFunctionADD_DAYS(#0, #1)MonthEndDateFunctionADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1))))DateFunctionTO_DATE(#0,'YYYY-MM-DD')FunctionSQL PatternLagFunctionlag(#0#<, #*#>) over ([#P] [#O]) LeadFunctionlead(#0#<, #*#>) over ([#P] [#O]) OLAPRankFunctionrank() over ([#P] [#O])FunctionSQL PatternRound2FunctionROUND(#0, #1)RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>Int2FunctionFLOOR(#0)AcosFunctionACOS(#0)AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#1, #0)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEILING(#0)CosFunctionCOS(#0)CoshFunctionCOSH(#0)ExpFunctionEXP(#0)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#1, #0)Log10FunctionLOG(10,#0)PowerFunctionPOWER(#0, #1)QuotientFunction
(CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END)RoundFunctionROUND(#0, 0)SinFunctionSIN(#0)SinhFunctionSINH(#0)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunctionTANH(#0)TruncFunctionCAST(#0 AS INTEGER)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunctionMOD(#0, #1)ItemExampleApplication server versionTomcat 7.0, IIS 7.5, etc.Application server operating systemWindows 2008 R2Operating system bit type64-bitStrategy Web version9.4.1 Hotfix 9Machine physical memory8 GBNumber of CPUs and speedDual core @ 2.4 GhzJava version1.8.0 update 60Java max heap size2048 MOther Java parametersVerbose GC loggingAverage user concurrency on peak times100 |
Explicit
create table ZZEA00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11create table ZZAM00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11 create table ZZAM01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZEA00 pa11
create table ZZEA02 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZAM01 pa11 create table ZZMQ03 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZEA02 pa11
where (pa11.WJXBFS1 <= 5.0)
create table ZZEA04 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ03 pa12
where a11.SUBCAT_ID = pa12.SUBCAT_ID
select pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.WJXBFS1 WJXBFS1,
pa11.WJXBFS2 WJXBFS2
from ZZEA04 pa11,
LU_SUBCATEG a12
where pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZAM00 pa11
where (pa11.WJXBFS1 <= 5.0)
select a11.SUBCAT_ID SUBCAT_ID,
a13.SUBCAT_DESC SUBCAT_DESC,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ01 pa12,
LU_SUBCATEG a13
where a11.SUBCAT_ID = pa12.SUBCAT_ID and
a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT base64(dt_binary)
FROM wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
SELECT c_to_string(dt_binary)
FROM wh2.dt_fact_table1;FunctionSQL PatternAdd(#0#< + #*#>) Average(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) AvgAVG(#0#<#*#>) CountCOUNT(#0#<#*#>) GeoMeanFunctionEXP(AVG(LN(#0))) GreatestFunctionGREATEST(#0#<, #*#>) LeastFunctionLEAST(#0#<, #*#>) MaxMAX(#0#<#*#>) MinMIN(#0#<#*#>) Multiply(#0#< * #*#>) StdevFunctionSTDDEV(#0#< #*#>) SumSUM(#0#<#*#>) VarFunctionVAR(#0#< #*#>) FunctionSQL PatternConcatFunctionCONCAT(#0, #1) LengthFunctionLENGTH(#0)LowerFunctionLOWER(#0) LTrimFunctionLTRIM(#0) PositionFunctionLOCATE(#1, #0) RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2) InitCapFunction(UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) TrimFunctionLTRIM(RTRIM(#0)) RightStrFunctionRIGHT(#0, #1) LeftStrFunctionLEFT(#0, #1) UpperFunctionUPPER(#0) ConcatBlankFunction(#0#< || ' ' || #*#>) FunctionSQL PatternDaysBetweenFunctionDAYS_BETWEEN(#0, #1)YearEndDateFunctionADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0))CurrentDateFunctionNOW()DayOfMonthFunctionDAYOFMONTH(#0)DayOfWeekFunction(CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END)DayOfYearFunctionDAYOFYEAR(#0)WeekFunctionTO_CHAR(#0,'WW')MonthFunctionMONTH(#0)QuarterFunctionTO_CHAR (#0, 'Q')YearFunctionYEAR(#0)CurrentDateTimeFunctionNOW()CurrentTimeFunctionNOW()HourFunctionHOUR(#0)MinuteFunctionMINUTE(#0)SecondFunctionROUND(SECOND(#0))MilliSecondFunctionsubstring(cast(extract(second from #0) as char(10)), 4, 3)MonthStartDateFunctionADD_DAYS(#0,1-DAYOFMONTH(#0))YearStartDateFunctionADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0)))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunction(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))AddDaysFunctionADD_DAYS(#0, #1)MonthEndDateFunctionADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1))))DateFunctionTO_DATE(#0,'YYYY-MM-DD')FunctionSQL PatternLagFunctionlag(#0#<, #*#>) over ([#P] [#O]) LeadFunctionlead(#0#<, #*#>) over ([#P] [#O]) OLAPRankFunctionrank() over ([#P] [#O])FunctionSQL PatternRound2FunctionROUND(#0, #1)RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>Int2FunctionFLOOR(#0)AcosFunctionACOS(#0)AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#1, #0)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEILING(#0)CosFunctionCOS(#0)CoshFunctionCOSH(#0)ExpFunctionEXP(#0)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#1, #0)Log10FunctionLOG(10,#0)PowerFunctionPOWER(#0, #1)QuotientFunction
(CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END)RoundFunctionROUND(#0, 0)SinFunctionSIN(#0)SinhFunctionSINH(#0)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunctionTANH(#0)TruncFunctionCAST(#0 AS INTEGER)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunctionMOD(#0, #1)ItemExampleApplication server versionTomcat 7.0, IIS 7.5, etc.Application server operating systemWindows 2008 R2Operating system bit type64-bitStrategy Web version9.4.1 Hotfix 9Machine physical memory8 GBNumber of CPUs and speedDual core @ 2.4 GhzJava version1.8.0 update 60Java max heap size2048 MOther Java parametersVerbose GC loggingAverage user concurrency on peak times100 | Explicit
create table ZZEA00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11create table ZZAM00 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from SUBCAT_SLS a11 create table ZZAM01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZEA00 pa11
create table ZZEA02 nologging as
select pa11.SUBCAT_ID SUBCAT_ID,
pa11.WJXBFS1 WJXBFS1
from ZZAM01 pa11 create table ZZMQ03 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZEA02 pa11
where (pa11.WJXBFS1 <= 5.0)
create table ZZEA04 nologging as
select a11.SUBCAT_ID SUBCAT_ID,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ03 pa12
where a11.SUBCAT_ID = pa12.SUBCAT_ID
select pa11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
pa11.WJXBFS1 WJXBFS1,
pa11.WJXBFS2 WJXBFS2
from ZZEA04 pa11,
LU_SUBCATEG a12
where pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select pa11.SUBCAT_ID SUBCAT_ID
from ZZAM00 pa11
where (pa11.WJXBFS1 <= 5.0)
select a11.SUBCAT_ID SUBCAT_ID,
a13.SUBCAT_DESC SUBCAT_DESC,
a11.TOT_DOLLAR_SALES WJXBFS1,
(a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from SUBCAT_SLS a11,
ZZMQ01 pa12,
LU_SUBCATEG a13
where a11.SUBCAT_ID = pa12.SUBCAT_ID and
a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT base64(dt_binary)
FROM wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';
SELECT c_to_string(dt_binary)
FROM wh2.dt_fact_table1;FunctionSQL PatternAdd(#0#< + #*#>) Average(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>) AvgAVG(#0#<#*#>) CountCOUNT(#0#<#*#>) GeoMeanFunctionEXP(AVG(LN(#0))) GreatestFunctionGREATEST(#0#<, #*#>) LeastFunctionLEAST(#0#<, #*#>) MaxMAX(#0#<#*#>) MinMIN(#0#<#*#>) Multiply(#0#< * #*#>) StdevFunctionSTDDEV(#0#< #*#>) SumSUM(#0#<#*#>) VarFunctionVAR(#0#< #*#>) FunctionSQL PatternConcatFunctionCONCAT(#0, #1) LengthFunctionLENGTH(#0)LowerFunctionLOWER(#0) LTrimFunctionLTRIM(#0) PositionFunctionLOCATE(#1, #0) RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2) InitCapFunction(UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) TrimFunctionLTRIM(RTRIM(#0)) RightStrFunctionRIGHT(#0, #1) LeftStrFunctionLEFT(#0, #1) UpperFunctionUPPER(#0) ConcatBlankFunction(#0#< || ' ' || #*#>) FunctionSQL PatternDaysBetweenFunctionDAYS_BETWEEN(#0, #1)YearEndDateFunctionADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0))CurrentDateFunctionNOW()DayOfMonthFunctionDAYOFMONTH(#0)DayOfWeekFunction(CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END)DayOfYearFunctionDAYOFYEAR(#0)WeekFunctionTO_CHAR(#0,'WW')MonthFunctionMONTH(#0)QuarterFunctionTO_CHAR (#0, 'Q')YearFunctionYEAR(#0)CurrentDateTimeFunctionNOW()CurrentTimeFunctionNOW()HourFunctionHOUR(#0)MinuteFunctionMINUTE(#0)SecondFunctionROUND(SECOND(#0))MilliSecondFunctionsubstring(cast(extract(second from #0) as char(10)), 4, 3)MonthStartDateFunctionADD_DAYS(#0,1-DAYOFMONTH(#0))YearStartDateFunctionADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0)))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunction(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))AddDaysFunctionADD_DAYS(#0, #1)MonthEndDateFunctionADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1))))DateFunctionTO_DATE(#0,'YYYY-MM-DD')FunctionSQL PatternLagFunctionlag(#0#<, #*#>) over ([#P] [#O]) LeadFunctionlead(#0#<, #*#>) over ([#P] [#O]) OLAPRankFunctionrank() over ([#P] [#O])FunctionSQL PatternRound2FunctionROUND(#0, #1)RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>Int2FunctionFLOOR(#0)AcosFunctionACOS(#0)AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#1, #0)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEILING(#0)CosFunctionCOS(#0)CoshFunctionCOSH(#0)ExpFunctionEXP(#0)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#1, #0)Log10FunctionLOG(10,#0)PowerFunctionPOWER(#0, #1)QuotientFunction
(CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END)RoundFunctionROUND(#0, 0)SinFunctionSIN(#0)SinhFunctionSINH(#0)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunctionTANH(#0)TruncFunctionCAST(#0 AS INTEGER)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunctionMOD(#0, #1)ItemExampleApplication server versionTomcat 7.0, IIS 7.5, etc.Application server operating systemWindows 2008 R2Operating system bit type64-bitStrategy Web version9.4.1 Hotfix 9Machine physical memory8 GBNumber of CPUs and speedDual core @ 2.4 GhzJava version1.8.0 update 60Java max heap size2048 MOther Java parametersVerbose GC loggingAverage user concurrency on peak times100 |
