sql - 如何计算SQL中的案例数？

Question

如何在 SQL 中找到 Cases 的数量？例如，下面的代码有 5 个 Case 语句

Select
up.user_id,
(
    case when up.email is null then 0 else 1 end
    +
    case when up.bio is null then 0 else 1 end
    +
    case when up.website is null then 0 else 1 end
    +
    case when up.location is null then 0 else 1 end
    +
    case when up.name is null then 0 else 1 end
) * 100 / **5** as complete
from users_profiles up

我正在使用 PostgreSQL

Answer 1

使用参数数量可变的函数，例如:

create or replace function not_null_ratio(variadic args text[])
returns numeric language plpgsql as $$
declare
    res numeric = 0;
begin
    for i in 1..array_length(args, 1) 
    loop
        res := res+ (args[i] is not null)::integer;
    end loop;
    return res * 100 / array_length(args, 1);
end $$;

select not_null_ratio('a', 'b', null, 'd');

   not_null_ratio    
---------------------
 75.0000000000000000
(1 row)

在您的查询中:

select
    up.user_id,
    not_null_ratio(up.email, up.bio, up.website, up.location, up.name) as complete
from users_profiles up

---

SQL 函数也可以有可变数量的参数。这个变体可能会快一点:

create or replace function not_null_ratio_sql(variadic args text[])
returns numeric language sql as $$
    select count(elem) * 100.0 / count(*)
    from unnest(args) elem
$$;

select not_null_ratio_sql('a', 'b', null);

 not_null_ratio_sql  
---------------------
 66.6666666666666667
(1 row)