如何根据每行中值误差幅度的上限和下限对 BigQuery 表进行排名

Question

我有一个百分比的BigQuery表&每个百分比的相关误差范围。是否有一种方法在BigQuery标准SQL中使用或复制RANK()函数来确定该表的编号，以确定误差范围所代表的值的范围?例如，如果我有:

WITH `test_data` AS (
SELECT 'A' Name, 91.4 Percentage, 0.9 Percentage_Error UNION ALL
SELECT 'B', 90.5, 0.5 UNION ALL
SELECT 'C', 89.9, 0.7 UNION ALL
SELECT 'D', 88.8, 0.3
)
SELECT
Name,
Percentage,
Percentage_Error,
Percentage + Percentage_Error AS UpperBound,
Percentage - Percentage_Error AS LowerBound,
/* 1 AS Uncalculated_Rank */
FROM
`test_data`

我希望排名是:

• 考虑UpperBound低于LowerBound的任何行作为对等行来接收相同的秩值
• 使下一个等级值按具有前一个等级值的对等体的数目递增

所以在这个例子中，我的结果是:

上界tbody> <<tr>BCD

条目	Percentage	Percentage_Error	下界	Rank
	91.4	0.9	92.3	90.5	1
	90.5	0.5	91.0	90.0	1
	89.9	0.7	90.6	89.2	1
	88.8	0.3	89.1	88.5	4

老实说，你的例子有点模糊，我想看更多的例子来更好地理解它。据我所知，您希望交叉连接表本身，但仅针对以下行。

下面的查询给出了你想要的输出，但我不确定这是否是你想要的。

WITH 
test_data AS 
(
SELECT 'A' Name, 91.4 Percentage, 0.9 Percentage_Error UNION ALL
SELECT 'B', 90.5, 0.5 UNION ALL
SELECT 'C', 89.9, 0.7 UNION ALL
SELECT 'D', 88.8, 0.3
),
bounds as 
(
SELECT
Name,
Percentage,
Percentage_Error,
Percentage + Percentage_Error AS UpperBound,
Percentage - Percentage_Error AS LowerBound,
row_number() over (order by Name) as rank1
FROM test_data
)
select 
b1.Name,
any_value(b1.Percentage) as Percentage,
any_value(b1.Percentage_Error) as Percentage_Error,
any_value(b1.UpperBound) as UpperBound,
any_value(b1.LowerBound) as LowerBound,
countif(b1.UpperBound < b2.LowerBound) + 1 as Rank
from bounds b1
left join bounds b2
on b1.rank1 > b2.rank1
group by 1
order by 1