排除空值

Question

我目前有一个数据库，我收集以下内容。当前试图排除所有空值，只保留数据条目。

with hipot_results as (
select *
from pico_bi.build_action_detailed_results b
join pico_bi.active_action_ref r on b.action_id = r.action_id
join pico_bi.build_data e on b.build_id = e.build_id
where r.action_label = 'Hipot Test'
order by created_at desc
limit 100)
select 
serial_number,
case when (step -> 'id')::numeric = '1' then (step -> 'measurement' ->> 'value')::numeric END as "IR-1 MΩ",
case when (step -> 'id')::numeric = '2' then (step -> 'measurement' ->> 'value')::numeric END as "LeakCurrent µA",
case when (step -> 'id')::numeric = '3' then (step -> 'measurement' ->> 'value')::numeric END as "IR-2 MΩ"
from hipot_results , jsonb_array_elements(action_detail -> 'steps') step
where serial_number = 'S226819'

当前查询。

当前查询

预期查询

Answer 1

如果我们可以假设对于每个序列号，每个列将只填充一次，或者您愿意使用"最大";

with hipot_results as (
select *
from pico_bi.build_action_detailed_results b
join pico_bi.active_action_ref r on b.action_id = r.action_id
join pico_bi.build_data e on b.build_id = e.build_id
where r.action_label = 'Hipot Test'
order by created_at desc
limit 100)
SELECT 
serial_number,
max(case when (step -> 'id')::numeric = '1' then (step -> 'measurement' ->> 'value')::numeric END) as "IR-1 MΩ",
max(case when (step -> 'id')::numeric = '2' then (step -> 'measurement' ->> 'value')::numeric END) as "LeakCurrent µA",
max(case when (step -> 'id')::numeric = '3' then (step -> 'measurement' ->> 'value')::numeric END) as "IR-2 MΩ"
FROM hipot_results , jsonb_array_elements(action_detail -> 'steps') step
GROUP BY Serial_number