в 13:46, 26 мая 2010

2010-05-26T13:46:16Z

Admin: 1 версия

2010-05-26T10:22:56Z

1 версия

Новая страница

== 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>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL Tutorial/Analytical Functions/GROUPING - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия