SQL Server/T-SQL Tutorial/Analytical Functions/PARTITION
Using several columns from a table to build different partitioning schemas in a query.
6>
7> create table department(
8> dept_name char(20) not null,
9> emp_cnt int not null,
10> budget float,
11> date_month datetime);
12> GO
1>
2> insert into department values("Research", 5, 50000, "01.01.2002");
3> insert into department values("Research", 10, 70000, "01.02.2002");
4> insert into department values("Research", 5, 65000, "01.07.2002");
5> insert into department values("Accounting", 5, 10000, "01.07.2002");
6> insert into department values("Accounting", 10, 40000, "01.02.2002");
7> insert into department values("Accounting", 6, 30000, "01.01.2002");
8> insert into department values("Accounting", 6, 40000, "01.02.2003");
9> insert into department values("Marketing", 6, 10000, "01.01.2003");
10> insert into department values("Marketing", 10, 40000, "01.02.2003");
11> insert into department values("Marketing", 3, 30000, "01.07.2003");
12> insert into department values("Marketing", 5, 40000, "01.01.2003");
13> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT dept_name, CAST(budget AS INT) AS budget,
3> SUM(emp_cnt) OVER(PARTITION BY budget) AS emp_cnt_sum,
4> AVG(budget) OVER(PARTITION BY dept_name) AS budget_avg
5> FROM department;
6> GO
dept_name budget emp_cnt_sum budget_avg
-------------------- ----------- ----------- ------------------------
Accounting 10000 11 30000
Accounting 30000 9 30000
Accounting 40000 31 30000
Accounting 40000 31 30000
Marketing 40000 31 30000
Marketing 30000 9 30000
Marketing 40000 31 30000
Marketing 10000 11 30000
Research 50000 5 61666.666666666664
Research 65000 5 61666.666666666664
Research 70000 10 61666.666666666664
(11 rows affected)
1> drop table department;
2> GO
1>
Window function can be an aggregate function.
8>
9>
10> create table department(
11> dept_name char(20) not null,
12> emp_cnt int not null,
13> budget float,
14> date_month datetime);
15> GO
1>
2> insert into department values("Research", 5, 50000, "01.01.2002");
3> insert into department values("Research", 10, 70000, "01.02.2002");
4> insert into department values("Research", 5, 65000, "01.07.2002");
5> insert into department values("Accounting", 5, 10000, "01.07.2002");
6> insert into department values("Accounting", 10, 40000, "01.02.2002");
7> insert into department values("Accounting", 6, 30000, "01.01.2002");
8> insert into department values("Accounting", 6, 40000, "01.02.2003");
9> insert into department values("Marketing", 6, 10000, "01.01.2003");
10> insert into department values("Marketing", 10, 40000, "01.02.2003");
11> insert into department values("Marketing", 3, 30000, "01.07.2003");
12> insert into department values("Marketing", 5, 40000, "01.01.2003");
13> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT dept_name, budget, SUM(budget) OVER(PARTITION BY dept_name) as
3> budget_sum,
4> budget/SUM(budget) OVER(PARTITION BY dept_name)* 100 AS percentage
5> FROM department;
6> GO
dept_name budget budget_sum percentage
-------------------- ------------------------ ------------------------ ------------------------
Accounting 10000 120000 8.3333333333333321
Accounting 40000 120000 33.333333333333329
Accounting 30000 120000 25
Accounting 40000 120000 33.333333333333329
Marketing 10000 120000 8.3333333333333321
Marketing 40000 120000 33.333333333333329
Marketing 30000 120000 25
Marketing 40000 120000 33.333333333333329
Research 50000 185000 27.027027027027028
Research 70000 185000 37.837837837837839
Research 65000 185000 35.135135135135137
(11 rows affected)
1>
2>
3> drop table department;
4> GO
1>
Window function can be a ranking function
5>
6> create table department(
7> dept_name char(20) not null,
8> emp_cnt int not null,
9> budget float,
10> date_month datetime);
11> GO
1>
2> insert into department values("Research", 5, 50000, "01.01.2002");
3> insert into department values("Research", 10, 70000, "01.02.2002");
4> insert into department values("Research", 5, 65000, "01.07.2002");
5> insert into department values("Accounting", 5, 10000, "01.07.2002");
6> insert into department values("Accounting", 10, 40000, "01.02.2002");
7> insert into department values("Accounting", 6, 30000, "01.01.2002");
8> insert into department values("Accounting", 6, 40000, "01.02.2003");
9> insert into department values("Marketing", 6, 10000, "01.01.2003");
10> insert into department values("Marketing", 10, 40000, "01.02.2003");
11> insert into department values("Marketing", 3, 30000, "01.07.2003");
12> insert into department values("Marketing", 5, 40000, "01.01.2003");
13> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT dept_name, budget,
3> SUM(emp_cnt) OVER(PARTITION BY dept_name) AS emp_cnt_sum,
4> AVG(budget) OVER(PARTITION BY dept_name) AS budget_avg,
5> COUNT(dept_name) OVER(PARTITION BY dept_name) AS dept_cnt
6> FROM department;
7> GO
dept_name budget emp_cnt_sum budget_avg dept_cnt
-------------------- ------------------------ ----------- ------------------------ -----------
Accounting 10000 27 30000 4
Accounting 40000 27 30000 4
Accounting 30000 27 30000 4
Accounting 40000 27 30000 4
Marketing 10000 24 30000 4
Marketing 40000 24 30000 4
Marketing 30000 24 30000 4
Marketing 40000 24 30000 4
Research 50000 20 61666.666666666664 3
Research 70000 20 61666.666666666664 3
Research 65000 20 61666.666666666664 3
(11 rows affected)
1>
2>
3> drop table department;
4> GO
1>
2>