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