Query editor
WITH-- 协议地址映射表(转换为二进制格式以提高匹配效率)protocol_mapping AS (SELECTLOWER(address) as address_str,FROM_HEX(address) as address_binary,protocol_nameFROM (VALUES('bF7A931cb46E0DAA118d90f44fDe285fe60438A7', 'Aspecta'),('5280D6afe6321c958cAce4029616d677957eb43B', 'fourmeme'),('aA3DC376c744A2EF29Ad84e116d1B5c46A3092b3', 'fourmeme'),('Ecd7Eef15713997528896Cb5db7ec316Db4C2101', 'Hashflow PMM'),('3cd22243d44f78b06e2e663083cefbd70ac095dd', 'Obric'),('69A52A0570636ca391175cE619Bbb9D348040Bbc', 'Dynamic Route'),('22ecf2Bed494976503A8087aDd30E134C30FBD98', 'Dynamic Route')) AS t(address, protocol_name)),-- 优化:添加时间范围限制,减少日志扫描量okx_bnb_tx AS (SELECTcontract_address,tx_hash,block_timeFROM bnb.logsWHERE 1=1AND block_time >= CURRENT_DATE - INTERVAL '30' DAYAND contract_address IN (0x9333C74BDd1E118634fE5664ACA7a9710b108Bab,0xc44Ad35B5A41C428c0eAE842F20F84D1ff6ed917,0x9b9efa5Efa731EA9Bbb0369E91fA17Abf249CFD4,0xA2604105e84DAe6eccEa41E61123AB8d9B3AfD75,0x6015126d7D23648C2e4466693b8DeaB005ffaba8,0xd547Eafde2410e63300Fc5308CceA0b356E7b5d8,0x3156020dfF8D99af1dDC523ebDfb1ad2018554a0,0x62cceF0b4545166f721cAa9fEe13c1d3767E27dc)AND topic0 = 0x1bb43f2da90e35f7b0cf38521ca95a49e68eb42fac49924930a5bd73cdf7576c),-- 优化:预先获取已知的dex交易hash,避免重复扫描existing_dex_hashes AS (SELECT DISTINCTa.tx_hash,a.projectFROM dex.trades AS aINNER JOIN okx_bnb_tx AS b ON a.tx_hash = b.tx_hashWHERE a.blockchain = 'bnb'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,