7.1 [Intermediate] SQL基础(一)

Footprint Analytics

0xf4De

January 5th, 2023

此文章是 #Footprint Analytics for SQL 系列的其中一个章节。

基础简介：

数据湖是什么？

数据湖是一个存储企业的各种各样原始数据的大型仓库，其中的数据可供存取、处理、分析及传输。数据湖是以其自然格式存储的数据的系统或存储库，通常是对象blob或文件。数据湖通常是企业所有数据的单一存储，包括源系统数据的原始副本，以及用于报告、可视化、分析和机器学习等任务的转换数据。数据湖可以包括来自关系数据库（行和列）的结构化数据，半结构化数据（CSV，日志，XML，JSON），非结构化数据（电子邮件，文档，PDF）和二进制数据（图像，音频，视频）

SQL是什么？

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

语法说明：

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 value /power(10,18) > 1000 --限制ETH Transfer量大于1000

“

Footprint Query URL

https://www.footprint.network/chart/Summary-of-Justin-Sun%27s-transfer-of-ETH-tokens-%3E-1000-fp-34870**
**

语法说明：

聚合函数
- count()：计数，统计有多少个；如果需要去重计数，括号内加distinct
- sum()：求和
- min()：求最小值
- max()：求最大值
- avg()：求平均

日期时间函数+分组聚合

案例3: 我想要按小时/天/周来看趋势

3.1 把时间戳转化成小时/天/周的格式，方便进一步做聚合统计

“

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 value /power(10,18) >1000 --限制ETH Transfer量大于100

order by block_timestamp --基于block_timestamp做升序排列，如果想降序排列需要在末尾加desc，因为默认是asc，所以不用末尾不用加asc也行

”

Footprint Query URL

语法说明

date_trunc(‘datepart’,timestamp)
- minute:将输入时间戳截断至分钟
- hour:将输入时间戳截断至小时
- day:将输入时间戳截断至天
- week:将输入时间戳截断至某周的星期一
- year:将输入时间戳截断至一年的第一天

3.2 基于之前得到的处理后的时间字段，使用 group by + sum 完成分组聚合

”

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 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

语法说明：

分组聚合（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 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

https://www.footprint.network/chart/Jastin-Sun-Transfers-%3E1000ETH-Transactions-left-join-Price-fp-34884**
**

联表查询
- 大部分情况下我们需要的数据不在同一张表中，比如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 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 逻辑 ]) ，多个视图用 ‘,’ 分隔

合并表（Union）

案例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

语法说明

合并表 (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