Phoenix常用函数列举及说明

痛定思痛。 2022-02-17 00:53 721阅读 0赞

### 这里写自定义目录标题 ###

函数来源于： [https://blog.csdn.net/lihui6636/article/details/40835645][https_blog.csdn.net_lihui6636_article_details_40835645]

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>AVG ( numericTerm )</td> 
   <td>计算平均值</td> 
  </tr> 
  <tr> 
   <td>COUNT (Term)</td> 
   <td>计算term的总数</td> 
  </tr> 
  <tr> 
   <td>COUNT (DISTINCT Term)</td> 
   <td>去重求总数</td> 
  </tr> 
  <tr> 
   <td>MAX ( term )</td> 
   <td>求最大值</td> 
  </tr> 
  <tr> 
   <td>MIN ( term )</td> 
   <td>求最小值</td> 
  </tr> 
  <tr> 
   <td>SUM ( numericTerm )</td> 
   <td>字段内容求和</td> 
  </tr> 
  <tr> 
   <td>PERCENTILE_CONT( 0.9 ) WITHIN GROUP (ORDER BY X ASC)</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>PERCENTILE_DISC( 0.9 ) WITHIN GROUP (ORDER BY X DESC)</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>PERCENT_RANK( 100 ) WITHIN GROUP (ORDER BY X ASC)</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>FIRST_VALUE( name ) WITHIN GROUP (ORDER BY salary DESC)</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>LAST_VALUE( name ) WITHIN GROUP (ORDER BY salary DESC)</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>NTH_VALUE( name, 2 ) WITHIN GROUP (ORDER BY salary DESC)</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>STDDEV_POP( X )</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>STDDEV_SAMP( X )</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>ARRAY_ELEM(my_array_col, 5)</td> 
   <td>获取my_array_col字段的角标为6的元素</td> 
  </tr> 
  <tr> 
   <td>ARRAY_LENGTH(my_array_col)</td> 
   <td>获取my_array_col（集合）字段的长度</td> 
  </tr> 
  <tr> 
   <td>10 &gt; ANY(my_array)</td> 
   <td>my_array字段中存在一个元素小于10</td> 
  </tr> 
  <tr> 
   <td>10 &gt; ALL(my_array)</td> 
   <td>my_array字段中所有元素都小于10</td> 
  </tr> 
  <tr> 
   <td>MD5(my_column)</td> 
   <td>对my_column字段进行MD5加密</td> 
  </tr> 
  <tr> 
   <td>INVERT(my_column)</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>ENCODE(myNumber, ‘BASE62’)</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>DECODE(‘000000008512af277ffffff8’, ‘HEX’)</td> 
   <td>对字符串进行hex转码</td> 
  </tr> 
  <tr> 
   <td>COALESCE(last_update_date, CURRENT_DATE())</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>ROUND(number, 2)</td> 
   <td>截取2位有效数字</td> 
  </tr> 
  <tr> 
   <td>CEIL(number, 3)</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>FLOOR(date, ‘DAY’, 7)</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>TRUNC(timestamp, ‘SECOND’, 30)</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>TO_NUMBER(‘123’)</td> 
   <td>字符串转数字，数字较大会转成科学计数法</td> 
  </tr> 
  <tr> 
   <td>SUBSTR(‘Hello World’, -5)</td> 
   <td>截取前五个字符</td> 
  </tr> 
  <tr> 
   <td>TRIM(’ Hello ')</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>LTRIM(’ Hello’)</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>RTRIM('Hello ')</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>LPAD(‘John’,30)</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>LENGTH(‘Hello’)</td> 
   <td>获取字符串的长度</td> 
  </tr> 
  <tr> 
   <td>REGEXP_SUBSTR(‘na1-appsrv35-sj35’, ‘[^-]+’) evaluates to ‘na1’</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>REGEXP_REPLACE(‘abc123ABC’, ‘[0-9]+’, ‘#’) evaluates to ‘abc#ABC’</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>REGEXP_SPLIT(‘one,two,three’, ‘,’) evaluates to [‘one’,‘two’,‘three’]</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>UPPER(‘Hello’)</td> 
   <td>将字符串中所有字母大写</td> 
  </tr> 
  <tr> 
   <td>LOWER(‘HELLO’)</td> 
   <td>将字符串中所有字母小写</td> 
  </tr> 
  <tr> 
   <td>REVERSE(‘Hello’)</td> 
   <td>将字符串中所有字母顺序翻转</td> 
  </tr> 
  <tr> 
   <td>TO_CHAR(myDate, ‘2001-02-03 04:05:06’)</td> 
   <td>将时间类型字段转换成字符串结构的字符串</td> 
  </tr> 
  <tr> 
   <td>TO_DATE(‘Sat, 3 Feb 2001 03:05:06 GMT’, ‘EEE, d MMM yyyy HH:mm:ss z’)</td> 
   <td>将字符串转换成时间类型的内容，结构按照传入结构</td> 
  </tr> 
  <tr> 
   <td>CURRENT_DATE()</td> 
   <td>获取当前机器时间（在客户端中为日期）</td> 
  </tr> 
  <tr> 
   <td>CURRENT_TIME()</td> 
   <td>获取当前机器时间（在客户端中为时间）</td> 
  </tr> 
  <tr> 
   <td>CONVERT_TZ(myDate, ‘UTC’, ‘Europe/Prague’)</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>TIMEZONE_OFFSET(‘Indian/Cocos’, myDate)</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

[https_blog.csdn.net_lihui6636_article_details_40835645]: https://blog.csdn.net/lihui6636/article/details/40835645