如何用BigQuery计算MTD Top1000卖家交易额占所有卖家的比例

需求说明 #

现有表包含date_key（日期）、shop_id（卖家ID）、transaction_amount（交易额）字段，每日约5万卖家产生交易。需计算每日的MTD Top1000卖家当日交易额占当日所有卖家交易额的比例，其中：

• MTD Top1000卖家定义：计算当日数据时，取截至前一日该月累计交易额排名前1000的卖家；
• 当月第一天无前置数据，按当日交易额取Top1000（可根据需求调整规则）。

BigQuery实现SQL #

WITH daily_shop_amount AS (
    -- 按日期+卖家聚合当日交易额，去重同一卖家当日多笔交易
    SELECT 
        date_key,
        shop_id,
        SUM(transaction_amount) AS daily_amount
    FROM `your_project.your_dataset.your_table`
    GROUP BY date_key, shop_id
),
shop_mtd_until_prev_day AS (
    -- 计算每个卖家截至前一日的月累计交易额
    SELECT
        date_key,
        shop_id,
        SUM(daily_amount) OVER (
            PARTITION BY EXTRACT(YEAR_MONTH FROM date_key), shop_id 
            ORDER BY date_key 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS mtd_amount_until_prev_day
    FROM daily_shop_amount
),
top_1000_shops_daily AS (
    -- 非当月第一天：按截至前一日的MTD累计取Top1000
    SELECT
        date_key,
        shop_id,
        ROW_NUMBER() OVER (
            PARTITION BY date_key 
            ORDER BY mtd_amount_until_prev_day DESC
        ) AS rank
    FROM shop_mtd_until_prev_day
    WHERE mtd_amount_until_prev_day IS NOT NULL
    QUALIFY rank <= 1000
    
    UNION ALL
    
    -- 当月第一天：按当日交易额取Top1000（无前置累计数据）
    SELECT
        date_key,
        shop_id,
        ROW_NUMBER() OVER (
            PARTITION BY date_key 
            ORDER BY daily_amount DESC
        ) AS rank
    FROM daily_shop_amount
    WHERE EXTRACT(DAY FROM date_key) = 1
    QUALIFY rank <= 1000
),
daily_total AS (
    -- 统计每日所有卖家的总交易额
    SELECT
        date_key,
        SUM(daily_amount) AS amount_from_all_sellers
    FROM daily_shop_amount
    GROUP BY date_key
),
top_1000_daily_amount AS (
    -- 统计每日Top1000卖家的当日交易额总和
    SELECT
        t.date_key,
        SUM(d.daily_amount) AS amount_from_top_1000_sellers
    FROM top_1000_shops_daily t
    JOIN daily_shop_amount d 
        ON t.date_key = d.date_key AND t.shop_id = d.shop_id
    GROUP BY t.date_key
)
-- 生成最终结果表
SELECT
    dt.date_key,
    COALESCE(t.amount_from_top_1000_sellers, 0) AS amount_from_top_1000_sellers,
    dt.amount_from_all_sellers,
    ROUND(
        COALESCE(t.amount_from_top_1000_sellers, 0) / dt.amount_from_all_sellers * 100,
        2
    ) AS ratio
FROM daily_total dt
LEFT JOIN top_1000_daily_amount t 
    ON dt.date_key = t.date_key
ORDER BY dt.date_key;

关键逻辑说明 #

1. daily_shop_amount：先聚合当日单卖家交易额，避免同一卖家当日多条交易数据干扰后续计算；
2. shop_mtd_until_prev_day：通过窗口函数的ROWS BETWEEN子句，精准计算截至前一日的月累计交易额；
3. top_1000_shops_daily：分场景处理当月第一天和其他日期的Top1000筛选逻辑，确保规则统一；
4. 最后通过关联计算，得到每日Top1000卖家交易额、全量卖家交易额及占比。

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