/*
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;