Создание разреженной матрицы Задача

Требуется создать разреженную матрицу, такую как представлена ниже, в которой транспонированы столбцы DEPTNO и JOB таблицы EMP:

CLERKS MGRS PREZ ANALS SALES

CLARK KING

MILLER

SMITH JONES

SCOTT

ADAMS FORD

SMITH

ALLEN WARD

MARTIN BLAKE

TURNER JAMES

JONES

BLAKE CLARK

ALLEN WARD

MARTIN

SCOTT

KING

TURNER

ADAMS JAMES

MILLER

FORD

Решение

Для создания разреженной таблицы, в которой строки транспонированы в столбцы, используйте выражения CASE:

1 select case deptno when 10 then ename

2 case deptno when 20 then ename

3 case deptno when 30 then ename

4 case job when CLERK then

5 case job when MANAGER then

6 case job when PRESIDENT then

7 case job when ANALYST then

8 case job when SALESMAN then

9 from emp

end as d10, end as d20, end as d30, ename end as clerks, ename end as mgrs, ename end as prez, ename end as anals, ename end as sales

Обсуждение

Чтобы превратить строки DEPTNO и JOB в столбцы, просто используем выражение CASE, обрабатывающее значения, которые могут быть возвращены в этих строках. Вот и все. В дополнение к этому, если требуется уплотнить отчет и избавиться от строк со значениями NULL, необходимо определиться с принципом группировки. Например, с помощью ранжирующей функции ROW NUMBER OVER присвойте ранги всем служащим, разделяя их по DEPTNO, и затем, применяя агрегатную функцию MAX, удалите некоторые значения NULL:

select max(case deptno when 10 then ename end) d10, max(case deptno when 20 then ename end) d20, max(case deptno when 30 then ename end) d30,

max(case job when CLERK then ename end) clerks, max(case job when MANAGER then ename end) mgrs, max(case job when PRESIDENT then ename end) prez, max(case job when ANALYST then ename end) anals, max(case job when SALESMAN then ename end) sales from (

select deptno, job, ename,

row number()over(partition by deptno order by empno) rn from emp ) x group by rn

CLERKS MGRS PREZ ANALS SALES

CLARK SMITH ALLEN SMITH CLARK ALLEN

KING JONES WARD JONES KING WARD

MILLER SCOTT MARTIN MILLER SCOTT MARTIN

ADAMS BLAKE ADAMS BLAKE

FORD TURNER FORD TURNER

JAMES JAMES

Группировка строк по интервалам времени Задача

Требуется обобщить данные по некоторому интервалу времени. Например, имеется журнал транзакций. Необходимо разбить период наблюдений на 5-секундные интервалы и показать, сколько транзакций имело место в каждый из этих интервалов. Строки таблицы TRX LOG показаны ниже:

Обсуждение

Первый и ключевой для всего решения шаг - сгруппировать строки. Сформировать логические группы можно путем деления уникальных идентификаторов записей на 5 и возвращения наименьшего целого числа, которое больше, чем остаток от деления. Например:

select trx id,

trx date, trx cnt,

trx id/5.0 as val,

ceil(trx id/5.0) as grp from trx log

TRX ID TRX DATE TRX CNT VAL GRP

14 28-JUL-2005 19:03:20 48

15 28-JUL-2005 19:03:21 46

16 28-JUL-2005 19:03:22 44

17 28-JUL-2005 19:03:23 36

18 28-JUL-2005 19:03:24 41

19 28-JUL-2005 19:03:25 33

20 28-JUL-2005 19:03:26 19

Должно быть получено следующее результирующее множество:

GRP TRX START TRX END TOTAL

1 28-JUL-2005 19:03:07 28-JUL-2005 19:03:11 141

2 28-JUL-2005 19:03:12 28-JUL-2005 19:03:16 178

3 28-JUL-2005 19:03:17 28-JUL-2005 19:03:21 202

4 28-JUL-2005 19:03:22 28-JUL-2005 19:03:26 173

Решение

Группировать записи в блоки по пять строк. Такую логическую группировку можно реализовать несколькими способами. В данном рецепте она осуществляется путем деления значений TRX ID на 5, т. е. используется техника, представленная ранее в разделе Создание блоков данных фиксированного размера .

Когда группы установлены, с помощью агрегатных функций MIN, MAX и SUM определяются начальное время, конечное время и общее количество транзакций в каждой группе (для SQL Server используется функция CEILING, а не CEIL):

1 select ceil(trx id/5.0) as grp,

2 min(trx date) as trx start,

3 max(trx date) as trx end,

4 sum(trx cnt) as total

5 from trx log

6 group by ceil(trx id/5.0)