![Cover image for 1.5[Intermediate] Iceberg-Trino 如何解决链上数据面临的挑战](https://img.paragraph.com/cdn-cgi/image/format=auto,width=3840,quality=85/https://storage.googleapis.com/papyrus_images/5f9d4430fcd78103e346b51a95d59f17df5ed3a8d9b08ea412a1d2ba116f9247.jpg)
Footprint Analytics1.5[Intermediate] Iceberg-Trino 如何解决链上数据面临的挑战
此文章是 #Web3 data 系列 的其中一个章节。链上数据处理面临的挑战区块链数据公司，在索引以及处理链上数据时，可能会面临一些挑战，包括：海量数据。随着区块链上数据量的增加，数据索引将需要扩大规模以处理增加的负载并提供对数据的有效访问。因此，它导致了更高的存储成本；缓慢的指标计算和增加数据库服务器的负载。复杂的数据生产流程。区块链技术是复杂的，建立一个全面和可靠的数据索引需要对底层数据结构和算法有深刻的理解。这是由区块链实现方式的多样性所决定的。举一个具体的例子，以太坊中的 NFT 通常是在遵循 ERC721 和 ERC1155 格式的智能合约中进行创建的，而像Polkadot 上通常是直接在区块链运行时间内构建的。对于用户来说，不管是任何形式的存在，这些数据应该被视为 NFT 的交易，需要被存储，并且处理为可读状态，方便分析以及进行计算。集成能力。为了给用户提供最大的价值，区块链索引解决方案可能需要将其数据索引与其他系统集成，如分析平台或 API。这很有挑战性，需要在架构设计上投入大量精力。随着区块链技术的使用越来越广泛，存储在区块链上的数据量也在增加。这是因为更多的人在...
Footprint Analytics区块链游戏概览：回顾 2023，展望 2024
作者：lesley@footprint.network 数据来源：区块链游戏年报关键要点今年，比特币（BTC）市值从上年的低谷中强劲回升，相较之下，区块链游戏市场的增长虽然较为平淡，但年末也迎来了显著的上升势头。今年的游戏中，仅有 6% 的游戏拥有超过 1,000 个活跃钱包，相比去年的 10% 有所减少。2023 年，尽管整体交易量比 2022 年的高点有所下降，交易次数却相对稳定。2023 年，区块链游戏行业正在发展，而 AI 也在深刻改变着这一行业。Layer 2 区块链也发展迅速，但 BNB 链等老牌区块链仍占据市场。市场正在期待顶尖区块链游戏的出现，GambleFi 或许将迎来增长，但仍需面对监管挑战。在用户获取上，利用 Telegram 和 X（Twitter）等社交平台平台来触达庞大用户群体已成为一种趋势区块链游戏是 Web3 领域对于大众普及的重要催化剂，在简化复杂概念、让人们理解区块链技术上发挥着重要作用。与其他领域不同，传统游戏里早已存在的游戏货币和道具概念，使区块链游戏成为普罗大众更易理解和接受的 Web3 普及方案。 尽管 2023 年区块链游戏市场交易量...
Footprint Analytics如何使用Footrace 钱包监控功能和设置自定义的交易警报
本文将介绍如何使用 Footrace 监控 CEX 的钱包地址并设置自定义警报。 2022-06-12 本文将介绍如何使用 Footrace 监控 CEX 的钱包地址并设置自定义警报。 什么是 Footrace？ Footrace (Foot Trace) 是一个多链的钱包追踪监控平台，可以监控CEX、DEX、鲸鱼、聪明钱、或任何你想关注的地址的钱包。 Footrace 帮助投资者保护他们的投资并监控异常的市场和资金流动。您可以设置自定义警报，通过电子邮件、电报、Discord、SMS 等向您发送重要的资金流通知和警报。Footrace 中心化交易所监控页Footrace目前监控了 122 交易所的资金流活动及其钱包余额。包括了中心化和去中心化交易所的地址数据。由于这些交易所和基金可能会添加新的钱包地址，用户可以使用Footrace的钱包地址的功能来添加 Footrace 缺少的任何钱包地址。Footrace 地址提交工具 如何开始监控?钱包余额在监控交易所时，钱包余额是需要仔细关注的指标之一。具有大量钱包余额的交易所通常意味著有大量的客户，并反映出市场对此加密货币交易所的高度信...
Footprint Analytics 是一个全面的区块链数据分析平台，简化了 Web3 项目的分析和社区管理，实现可持续增长。
Footprint Analytics1.5[Intermediate] Iceberg-Trino 如何解决链上数据面临的挑战
此文章是 #Web3 data 系列 的其中一个章节。链上数据处理面临的挑战区块链数据公司，在索引以及处理链上数据时，可能会面临一些挑战，包括：海量数据。随着区块链上数据量的增加，数据索引将需要扩大规模以处理增加的负载并提供对数据的有效访问。因此，它导致了更高的存储成本；缓慢的指标计算和增加数据库服务器的负载。复杂的数据生产流程。区块链技术是复杂的，建立一个全面和可靠的数据索引需要对底层数据结构和算法有深刻的理解。这是由区块链实现方式的多样性所决定的。举一个具体的例子，以太坊中的 NFT 通常是在遵循 ERC721 和 ERC1155 格式的智能合约中进行创建的，而像Polkadot 上通常是直接在区块链运行时间内构建的。对于用户来说，不管是任何形式的存在，这些数据应该被视为 NFT 的交易，需要被存储，并且处理为可读状态，方便分析以及进行计算。集成能力。为了给用户提供最大的价值，区块链索引解决方案可能需要将其数据索引与其他系统集成，如分析平台或 API。这很有挑战性，需要在架构设计上投入大量精力。随着区块链技术的使用越来越广泛，存储在区块链上的数据量也在增加。这是因为更多的人在...
Footprint Analytics区块链游戏概览：回顾 2023，展望 2024
作者：lesley@footprint.network 数据来源：区块链游戏年报关键要点今年，比特币（BTC）市值从上年的低谷中强劲回升，相较之下，区块链游戏市场的增长虽然较为平淡，但年末也迎来了显著的上升势头。今年的游戏中，仅有 6% 的游戏拥有超过 1,000 个活跃钱包，相比去年的 10% 有所减少。2023 年，尽管整体交易量比 2022 年的高点有所下降，交易次数却相对稳定。2023 年，区块链游戏行业正在发展，而 AI 也在深刻改变着这一行业。Layer 2 区块链也发展迅速，但 BNB 链等老牌区块链仍占据市场。市场正在期待顶尖区块链游戏的出现，GambleFi 或许将迎来增长，但仍需面对监管挑战。在用户获取上，利用 Telegram 和 X（Twitter）等社交平台平台来触达庞大用户群体已成为一种趋势区块链游戏是 Web3 领域对于大众普及的重要催化剂，在简化复杂概念、让人们理解区块链技术上发挥着重要作用。与其他领域不同，传统游戏里早已存在的游戏货币和道具概念，使区块链游戏成为普罗大众更易理解和接受的 Web3 普及方案。 尽管 2023 年区块链游戏市场交易量...
Footprint Analytics如何使用Footrace 钱包监控功能和设置自定义的交易警报
本文将介绍如何使用 Footrace 监控 CEX 的钱包地址并设置自定义警报。 2022-06-12 本文将介绍如何使用 Footrace 监控 CEX 的钱包地址并设置自定义警报。 什么是 Footrace？ Footrace (Foot Trace) 是一个多链的钱包追踪监控平台，可以监控CEX、DEX、鲸鱼、聪明钱、或任何你想关注的地址的钱包。 Footrace 帮助投资者保护他们的投资并监控异常的市场和资金流动。您可以设置自定义警报，通过电子邮件、电报、Discord、SMS 等向您发送重要的资金流通知和警报。Footrace 中心化交易所监控页Footrace目前监控了 122 交易所的资金流活动及其钱包余额。包括了中心化和去中心化交易所的地址数据。由于这些交易所和基金可能会添加新的钱包地址，用户可以使用Footrace的钱包地址的功能来添加 Footrace 缺少的任何钱包地址。Footrace 地址提交工具 如何开始监控?钱包余额在监控交易所时，钱包余额是需要仔细关注的指标之一。具有大量钱包余额的交易所通常意味著有大量的客户，并反映出市场对此加密货币交易所的高度信...
Share Dialog
Share Dialog
Footprint Analytics 是一个全面的区块链数据分析平台，简化了 Web3 项目的分析和社区管理，实现可持续增长。
Subscribe to Footprint Analytics
Subscribe to Footprint Analytics
<100 subscribers
<100 subscribers
此文章是 #Footprint Analytics for SQL 系列 的其中一个章节。
数据湖是一个存储企业的各种各样原始数据的大型仓库，其中的数据可供存取、处理、分析及传输。数据湖是以其自然格式存储的数据的系统或存储库，通常是对象blob或文件。数据湖通常是企业所有数据的单一存储，包括源系统数据的原始副本，以及用于报告、可视化、分析和机器学习等任务的转换数据。数据湖可以包括来自关系数据库（行和列）的结构化数据，半结构化数据（CSV，日志，XML，JSON），非结构化数据（电子邮件，文档，PDF）和二进制数据（图像，音频，视频）
SQL ，又称结构化查询语言 (Structured Query Language)，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
举个例子：假如你想吃披萨，榴莲味的，你现在又没有空，于是你叫了一个跑腿和跑腿的人说：帮我买一个披萨，要榴莲味的，跑腿就会去披萨店帮你买了一个榴莲味的披萨送到你家里。类别过来SQL就是你对跑腿说的话，Footprint Analytics 就是个跑腿，可以让你和数据湖对话，并将你想要的数据搬出来给你。SQL最基本的语法结构包含三个模块，几乎所有的SQL都包含这三部分：
select: 选择哪个字段？
from: 从哪个表中取数据？
where：筛选或者限制的条件是什么？
以ethereum_transactions(以太坊上的transactions记录)为例：
下面是这个表里用的比较多的几个字段
block_timestamp 打包交易的时间
hash 这条交易流水的hash
block_number 交易的区块高度
value 转出的ETH(需要除以ETH 代币的18位decimals换算精度，power(10,18))
from_address 转出ETH代币的钱包地址
to_address 转入ETH代币的合约地址
receipt_gas_used 被执行的命令的gas消耗值总和
receipt_effective_gas_price gas该交易中单位gas的价格（用ETH代币计算）
案例1: 查询币圈大佬孙哥的钱包(0x3DdfA8eC3052539b6C9549F12cEA2C295cfF5296）在2022年1月份以来的每一笔转出金额大于1000ETH是在什么时候和具体的转出数量已经每笔转出消耗的Gas
SQL
“
select --Select后跟着需要查询的字段，多个字段用英文逗号分隔
block_timestamp
,from_address
,to_address
,hash
,value /power(10,18) as value --通过将value除以/power(10,18)来换算精度，18是ETH代币的精度
,(receipt_gas_used*receipt_effective_gas_price*power(0.1,18)) as gas_fee
from ethereum_transactions --从 ethereum.transactions表中获取数据
where block_timestamp > date '2022-01-01' --限制Transfer时间是在2022年1月1日之后
and from_address = lower('0x3DdfA8eC3052539b6C9549F12cEA2C295cfF5296') --限制孙哥的钱包，这里用lower()将字符串里的字母变成小写格式(footprint数据库里存的模式是小写，直接从以太坊浏览器粘贴可能大些混着小写)
and value /power(10,18) >1000 --限制ETH Transfer量大于1000
order by block_timestamp --基于block_timestamp做升序排列，如果想降序排列需要在末尾加desc，因为默认是asc，所以不用末尾不用加asc也行
”
Footprint Query URL
https://www.footprint.network/chart/Jastin-Sun-Transfers-%3E1000ETH-Transactions-fp-34869
语法说明：
select
select 后面跟着需要查询的字段，多个字段用英文逗号隔开
from
from 跟着数据来源的表
where
where 后面跟着对数据的筛选条件
运算符号：and / or
如果筛选条件有多个，可以用运算符来连接
and: 多个条件的并集
or : 多个条件的交集
排序： order by [字段A] , 按照A字段升序，如果需要按照降序排序就在字段末尾加上 desc,因为默认是升序的，所以需要升序的时候不用在末尾加asc
幂乘计算：用户换算精度，函数是Power(Number,Power),其中number表示底数，power表示指数
字符串中字母换算大小写
lower([字段]):字符串中的字母统一换成小写
upper([字段]) :字符串中的字母统一换成小写
案例2:表里的数据都是明细数据，我如果需要把数据汇总，了解数据的概况，需要如何做呢？
SQL:
“
select
sum( value /power(10,18) ) as value --对符合要求的数据的value字段求和
,max( value /power(10,18) ) as max_value --求最大值
,min( value /power(10,18) ) as min_value--求最小值
,count( hash ) as tx_count --对符合要求的数据计数，统计有多少条
,count( distinct to_address ) as tx_to_address_count --对符合要求的数据计数，统计有多少条(按照去向地址to去重)
from ethereum_transactions --从 ethereum.transactions表中获取数据
where block_timestamp > date '2022-01-01' --限制Transfer时间是在2022年1月1日之后
and from_address = lower('0x3DdfA8eC3052539b6C9549F12cEA2C295cfF5296') --限制孙哥的钱包，这里用lower()将字符串里的字母变成小写格式(footprint数据库里存的模式是小写，直接从以太坊浏览器粘贴可能大些混着小写)
and value /power(10,18) > 1000 --限制ETH Transfer量大于1000
“
Footprint Query URL
语法说明：
聚合函数
count()：计数，统计有多少个；如果需要去重计数，括号内加distinct
sum()：求和
min()：求最小值
max()：求最大值
avg()：求平均
案例3: 我想要按小时/天/周来看趋势
3.1 把时间戳转化成小时/天/周的格式，方便进一步做聚合统计
SQL
“
select --Select后跟着需要查询的字段，多个字段用英文逗号分隔
block_timestamp -- transactions发生的时间
,date_trunc('hour',block_timestamp) as block_hour -- 转化成小时的粒度
,date_trunc('day',block_timestamp) as block_date -- 转化成天的粒度
,date_trunc('week',block_timestamp) as block_week -- 转化成周的粒度
,from_address
,to_address
,hash
,value /power(10,18) as value --通过将value除以/power(10,18)来换算精度，18是ETH代币的精度
,(receipt_gas_used*receipt_effective_gas_price*power(0.1,18)) as gas_fee
from ethereum_transactions --从 ethereum.transactions表中获取数据
where block_timestamp > date '2022-01-01' --限制Transfer时间是在2022年1月1日之后
and from_address = lower('0x3DdfA8eC3052539b6C9549F12cEA2C295cfF5296') --限制孙哥的钱包，这里用lower()将字符串里的字母变成小写格式(footprint数据库里存的模式是小写，直接从以太坊浏览器粘贴可能大些混着小写)
and value /power(10,18) >1000 --限制ETH Transfer量大于100
order by block_timestamp --基于block_timestamp做升序排列，如果想降序排列需要在末尾加desc，因为默认是asc，所以不用末尾不用加asc也行
”
Footprint Query URL
https://www.footprint.network/chart/Jastin-Sun-Transfers-%3E1000ETH-Transactions-DateTrunc-fp-34873
语法说明
date_trunc(‘datepart’,timestamp)
minute:将输入时间戳截断至分钟
hour:将输入时间戳截断至小时
day:将输入时间戳截断至天
week:将输入时间戳截断至某周的星期一
year:将输入时间戳截断至一年的第一天
3.2 基于之前得到的处理后的时间字段，使用 group by + sum 完成分组聚合
SQL
”
select --Select后跟着需要查询的字段，多个字段用英文逗号分隔
date_trunc('day',block_timestamp) as block_date -- 转化成天的粒度
,sum(value /power(10,18)) as value --通过将value除以/power(10,18)来换算精度，18是ETH代币的精度
,sum(receipt_gas_used*receipt_effective_gas_price*power(0.1,18)) as gas_fee
from ethereum_transactions --从 ethereum.transactions表中获取数据
where block_timestamp > date '2022-01-01' --限制Transfer时间是在2022年1月1日之后
and from_address = lower('0x3DdfA8eC3052539b6C9549F12cEA2C295cfF5296') --限制孙哥的钱包，这里用lower()将字符串里的字母变成小写格式(footprint数据库里存的模式是小写，直接从以太坊浏览器粘贴可能大些混着小写)
and value /power(10,18) >1000 --限制ETH Transfer量大于1000
group by 1 --按照第一个字段做分组，即date_trunc('day',block_timestamp) 转化后的字段
order by block_date --基于block_date，做升序排列，block_date 是用'as'对date_trunc('day',block_time)取别名，也可以写‘1’即表第几个字段，，如果想降序排列需要在末尾加desc，因为默认是asc，所以不用末尾不用加asc也行
“
Footprint Query URL
https://www.footprint.network/chart/Jastin-Sun-Transfers-%3E1000ETH-Transactions-by-day-fp-34877
语法说明：
分组聚合（group by）
分组聚合的语法是 group by + 分组的字段，顾名思义就是先分组后聚合，需要配合聚合函数来使用
假设上面的表格是三个销售成员2020年前2个月的销售额，如果你用sum，那你只能得到497000；如果你想得到右边两种统计数据，那就需要用到分组聚合 group by 按照成员或者月份分组聚合
案例4: 我想看当时孙哥转出ETH的换算回USD金额是多少
SQL
”
select
block_timestamp
,transactions_info.block_hour as block_hour
,from_address
,to_address
,eth_amount
,hash
,price
,eth_amount*price as usd_value – eth 的数量乘以币价的价格
from
(
select --Select后跟着需要查询的字段，多个字段用英文逗号分隔
block_timestamp
,date_trunc('hour',block_timestamp) as block_hour --把block_timestamp用date_trunc处理成小时，方便作为主键去关联
,from_address
,to_address
,hash
,value /power(10,18) as eth_amount --通过将value除以/power(10,18)来换算精度，18是ETH代币的精度
,(receipt_gas_used*receipt_effective_gas_price*power(0.1,18)) as gas_fee
from ethereum_transactions --从 ethereum.transactions表中获取数据
where block_timestamp > date '2022-01-01' --限制Transfer时间是在2022年1月1日之后
and from_address = lower('0x3DdfA8eC3052539b6C9549F12cEA2C295cfF5296') --限制孙哥的钱包，这里用lower()将字符串里的字母变成小写格式(footprint数据库里存的模式是小写，直接从以太坊浏览器粘贴可能大些混着小写)
and value /power(10,18) >1000 --限制ETH Transfer量大于1000 order by block_timestamp --基于block_timestamp做升序排列，如果想降序排列需要在末尾加desc，因为默认是asc，所以不用末尾不用加asc也行
) as transactions_info
left join
--将transactions_info与price_info的数据关联，关联方式为 left join
(
select
date_trunc('hour',timestamp) as block_hour --把timestamp用date_trunc处理成小时，方便作为主键去关联
,avg(price) as price
from
"token_price_5min"
where token_symbol = 'ETH' -- 取ETH 代币的数据
and chain ='Ethereum' -- 取以太坊上的价格数据
group by 1
) price_info
on transactions_info.block_hour = price_info.block_hour
“
Footprint Query URL
联表查询
大部分情况下我们需要的数据不在同一张表中，比如transactions表存的只有transaction的数据，没有代币币价的数据。如果我们希望能够计算出transaction对应的USD 价值，那就需要用联表查询把价格的数据给关联进来
联表查询可以理解为把两张表通过一定的条件关联起来形成一张虚拟的表，你可以方便的对虚拟表做更多的处理
联表查询由两个部分组成
联表方式（join，left join rigth join , cross join ,full join）
关联条件（on）多个条件用 and 连接
下面是关联条件的结构图
用的比较多的联表方式是left join,下面是一个示例
left join：以左表为主，把右表按照关联条件(on)往左表去关联，如果关联不到就用null填充
Table A 跟 Table B通过姓名关联，因为是以左表为主，所以尽管左表中lucy和在右表中没有符合关联条件的数据，但是lucy会出现在结果中，右表那部分因为关联不到数据，因此都用null填充
案例5：把案例4的明细数据按天分组聚合，不想嵌套太多层sql
SQL
“
with transaction_info as -- 通过with as 建立子查询并且命名为 transaction_info
(
select
block_timestamp
,transactions_info.block_hour as block_hour
,from_address
,to_address
,eth_amount
,hash
,price
,eth_amount*price as usd_value
from
(
select --Select后跟着需要查询的字段，多个字段用英文逗号分隔
block_timestamp
,date_trunc('hour',block_timestamp) as block_hour --把block_timestamp用date_trunc处理成小时，方便作为主键去关联
,from_address
,to_address
,hash
,value /power(10,18) as eth_amount --通过将value除以/power(10,18)来换算精度，18是ETH代币的精度
,(receipt_gas_used*receipt_effective_gas_price*power(0.1,18)) as gas_fee
from ethereum_transactions --从 ethereum.transactions表中获取数据
where block_timestamp > date '2022-01-01' --限制Transfer时间是在2022年1月1日之后
and from_address = lower('0x3DdfA8eC3052539b6C9549F12cEA2C295cfF5296') --限制孙哥的钱包，这里用lower()将字符串里的字母变成小写格式(footprint数据库里存的模式是小写，直接从以太坊浏览器粘贴可能大些混着小写)
and value /power(10,18) >1000 --限制ETH Transfer量大于500
order by block_timestamp --基于block_timestamp做升序排列，如果想降序排列需要在末尾加desc，因为默认是asc，所以不用末尾不用加asc也行
) as transactions_info
left join
--将transactions_info与price_info的数据关联，关联方式为 left join
(
select
date_trunc('hour',timestamp) as block_hour --把timestamp用date_trunc处理成小时，方便作为主键去关联
,avg(price) as price
from
"token_price_5min"
where token_symbol = 'ETH' -- 取ETH 代币的数据
and chain ='Ethereum' -- 取以太坊上的价格数据
group by 1
) price_info
on transactions_info.block_hour = price_info.block_hour
)
select
date_trunc('day',block_timestamp) as block_date
,sum(eth_amount) as eth_amount
,sum(usd_value) as usd_value
from transaction_info -- 从子查询形成的'虚拟表', transactions_info 中取需要的数据
group by 1 -- 按照第一个字段进行分组，也可以用 date_trunc('day',block_timestamp) 进行替换
order by 1 -- 按照第一个字段进行升序排序，也可以用 date_trunc('day',block_timestamp) 或者别名block_date进行替换做升序排序
”
Footprint Query URL
语法说明
子查询( with as )
通过with as 可以构建一个子查询，把一段 SQL 的结果变成一个’虚拟表’ （可以说是一个视图或者子查询），接下来的SQL中可以直接从这个’虚拟表/视图’中取数据
作用：通过 with as 可以比较好的提高SQL逻辑的可读性，避免多重嵌套
语法： with [视图名] as ( [SQL 逻辑 ]) ，多个视图用 ‘,’ 分隔
案例6：计算最近90天内孙哥每天TUSD稳定币转入转出情况
SQL
”
with out_tusd_amount as ( -- 通过with as 建立子查询并且命名为 out_usdc_amount
select
date_trunc('day',block_timestamp) as block_date
,sum(amount_raw*power(0.1,18)) as amount -- TUSD代币的精度换算
,'out_amount' as transfer_type -- 新增一列并且命名为transfer_type,值为 out_amount
from ethereum_token_transfers
where
block_timestamp >= date_add('day',-90,current_date) -- 获取最近90天的数据
and token_address = lower('0x0000000000085d4780B73119b644AE5ecd22b376') -- 筛选TUSD代币的地址的数据，TUSD的Decimals : 18,
and from_address = lower('0x3DdfA8eC3052539b6C9549F12cEA2C295cfF5296') -- 筛选从justin sun 的 地址中转出的金额
group by 1
)
,in_tusd_amount as ( -- 建立子查询并且命名为 in_TUSD_amount
select
date_trunc('day',block_timestamp) as block_date
,sum(amount_raw*power(0.1,18)) as amount -- TUSD代币的精度换算
,'in_amount' as transfer_type -- 新增一列并且命名为transfer_type，值为in_amount** **
from ethereum_token_transfers
where
block_timestamp >= date_add('day',-90,current_date) -- 筛选最近90天的数据
and token_address = lower('0x0000000000085d4780B73119b644AE5ecd22b376') -- 筛选TUSD代币的地址的数据，USDC的Decimals : 18,
and to_address = lower('0x3DdfA8eC3052539b6C9549F12cEA2C295cfF5296') -- 筛选从justin sun 的 地址中转入的金额
group by 1
)
select
block_date
,transfer_type
,sum(amount) as amount
from (
select * from out_tusd_amount
union all
select * from in_tusd_amount
) as un
group by 1,2 -- 根据第一个和第二个字段进行分组，即block_date和transfer_type 两个字段
order by 1
“
Footprint Query URL
https://www.footprint.network/chart/Justin-Sun%27s-TUSD-Token-in-and-out-amount-fp-34948
语法说明
合并表 (union, union all)
UNION 操作符选取不同的值。如果允许重复的值，使用 UNION ALL。
用法：UNION 操作符用于合并两个或多个 SELECT 语句的结果集
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
语法
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
推荐阅读
#Footprint Analytics for SQL
Footprint Analytics 是首家 Crypto 领域支持无代码数据分析平台。平台还提供一个统一的数据 API，让用户可以快速检索超过23条公链生态的 NFT，GameFi 以及 DeFi 数据。
如果您对该课程有任何反馈或建议，您可以通过以下方式联系我们。
Footprint Website: https://www.footprint.network
Discord: https://discord.gg/3HYaR6USM7
Twitter: https://twitter.com/Footprint_Data
此文章是 #Footprint Analytics for SQL 系列 的其中一个章节。
数据湖是一个存储企业的各种各样原始数据的大型仓库，其中的数据可供存取、处理、分析及传输。数据湖是以其自然格式存储的数据的系统或存储库，通常是对象blob或文件。数据湖通常是企业所有数据的单一存储，包括源系统数据的原始副本，以及用于报告、可视化、分析和机器学习等任务的转换数据。数据湖可以包括来自关系数据库（行和列）的结构化数据，半结构化数据（CSV，日志，XML，JSON），非结构化数据（电子邮件，文档，PDF）和二进制数据（图像，音频，视频）
SQL ，又称结构化查询语言 (Structured Query Language)，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
举个例子：假如你想吃披萨，榴莲味的，你现在又没有空，于是你叫了一个跑腿和跑腿的人说：帮我买一个披萨，要榴莲味的，跑腿就会去披萨店帮你买了一个榴莲味的披萨送到你家里。类别过来SQL就是你对跑腿说的话，Footprint Analytics 就是个跑腿，可以让你和数据湖对话，并将你想要的数据搬出来给你。SQL最基本的语法结构包含三个模块，几乎所有的SQL都包含这三部分：
select: 选择哪个字段？
from: 从哪个表中取数据？
where：筛选或者限制的条件是什么？
以ethereum_transactions(以太坊上的transactions记录)为例：
下面是这个表里用的比较多的几个字段
block_timestamp 打包交易的时间
hash 这条交易流水的hash
block_number 交易的区块高度
value 转出的ETH(需要除以ETH 代币的18位decimals换算精度，power(10,18))
from_address 转出ETH代币的钱包地址
to_address 转入ETH代币的合约地址
receipt_gas_used 被执行的命令的gas消耗值总和
receipt_effective_gas_price gas该交易中单位gas的价格（用ETH代币计算）
案例1: 查询币圈大佬孙哥的钱包(0x3DdfA8eC3052539b6C9549F12cEA2C295cfF5296）在2022年1月份以来的每一笔转出金额大于1000ETH是在什么时候和具体的转出数量已经每笔转出消耗的Gas
SQL
“
select --Select后跟着需要查询的字段，多个字段用英文逗号分隔
block_timestamp
,from_address
,to_address
,hash
,value /power(10,18) as value --通过将value除以/power(10,18)来换算精度，18是ETH代币的精度
,(receipt_gas_used*receipt_effective_gas_price*power(0.1,18)) as gas_fee
from ethereum_transactions --从 ethereum.transactions表中获取数据
where block_timestamp > date '2022-01-01' --限制Transfer时间是在2022年1月1日之后
and from_address = lower('0x3DdfA8eC3052539b6C9549F12cEA2C295cfF5296') --限制孙哥的钱包，这里用lower()将字符串里的字母变成小写格式(footprint数据库里存的模式是小写，直接从以太坊浏览器粘贴可能大些混着小写)
and value /power(10,18) >1000 --限制ETH Transfer量大于1000
order by block_timestamp --基于block_timestamp做升序排列，如果想降序排列需要在末尾加desc，因为默认是asc，所以不用末尾不用加asc也行
”
Footprint Query URL
https://www.footprint.network/chart/Jastin-Sun-Transfers-%3E1000ETH-Transactions-fp-34869
语法说明：
select
select 后面跟着需要查询的字段，多个字段用英文逗号隔开
from
from 跟着数据来源的表
where
where 后面跟着对数据的筛选条件
运算符号：and / or
如果筛选条件有多个，可以用运算符来连接
and: 多个条件的并集
or : 多个条件的交集
排序： order by [字段A] , 按照A字段升序，如果需要按照降序排序就在字段末尾加上 desc,因为默认是升序的，所以需要升序的时候不用在末尾加asc
幂乘计算：用户换算精度，函数是Power(Number,Power),其中number表示底数，power表示指数
字符串中字母换算大小写
lower([字段]):字符串中的字母统一换成小写
upper([字段]) :字符串中的字母统一换成小写
案例2:表里的数据都是明细数据，我如果需要把数据汇总，了解数据的概况，需要如何做呢？
SQL:
“
select
sum( value /power(10,18) ) as value --对符合要求的数据的value字段求和
,max( value /power(10,18) ) as max_value --求最大值
,min( value /power(10,18) ) as min_value--求最小值
,count( hash ) as tx_count --对符合要求的数据计数，统计有多少条
,count( distinct to_address ) as tx_to_address_count --对符合要求的数据计数，统计有多少条(按照去向地址to去重)
from ethereum_transactions --从 ethereum.transactions表中获取数据
where block_timestamp > date '2022-01-01' --限制Transfer时间是在2022年1月1日之后
and from_address = lower('0x3DdfA8eC3052539b6C9549F12cEA2C295cfF5296') --限制孙哥的钱包，这里用lower()将字符串里的字母变成小写格式(footprint数据库里存的模式是小写，直接从以太坊浏览器粘贴可能大些混着小写)
and value /power(10,18) > 1000 --限制ETH Transfer量大于1000
“
Footprint Query URL
语法说明：
聚合函数
count()：计数，统计有多少个；如果需要去重计数，括号内加distinct
sum()：求和
min()：求最小值
max()：求最大值
avg()：求平均
案例3: 我想要按小时/天/周来看趋势
3.1 把时间戳转化成小时/天/周的格式，方便进一步做聚合统计
SQL
“
select --Select后跟着需要查询的字段，多个字段用英文逗号分隔
block_timestamp -- transactions发生的时间
,date_trunc('hour',block_timestamp) as block_hour -- 转化成小时的粒度
,date_trunc('day',block_timestamp) as block_date -- 转化成天的粒度
,date_trunc('week',block_timestamp) as block_week -- 转化成周的粒度
,from_address
,to_address
,hash
,value /power(10,18) as value --通过将value除以/power(10,18)来换算精度，18是ETH代币的精度
,(receipt_gas_used*receipt_effective_gas_price*power(0.1,18)) as gas_fee
from ethereum_transactions --从 ethereum.transactions表中获取数据
where block_timestamp > date '2022-01-01' --限制Transfer时间是在2022年1月1日之后
and from_address = lower('0x3DdfA8eC3052539b6C9549F12cEA2C295cfF5296') --限制孙哥的钱包，这里用lower()将字符串里的字母变成小写格式(footprint数据库里存的模式是小写，直接从以太坊浏览器粘贴可能大些混着小写)
and value /power(10,18) >1000 --限制ETH Transfer量大于100
order by block_timestamp --基于block_timestamp做升序排列，如果想降序排列需要在末尾加desc，因为默认是asc，所以不用末尾不用加asc也行
”
Footprint Query URL
https://www.footprint.network/chart/Jastin-Sun-Transfers-%3E1000ETH-Transactions-DateTrunc-fp-34873
语法说明
date_trunc(‘datepart’,timestamp)
minute:将输入时间戳截断至分钟
hour:将输入时间戳截断至小时
day:将输入时间戳截断至天
week:将输入时间戳截断至某周的星期一
year:将输入时间戳截断至一年的第一天
3.2 基于之前得到的处理后的时间字段，使用 group by + sum 完成分组聚合
SQL
”
select --Select后跟着需要查询的字段，多个字段用英文逗号分隔
date_trunc('day',block_timestamp) as block_date -- 转化成天的粒度
,sum(value /power(10,18)) as value --通过将value除以/power(10,18)来换算精度，18是ETH代币的精度
,sum(receipt_gas_used*receipt_effective_gas_price*power(0.1,18)) as gas_fee
from ethereum_transactions --从 ethereum.transactions表中获取数据
where block_timestamp > date '2022-01-01' --限制Transfer时间是在2022年1月1日之后
and from_address = lower('0x3DdfA8eC3052539b6C9549F12cEA2C295cfF5296') --限制孙哥的钱包，这里用lower()将字符串里的字母变成小写格式(footprint数据库里存的模式是小写，直接从以太坊浏览器粘贴可能大些混着小写)
and value /power(10,18) >1000 --限制ETH Transfer量大于1000
group by 1 --按照第一个字段做分组，即date_trunc('day',block_timestamp) 转化后的字段
order by block_date --基于block_date，做升序排列，block_date 是用'as'对date_trunc('day',block_time)取别名，也可以写‘1’即表第几个字段，，如果想降序排列需要在末尾加desc，因为默认是asc，所以不用末尾不用加asc也行
“
Footprint Query URL
https://www.footprint.network/chart/Jastin-Sun-Transfers-%3E1000ETH-Transactions-by-day-fp-34877
语法说明：
分组聚合（group by）
分组聚合的语法是 group by + 分组的字段，顾名思义就是先分组后聚合，需要配合聚合函数来使用
假设上面的表格是三个销售成员2020年前2个月的销售额，如果你用sum，那你只能得到497000；如果你想得到右边两种统计数据，那就需要用到分组聚合 group by 按照成员或者月份分组聚合
案例4: 我想看当时孙哥转出ETH的换算回USD金额是多少
SQL
”
select
block_timestamp
,transactions_info.block_hour as block_hour
,from_address
,to_address
,eth_amount
,hash
,price
,eth_amount*price as usd_value – eth 的数量乘以币价的价格
from
(
select --Select后跟着需要查询的字段，多个字段用英文逗号分隔
block_timestamp
,date_trunc('hour',block_timestamp) as block_hour --把block_timestamp用date_trunc处理成小时，方便作为主键去关联
,from_address
,to_address
,hash
,value /power(10,18) as eth_amount --通过将value除以/power(10,18)来换算精度，18是ETH代币的精度
,(receipt_gas_used*receipt_effective_gas_price*power(0.1,18)) as gas_fee
from ethereum_transactions --从 ethereum.transactions表中获取数据
where block_timestamp > date '2022-01-01' --限制Transfer时间是在2022年1月1日之后
and from_address = lower('0x3DdfA8eC3052539b6C9549F12cEA2C295cfF5296') --限制孙哥的钱包，这里用lower()将字符串里的字母变成小写格式(footprint数据库里存的模式是小写，直接从以太坊浏览器粘贴可能大些混着小写)
and value /power(10,18) >1000 --限制ETH Transfer量大于1000 order by block_timestamp --基于block_timestamp做升序排列，如果想降序排列需要在末尾加desc，因为默认是asc，所以不用末尾不用加asc也行
) as transactions_info
left join
--将transactions_info与price_info的数据关联，关联方式为 left join
(
select
date_trunc('hour',timestamp) as block_hour --把timestamp用date_trunc处理成小时，方便作为主键去关联
,avg(price) as price
from
"token_price_5min"
where token_symbol = 'ETH' -- 取ETH 代币的数据
and chain ='Ethereum' -- 取以太坊上的价格数据
group by 1
) price_info
on transactions_info.block_hour = price_info.block_hour
“
Footprint Query URL
联表查询
大部分情况下我们需要的数据不在同一张表中，比如transactions表存的只有transaction的数据，没有代币币价的数据。如果我们希望能够计算出transaction对应的USD 价值，那就需要用联表查询把价格的数据给关联进来
联表查询可以理解为把两张表通过一定的条件关联起来形成一张虚拟的表，你可以方便的对虚拟表做更多的处理
联表查询由两个部分组成
联表方式（join，left join rigth join , cross join ,full join）
关联条件（on）多个条件用 and 连接
下面是关联条件的结构图
用的比较多的联表方式是left join,下面是一个示例
left join：以左表为主，把右表按照关联条件(on)往左表去关联，如果关联不到就用null填充
Table A 跟 Table B通过姓名关联，因为是以左表为主，所以尽管左表中lucy和在右表中没有符合关联条件的数据，但是lucy会出现在结果中，右表那部分因为关联不到数据，因此都用null填充
案例5：把案例4的明细数据按天分组聚合，不想嵌套太多层sql
SQL
“
with transaction_info as -- 通过with as 建立子查询并且命名为 transaction_info
(
select
block_timestamp
,transactions_info.block_hour as block_hour
,from_address
,to_address
,eth_amount
,hash
,price
,eth_amount*price as usd_value
from
(
select --Select后跟着需要查询的字段，多个字段用英文逗号分隔
block_timestamp
,date_trunc('hour',block_timestamp) as block_hour --把block_timestamp用date_trunc处理成小时，方便作为主键去关联
,from_address
,to_address
,hash
,value /power(10,18) as eth_amount --通过将value除以/power(10,18)来换算精度，18是ETH代币的精度
,(receipt_gas_used*receipt_effective_gas_price*power(0.1,18)) as gas_fee
from ethereum_transactions --从 ethereum.transactions表中获取数据
where block_timestamp > date '2022-01-01' --限制Transfer时间是在2022年1月1日之后
and from_address = lower('0x3DdfA8eC3052539b6C9549F12cEA2C295cfF5296') --限制孙哥的钱包，这里用lower()将字符串里的字母变成小写格式(footprint数据库里存的模式是小写，直接从以太坊浏览器粘贴可能大些混着小写)
and value /power(10,18) >1000 --限制ETH Transfer量大于500
order by block_timestamp --基于block_timestamp做升序排列，如果想降序排列需要在末尾加desc，因为默认是asc，所以不用末尾不用加asc也行
) as transactions_info
left join
--将transactions_info与price_info的数据关联，关联方式为 left join
(
select
date_trunc('hour',timestamp) as block_hour --把timestamp用date_trunc处理成小时，方便作为主键去关联
,avg(price) as price
from
"token_price_5min"
where token_symbol = 'ETH' -- 取ETH 代币的数据
and chain ='Ethereum' -- 取以太坊上的价格数据
group by 1
) price_info
on transactions_info.block_hour = price_info.block_hour
)
select
date_trunc('day',block_timestamp) as block_date
,sum(eth_amount) as eth_amount
,sum(usd_value) as usd_value
from transaction_info -- 从子查询形成的'虚拟表', transactions_info 中取需要的数据
group by 1 -- 按照第一个字段进行分组，也可以用 date_trunc('day',block_timestamp) 进行替换
order by 1 -- 按照第一个字段进行升序排序，也可以用 date_trunc('day',block_timestamp) 或者别名block_date进行替换做升序排序
”
Footprint Query URL
语法说明
子查询( with as )
通过with as 可以构建一个子查询，把一段 SQL 的结果变成一个’虚拟表’ （可以说是一个视图或者子查询），接下来的SQL中可以直接从这个’虚拟表/视图’中取数据
作用：通过 with as 可以比较好的提高SQL逻辑的可读性，避免多重嵌套
语法： with [视图名] as ( [SQL 逻辑 ]) ，多个视图用 ‘,’ 分隔
案例6：计算最近90天内孙哥每天TUSD稳定币转入转出情况
SQL
”
with out_tusd_amount as ( -- 通过with as 建立子查询并且命名为 out_usdc_amount
select
date_trunc('day',block_timestamp) as block_date
,sum(amount_raw*power(0.1,18)) as amount -- TUSD代币的精度换算
,'out_amount' as transfer_type -- 新增一列并且命名为transfer_type,值为 out_amount
from ethereum_token_transfers
where
block_timestamp >= date_add('day',-90,current_date) -- 获取最近90天的数据
and token_address = lower('0x0000000000085d4780B73119b644AE5ecd22b376') -- 筛选TUSD代币的地址的数据，TUSD的Decimals : 18,
and from_address = lower('0x3DdfA8eC3052539b6C9549F12cEA2C295cfF5296') -- 筛选从justin sun 的 地址中转出的金额
group by 1
)
,in_tusd_amount as ( -- 建立子查询并且命名为 in_TUSD_amount
select
date_trunc('day',block_timestamp) as block_date
,sum(amount_raw*power(0.1,18)) as amount -- TUSD代币的精度换算
,'in_amount' as transfer_type -- 新增一列并且命名为transfer_type，值为in_amount** **
from ethereum_token_transfers
where
block_timestamp >= date_add('day',-90,current_date) -- 筛选最近90天的数据
and token_address = lower('0x0000000000085d4780B73119b644AE5ecd22b376') -- 筛选TUSD代币的地址的数据，USDC的Decimals : 18,
and to_address = lower('0x3DdfA8eC3052539b6C9549F12cEA2C295cfF5296') -- 筛选从justin sun 的 地址中转入的金额
group by 1
)
select
block_date
,transfer_type
,sum(amount) as amount
from (
select * from out_tusd_amount
union all
select * from in_tusd_amount
) as un
group by 1,2 -- 根据第一个和第二个字段进行分组，即block_date和transfer_type 两个字段
order by 1
“
Footprint Query URL
https://www.footprint.network/chart/Justin-Sun%27s-TUSD-Token-in-and-out-amount-fp-34948
语法说明
合并表 (union, union all)
UNION 操作符选取不同的值。如果允许重复的值，使用 UNION ALL。
用法：UNION 操作符用于合并两个或多个 SELECT 语句的结果集
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
语法
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
推荐阅读
#Footprint Analytics for SQL
Footprint Analytics 是首家 Crypto 领域支持无代码数据分析平台。平台还提供一个统一的数据 API，让用户可以快速检索超过23条公链生态的 NFT，GameFi 以及 DeFi 数据。
如果您对该课程有任何反馈或建议，您可以通过以下方式联系我们。
Footprint Website: https://www.footprint.network
Discord: https://discord.gg/3HYaR6USM7
Twitter: https://twitter.com/Footprint_Data
No activity yet