等效的字符串包含在 google bigquery

Question

我有一个如下所示的表格

我想创建 two new binary columns 表明主题是否有 steroids 和 aspirin 。我希望在 Postgresql and google bigquery 中实现这一点

我尝试了以下但它不起作用

select subject_id
case when lower(drug) like ('%cortisol%','%cortisone%','%dexamethasone%')
then 1 else 0 end as steroids,
case when lower(drug) like ('%peptide%','%paracetamol%')
then 1 else 0 end as aspirin,
from db.Team01.Table_1

SELECT
db.Team01.Table_1.drug
FROM `table_1`,
UNNEST(table_1.drug) drug
WHERE REGEXP_CONTAINS( db.Team01.Table_1.drug,r'%cortisol%','%cortisone%','%dexamethasone%')

我希望我的输出如下所示

原文由 The Great 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

以下是 BigQuery 标准 SQL

 #standardSQL
SELECT
  subject_id,
  SUM(CASE WHEN REGEXP_CONTAINS(LOWER(drug), r'cortisol|cortisone|dexamethasone') THEN 1 ELSE 0 END) AS steroids,
  SUM(CASE WHEN REGEXP_CONTAINS(LOWER(drug), r'peptide|paracetamol') THEN 1 ELSE 0 END) AS aspirin
FROM `db.Team01.Table_1`
GROUP BY subject_id

如果适用于您的问题的样本数据 - 结果是

Row subject_id  steroids    aspirin
1   1           3           1
2   2           1           1

注意：我使用的是 LIKE on steroids 而不是简单的 LIKE 以冗长和冗余的文本结尾 - 这是 REGEXP_CONTAINS

原文由 Mikhail Berlyant 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

在 Postgres 中，我建议使用 filter 子句：

 select subject_id,
       count(*) filter (where lower(drug) ~ 'cortisol|cortisone|dexamethasone') as steroids,
       count(*) filter (where lower(drug) ~ 'peptide|paracetamol') as aspirin,
from db.Team01.Table_1
group by subject_id;

在 BigQuery 中，我会推荐 countif() ：

 select subject_id,
       countif(regexp_contains(drug, 'cortisol|cortisone|dexamethasone') as steroids,
       countif(drug ~ ' 'peptide|paracetamol') as aspirin,
from db.Team01.Table_1
group by subject_id;

您可以使用 sum(case when . . . end) 作为更通用的方法。但是，每个数据库都有一种更“本地”的方式来表达这种逻辑。顺便说一句， FILTER 子句 是 标准 SQL，只是没有被广泛采用。

原文由 Gordon Linoff 发布，翻译遵循 CC BY-SA 4.0 许可协议