SAPHANA学习（13）：SQL Function(M)

/*

94.MAP

MAP(<expression>, <search_value>, <result> [, <search_value>, <result> [...] ] [, <default_result>])

查找<expression>中指定<search_value>是否存在，若存在返回<result>,若不存在返回<default_result>,<default_result>没设置，返回null

*/

--返回‘Two’
SELECT MAP(2, 0, 'Zero', 1, 'One', 2, 'Two', 3, 'Three', 'Default') "map" FROM DUMMY;

--返回‘Default’
SELECT MAP(99, 0, 'Zero', 1, 'One', 2, 'Two', 3, 'Three', 'Default') "map" FROM DUMMY;

--返回‘null’
SELECT MAP(99, 0, 'Zero', 1, 'One', 2, 'Two', 3, 'Three') "map" FROM DUMMY;

/*

95.MAX()

获取表达式，列值中最大值

Aggregate function:

MAX( [ ALL | DISTINCT ] <expression> )

Window function:

MAX( <expression> ) <window_specification>

 

MEDIAN()

获取表达式，列值中间值

Aggregate function:

MEDIAN( <expression> )

Window function:

MEDIAN( <expression> ) <window_specification>

 

MIN()

Aggregate function:

MIN( [ ALL | DISTINCT ] <expression> )

Window function:

MIN( <expression> ) <window_specification>

*/

--最大值
SELECT MAX("Price") FROM "MyProducts";

--中间值
--示例：[1,2,5],中间值为2;
--示例：[1,null,3],null不计入计算，中间值为(1+3)/2 = 2;
--示例:[1,2,4,6],中间值计算(2+4)/2 = 3
SELECT MEDIAN("Price") FROM "MyProducts";

--按照"Category"分组，默认升序排序
SELECT MEDIAN("Price") OVER(PARTITION BY "Category") FROM "MyProducts";

--按照"Category"分组
SELECT MEDIAN("Price") OVER(PARTITION BY "Category" ORDER BY "Quantity") FROM "MyProducts";

--按照"Category"分组
--分组统计中间值，排序后[A,B,C,D]； [A,(A+B)/2,B,(B+C)/2]
SELECT MEDIAN("Price") OVER (PARTITION BY "Category" ORDER BY "Quantity" GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM "MyProducts";

--最小值
SELECT MIN("Price") FROM "MyProducts";

/*

96.MENBER_AT

MEMBER_AT(<array_value_expression>, <position> [, <default_value>])

返回array指定位置元素

*/

--如果没有设置<default_value>，返回<position>大于<array>的长度，返回null
SELECT MEMBER_AT(VAL,4) FROM ARRAY_TEST;
SELECT MEMBER_AT(VAL,4,0) FROM ARRAY_TEST;

/*

97.MIMETYPE

MIMETYPE(<column_name>)

返回列的MIME类型

*/

SELECT MIMETYPE(CONTENT),CONTENT FROM TEST_INDEX_ERROR_CODE;

/*

98.MINUTE

MINUTE(<time>)

返回分钟

 

MONTH(<date>)

返回月份

 

MONTHNAME(<date>)

返回月份英文名称

 

MONTHS_BETWEEN(<date_1>, <date_2>)

返回两个日期之间月份数

 

NEXT_DAY(<date>)

返回下一天

 

NOW()

返回当前时间戳

*/

--返回分钟
SELECT MINUTE ('12:34:56') FROM DUMMY;

--返回月份
SELECT MONTH ('2011-05-30') FROM DUMMY;

--返回月份名
SELECT MONTHNAME ('2011-05-30') FROM DUMMY;

--返回2
SELECT MONTHS_BETWEEN('2003-01-01','2003-03-14') FROM DUMMY;

--返回-9
SELECT MONTHS_BETWEEN('2004-01-01','2003-03-14') FROM DUMMY;

--返回下一天
SELECT NEXT_DAY('2009-12-31') FROM DUMMY;

--返回当前时间戳
SELECT NOW () FROM DUMMY;

/*

99.MOD

MOD(<number>, <divisor>)

取余

 

NDIV0( <numerator>, <denominator> )

当除数为0，返回0；

*/

SELECT MOD (9, 4) FROM DUMMY;
--如果<divisor>等于0，返回<number>？,报错
--SELECT MOD (9, 0) FROM DUMMY;

--返回-1
SELECT MOD (-9, 4) FROM DUMMY;

--如果<number>小于0，<number>大于<divisor>,返回<number>
SELECT MOD (-9, -10) FROM DUMMY;

--1/0 = 0,不存在？
--SELECT NDIV0(1, 0) FROM DUMMY;