SQL查询结果除法运算报错求助：计算持仓大于0地址占总地址比例

我一眼就看出问题所在了——你那两个子查询里的统计结果没有指定列别名，导致SQL引擎找不到x.number和y.number这两个字段，这就是报错的核心原因。

你看，原代码里的子查询：

( SELECT COUNT(address) FROM transferAmounts WHERE poolholdings > 0 ) x 

这里的COUNT(address)结果默认没有列名，数据库根本不知道你说的x.number指的是什么。同样的问题也出现在子查询y上。

修正后的完整代码 #

我帮你把代码改好了，同时还优化了除法的精度问题（避免整数除法导致结果被截断）：

WITH transfers AS (
 SELECT evt_tx_hash AS tx_hash, tr."from" AS address, -tr.value AS amount, contract_address
 FROM erc20."ERC20_evt_Transfer" tr
 WHERE contract_address = '\xD533a949740bb3306d119CC777fa900bA034cd52'
 UNION ALL
 SELECT evt_tx_hash AS tx_hash, tr."to" AS address, tr.value AS amount, contract_address
 FROM erc20."ERC20_evt_Transfer" tr
 where contract_address = '\xD533a949740bb3306d119CC777fa900bA034cd52'
), transferAmounts AS (
 SELECT address, sum(amount)/1e18 as poolholdings
 FROM transfers
 GROUP BY 1
 ORDER BY 2 DESC
)
SELECT (x.number * 1.0) / y.number AS holding_ratio
FROM ( SELECT COUNT(address) AS number FROM transferAmounts WHERE poolholdings > 0 ) x 
JOIN ( SELECT COUNT(address) AS number FROM transferAmounts ) y ON 1=1

关键修正点说明 #

1. 添加列别名：给每个子查询的COUNT(address)加上AS number，这样x.number和y.number就能被正确识别
2. 处理整数除法：把x.number乘以1.0，强制转换成浮点型，这样除法结果会是小数（比如100/200会得到0.5，而不是0）
3. 命名结果列：给最终的比值结果加上AS holding_ratio，让输出更清晰

更简洁的写法（不用JOIN） #

其实你完全可以不用JOIN，直接把两个子查询当作标量值来计算，代码更简洁：

WITH transfers AS (
 SELECT evt_tx_hash AS tx_hash, tr."from" AS address, -tr.value AS amount, contract_address
 FROM erc20."ERC20_evt_Transfer" tr
 WHERE contract_address = '\xD533a949740bb3306d119CC777fa900bA034cd52'
 UNION ALL
 SELECT evt_tx_hash AS tx_hash, tr."to" AS address, tr.value AS amount, contract_address
 FROM erc20."ERC20_evt_Transfer" tr
 where contract_address = '\xD533a949740bb3306d119CC777fa900bA034cd52'
), transferAmounts AS (
 SELECT address, sum(amount)/1e18 as poolholdings
 FROM transfers
 GROUP BY 1
 ORDER BY 2 DESC
)
SELECT 
  (SELECT COUNT(address) FROM transferAmounts WHERE poolholdings > 0) * 1.0 /
  (SELECT COUNT(address) FROM transferAmounts) AS holding_ratio

这样写逻辑更直观，也能避免JOIN带来的不必要的表连接开销。

内容的提问来源于stack exchange，提问作者Alex