如何判断企业在BigQuery中的排名是否在第25位呢？

Question

我有如下数据：

business_id|business_name|  time_value  | time_bin |yoy_txn_growth
    1111   |     A       |  July 1,2022 |  Monthly |   .43
    2222   |     B       |  July 1,2022 |  Monthly |   .35
    1111   |     A       |  June 1,2022 |  Monthly |   .27
    2222   |     B       |  June 1,2022 |  Monthly |   .25
    1111   |     A       |  May 1,2022  |  Monthly |   .13
    2222   |     B       |  May 1,2022  |  Monthly |   .12
    1111   |     A       |  April 1,2022|  Monthly |   .09
    2222   |     B       |  April 1,2022|  Monthly |   .08
    1111   |     A       |  March 1,2022|  Monthly |   .20
    2222   |     B       |  March 1,2022|  Monthly |   .19
    1111   |     A       |  July 1,2022 | Quarterly|   .49
    2222   |     B       |  July 1,2022 | Quarterly|   .47
    1111   |     A       |  April 1,2022| Quarterly|   .02
    2222   |     B       |  April 1,2022| Quarterly|   .01

我想知道在每一个time_value + time_bin中，一个企业是否处于yoy交易增长的前25个百分点，另外，该业务是否处于yoy交易增长的后25个百分位数。可能会有更多的业务，或者在整个表中只有1种情况(如果是真的，那么在top25和bottom25中都是默认的)。

business_id|business_name|  time_value  | time_bin |yoy_txn_growth| top25perc | bot25perc
    1111   |     A       |  July 1,2022 |  Monthly |   .43        |   TRUE    |   FALSE
    2222   |     B       |  July 1,2022 |  Monthly |   .35        |   FALSE   |   TRUE
    1111   |     A       |  June 1,2022 |  Monthly |   .23        |   TRUE    |   FALSE
    2222   |     B       |  June 1,2022 |  Monthly |   .25        |   FALSE   |   TRUE
    1111   |     A       |  May 1,2022  |  Monthly |   .13        |   TRUE    |   FALSE
    2222   |     B       |  May 1,2022  |  Monthly |   .12        |   FALSE   |   TRUE
    1111   |     A       |  April 1,2022|  Monthly |   .09        |   TRUE    |   FALSE
    2222   |     B       |  April 1,2022|  Monthly |   .08        |   FALSE   |   TRUE
    1111   |     A       |  March 1,2022|  Monthly |   .18        |   TRUE    |   FALSE
    2222   |     B       |  March 1,2022|  Monthly |   .19        |   FALSE   |   TRUE
    1111   |     A       |  July 1,2022 | Quarterly|   .46        |   TRUE    |   FALSE
    2222   |     B       |  July 1,2022 | Quarterly|   .47        |   FALSE   |   TRUE
    1111   |     A       |  April 1,2022| Quarterly|   .02        |   TRUE    |   FALSE
    2222   |     B       |  April 1,2022| Quarterly|   .01        |   FALSE   |   TRUE

我如何在BigQuery中实现这一点？

Answer 1

对于您的需求，续可以用于获取带有如果条件的百分位数。您可以在下面的查询中尝试获得预期的输出。

查询

SELECT
 *,
IF
 (PERCENTILE_CONT(yoy_txn_growth,
     0.75) OVER(PARTITION BY time_bin, time_value) <yoy_txn_growth,
   TRUE,
   FALSE) AS top25perc,
IF
 (PERCENTILE_CONT(yoy_txn_growth,
     0.25) OVER(PARTITION BY time_bin, time_value)>yoy_txn_growth,
   TRUE,
   FALSE) AS bot25perc
FROM `project.dataset.Percentiletest`
ORDER BY time_value,time_bin

输出

​

​