我需要将指标(目前每天)扩展到更大的组(将连续多天分组为一个grp
)。我有以下类型的数据:
id date grp new_ind traditional_ind
--------------------------------------------------
1 02-01-2021 1 1 0
1 02-02-2021 1 0 1
1 02-03-2021 1 0 0
1 02-04-2021 1 null null
1 02-06-2021 2 0 1
1 02-07-2021 2 0 0
2 02-01-2021 1 null null
其中new_ind
和traditional_ind
是相互排斥的。有了这个,我正在尝试创建新指标,将当前处于每日水平的指标扩展到grp
水平,如下所示:
id date grp new_ind traditional_ind final_type
----------------------------------------------------------------
1 02-01-2021 1 1 0 new
1 02-02-2021 1 0 1 new
1 02-03-2021 1 0 0 new
1 02-04-2021 1 null null new
1 02-06-2021 2 0 1 traditional
1 02-07-2021 2 0 0 traditional
2 02-01-2021 1 null null none
基本上
- 如果
new_ind
曾经是 1,我想将整个grp
标记为"新"> - 如果
new_ind=0
并且traditional_ind
永远为 1,则将整个grp
标记为"传统"> - 如果两个指标都为 null,则将整个
grp
标记为"无">
以便每个id
和grp
可以具有单个值final_type
。
我试过:
max(case when new_ind = 1 then 'New'
when traditional_ind = 1 then 'Traditional'
else 'None' end) over (partition by id, grp) as final_type
但这不会识别when new_ind=1 then 'New'
并将new_ind = 1
标记为"无"(但正确显示繁体):
id date grp new_ind traditional_ind final_type
----------------------------------------------------------------
1 02-01-2021 1 1 0 none
1 02-02-2021 1 0 1 none
1 02-03-2021 1 0 0 none
1 02-04-2021 1 null null none
1 02-06-2021 2 0 1 traditional
1 02-07-2021 2 0 0 traditional
2 02-01-2021 1 null null none
但是如果我删除 else 语句并且只运行:
max(case when new_ind = 1 then 'New'
when traditional_ind = 1 then 'Traditional'
end) over (partition by id, grp) as final_type
那么这确实像我希望的那样准确地扩展了指标,只返回空值(我需要将其显示为"无"而不是空值):
id date grp new_ind traditional_ind final_type
----------------------------------------------------------------
1 02-01-2021 1 1 0 new
1 02-02-2021 1 0 1 new
1 02-03-2021 1 0 0 new
1 02-04-2021 1 null null new
1 02-06-2021 2 0 1 traditional
1 02-07-2021 2 0 0 traditional
2 02-01-2021 1 null null null
任何人都可以在陈述时帮助确定我的最大案例问题吗?
我认为这样的事情应该有效:
WITH final_types AS (
SELECT
id,
grp,
( case
when bool_or(new_ind = 1) then 'New'
when bool_or(traditional_ind = 1) then 'Traditional'
else 'None'
end
) AS final_type
FROM your_table
GROUP BY id, grp
)
SELECT
t1.*,
t2.final_type
FROM your_table t1
JOIN final_types t2 ON t1.id = t2.id AND t1.grp = t2.grp