SQL中级内容: 1、公用表表达【Common Table Expressions (CTEs)】 2、自联接【SELF JOIN】 3、PARTITION、LEAD、LAG、NTILE 等窗口函数 4、在查询中使用索引使操作更快 5、子查询及其对查询效率的影响 💡 原文:https://towardsdatascience.com/your-guide-to-intermediate-sql-while-learning-ethereum-at-the-same-time-7b25119ef1e2
本文关于SQL的新函数较多，使用中不明白的请自行查阅SQL相关手册了解每个函数的意义，我就不再一一贴链接啦。 公用表表达 在开始公用表部分讲解前，我们需要了解什么是工厂模式（Factory Pattern）和代理模式（Proxy Pattern）
Factory:https://www.runoob.com/design-pattern/factory-pattern.html Proxy:https://www.runoob.com/design-pattern/proxy-pattern.html
具体意思我们不需要知道，我们只需明白工厂模式主要是通过使用一个共同的接口来指向新创建的对象，而代理模式是创建具有现有对象的对象，以便向外界提供功能接口访问。
而Uniswap的代币兑换机制正是使用的工厂模式，通过一个共同的接口Deploy new instance来指向所有的代币交易对。
Uniswap有非常多的智能合约，将其运行在24Kb大小字节限制的空间就非常困难，加之查询和子查询相互交错会影响代码可读性，因此我们将常用的子查询单独放在一起就形成了公用表表达式。
接下来，我们来查询一下Uniswap过去一周创建的代币对都有哪些，并且查询池中代币剩余资产：
WITH
-- created pairs
cp as (
SELECT date_trunc('day', call_block_time) as day, "tokenA", "tokenB", output_pair from uniswap_v2."Factory_call_createPair"
WHERE call_success='true' AND call_block_time > now() - interval '7 days'
ORDER BY 1 DESC --want most recent date first
),
--pair reserves sync
r as (
SELECT date_trunc('day', evt_block_time) as day, reserve0, reserve1, contract_address from uniswap_v2."Pair_evt_Sync"
WHERE evt_block_time > now() - interval '7 days'
ORDER BY 1 DESC --want most recent date first
),
--getting only most recent sync for each contract_address in r
r_recent as (select day, reserve0, reserve1, contract_address from (
select day, reserve0, reserve1, contract_address,
ROW_NUMBER() OVER (PARTITION BY contract_address) rn --don't worry about partitions for now
from r
) as tmp where rn = 1
)
SELECT cp."tokenA", r_recent.reserve0, cp."tokenB", r_recent.reserve1, r_recent.contract_address as pair_address FROM cp
INNER JOIN r_recent ON r_recent.contract_address = cp.output_pair
这里的cp（过去7天创建的所有交易对）、r（过去7天所有交易对的余额）、r_recent（保留r中每一个交易对的最近更新）均为公用表表达式，稍后会提到partition和row_number（），最后通过对cp和r_recent的交易对合约地址进行INNER JOIN得到了最终数据表，如果不记得INNER JION可以看之前文章数据 | 数据分析之Dune Analytics（二），我将图片再次放在下方。
使用公用表表达，能够让我们分别构建和测试单个试图，无需等待每一次的整个查询完成，且代码易于阅读，调试起来也更加方便。
自联结Union Join 上方提到的工厂模式和交易对基础合约都是非常基础的，不会被直接调用，而Uniswap创建了一个名为Router02的合约，它不同于大部分合约那样复杂，对用户更加友好。每当有新的交易对被创建时，你就可以通过Router02合约为交易池添加流动性，流动资金池在去中心化金融（DeFi）中是一个非常重要的概念，下面我会花一些时间从高的层次上来解释它。
在为交易对添加流动性时，你是从其他的流动性提供者 ( LP )的储备金之上添加流动性。Uniswap 要求您根据代币价值以各自50%的比例来添加，然后您将获得一个 Uniswap 交易池代币，代表你在总池中所占的份额。以下是通过 Router02 合约添加流动性的示例：
当有人想兑换代币时，他们会增加池中一种代币的储量并提走另一种代币——并影响两种代币之间的汇率。兑换和汇率定价曲线我们将在下一部分中深入探讨。
现在，假设我们想要获得池中所有添加的流动性的 LP。这时SELF JOIN就派上用场了，一个很好的例子是当你拥有一个确定列的名称和位置时，然后通过SELF JOIN来显示谁（原始名称列）与谁（复制名称列）在同一位置，我们可以基于某一列在不同行之间建立关系。现在，我们有一列按地址排列的 LP，我们通过代码来查看哪些 LP 正在为同一个池子添加流动性。
💡 SELF JOIN说明:http://c.biancheng.net/sql/self-join.html
该查询创建了一个名为LP的公用表表达，这些 LP 在过去 7天内为每个代币对都增加了流动性。在最后的查询中，我使用了CASE WHEN（这只是
WITH
LP as (
SELECT DISTINCT "to" as liquidity_provider, "tokenA", "tokenB", CONCAT("tokenA",' , ',"tokenB") as pair, call_block_time
FROM uniswap_v2."Router02_call_addLiquidity"
WHERE call_success = 'true' AND call_block_time > now() - interval '7 days'
ORDER BY call_block_time DESC
)
--getting unique overlapping liquidity providers for two pools (WETH/WBTC, WETH/USDC).
SELECT DISTINCT LP1.liquidity_provider, LP2.liquidity_provider as related_lp,
CASE
WHEN LP1."tokenB" = '\xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' THEN 'WETH/USDC'
WHEN LP1."tokenB" = '\x2260fac5e5542a773aa44fbcfedf7c193bc2c599' THEN 'WETH/WBTC'
ELSE 'Unknown Pair'
END as token_pair
FROM LP as LP1, LP as LP2
WHERE LP1."tokenA" = '\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' --WETH
AND LP1."tokenB" = '\xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' --USDC
OR LP1."tokenB" = '\x2260fac5e5542a773aa44fbcfedf7c193bc2c599' --WBTC
AND LP1.liquidity_provider <> LP2.liquidity_provider --don't duplicate same lp
AND LP1.pair = LP2.pair --self join is on the token contract pair
-- this query is for getting number of liquidity providers and number of unique pairs that have liquidity in them
-- select COUNT( DISTINCT liquidity_provider) as num_lps, COUNT( DISTINCT pair ) as num_pairs from LP
一个简单的“if-else”语句）函数使该列为代币对名称，这相比于合约地址会更具可读性。这段代码的关键是在最后两行中的SELF JOIN，我选择同一池子中所有的LPs作为目标LP。
然后，其会在表格中循环，并为每个合约对列出相关的LP，因此，如果我有4个LPs提供给同一个合约对（LP1.pair=LP2.pair全部满足），那么我最终会有12行（LP1.liquidity_provider <> LP2.liquidity_provider每一个Lp都有三个不同的Lp提供者），第20行的 "不等于 "操作符<>确保我们跳过锚点，否则它也会显示为一行。最后，第21行作为一个典型的连接操作，使我们能够连接LP1和LP2。
如果该列有非常多的id，那么该查询很快就会耗尽内存（我们会在文末讨论相关子查询的时候再来讨论这个问题）。这就是为什么我限制了日期间隔为7天并预选了两个代币交易对（否则77,000个LPs和10,000个交易对运算结果可能会有超过一百万行）。我们现在不会使用该数据做其它事情，但我们可以用它来对用户进行分类，或对交易对的流动性进行图表分析。
窗口函数PARTITION、LEAD、LAG、NTILE 开始本小节内容前，我们需要明白Uniswap的代币兑换原理:
X*Y=K 若我们想将WETH换为USDC，上式中的X就是WETH的储量，Y就是USDC的储量，K就是他们的流动性，其定价图线即为:
这意味着当我将代币 A 换成代币 B 时，代币A 的储量会增加，而代币 B 的储量会减少，这使得未来将代币 A 换成代币 B 的成本会更高。
接下来讲窗口函数，想象一下我们无需使用GROUP BY就能够实现聚合、偏移和统计等功能且保持原始表不变——您只需根据每一行中的值来添加列。
通常我们通过OVER或者 窗口函数 (PARTITION BY column_name) 来实现一些特殊的功能。早些时候，我们的特殊功能ROW_NUMBER()是为每一个独立分区从 1 开始计算新行（在这种情况下，每个合约地址都对应唯一的交易对），这就是为什么我们能保留每个新的代币合约的储备能够同步rn=1
ROW_NUMBER() OVER (PARTITION BY contract_address) rn
如同起初我们使用 GROUP BY一样，也可以将窗口函数 PARTITION 视为列，尽管它也可以是一个只有唯一值的列。让我们尝试计算过去 7 天内用WETH兑换USDC所占交易池的百分比。我们仍使用 Router02 合约，但使用swapExactTokensForTokens()函数而不是addLiquidity()
SELECT "amountIn",
NTILE(10) OVER ntile_window AS ten_tile,
NTILE(100) OVER ntile_window AS percentile
FROM uniswap_v2."Router02_call_swapExactTokensForTokens"
WHERE call_block_time > now() - interval '7 days' AND call_success='true'
AND '\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'=path[1] AND '\xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'=path[array_length(path, 1)] --WETH to USDC
WINDOW ntile_window AS
(PARTITION BY contract_address ORDER BY "amountIn")
这里我们使用了统计函数NTILE()而不是计数函数ROW_NUMBER()，NTILE()将根据总共有多少组（四分位数NTILE(4)、五分位数NTILE(5)、百分位数NTILE(100)）为每一行分配一个组。因为我们两次使用同一个窗口函数，所以我们可以创建一个WINDOW变量来减少冗余。整列只有一个唯一的contract_address值，因为它始终是 Router02 被调用，否则我们将有多个分区，并且每个唯一的contract_address值将获得其自己的 100个 NTILE集合。
下面是我们的查询结果：
通过窗口(Window)函数使用聚合功能，可以通过下方的手册学习，这里不多讲解，此处需要着重讲的是偏移函数LEAD 和 LAG ，因为它们对于时间序列趋势分析非常有用。
英(原):https://mode.com/sql-tutorial/sql-window-functions/ 中（该文译为开窗函数）:https://blog.51cto.com/u_15444474/4728625
WITH
--daily added liquidity
DAL as (
SELECT date_trunc('day',call_block_time) as day, SUM("amountIn") as ETH_swapped_to_USDC
FROM uniswap_v2."Router02_call_swapExactTokensForTokens"
WHERE call_block_time > now() - interval '100 days' AND call_success='true'
AND '\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'=path[1] AND '\xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'=path[array_length(path, 1)] --WETH to USDC
GROUP BY 1
)
SELECT day,
ETH_swapped_to_USDC,
ETH_swapped_to_USDC - LAG(ETH_swapped_to_USDC,1) OVER(ORDER BY day) as daily_diff_swaps,
ETH_swapped_to_USDC - LAG(ETH_swapped_to_USDC,7) OVER(ORDER BY day) as weekly_diff_swaps
FROM DAL
在这里，我们创建了一个名为DAL的公共表表达 ，以表示过去 28 天 WETH兑USDC的每日总数amountIn。然后我们在查询DAL时创建两个LAG列，一列滞后 1 行（天），另一列滞后 7 行（天）:
如果我们在每一个LAG 列前添加一个 ETH_swapped_to_USDC -，那么我们可以获得交易量的每日和每周差异：
在查询中使用索引使操作更快 在你进行查询时，会根据你脚本中的操作运行一个执行计划。虽然这会做很多优化，但还是免不了想以最有效的方式来组织查询和表。我们将在下一节中讨论子查询的排序问题，首先让我们谈谈索引。
如果列是一个主要的关键词（Primary key），这意味着你可以创建一个与之相连的聚类索引。如果列是一个外部关键词（Foreign key，非唯一值），你可以给它附加一个非聚类索引。有了这些索引，在查询数据时就会有很大的不同，特别是在使用WHERE和（或）JOIN的时候。没有索引，你的查询将以表扫描的方式运行（即线性地通过每一行）；有了索引，就会运行一个索引扫描/查找。这就是二进制搜索和线性搜索的区别，导致O(log n)和O(n)的搜索时间差异。最终，创建索引会导致更长的写入时间（因为每次添加新的数据时，必须对索引进行重新排序），但你最终会得到更快的读取时间。如果你想了解更多关于索引查询与非索引查询的演练和比较，我强烈建议你看看下面的视频。
O(log n)和O(n):https://www.jianshu.com/p/f31744e423eb 视频:https://www.youtube.com/watch?v=toGvjN5mfp8 即使表没有索引，你也可以通过对你要过滤或联接的列使用ORDER BY来改善线性扫描。这可以使查询更有效率，特别是对于联接。
子查询及其对查询效率的影响 最后，我们来谈谈子查询，它一般有两种类型，一种是相关的子查询，该类型的子查询必须为外部查询的每一行重新估值，另一种是不相关的子查询，其只需估值一次，然后作为外部查询的常量。
下面是一个不相关的子查询，因为SELECT MAX("amountIn")/2 FROM swaps在检查外部查询的每一行 "amountIn "之前只被估值一次。
WITH
swaps as (
SELECT "amountIn"
FROM uniswap_v2."Router02_call_swapExactTokensForTokens"
WHERE call_block_time > now() - interval '7 days' AND call_success='true'
AND '\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'=path[1]
AND '\xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'=path[array_length(path, 1)] --WETH to USDC
)
SELECT "amountIn" FROM swaps
WHERE "amountIn" > (SELECT MAX("amountIn")/2 FROM swaps)
接下来的这个查询是一个相关的子查询，它看起来与SELF JOIN非常相似，但只是将行与其自身进行比较，而不是连接，我们只是想获得 Uniswap 中每个交易对的平均兑换金额。
WITH
swaps as (
SELECT "amountIn", path
FROM uniswap_v2."Router02_call_swapExactTokensForTokens"
WHERE call_block_time > now() - interval '7 days' AND call_success='true'
)
-- --correlated subquery (don't try to run this, it takes >30 minutes)
-- SELECT s1."amountIn", path FROM swaps s1
-- WHERE "amountIn" > (SELECT AVG("amountIn")
-- FROM swaps s2
-- WHERE s1.path=s2.path)
----join query instead
SELECT s1."amountIn", s1.path FROM swaps as s1
INNER JOIN
(SELECT AVG("amountIn") as avg_swap, path
FROM swaps
GROUP BY 2) as s2
ON s2.path = s1.path
WHERE s1."amountIn" > s2.avg_swap
尽管这仍然是一个聚合函数的比较（使用AVG而不是MAX），但在超过30分钟后，这个函数就超时了，而非相关查询只用了不到5秒。你可以通过把相关的子查询变成一个连接子查询来使这个查询更快--这只是需要费一些脑力，我们用这个方法把它缩短到28秒。
如果你确实需要使用相关子查询，你应该利用我们在上一节谈到的索引--否则它可能需要很长的时间来运行，因为它变成了n_rows*n_rows=O(n²)比较操作。
最后，如果任何父查询中有多个子查询，请确保所有子查询都以最有效的方式排序。特别是当涉及到连接时，确保你在连接之前用WHERE和HAVING进行过滤，而不是在连接之后。如果你的查询耗时太长，试着考虑一下子查询的类型和你在逻辑和排序方面的其他选择，以使它更快。这是另一个例子，将子查询分解成公用表表达可以帮助你重新组织和部署你的代码，使之更快、更干净。
💡 若你想了解更多关于以太坊的内容，可以访问:https://medium.com/coinmonks/crypto-and-web-3-0-are-the-future-of-product-and-work-3d19e3733181
我的推特: