sql - 计算每行的不同值的数量(SQL)

Question

如何创建一个新列来返回表中每一行中不同值的数量？例如，

ID   Description   Pay1    Pay2   Pay3    #UniquePays     
1    asdf1         10      20     10      2
2    asdf2         0       10     20      3
3    asdf3         100     100    100     1
4    asdf4                 0      10      3

查询可能会返回 >100 万行，因此它需要有一定的效率。总共有 8 个 'Pay' 列，它们要么是 NULL，要么是一个整数。另请注意，“0”应该与 NULL 不同。

到目前为止我能完成的最多的事情(我刚刚意识到这甚至不准确)是计算每行中的 Pay 条目总数:

nvl(length(length(Pay1)),0)
+nvl(length(length(Pay2)),0)
+nvl(length(length(Pay3)),0) "NumPays"

典型的行仅填充了 8 列中的 4 列，其余为空，Pay 列中的最大整数为“999”(因此尝试进行长度-长度转换..)

我的 SQL 技能很原始，但非常感谢任何帮助!

Answer 1

如果您拥有或可以创建用户定义的数字表，则可以使用创建集合，使用 the set function删除重复项，然后使用 the cardinality function计算剩余值:

cardinality(set(t_num(pay1, pay2, pay3))) as uniquepays

要包含所有八列，只需将额外的列名称添加到传递给 tnum() 构造函数的列表中即可。

cardinality(set(t_num(pay1, pay2, pay3, pay4, pay5, pay6, pay7, pay8))) as uniquepays

使用生成为 CTE 的示例表进行演示:

create type t_num as table of number
/

with t (ID, Description, Pay1, Pay2, Pay3) as (
  select 1, 'asdf1', 10, 20, 10 from dual
  union all select 2, 'asdf2', 0, 10, 20 from dual
  union all select 3, 'asdf3', 100, 100, 100 from dual
  union all select 4, 'asdf4', null, 0, 10 from dual
)
select id, description, pay1, pay2, pay3,
  cardinality(set(t_num(pay1, pay2, pay3))) as uniquepays
from t
order by id;

        ID DESCR       PAY1       PAY2       PAY3 UNIQUEPAYS
---------- ----- ---------- ---------- ---------- ----------
         1 asdf1         10         20         10          2
         2 asdf2          0         10         20          3
         3 asdf3        100        100        100          1
         4 asdf4                     0         10          3

这对于数百万行是否足够有效需要进行测试。