提问人：justnewbie89 提问时间：9/4/2022 最后编辑：justnewbie89 更新时间：9/6/2022 访问量：70
如何使用 bigquery 从前 1000 名卖家那里获得所有卖家的交易金额百分比，从月至今
how to get % of transaction amount from top 1000 sellers over all sellers, Month to Date, using bigquery
问：
所以我有表格，包括：表格、shop_id和交易金额。
date_key shop_id transaction_amount
2022-01-01 S_001 2000
2022-01-01 S_002 2500
2022-01-02 S_001 2600
2022-01-02 S_002 2200
.
.
.
.
目标是我想计算前 1000 名卖家 MTD 的交易金额百分比。平均而言，我每天大约有 50k 卖家进行交易。由于计算是月初至今，因此我们有可能每天找到不同的前 1000 名卖家。
前 1000 名卖家 MTD 含义：表现基于累计 MTD 交易金额，例如对于 2022-01-05 的数据，我们需要计算每个shop_id从 2022-01-01 到 2022-01-04 的累计交易金额，然后根据最高交易金额进行排序。
目标是在下面创建表格
date_key amount_from_top_1000_sellers(a) amount_from_all_sellers (b) ratio (a/b)
2022-01-01 4000 200000 2%
...
...
...
答：
2赞
Samuel
9/5/2022
#1
窗口函数将完成此任务。对于每一天，您都需要一个按销售金额列排序。row_number
首先，我们在表中生成一些随机数据。然后我们添加每天的行号。接下来，我们需要过滤每天的前 100 个畅销书（语句）。每天增加到 1000 个畅销书是显而易见的。剩余销售量和配给的计算可以通过将最后一个包含在下表中来完成，然后再次查询此表。tblifSELECTWITHSELECT
WITH
tbl AS (
SELECT
DATE_SUB(CURRENT_DATE(),INTERVAL d day) AS date_key,
a AS shop_id,
100*RAND() AS transaction_amount
FROM
UNNEST(GENERATE_ARRAY(1,1000)) a,
UNNEST(GENERATE_ARRAY(0,100)) d ),
trunc_to_month AS (
Select
date_trunc(date_key,month) as date_key_month,#aggregate to month and year
shop_id,
sum(transaction_amount) as transaction_amount,
from tbl
group by 1,2
),
tmp AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY date_key_month ORDER BY transaction_amount DESC ) AS rownum
FROM
trunc_to_month )
SELECT
date_key_month, # keep only the month and year; remove the day
SUM(transaction_amount) AS transaction_amount_total,
SUM(IF(rownum<=100,transaction_amount,0)) AS amount_from_top_100_sellers
FROM
tmp
GROUP BY 1
评论
0赞
justnewbie89
9/6/2022
嗨，塞缪尔，谢谢你回答我的问题。真的很感激。想澄清一些事情。因此，查询中的前 100 名是基于每日表现的，对吧？你知道如何处理这种情况吗，例如前 100 名是基于累积的 MTD 性能。因此，每月前 10 天的前 100 名可能与 20 天内的前 100 名不同。
0赞
Samuel
9/6/2022
嗨，我添加了一个来获取一个月的数据，而不是日期。date_trunc(date_key,month)
评论