SQL按运营商分组，统计NULL与非NULL Aircraft ID并分列展示

以下是修改后的SQL查询，可实现按operator分组，将aircraft_id非NULL和NULL的统计结果分别放在两列中：

SELECT 
    org.organization AS "operator",
    ah.aircraft_registration_country AS "country",
    ah.aircraft_registration_region AS "region",
    acl.aircraft_master_series AS "aircraft type",
    ah.publish_date AS "publish date",
    -- 统计aircraft_id非NULL的数量
    COUNT(CASE WHEN f.aircraft_id IS NOT NULL THEN 1 END) AS "aircraft_id_not_null_count",
    -- 统计aircraft_id为NULL的数量
    COUNT(CASE WHEN f.aircraft_id IS NULL THEN 1 END) AS "aircraft_id_null_count"
FROM 
    flights.tracked_utilization f
LEFT JOIN pond_dataops_analysis.latest_aircraft a 
    ON a.aircraft_id = f.aircraft_id
LEFT JOIN fleets.aircraft_all_history_latest ah 
    ON ah.aircraft_id = f.aircraft_id
    AND COALESCE(f.actual_runway_departure_time_local, f.actual_gate_departure_time_local, f.published_gate_departure_time_local) >= ah.start_event_date
    AND COALESCE(f.actual_runway_departure_time_local, f.actual_gate_departure_time_local, f.published_gate_departure_time_local) < ah.end_event_date
LEFT JOIN fleets.organizations_latest org 
    ON org.organization_id = ah.operator_organization_id
LEFT JOIN fleets.aircraft_usage_history_latest ash 
    ON ash.aircraft_id = f.aircraft_id
    AND start_event_date >= ash.usage_start_date
    AND start_event_date < ash.usage_end_date
    AND ash.aircraft_usage_classification = 'Primary'
LEFT JOIN fleets.aircraft_configuration_history_latest accl 
    ON ash.aircraft_id = accl.aircraft_id
LEFT JOIN fleets.aircraft_configurations_latest acl 
    ON accl.aircraft_configuration_id = acl.aircraft_configuration_id
WHERE 
    f.flight_departure_date > NOW() - INTERVAL '90' day
GROUP BY 
    org.organization,
    ah.aircraft_registration_country,
    ah.aircraft_registration_region,
    acl.aircraft_master_series,
    ah.publish_date
ORDER BY 
    org.organization;

关键说明： #

• 条件聚合实现分列统计：利用CASE语句判断aircraft_id的状态，配合COUNT函数分别统计两类记录数（COUNT会自动忽略CASE返回的NULL值）
• 分组逻辑：将所有非聚合字段（operator、country、region等）加入GROUP BY，确保每个分组的维度信息准确对应
• 保留原查询逻辑：保留了原有的表关联关系和近90天的过滤条件，保证统计范围和原查询一致

内容的提问来源于stack exchange，提问作者Mark k