SAPHANA学习（8）：SQL Function(G)

/*

68.GENERATE_PASSWORD

GENERATE_PASSWORD( <password_length> [, <usergroup_name>] )

生成password

*/

--不存在
--SELECT GENERATE_PASSWORD(16) FROM Dummy;

/*

69.GREATEST

GREATEST(<argument> [{, <argument>}...])

返回最大值

*/

SELECT GREATEST ('aa', 'ab', 'ba', 'bb') FROM DUMMY; 

/*

70.GROUPING

GROUPING(<column_name>)

分组

GROUPING_ID(<column_name_list>)

为每行分配分组id

*/

CREATE COLUMN TABLE CUSTOMERS (
  cust_id INTEGER NOT NULL,
  cust_name NVARCHAR(20),
  num_emp INTEGER,
  region NVARCHAR(20),
  s_tier NVARCHAR(20),
  PRIMARY KEY ("CUST_ID") );

INSERT INTO CUSTOMERS VALUES( 1, 'CustA', 5, 'NorthEast', 'gold' );
INSERT INTO CUSTOMERS VALUES( 2, 'CustB', 26, 'NorthWest', 'gold' );
INSERT INTO CUSTOMERS VALUES( 3, 'CustC', 250, 'NorthEast', 'silver' );
INSERT INTO CUSTOMERS VALUES( 4, 'CustD', 180, 'SouthEast', 'platinum' );
INSERT INTO CUSTOMERS VALUES( 5, 'CustE', 32, 'SouthWest', 'silver' );
INSERT INTO CUSTOMERS VALUES( 6, 'CustF', 45, 'NorthEast', 'platinum' );
INSERT INTO CUSTOMERS VALUES( 7, 'CustG', 15, 'NorthWest', 'platinum' );
INSERT INTO CUSTOMERS VALUES( 8, 'CustH', 99, 'SouthEast', 'gold' );
INSERT INTO CUSTOMERS VALUES( 9, 'CustI', 6, 'NorthEast', 'silver' );
INSERT INTO CUSTOMERS VALUES( 10,'CustJ', 101, 'NorthEast', 'silver' );
INSERT INTO CUSTOMERS VALUES( 11,'Custk', 108, 'SouthEast', 'silver' );

SELECT
    cust_name AS "cust_name",
       cust_id AS "cust_id",
       region AS "region",
       s_tier AS "s_tier",
       num_emp AS "num_emp",
 GROUPING (region) AS "gr_reg",
 GROUPING (s_tier) AS "gr_tier",
 GROUPING (num_emp) AS "gr_num" FROM CUSTOMERS
 GROUP BY GROUPING SETS (   
      (s_tier, region),   
      (region, s_tier),    
      (cust_id, cust_name, num_emp)
 );

 --根据分组分配Customer id
 SELECT cust_id,cust_name,region,s_tier, SUM(num_emp),
    GROUPING_ID(cust_id,cust_name,region)  
    FROM CUSTOMERS 
    GROUP BY GROUPING SETS (
    (cust_id,cust_name,region),
    (cust_id,cust_name), 
    (cust_id,region),
    (cust_name,region),
    (cust_id),
    (cust_name),
    (region),
    (s_tier));