Mega Code Archive
Using several columns from a table to build different partitioning schemas in a query
<![CDATA[
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
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>
]]>