lois meg

mayorwest tchi

brian sizlack

chris cleveland flanders ken

И, наконец, чтобы создать пригодное для восприятия человеком результирующее множество, к возвращенным SUBSTR значениям применяем агрегатную функцию, проводя при этом группировку по ID:

with cartesian as ( select level id from dual

connect by level <= 100

select max(decode(id,1,substr(strings,p1+1,p2-1))) val1,

max(decode(id,2,substr(strings,p1+1,p2-1))) val2,

max(decode(id,3,substr(strings,p1+1,p2-1))) val3 from ( select v.strings,

c.id,

instr(v.strings,:,1,c.id) p1,

instr(v.strings,:,1,c.id+1)-instr(v.strings,:,1,c.id) p2 from v,cartesian c where c.id <= (length(v.strings)-length(replace(v.strings,:)))-1 )

group by strings order by 1

VAL1

VAL2

VAL3

petergriffin quagmire

robo

stewiegriffin willie

mayorwest

tchi lois

sizlack

chris

cleveland

brian

flanders

Определение доли от целого впроцентномвыражении

Задача

Требуется вывести множество числовых значений, представив каждое из них как долю от целого в процентном выражении. Например, стоит

Обсуждение

Первый шаг - с помощью оконной функции COUNT OVER получить количество служащих для каждого значения JOB. Затем, используя RATIO TO REPORT, возвратить долю суммарной заработной платы для каждой должности от общей заработной платы (в виде десятичной

дроби):

select job,

count(*)over(partition by job) num emps,

ratio to report(sal)over()*100 pct from emp

JOB NUM EMPS PCT

задача получить в системе Oracle результирующее множество, отражающее распределение заработных плат по должностям, чтобы можно было увидеть, какая из позиций JOB обходится компании дороже всего. Кроме того, чтобы данные были истолкованы правильно, должно быть приведено количество служащих, занимающих каждую из должностей. Ожидается получить следующее результирующее множество:

JOB NUM EMPS PCT OF ALL SALARIES

CLERK 4 14

ANALYST 2 20

MANAGER 3 28

SALESMAN 4 19

PRESIDENT 1 17

Как видите, если бы в отчет не было включено количество служащих, могло бы показаться, что президент получает самую маленькую заработную плату. И только информация о том, что ее получает один человек, обеспечивает представление о том, что означают эти 17% от общей суммы заработных плат на самом деле.

Решение

Только Oracle обеспечивает достойное решение этой задачи, в котором используется встроенная функция RATIO TO REPORT. Чтобы подсчитать процентные соотношения в других базах данных, можно использовать деление, как показано в разделе Вычисление доли от целого в процентном выражении в главе 7:

1 select ]ob,num emps,sum(round(pct)) pct of all salaries

2 from (

3 select job,

4 count(*)over(partition by job) num emps,

5 ratio to report(sal)over()*100 pct

6 from emp

8 group by job,num emps

Последний шаг - посредством агрегатной функции SUM суммировать возвращенные RATIO TO REPORT значения. Не забудьте группировать по JOB и NUM EMPS. Умножаем на 100, чтобы выражение доли в процентах было представлено целым числом (например, чтобы 25% были представлены как 25, а не 0,25):

select job,num emps,sum(round(pct)) pct of all salaries

from ( select job,

count(*)over(partition by job) num emps, ratio to report(sal)over()*100 pct from emp )

group by job,num emps

JOB NUM EMPS PCT OF ALL SALARIES

CLERK 4 14

ANALYST 2 20

MANAGER 3 28

SALESMAN 4 19

PRESIDENT 1 17

Создание списка разделенных запятыми значений в Oracle

Задача

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

DEPTNO LIST

10 MILLER,KING,CLARK

20 FORD,ADAMS,SCOTT,JONES,SMITH

30 JAMES,TURNER,BLAKE,MARTIN,WARD,ALLEN