名称相同的列显示为不同的列Postgresql

Question

select 
    timestamp, organization_id, cluster_safe_name,node_name, correlation_id, cascade_peer_reachability,
    is_cascade, is_cascade_client, device_id, device_type,
    case when audio_main_rx_bitrate > 0 or audio_main_tx_bitrate > 0 then 'true' end as audio_main,
    case when audio_main_rx_bitrate > 0 or audio_main_tx_bitrate > 0 then 'false' end as audio_slides,
    case when audio_main_rx_bitrate > 0 or audio_main_tx_bitrate > 0 then 'false' end as video_main,
    case when audio_main_rx_bitrate > 0 or audio_main_tx_bitrate > 0 then 'false' end as video_slides,
    case when audio_main_rx_bitrate > 0 or audio_main_tx_bitrate > 0 then audio_main_rx_bitrate end as rx_rtcp_byte,
    case when audio_main_rx_bitrate > 0 or audio_main_tx_bitrate > 0 then audio_main_tx_bitrate end as tx_rtcp_byte,
    case when audio_slides_rx_bitrate > 0 or audio_slides_tx_bitrate > 0 then 'false' end as audio_main,
    case when audio_slides_rx_bitrate > 0 or audio_slides_tx_bitrate > 0 then 'true' end as audio_slides,
    case when audio_slides_rx_bitrate > 0 or audio_slides_tx_bitrate > 0 then 'false' end as video_main,
    case when audio_slides_rx_bitrate > 0 or audio_slides_tx_bitrate > 0 then 'false' end as video_slides,
    case when audio_slides_rx_bitrate > 0 or audio_slides_tx_bitrate > 0 then audio_slides_rx_bitrate end as rx_rtcp_byte,
    case when audio_slides_rx_bitrate > 0 or audio_slides_tx_bitrate > 0 then audio_slides_tx_bitrate end as tx_rtcp_byte
from (
select 
    timestamp, organization_id, cluster_safe_name, node_name, correlation_id, cascade_peer_reachability,
    is_cascade, is_cascade_client, device_id, device_type,
    audio_main_rx_bitrate, audio_main_tx_bitrate,
    audio_slides_rx_bitrate, audio_slides_tx_bitrate
from homer_cascade_bandwidth_usage_metrics_v2 hcbumv 
) t
where correlation_id = '62183eb2-94c7-49ff-a98a-3a171d75ac07'   
order by timestamp desc;

我写了这个查询，注意audio_main列在case语句中出现了2次。作为这个查询的结果，我有两个独立的audio_main列，而我想要一个列。这是否可以用相同的查询完成，还是应该编写两个diff查询？

Answer 1

您应该重写CASE语句，以便在每个相同的列中包含两个可能的事件组合。我要做以下几点：

case 
  when audio_main_rx_bitrate > 0 or audio_main_tx_bitrate > 0 then true
  when audio_slides_rx_bitrate > 0 or audio_slides_tx_bitrate > 0 then false 
end as audio_main,
case 
  when audio_slides_rx_bitrate > 0 or audio_slides_tx_bitrate > 0 then true
  when audio_main_rx_bitrate > 0 or audio_main_tx_bitrate > 0 then false
end as audio_slides,
[...]

另外，请注意，如果同时引用'true'和'false'，您将创建一个字符串而不是一个布尔字段。