如何按各国家ALL行总数排序且保持同国家数据聚合

安全阿岳

2026-6-20

解决方案

你可以通过先计算每个国家的总港口数并将其作为排序依据，关联到所有结果行中，实现按国家总港口数排序且同一国家数据聚合的需求。

修改后的查询语句如下：

WITH country_totals AS (
    -- 计算每个国家的总港口数，作为全局排序的核心依据
    SELECT 
        country,
        COUNT(*) AS total_harbors
    FROM bigquery-public-data.geo_international_ports.world_port_index
    GROUP BY country
),
port_counts AS (
    -- 统计每个国家各港口尺寸的数量
    SELECT 
        country,
        harbor_size,
        COUNT(*) AS hb_count
    FROM bigquery-public-data.geo_international_ports.world_port_index
    GROUP BY country, harbor_size
    UNION ALL
    -- 添加每个国家的"ALL"汇总行
    SELECT 
        country,
        'ALL' AS harbor_size,
        total_harbors AS hb_count
    FROM country_totals
)
SELECT 
    pc.country,
    pc.harbor_size,
    pc.hb_count
FROM port_counts pc
JOIN country_totals ct ON pc.country = ct.country
-- 排序逻辑：先按国家总港口数降序，再确保同国家行聚合，最后让"ALL"行排在国家数据最前面，其余按港口数量降序
ORDER BY ct.total_harbors DESC, pc.country, CASE WHEN pc.harbor_size = 'ALL' THEN 0 ELSE 1 END, pc.hb_count DESC;