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;