SAPHANA学习（16）：SQL Function(P)

107. PERCENT_RANK

PERCENT_RANK() <window_specification>

SELECT "CLASS", "VAL", "OFFSET", 
       PERCENT_RANK() OVER (PARTITION BY "CLASS" ORDER BY "VAL" ASC) AS PER_RANK
       FROM "TEST_RANK" ORDER BY "VAL" DESC;

108. PERCENTILE_CONT

PERCENTILE_CONT( <constant_literal> )

WITHIN GROUP ( ORDER BY <expression> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] ) <window_specification>

NR = the number of rows in the window partition R*P = the specified percentile value**

INDEX1 = floor( 1 + ( P * (NR - 1) ) )

INDEX2 = ceil( 1 + ( P * (NR - 1) ) )

COEF1 = INDEX2 - ( 1 + ( P * (NR - 1) ) )

COEF2 = INDEX1 - ( 1 + ( P * (NR - 1) ) )

if INDEX1 == INDEX2

then returns (value at INDEX1)

else returns ( ( value at INDEX1 ) * COEF1 + ( value at INDEX2 ) * COEF2 )

PERCENTILE_DISC( <constant_literal> )

WITHIN GROUP ( ORDER BY <expression> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] ) <window_specification>

SELECT
  class,
  val, 
  PERCENTILE_CONT(0.125) WITHIN GROUP (ORDER BY val) OVER (PARTITION BY class) AS pc1, 
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY val) OVER (PARTITION BY class) AS pc2, 
  PERCENTILE_CONT(0.875) WITHIN GROUP (ORDER BY val) OVER (PARTITION BY class) AS pc3
 FROM TEST_RANK;

SELECT
  class,
  val, 
  PERCENTILE_DISC(0.125) WITHIN GROUP (ORDER BY val) OVER (PARTITION BY class) AS pd1,
  PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) OVER (PARTITION BY class) AS pd2, 
  PERCENTILE_DISC(0.875) WITHIN GROUP (ORDER BY val) OVER (PARTITION BY class) AS pd3
 FROM TEST_RANK;

109. PLAINTEXT

PLAINTEXT(<column_name>)

returns the plain text representation of a value in a column that has a full text index.

CREATE COLUMN TABLE TEST_PLAINTEXT (CONTENT BLOB);
CREATE FULLTEXT INDEX I1 ON TEST_PLAINTEXT(CONTENT);
INSERT INTO TEST_PLAINTEXT VALUES ('<somePDF>');
SELECT PLAINTEXT(CONTENT),CONTENT FROM TEST_PLAINTEXT;

110. POWER

POWER(<base>, <exponent>)

计算<base>的<exponent>次方

SELECT POWER (2, 10) FROM DUMMY;
SELECT POWER (3, 3) FROM DUMMY;