提问人: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 个畅销书是显而易见的。剩余销售量和配给的计算可以通过将最后一个包含在下表中来完成,然后再次查询此表。tbl
if
SELECT
WITH
SELECT
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)
评论