SQL Server/T-SQL Tutorial/Analytical Functions/GROUPING

Материал из SQL эксперт
Перейти к: навигация, поиск

GROUPING() Comes to the Rescue

   <source lang="sql">

You use the GROUPING() function with an expression that might contain NULLs. GROUPING() function returning value is only relevant when the value is NULL. GROUPING() function returns 1 when the NULL represents a super aggregate. GROUPING() function returns 0 when the NULL represents a group of NULLs in the base table. CUBE Query and the GROUPING() function 10> 11> CREATE TABLE employee( 12> id INTEGER NOT NULL PRIMARY KEY, 13> first_name VARCHAR(10), 14> last_name VARCHAR(10), 15> salary DECIMAL(10,2), 16> start_Date DATETIME, 17> region VARCHAR(10), 18> city VARCHAR(20), 19> managerid INTEGER 20> ); 21> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid


---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
         1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
         2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
         3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
         4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
         5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
         6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
         7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
         8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
         9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10

(9 rows affected) 1> 2> 3> 4> SELECT 5> ID, 6> GROUPING(ID) AS Grp_Cust, 7> YEAR(Start_Date) AS Order_Year, 8> GROUPING(YEAR(Start_Date)) AS Grp_Year, 9> COUNT(*) as Order_Count 10> FROM 11> Employee 12> GROUP BY 13> ID, 14> YEAR(Start_Date) 15> WITH CUBE 16> GO ID Grp_Cust Order_Year Grp_Year Order_Count


-------- ----------- -------- -----------
         1        0        2005        0           1
         1        0        NULL        1           1
         2        0        2003        0           1
         2        0        NULL        1           1
         3        0        2001        0           1
         3        0        NULL        1           1
         4        0        2006        0           1
         4        0        NULL        1           1
         5        0        2004        0           1
         5        0        NULL        1           1
         6        0        2002        0           1
         6        0        NULL        1           1
         7        0        2008        0           1
         7        0        NULL        1           1
         8        0        2007        0           1
         8        0        NULL        1           1
         9        0        2001        0           1
         9        0        NULL        1           1
      NULL        1        NULL        1           9
      NULL        1        2001        0           2
      NULL        1        2002        0           1
      NULL        1        2003        0           1
      NULL        1        2004        0           1
      NULL        1        2005        0           1
      NULL        1        2006        0           1
      NULL        1        2007        0           1
      NULL        1        2008        0           1

(27 rows affected) 1> 2> drop table employee; 3> GO</source>


GROUPING() function returns a bit value (1 or 0) to indicate that a row is a rollup.

   <source lang="sql">

This makes it easy to separate the aggregation of null values. 6> 7> CREATE TABLE employee( 8> id INTEGER NOT NULL PRIMARY KEY, 9> first_name VARCHAR(10), 10> last_name VARCHAR(10), 11> salary DECIMAL(10,2), 12> start_Date DATETIME, 13> region VARCHAR(10), 14> city VARCHAR(20), 15> managerid INTEGER 16> ); 17> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid


---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
         1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
         2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
         3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
         4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
         5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
         6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
         7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
         8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
         9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10

(9 rows affected) 1> 2> 3> 4> SELECT Region,GROUPING(Region) As SO_Group 5> FROM Employee 6> GROUP BY Region 7> WITH ROLLUP; 8> GO Region SO_Group


--------

East 0 North 0 South 0 West 0 NULL 1 (5 rows affected) 1> 2> drop table employee; 3> GO 1> 2></source>


Replacing NULLs with ALL and UNKNOWN

   <source lang="sql">

4> 5> CREATE TABLE employee( 6> id INTEGER NOT NULL PRIMARY KEY, 7> first_name VARCHAR(10), 8> last_name VARCHAR(10), 9> salary DECIMAL(10,2), 10> start_Date DATETIME, 11> region VARCHAR(10), 12> city VARCHAR(20), 13> managerid INTEGER 14> ); 15> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid


---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
         1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
         2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
         3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
         4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
         5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
         6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
         7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
         8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
         9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10

