SQL百分比聚合计算怎么实现_SQL计算百分比聚合方法

sales

SELECT
    product_id,
    category_id,
    sales_amount,
    -- 计算当前商品销售额占其所属品类总销售额的百分比
    -- 注意：乘以1.0或CAST是为了确保浮点数除法，避免整数除法截断
    (sales_amount * 1.0 / SUM(sales_amount) OVER (PARTITION BY category_id)) AS percentage_in_category,
    -- 也可以计算占总销售额的百分比，这时OVER()里不需要PARTITION BY
    (sales_amount * 1.0 / SUM(sales_amount) OVER ()) AS percentage_overall
FROM
    sales_data;

SUM(sales_amount) OVER (PARTITION BY category_id)

category_id

SUM(sales_amount) OVER ()

orders

region_id

order_amount

WITH RegionSales AS (
    -- 第一步：计算每个区域的总销售额
    SELECT
        region_id,
        SUM(order_amount) AS total_region_sales
    FROM
        orders
    GROUP BY
        region_id
),
TotalCompanySales AS (
    -- 第二步：计算公司总销售额
    SELECT
        SUM(order_amount) AS grand_total_sales
    FROM
        orders
)
-- 第三步：将区域销售额与公司总销售额结合，计算百分比
SELECT
    rs.region_id,
    rs.total_region_sales,
    (rs.total_region_sales * 1.0 / tcs.grand_total_sales) AS percentage_of_company_total
FROM
    RegionSales rs,
    TotalCompanySales tcs; -- 这里使用交叉连接，因为TotalCompanySales只有一行

5 / 10

0

0.5

CAST

DECIMAL

FLOAT

1.0

sales_amount * 1.0 / total_sales

1.0

NULLIF

sales_amount * 1.0 / NULLIF(SUM(sales_amount) OVER (PARTITION BY category_id), 0)

0

NULL

NULL

NULL

PARTITION BY

PARTITION BY

daily_sales

SELECT
    sale_date,
    sales_amount,
    -- 当月总销售额（作为分母）
    SUM(sales_amount) OVER (PARTITION BY DATE_TRUNC('month', sale_date)) AS monthly_total_sales,
    -- 截至当前日期的当月累积销售额（作为分子）
    SUM(sales_amount) OVER (PARTITION BY DATE_TRUNC('month', sale_date) ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_monthly_sales,
    -- 计算累积百分比
    (SUM(sales_amount) OVER (PARTITION BY DATE_TRUNC('month', sale_date) ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) * 1.0 /
     SUM(sales_amount) OVER (PARTITION BY DATE_TRUNC('month', sale_date))) AS cumulative_percentage_of_month
FROM
    daily_sales
ORDER BY
    sale_date;

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

SUM()

DATE_TRUNC('month', sale_date)

TRUNC(sale_date, 'MM')

DATEADD(month, DATEDIFF(month, 0, sale_date), 0)

monthly_sales_summary

SELECT
    sales_year,
    sales_month,
    monthly_amount,
    -- 计算当年总销售额
    SUM(monthly_amount) OVER (PARTITION BY sales_year) AS annual_total_sales,
    -- 计算月销售额占当年总销售额的百分比
    (monthly_amount * 1.0 / SUM(monthly_amount) OVER (PARTITION BY sales_year)) AS percentage_of_year_total
FROM
    monthly_sales_summary
ORDER BY
    sales_year, sales_month;

PARTITION BY sales_year

WITH BrandSales AS (
    SELECT
        brand_name,
        SUM(order_value) AS total_brand_sales
    FROM
        orders
    GROUP BY
        brand_name
),
TotalMarketSales AS (
    SELECT
        SUM(order_value) AS grand_total_sales
    FROM
        orders
)
SELECT
    bs.brand_name,
    bs.total_brand_sales,
    (bs.total_brand_sales * 1.0 / tms.grand_total_sales) AS market_share_percentage
FROM
    BrandSales bs,
    TotalMarketSales tms
ORDER BY
    market_share_percentage DESC;

SELECT
    COUNT(DISTINCT user_id) AS total_registered_users,
    COUNT(DISTINCT CASE WHEN has_made_purchase = TRUE THEN user_id END) AS users_with_first_purchase,
    (COUNT(DISTINCT CASE WHEN has_made_purchase = TRUE THEN user_id END) * 1.0 / COUNT(DISTINCT user_id)) AS conversion_rate_percentage
FROM
    user_activity;

CASE WHEN

GROUPING SETS

ROLLUP

-- 假设我们想看不同层级的销售额占比
WITH SalesSummary AS (
    SELECT
        category,
        sub_category,
        SUM(sales_amount) AS total_sales
    FROM
        product_sales
    GROUP BY GROUPING SETS (
        (category, sub_category), -- 按品类和子品类聚合
        (category),               -- 只按品类聚合
        ()                        -- 总聚合
    )
)
SELECT
    category,
    sub_category,
    total_sales,
    -- 计算当前分组占其父级分组的百分比
    -- 这里的逻辑会复杂一些，需要识别出不同层级的总额
    -- 比如，如果sub_category是NULL，它就是category的总额
    -- 实际操作中，可能需要更复杂的CTE或多次JOIN来完成
    CASE
        WHEN category IS NOT NULL AND sub_category IS NOT NULL THEN total_sales * 1.0 / (SELECT total_sales FROM SalesSummary WHERE category = s.category AND sub_category IS NULL)
        WHEN category IS NOT NULL AND sub_category IS NULL THEN total_sales * 1.0 / (SELECT total_sales FROM SalesSummary WHERE category IS NULL AND sub_category IS NULL)
        ELSE 1.0 -- 总计的百分比是100%
    END AS percentage_of_parent
FROM
    SalesSummary s
ORDER BY
    category, sub_category;

GROUPING SETS

NULL

GROUPING()

GROUPING_ID()