sql - 如何为以下结果创建 SQL 语句？

Question

我有一张包含以下内容的表格 -

我正在尝试创建 4 列，如下所示 -

• 风险
• 风险计数
• 收入
• Revenue_Count

以下 SQL 查询为我提供了所需的 4 列，但它也会生成 NULL 值。

select CASE when "BUCKET"='High Revenue' OR "BUCKET"='Low Revenue' OR "BUCKET"='Medium Revenue' then BUCKET end as Revenue,
CASE when "BUCKET"='High Revenue' OR "BUCKET"='Low Revenue' OR "BUCKET"='Medium Revenue' then CUSTOMER_COUNT end as Revenue_count,
 CASE when "BUCKET"='High Risk' OR "BUCKET"='Low Risk' OR "BUCKET"='Medium Risk' then BUCKET end as Risk,
  CASE when "BUCKET"='High Risk' OR "BUCKET"='Low Risk' OR "BUCKET"='Medium Risk' then CUSTOMER_COUNT end as Risk_count
FROM "TABLE_NAME"

结果-

如何删除 NULL 值并将结果放在一行中，以便理想情况下输出应包含 3 行 4 列。

问候

Answer 1

你想要 SUM() 或 MAX():

select MAX(CASE when "BUCKET"='High Revenue' OR "BUCKET"='Low Revenue' OR "BUCKET"='Medium Revenue' then BUCKET end) as Revenue,
       MAX(CASE when "BUCKET"='High Revenue' OR "BUCKET"='Low Revenue' OR "BUCKET"='Medium Revenue' then CUSTOMER_COUNT end) as Revenue_count,
       MAX(CASE when "BUCKET"='High Risk' OR "BUCKET"='Low Risk' OR "BUCKET"='Medium Risk' then BUCKET end) as Risk,
       MAX(CASE when "BUCKET"='High Risk' OR "BUCKET"='Low Risk' OR "BUCKET"='Medium Risk') then CUSTOMER_COUNT end as Risk_count
FROM "TABLE_NAME"

然后您可以使用IN 或LIKE 来简化逻辑:

select max(case when "BUCKET" in ('High Revenue', 'Low Revenue', 'Medium Revenue') then BUCKET end) as Revenue,
       max(case when "BUCKET" in ('High Revenue', 'Low Revenue', 'Medium Revenue') then CUSTOMER_COUNT end) as Revenue_count,
       max(case when "BUCKET" in ('High Risk', 'Low Risk', 'Medium Risk') then BUCKET end) as Risk,
       max(case when "BUCKET" in ('High Risk', 'Low Risk', 'Medium Risk') then CUSTOMER_COUNT end as Risk_count
FROM "TABLE_NAME";

我还建议您去掉标识符周围的双引号。仅在确实需要时引用标识符，然后选择不需要引用的标识符(例如您拥有的标识符)。

编辑:

我想你想要:

select max(case when bucket like '%Revenue' then BUCKET end) as Revenue,
       max(case when bucket like '%Revenue' then CUSTOMER_COUNT end) as Revenue_count,
       max(case when bucket like '%Risk' then BUCKET end) as Risk,
       max(case when bucket like '%Risk' then CUSTOMER_COUNT end) as Risk_count
FROM "TABLE_NAME"
GROUP BY LEFT(bucket, 3)  -- sufficient to distinct high/medium/low