(9 rows affected) 1> 2> 3> 4> CREATE VIEW Vbase_cube 5> AS 6> SELECT 7> ID, 8> GROUPING(ID) AS Grp_Cust, 9> YEAR(Start_Date) AS Order_Year, 10> GROUPING(YEAR(Start_Date)) AS Grp_Year, 11> COUNT(*) as Order_Count 12> FROM 13> Employee 14> GROUP BY 15> ID, 16> YEAR(Start_Date) 17> WITH CUBE 18> GO 1> --Selecting All Rows from the Vbase_cube View 2> SELECT 3> * 4> FROM 5> Vbase_cube 6> GO ID Grp_Cust Order_Year Grp_Year Order_Count


-------- ----------- -------- -----------
         1        0        2005        0           1
         1        0        NULL        1           1
         2        0        2003        0           1
         2        0        NULL        1           1
         3        0        2001        0           1
         3        0        NULL        1           1
         4        0        2006        0           1
         4        0        NULL        1           1
         5        0        2004        0           1
         5        0        NULL        1           1
         6        0        2002        0           1
         6        0        NULL        1           1
         7        0        2008        0           1
         7        0        NULL        1           1
         8        0        2007        0           1
         8        0        NULL        1           1
         9        0        2001        0           1
         9        0        NULL        1           1
      NULL        1        NULL        1           9
      NULL        1        2001        0           2
      NULL        1        2002        0           1
      NULL        1        2003        0           1
      NULL        1        2004        0           1
      NULL        1        2005        0           1
      NULL        1        2006        0           1
      NULL        1        2007        0           1
      NULL        1        2008        0           1

(27 rows affected) 1> 2> SELECT 3> ISNULL(CAST(ID AS varchar(7)), 4> CASE Grp_Cust 5> WHEN 1 THEN "ALL" 6> ELSE "UNKNOWN" 7> END) AS Customer, 8> ISNULL(CAST(Order_Year AS varchar(7)), 9> CASE Grp_Year 10> WHEN 1 THEN "ALL" 11> ELSE "UNKNOWN" 12> END) AS Order_Year, 13> Order_Count 14> FROM 15> Vbase_cube 16> GO Customer Order_Year Order_Count


---------- -----------

1 2005 1 1 ALL 1 2 2003 1 2 ALL 1 3 2001 1 3 ALL 1 4 2006 1 4 ALL 1 5 2004 1 5 ALL 1 6 2002 1 6 ALL 1 7 2008 1 7 ALL 1 8 2007 1 8 ALL 1 9 2001 1 9 ALL 1 ALL ALL 9 ALL 2001 2 ALL 2002 1 ALL 2003 1 ALL 2004 1 ALL 2005 1 ALL 2006 1 ALL 2007 1 ALL 2008 1 (27 rows affected) 1> 2> drop view Vbase_cube 3> GO 1> drop table employee; 2> GO</source>


The syntax of the GROUPING function: GROUPING(column_name)

   <source lang="sql">

18> --A summary query that uses the GROUPING function 21> create table Bankers( 22> BankerID Integer, 23> BankerName VARCHAR(20), 24> BankerContactLName VARCHAR(20), 25> BankerContactFName VARCHAR(20), 26> BankerCity VARCHAR(20), 27> BankerState VARCHAR(20), 28> BankerZipCode VARCHAR(20), 29> BankerPhone VARCHAR(20) 30> ) 31> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> 3> 4> 5> SELECT 6> CASE 7> WHEN GROUPING(BankerState) = 1 THEN "All" 8> ELSE BankerState 9> END AS BankerState, 10> CASE 11> WHEN GROUPING(BankerCity) = 1 THEN "All" 12> ELSE BankerCity 13> END AS BankerCity, 14> COUNT(*) AS QtyBankers 15> FROM Bankers 16> WHERE BankerState IN ("IA", "NJ") 17> GROUP BY BankerState, BankerCity WITH ROLLUP 18> ORDER BY BankerState DESC, BankerCity DESC 19> GO BankerState BankerCity QtyBankers


-------------------- -----------

(0 rows affected) 1> 2> drop table Bankers; 3> GO 1> 2></source>