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

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

Using several columns from a table to build different partitioning schemas in a query.

   <source lang="sql">

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></source>


Window function can be an aggregate function.

   <source lang="sql">

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></source>


Window function can be a ranking function

   <source lang="sql">

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></source>

Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL_Tutorial/Analytical_Functions/PARTITION&oldid=6413»