如何基于子查询结果计算厂商各产品型号数量占比？

现有如下SQL查询及对应结果，需要计算每个厂商（maker）下，每种产品类型（type）的型号数量占该厂商总型号数量的百分比（即各类型型号数除以该厂商的型号总数），并得到指定格式的结果。

原查询语句 #

SELECT 
    maker, p.type, COUNT(DISTINCT pc.model) pcs
FROM 
    Product p 
INNER JOIN 
    Pc pc ON p.model = pc.model
GROUP BY 
    maker, p.type

UNION

SELECT 
    maker, p.type, COUNT(DISTINCT l.model) pcs
FROM
    Product p 
INNER JOIN 
    Laptop l ON p.model = l.model
GROUP BY 
    maker, p.type

UNION

SELECT 
    maker, p.type, COUNT(DISTINCT pr.model) pcs
FROM 
    Product p 
INNER JOIN 
    Printer pr ON p.model = pr.model
GROUP BY 
    maker, p.type

原查询结果 #

解决方案SQL #

将原查询作为子查询，结合窗口函数SUM() OVER (PARTITION BY maker)计算每个厂商的总型号数，再计算百分比并格式化保留两位小数：

SELECT
    maker,
    type,
    ROUND((pcs * 100.0) / SUM(pcs) OVER (PARTITION BY maker), 2) AS model
FROM (
    SELECT 
        maker, p.type, COUNT(DISTINCT pc.model) pcs
    FROM 
        Product p 
    INNER JOIN 
        Pc pc ON p.model = pc.model
    GROUP BY 
        maker, p.type

    UNION

    SELECT 
        maker, p.type, COUNT(DISTINCT l.model) pcs
    FROM
        Product p 
    INNER JOIN 
        Laptop l ON p.model = l.model
    GROUP BY 
        maker, p.type

    UNION

    SELECT 
        maker, p.type, COUNT(DISTINCT pr.model) pcs
    FROM 
        Product p 
    INNER JOIN 
        Printer pr ON p.model = pr.model
    GROUP BY 
        maker, p.type
) AS product_counts
ORDER BY maker, type;

期望结果 #

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