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