如何使用 bigquery 从前 1000 名卖家那里获得所有卖家的交易金额百分比,从月至今

how to get % of transaction amount from top 1000 sellers over all sellers, Month to Date, using bigquery

提问人:justnewbie89 提问时间:9/4/2022 最后编辑:justnewbie89 更新时间:9/6/2022 访问量:70

问:

所以我有表格,包括:表格、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%
...
...
...
SQL Google-BigQuery

评论


答:

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)