Query editor
WITH-- 协议地址映射表(转换为二进制格式以提高匹配效率)protocol_mapping AS (SELECTLOWER(address) as address_str,FROM_HEX(address) as address_binary,protocol_nameFROM (VALUES('fb719654604f44Df1D5CbfeA736095444D5F6c88', 'Hashflow PMM'),('822E1755386DF8E38F5aadB0F0D328bBa88b4F50', 'Obric'),('69A52A0570636ca391175cE619Bbb9D348040Bbc', 'Dynamic Route'),('22ecf2Bed494976503A8087aDd30E134C30FBD98', 'Dynamic Route')) AS t(address, protocol_name)),-- 优化:添加时间范围限制,减少日志扫描量okx_arbitrum_tx AS (SELECTcontract_address,tx_hash,block_timeFROM arbitrum.logsWHERE 1=1AND block_time >= CURRENT_DATE - INTERVAL '30' DAYAND contract_address IN (0xf332761c673b59B21fF6dfa8adA44d78c12dEF09,0x01D8EDB8eF96119d6Bada3F50463DeE6fe863B4C,0x6088d94C5a40CEcd3ae2D4e0710cA687b91c61d0,0x69C236E021F5775B0D0328ded5EaC708E3B869DF,0x5e2F47bD7D4B357fCfd0Bb224Eb665773B1B9801,0x3608c8186fF3dCa322DeEFb8c27162162d581081,0x368E01160C2244B0363a35B3fF0A971E44a89284,0x7CF6b330b437E9fb432B1400DE17B03357Cf049A)AND topic0 = 0x1bb43f2da90e35f7b0cf38521ca95a49e68eb42fac49924930a5bd73cdf7576c),-- 优化:预先获取已知的dex交易hash,避免重复扫描existing_dex_hashes AS (SELECT DISTINCTa.tx_hash,a.projectFROM dex.trades AS aINNER JOIN okx_arbitrum_tx AS b ON a.tx_hash = b.tx_hashWHERE a.blockchain = 'arbitrum'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,a.tx_hash,a.tx_from as trader,