以一种非常奇怪的方式计算非空列 [英] Counting non-null columns in a rather strange way

问题描述

我有一个表，该表在Oracle表中具有32列.

I have a table which has 32 columns in an Oracle table.

• 其中两列是身份列
• 其余就是值

我想获取所有value列的平均值，这由于null(标识)列而变得很复杂.以下是我要实现的伪代码:

I would like to get the average of all the value columns, which is complicated by the null (identity) columns. Below is the pseudocode for what I am trying to achieve:

    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))

Such that: nonZero_Column_Count_In_This_Row = (ifNullThenZeroElse1(val0) + ifNullThenZeroElse1(val1) ... ifNullThenZeroElse(valn))

这里的困难当然是要为任何非null列获取1.似乎我需要一个类似于NVL的函数，但带有else子句.如果值为null，则返回0，否则返回1，而不是返回值本身.

The difficulty here is of course in getting 1 for any non-null column. It seems I need a function similar to NVL, but with an else clause. Something that will return 0 if the value is null, but 1 if not, rather than the value itself.

我应该如何获取分母的值?

PS:我觉得我必须解释这种设计背后的一些动机.理想情况下，该表将被组织为标识列，每行一个值，并为行本身添加一些标识符.这将使它更加规范化，并且解决该问题的方法将非常简单.不这样做的原因是吞吐量和节省空间.这是一个巨大的数据库，我们每分钟向其中插入一千万个值.将这些值中的每一个设为一行将意味着每分钟1000万行，这绝对是无法实现的.将它们中的30个打包到单个行中可以减少插入到单个DB可以执行的操作的行数，并且开销数据量(身份数据)要少得多.

PS: I feel I must explain some motivation behind this design. Ideally this table would have been organized as the identity columns and one value per row with some identifier for the row itself. This would have made it more normalized and the solution to this problem would have been pretty simple. The reasons for it not to be done like this are throughput, and saving space. This is a huge DB where we insert 10 million values per minute into. Making each of these values one row would mean 10M rows per minute, which is definitely not attainable. Packing 30 of them into a single row reduces the number of rows inserted to something we can do with a single DB, and the overhead data amount (the identity data) much less.

推荐答案

(案例，当col为null时，则为0，否则1结尾)

(Case When col is null then 0 else 1 end)

这篇关于以一种非常奇怪的方式计算非空列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！