查找Bigquery表中每一行所在的范围

Question

我有一个如下的查找表，它有助于为公司的总销售额添加描述。

描述

限制
99	十
999	数百
999999	数千
999999999	百万
999999999999	数十亿

试试这个：

with lookup_table as
(
select  99 as `limit`, 'tens' as description
union all select  999, 'hundreds'
union all select  999999, 'thousands'
union all select  999999999, 'millions'
union all select  999999999999, 'billions'
),
sales_table as
(
select 89 as total_sales
union all select 45879
union all select 12254785
union all select 999999
)
select  t.total_sales,
(
select  tt.description
from    lookup_table tt
where   t.total_sales <= tt.`limit`
order by tt.`limit`
limit 1
) as description
from    sales_table t
;

或者这个：

with lookup_table as
(
select  99 as `limit`, 'tens' as description
union all select  999, 'hundreds'
union all select  999999, 'thousands'
union all select  999999999, 'millions'
union all select  999999999999, 'billions'
),
sales_table as
(
select 89 as total_sales
union all select 45879
union all select 12254785
union all select 999999
)
select  t.total_sales,
t2.description,
from    sales_table t
left join (
select  tt.`limit`,
tt.description,
ifnull(lag(tt.`limit`) over(order by tt.`limit`)+1,0) as prev_value
from lookup_table tt
) t2 on t.total_sales between t2.prev_value and t2.`limit`;

选择一个，会更快(

使用RANGE_BUCKET函数的Consdier，

WITH lookup_table  AS (
SELECT ANY_VALUE(description) description, ARRAY_AGG(`limit` ORDER BY `limit`) limits
FROM UNNEST([1, 2, 3, 6, 9]) n, UNNEST([CAST(POW(10, n) AS INT64)]) `limit`,
UNNEST([STRUCT(['tens', 'hundreds', 'thousands', 'millions', 'billions'] AS description)])
),
sales AS (
SELECT * FROM UNNEST([9, 89, 99, 100, 1000, 45879, 12254785]) total_sales
)
SELECT total_sales,
description[SAFE_OFFSET(RANGE_BUCKET(total_sales, limits) - 1)] AS description
FROM sales, lookup_table;

• https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#range_bucket

+-----+-------------+-------------+
| Row | total_sales | description |
+-----+-------------+-------------+
|   1 |           9 | null        |
|   2 |          89 | tens        |
|   3 |          99 | tens        |
|   4 |         100 | hundreds    |
|   5 |        1000 | thousands   |
|   6 |       45879 | thousands   |
|   7 |    12254785 | millions    |
+-----+-------------+-------------+