sql - 如何在某些条件下计算选定行中的值 - Postgresql

Question

我正在寻找一些内置的 Postresql 功能，它可以在某些条件下计算行值(而不是列)。一些模拟

=countif(a:a;"Yes")

在 Excel 中

我在 stackoverflow 上看到了很多类似问题的答案，但是当您在表列而不是行中过滤数据时提供的所有解决方案。但我需要按行解析数据。我不喜欢交叉表的解决方案，因为原始选择有超过 60 列，并且不喜欢执行相同的查询两次(但如果它是唯一的解决方案，我会这样做)。

一些测试示例，其中最后一列“num_of_yes”应该显示一行中"is"答案的数量。测试数据

CREATE TABLE main_data (
    id serial PRIMARY KEY,
    name VARCHAR(255) default NULL,
    lastname VARCHAR(255) default NULL,
    username VARCHAR(255) default NULL,
    job VARCHAR(255) default NULL,
    age integer  default NULL,
    licenseid integer  default NULL,
    account integer  default NULL
);

INSERT INTO main_data VALUES(1,'Jhon', 'Brown', 'jbrown', 'some job', 35, 11112333, 3333455);
INSERT INTO main_data VALUES(2,'Bob', NULL, 'bob', 'another job', 64, 1000500, 5555252);
INSERT INTO main_data VALUES(3,'Mike', 'McDonald', 'mike', NULL, 8, NULL, NULL);

选择查询:

select id, name,
case when lastname notnull then 'Yes'::text else 'No'::text end as "has_lastname",
case when username notnull then 'Yes'::text else 'No'::text end as "has_username",
case when job notnull then 'Yes'::text else 'No'::text end as "has_job",
case when age < 16 then 'Yes'::text else 'No'::text end as "is_child",
case when licenseid notnull then 'Yes'::text else 'No'::text end as "has_licenseid",
case when account notnull then 'Yes'::text else 'No'::text end as "has_account"
from main_data
order by id;

我的选择查询有以下输出:

| id | name | has_lastname | has_username | has_job | is_child | has_licenseid | has_account |
|----|------|--------------|--------------|---------|----------|---------------|-------------|
|  1 | Jhon | Yes          | Yes          | Yes     | No       | Yes           | Yes         |
|  2 | Bob  | No           | Yes          | Yes     | No       | Yes           | Yes         |
|  3 | Mike | Yes          | Yes          | No      | Yes      | No            | No          |

我需要在最后一列添加"is"答案的数量。

所需的输出应该是这样的:

| id | name | has_lastname | has_username | has_job | is_child | has_licenseid | has_account | num_of_yes |
|----|------|--------------|--------------|---------|----------|---------------|-------------|------------|
|  1 | Jhon | Yes          | Yes          | Yes     | No       | Yes           | Yes         |          5 |
|  2 | Bob  | No           | Yes          | Yes     | No       | Yes           | Yes         |          4 |
|  3 | Mike | Yes          | Yes          | No      | Yes      | No            | No          |          3 |

我正在使用 Postgresql 9.6.5

Answer 1

您可以将行转换为 JSONB 值，然后计算 Yes 的值:

select *, 
       (select count(*) 
        from jsonb_each_text(to_jsonb(t) - 'id' - 'name') as x(k,v)
        where v = 'Yes') as num_of_yes
from (
  select id, name,
         case when lastname is not null then 'Yes' else 'No' end as "has_lastname",
         case when username is not null then 'Yes' else 'No' end as "has_username",
         case when job is not null then 'Yes' else 'No' end as "has_job",
         case when age < 16 then 'Yes' else 'No' end as "is_child",
         case when licenseid is not null then 'Yes' else 'No' end as "has_licenseid",
         case when account is not null then 'Yes' else 'No' end as "has_account"
  from main_data
) t  
order by id;

表达式to_jsonb(t) - 'id' - 'name'将整行转换为 JSON 值并删除 id和 name从那 key 。然后 jsonb_each_text()遍历所有键/值对和 where v = 'Yes'然后使子查询计算那些 Yes

在线示例:https://rextester.com/PLJA96007

---

另一种选择是使用 num_nonnulls()功能:

select id, name,
       case when lastname is not null then 'Yes' else 'No' end as "has_lastname",
       case when username is not null then 'Yes' else 'No' end as "has_username",
       case when job is not null then 'Yes' else 'No' end as "has_job",
       case when age < 16 then 'Yes' else 'No' end as "is_child",
       case when licenseid is not null then 'Yes' else 'No' end as "has_licenseid",
       case when account is not null then 'Yes' else 'No' end as "has_account",
       num_nonnulls(lastname, username, job, licenseid, account, nullif(age < 16, false)) as num_of_yes
from main_data
order by id;

这很可能比 JSONB 解决方案更快。

---

请注意，如果您想要一个真正的 boolean列，case表达式可以简化为:例如lastname is not null as "has_lastname"或 age < 16 as "is_child"