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

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

1 ... 66 67 68 [ 69 ] 70 71 72 ... 219

щее множество вложенного представления, можно увидеть, что заработная плата 3000 имеет максимальное значение CNT, 2. MAX(SAL) возвращает самое большое значение SAL, которому соответствует наибольшое значение CNT, в данном случае это 3000.

См. также

Главу 11 раздел Ход конем , в котором расширение агрегатных функций KEEP Oracle обсуждается более подробно.

MySQL и PostgreSQL

Подзапрос подсчитывает, сколько раз встречается каждое из значений столбца SAL. Внешний запрос возвращает каждое значение SAL, которое встречается чаще других или столько же раз, сколько все остальные значения (или иначе говоря, внешний запрос возвращает самые распространенные заработные платы для DEPTNO 20).

Вычисление медианы Задача

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

select sal

from emp

where deptno = 20

order by sal

1100

2975 3000 3000

медианой является 2975.

Решение

Кроме решения Oracle (в котором для вычисления медианы используются предоставляемые СУБД функции), все решения основаны на методе, описанном Розенштейном, Абрамовичем и Бёргером в книге Optimizing Transact-SQL: Advanced Programming Techniques (SQL Forum Press, 1997). Введение ранжирующих функций обеспечивает более эффективное решение по сравнению с традиционным рефлексивным объединением.

Для поиска медианы используйте ранжирующие функции COUNT(*)

OVER и ROW NUMBER:

avg(sal)

sal,

count(*) over() total, cast(count(*) over() as decimal)/2 mid, ceil(cast(count(*) over() as decimal)/2) next, row number() over (order by sal) rn emp

deptno = 20 ) x

( mod(total,2) = 0 and rn in ( mid, mid+1 )

( mod(total,2) = 1 and rn = next

MySQL и PostgreSQL

Для поиска медианы используйте рефлексивное объединение:

	select
	from
	select




	from
	where

	where

	select	avg(sal)
	from
	select	e.sal
	from	emp e, emp d
	where	e.deptno = d.deptno
		e.deptno = 20
	group	by e.sal
	having	sum(case when e.sal =
		>=

Oracle

Используйте функции MEDIAN (Oracle10g Database) или PERCENTI-LE CONT (Oracle 9i Database):

1 select median(sal)

2 from emp

3 where deptno=20

1 select percentile cont(0.5)

2 within group(order by sal)

3 from emp

4 where deptno=20

Для Oracle 8i Database используйте решение для DB2. Для версий ранее Oracle 8i Database можно применять решение PostgreSQL/MySQL.

	select
	from
	select




	from
	where

	where

SQL Server

Для поиска медианы используйте ранжирующие функции COUNT(*)

OVER и ROW NUMBER:

avg(sal)

sal,

count(*)over() total, cast(count(*)over() as decimal)/2 mid, ceiling(cast(count(*)over() as decimal)/2) next, row number()over(order by sal) rn emp

deptno = 20 ) x

( total%2 = 0

and rn in ( mid, mid+1 )

( total%2 = 1

and rn = next

Обсуждение

DB2 и SQL Server

Решения для DB2 и SQL Server лишь немного отличаются синтаксисом: SQL Server использует для вычисления остатка от деления оператор % , а DB2 - функцию MOD. Во всем остальном решения аналогичны. Вложенное представление Х возвращает три разных счетчика, TOTAL, MID и NEXT, вместе с номером строки (RN), генерируемым функцией ROW NUMBER. Эти дополнительные столбцы помогают в поиске медианы. Рассмотрим результирующее множество вложенного представления Х, чтобы понять, что находится в этих столбцах:

select sal,

count(*)over() total, cast(count(*)over() as decimal)/2 mid, ceil(cast(count(*)over() as decimal)/2) next, row number()over(order by sal) rn from emp where deptno = 20

SAL TOTAL MID NEXT RN

800 5 2.5 3 1

1100 5 2.5 3 2

2975 5 2.5 3 3

3000 5 2.5 3 4

3000 5 2.5 3 5

Чтобы найти медиану, значения столбца SAL должны быть упорядочены от наименьшего к наибольшему. Поскольку в 20-м отделе (DEPT-

1 ... 66 67 68 [ 69 ] 70 71 72 ... 219