Query editor
WITH-- 协议地址映射表(转换为二进制格式以提高匹配效率)protocol_mapping AS (SELECTLOWER(address) as address_str,FROM_HEX(address) as address_binary,protocol_nameFROM (VALUES('643C607219513AaEdC96b78b152c71E3DB976ac7', 'Hashflow PMM'),('dea1ccaf997ec68fe2e9839a581e493d0e984a06', 'Tessera'),('33a8547ddE7fBFd031fD82685dB8a06a3Db98fF7', 'Obric'),('22ecf2Bed494976503A8087aDd30E134C30FBD98', 'Dynamic Route'),('69A52A0570636ca391175cE619Bbb9D348040Bbc', 'Dynamic Route'),('ee9AD4812cf87B500f689217BF1996ECE3284Fd9', 'BinaryFi')) AS t(address, protocol_name)),-- 优化:添加时间范围限制,减少日志扫描量okx_base_tx AS (SELECTcontract_address,tx_hash,block_timeFROM base.logsWHERE 1=1AND block_time >= CURRENT_DATE - INTERVAL '30' DAYAND contract_address IN (0x6b2C0c7be2048Daa9b5527982C29f48062B34D58,0x3D98f6F05E7940c056788Ff8492A943A0904240D,0x9b9efa5Efa731EA9Bbb0369E91fA17Abf249CFD4,0x5e2F47bD7D4B357fCfd0Bb224Eb665773B1B9801,0x69C236E021F5775B0D0328ded5EaC708E3B869DF,0x2bD541Ab3b704F7d4c9DFf79EfaDeaa85EC034f1,0x4409921ae43a39a11d90f7b7f96cfd0b8093d9fc,0xC8F6b8Ba0DC0f175B568B99440B0867F69A29265)AND topic0 = 0x1bb43f2da90e35f7b0cf38521ca95a49e68eb42fac49924930a5bd73cdf7576c),-- 优化:预先获取已知的dex交易hash,避免重复扫描existing_dex_hashes AS (SELECT DISTINCTa.tx_hash,a.projectFROM dex.trades AS aINNER JOIN okx_base_tx AS b ON a.tx_hash = b.tx_hashWHERE a.blockchain = 'base'AND a.block_time >= CURRENT_DATE - INTERVAL '30' DAY),-- 获取dex.trades中的数据,修正窗口函数逻辑dex_trades_with_first AS (SELECTa.block_time,CASEWHEN a.project = 'uniswap' THEN a.project || '_v' || a.versionELSE a.projectEND AS project,a.amount_usd,