Преобразование значений null. Программирование

Программирование >> Преобразование значений null

1 ... 159 160 161 [ 162 ] 163 164 165 ... 219

select e.hiredate, e.sal,

(select sum(sal) from emp d where d.hiredate between e.hiredate-90

and e.hiredate) as spending pattern

from emp e order by 1

HIREDATE		SPENDING PATTERN
17-DEC-1980
20-FEB-1981	1600	2400
22-FEB-1981	1250	3650
02-APR-1981	2975	5825
01-MAY-1981	2850	8675
09-JUN-1981	2450	8275
08-SEP-1981	1500	1500
28-SEP-1981	1250	2750
17-NOV-1981	5000	7750
03-DEC-1981		11700
03-DEC-1981	3000	11700
23-JAN-1982	1300	10250
09-DEC-1982	3000	3000
12-JAN-1983	1100	4100

Разворачивание результирующего множества, содержащего подсуммы

Задача

Требуется вычислить подсуммы, создать отчет и транспонировать его, чтобы обеспечить более наглядный результат. Например, поставлена задача создать отчет, представляющий руководителей каждого отдела и суммы заработных плат подчиненных каждого руководителя. Кроме того, необходимо получить две подсуммы: сумму всех заработных плат по отделам для служащих, работающих в чьем-то подчинении, и сумму всех заработных плат (сумму подсумм отдела). На данный момент имеется следующий отчет:

DEPTNO
	7782	1300
	7839	2450
		3750
	7566	6000
	7788	1100
	7839	2975
	7902
		10875
	7698	6550
	7839	2850

9400 24025

Необходимо сделать отчет более удобным для чтения и преобразовать приведенное выше результирующее множество в следующее:

	DEPT10	DEPT20	DEPT30	TOTAL
7566		6000
7698			6550
7782	1300
7788		1100
7839	2450	2975	2850
7902
	3750	10875	9400	24025

Решение

Первый шаг - получить подсуммы, используя расширение ROLLUP оператора GROUP BY. Следующий шаг - выполнить классический разворот (с помощью агрегатной функции и выражения CASE) для создания необходимых столбцов отчета. Функция GROUPING обеспечивает возможность без труда определять значения, являющиеся подсуммами (т. е. полученные в результате выполнения ROLLUP). В зависимости от того, как сортируются значения NULL в используемой СУБД, может потребоваться добавить в решение оператор ORDER BY, чтобы получить такое же результирующее множество, как представлено выше.

DB2 и Oracle

Используйте расширение ROLLUP оператора GROUP BY и затем выражение CASE для представления данных в более удобном формате:

	select	mgr,
		sum(case deptno when 10 then sal else 0 end) dept10,
		sum(case deptno when 20 then sal else 0 end) dept20,
		sum(case deptno when 30 then sal else 0 end) dept30,
		sum(case flag when 11 then sal else null end) total
	from
	select	deptno,mgr,sum(sal) sal,
		cast(grouping(deptno) as char(1))
		cast(grouping(mgr) as char(1)) flag
	from
	where	mgr is not null
	group	by rollup(deptno,mgr)

	group	by mgr
SQL Server

	select	mgr,
		sum(case deptno when 10 then sal else 0 end) dept10,
		sum(case deptno when 20 then sal else 0 end) dept20,
		sum(case deptno when 30 then sal else 0 end) dept30,
		sum(case flag when 11 then sal else null end) total
	from
	select	deptno,mgr,sum(sal) sal,
		cast(grouping(deptno) as char(1))+
		cast(grouping(mgr) as char(1)) flag
	from
	where	mgr is not null
	group	by deptno,mgr with rollup

	group	by mgr

MySQL и PostgreSQL

Функция GROUPING не поддерживается ни одной из этих СУБД.

Обсуждение

Приведенные выше решения идентичны, за исключением строки конкатенации и описания GROUPING, поэтому промежуточные результаты обсудим на примере решения для SQL Server (все сказанное здесь будет правомочно и для DB2, и для Oracle).

Первый шаг - сформировать результирующее множество, суммируя значения SAL всех подчиненных каждого руководителя (MGR) для каждого отдела (DEPTNO). Идея в том, чтобы показать, сколько служащих подчиняется каждому руководителю в каждом отделе. Например, приведенный ниже запрос позволит сравнить заработные платы подчиненных руководителя KING из 10-го отдела с заработными платами подчиненных KING их 30-го отдела.

select	deptno,mgr,sum(sal) sal
from
where	mgr is not null
group	by mgr,deptno
order	by 1,2
DEPTNO
	7782	1300
	7839	2450
	7566	6000
	7788	1100
	7839	2975
	7902
	7698	6550
	7839	2850

1 ... 159 160 161 [ 162 ] 163 164 165 ... 219