Подсчет ненулевых столбцов довольно странным образом

У меня есть таблица с 32 столбцами в таблице Oracle.

  • Два из этих столбцов являются столбцами идентификаторов.
  • остальное - ценности

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

    SELECT 
           ((nvl(val0, 0) + nvl(val1, 0) + ... nvl(valn, 0)) 
           / nonZero_Column_Count_In_This_Row)

Таким образом, что: nonZero_Column_Count_In_This_Row = (ifNullThenZeroElse1(val0) + ifNullThenZeroElse1(val1) ... ifNullThenZeroElse(valn))

Трудность здесь, конечно, в том, чтобы получить 1 для любого ненулевого столбца. Кажется, мне нужна функция, похожая на NVL, но с предложением else. Что-то, что вернет 0, если значение равно null, и 1, если нет, а не само значение.

Как мне получить значение знаменателя?

PS: я чувствую, что должен объяснить некоторые мотивы этого дизайна. В идеале эта таблица должна быть организована как столбцы идентификаторов и одно значение в строке с некоторым идентификатором самой строки. Это сделало бы его более нормализованным, и решение этой проблемы было бы довольно простым. Причины, по которым этого нельзя делать, - пропускная способность и экономия места. Это огромная БД, куда мы вставляем 10 миллионов значений в минуту. Если сделать каждое из этих значений одной строкой, это будет означать 10 миллионов строк в минуту, что определенно недостижимо. Упаковка 30 из них в одну строку уменьшает количество вставляемых строк до того, что мы можем сделать с одной БД, а количество служебных данных (идентификационных данных) намного меньше.


null sql oracle count
person Community    schedule 19.06.2009    source источник

Ответы (5)


arrow_upward
2
arrow_downward

(Случай, когда столбец равен нулю, тогда 0, иначе 1 конец)

person KristoferA    schedule 19.06.2009

arrow_upward
1
arrow_downward

Вы можете использовать NVL2(val0, 1, 0) + NVL2(val1, 1, 0) + ..., так как вы используете Oracle.

person jerryjvl    schedule 19.06.2009

arrow_upward
1
arrow_downward

Другой вариант — использовать функцию AVG, которая игнорирует NULL:

SELECT AVG(v) FROM (
WITH q AS (SELECT val0, val1, val2, val3 FROM mytable)
SELECT val0 AS v FROM q
UNION ALL SELECT val1 FROM q
UNION ALL SELECT val2 FROM q
UNION ALL SELECT val3 FROM q
);

Если вы используете Oracle11g, вы можете использовать синтаксис UNPIVOT, чтобы сделать его еще проще.

person Jeffrey Kemp    schedule 19.06.2009
comment
Всем большое спасибо. Вы все абсолютно великолепны. Я буду экспериментировать со всеми этими предложениями. Спасибо еще раз. - person ; 19.06.2009

arrow_upward
1
arrow_downward

Я вижу, что это довольно старый вопрос, но я не вижу достаточного ответа. У меня была аналогичная проблема, и ниже описано, как я ее решил. Совершенно ясно, что требуется оператор case. Это решение является обходным путем для таких случаев, когда

SELECT COUNT(column) WHERE column {IS | IS NOT} NULL

не работает по какой-то причине, или вам нужно сделать несколько

SELECT   COUNT ( * )
  FROM   A_TABLE
 WHERE   COL1 IS NOT NULL;

SELECT   COUNT ( * )
  FROM   A_TABLE
 WHERE   COL2 IS NOT NULL;

запросы, но хотите, чтобы он был набором данных при запуске скрипта. Смотри ниже; Я использую это для анализа, и до сих пор он отлично работал для меня.

SUM(CASE NVL(valn, 'X')
                WHEN 'X'
                THEN 0 
                ELSE 1 
                END) as COLUMN_NAME
            FROM YOUR_TABLE;


Ура!
Дуг

person CaptainMarvel    schedule 20.01.2014

arrow_upward
0
arrow_downward

В общем, вы можете сделать что-то вроде этого:

SELECT (
       (COALESCE(val0, 0) + COALESCE(val1, 0) + ...... COALESCE(valn, 0))
       /
       (SIGN(ABS(COALESCE(val0, 0))) + SIGN(ABS(COALESCE(val1, 0))) + .... )
       ) AS MyAverage

Верхняя строка вернет сумму значений (без значений NULL), тогда как нижняя строка вернет количество ненулевых значений.

FYI - это синтаксис SQL Server, но COALESCE по большей части такой же, как ISNULL. ЗНАК просто возвращает -1 для отрицательного числа, 0 для нуля и 1 для положительного числа. ABS - это "абсолютное значение".

person Aaron Alton    schedule 19.06.2009
comment
Привет, Аарон. Единственная проблема в том, что это значение равно нулю и NULL. На самом деле я хочу добавить к знаменателю значение 0, а не ноль. Спасибо. - person ; 19.06.2009