The Space 链上分析之二： Hall of Fame 绘图英雄榜，占地排名榜，以及地价榜单

上周看到『 The Space | 疯狂的一夜，一晚画了一千像素』就一直想知道，当前地图上这样疯狂画图的人除了@Daisy还有多少？

续上一篇thespace."TheSpaceRegistry_evt_Color" events table 的使用，因为所有的绘图事件(events) 都一定会emit 出一个color event, 那么查询evt_Color table 就能拿到所有人的绘图过程，

 SELECT owner, COUNT(*) AS count_drawings,
 COUNT(DISTINCT "tokenId") AS count_pixels
FROM thespace."TheSpaceRegistry_evt_Color"
GROUP BY 1
ORDER BY count_drawings DESC

上一篇已提到COUNT(*) 可以做整体统计，除此以外它还可以分类统计，就是GROUP BY 语句， 在SELECT 之后放上想要分类的项，此处用owner, 并且在FROM ...table 之后加上GROUP BY ... 那么SELECT 后面就可以用COUNT 作「对此owner 分类的」计数，比如COUNT(*) 统计了整个绘图次数，还可以用COUNT(DISTINCT "tokenId") 统计不重复的tokenId 数，此处tokenId 对应的是一百万个像素的编号，也通俗理解为地块编号；点击「Run」查询，可以看到截止现在有203 个钱包曾有过画过；还可以看到count_drawings vs count_pixels 是有差别的，应该是有很多像素发生了争抢的情况，被人绘成别的，自己再抢回来，当然也可能是很多人对画好的颜色并不满意，再来绘一次别的颜色。

 SELECT owner, COUNT(*) AS count_drawings,
 COUNT(DISTINCT "tokenId") AS count_pixels,
 SUM(gas_used*gas_price)/1e18 AS gas_matic
FROM thespace."TheSpaceRegistry_evt_Color"
JOIN polygon.transactions ON hash=evt_tx_hash
GROUP BY 1
ORDER BY count_drawings DESC

除此之外，因为event table 都有存了原始的对应链上交易的evt_tx_hash 此处也可以顺便展示其用途，即对polygon.transactions 这样的raw table 进行JOIN Query, 联合查询，联合的field 是evt_tx_hash 对应polygon.transactions 上的hash 这样可以想像两张table 按相同的hash 对接在一起方便用。这里的例子只用到polygon.transactions 里面的gas_used*gas_price 这就是实际用到的matic gas fee, 在GROUP BY 语句中除了COUNT 还有类似的SUM(...) 顾名思义就是统计求和，看看他们分别在TheSpace 费了多少Gas, 因为Gas 的使用单位是Matic, 内部与Ether 相同的有小数点后18位，所以除以科学计数法(1e18) 就是变为以Matic 为单位了

查询结果已加入https://dune.com/bluebasketbooks/thespace-dashboard这张Dashboard

其中Daisy 的帐号\x2af159c310... 也仅排第二而已，那么排名榜单第一的\x64110f5b... 究竟是谁？

又或者，因为上面结果是TheSpace上线十几天以来的总计，如果按单日计算呢，Daisy的一晚画了一千像素会不会是第一？答案在这里有了， https://dune.com/queries/920536?d=7提示：按ㄖ分类也只不过是增加一个GROUP BY 的维度，在PostgreSQL 的语法是date_trunc('day', evt_block_time) ::date AS ... 转换为日期，结果仍然是\x64110f5b... 以单日1,117 drawings 排名榜首～ ( 当然这里通用的是UTC 国际日期，而你所实际住地的单日绘了很多有可能在UTC 被分到前后两日去了，有兴趣者还可以查PostgreSQL 的时区函式来转换使用当地ㄖ期)

(二) 占地排名榜

上面问题在前几天已加上到Dashboard, 被眼尖的读者( @裸子) 已发现，与Polygonscan 上去对照，怎么会不一样？确实有可能有差异，因为上一篇说了，Dune的数据可能有up to 3 hours delay。但如果三小时已过， TheSpace 上当前的近三小时并未有新的绘图events, 那么就肯定是有一边数据并未更新到最新。

另外一个原因，则来自，可能统计的是事情的不同面向。

与Poygonscan 上的这个Quantity(token) 数量对不起来，

具体来说，绘图事件与Planck (PLK) holders 并非同一概念。绘图事件仅仅是绘图，可能是自己涂自己的地，只是变换颜色未有所有权转移；而hold 一个Planck (PLK) 则有可能只是单纯的买地，并未变换颜色。

于是，在Polygonscan 上再研究，如果只买地不重画颜色，会有何种events?答案在thespace."TheSpaceRegistry_evt_Transfer" 里面找到了，这一张events table 记录的就是单纯ownership 转移，有"from" "to" "tokenId" 这几项就够用了，当然，与其它events table 类似的是也有evt_block_time/evt_hash/.. 等等，在有需要时可以用于JOIN Query

 WITH last_owners AS (
  SELECT DISTINCT ON ("tokenId") *
  FROM thespace."TheSpaceRegistry_evt_Transfer"
  ORDER BY "tokenId", evt_block_number DESC, evt_index DESC
)

SELECT "to", COUNT(*),
  MAX(evt_block_time) AS latest,
  (ARRAY_AGG("tokenId" ORDER BY evt_block_time DESC))[1:5] AS last_5
FROM last_owners
GROUP BY 1
ORDER BY count DESC, latest DESC

这里同样先说结好了，需要从thespace."TheSpaceRegistry_evt_Transfer" 里面查到结果，但因为这张事件表记录了所有ownership 转移的情况，而为了对每Planck (PLK) 只查最后一任owner (地主), 需要一次DISTINCT ON ("tokenId") 查询，其含义是对每个Planck 交易情况，只需要知道最后一次易手纪录，在ORDER BY "tokenId" 之后可以按时间查询Descending 排序的最后一次记录，里面可以使用evt_block_time DESC 与evt_block_number DESC 也几乎相同，但在其它类似高频交易中有多个tokenId 在相同block_number 的情况，得到相同的evt_block_time, 于是外加上evt_index DESC 更多一层保障。

得到\x2af159c310e... 以2841 holdings 与Polygonscan 结果完全对应！ 197 个帐号持有27,000+ 对应2万7千多个已开发地块，并且与前面203 accounts 并不一致的原因是有人曾画过但后来其手上所有地块都被人买走了

这里，还同时使用了MAX(evt_block_time) 得到每owner 的最后一次入手时间，以及(ARRAY_AGG(...))[1:5] 等等顺便取得每人的最后5 次入手的地块(Planck) 编号，更为方便对照。

(三) 地价榜单

地价榜单，也是最多人问及的。同样的工作原理，也是在Polygonscan 上去寻找，定价过程中会触发什么events? 然后在thespace.* 这些decoded tables 中去寻找

答案在thespace."TheSpaceRegistry_evt_Price" 找到

WITH prices AS (
  SELECT DISTINCT ON ("tokenId") price/1e18 AS price_spaces, *
  FROM thespace."TheSpaceRegistry_evt_Price"
  ORDER BY "tokenId", evt_block_time DESC
)
SELECT *
FROM prices
-- WHERE price_spaces>=100
ORDER BY price_spaces DESC, evt_block_time DESC

同样的原理，也使用DISTINCT ON ("tokenId") 只关心查每Planck 的最后一次定价，去重复，得到临时prices table, 同时，因为$Space币同样内部使用了小数点后18位， 为了读价方便，这里先计算price/1e18 AS price_spaces 得到以$Space为单位的数字

最后从SELECT * FROM prices 读出所有columns, 想知道定价榜单？就按定价price_spaces 排降序就是了，得到已开发过的(minted) 所有27,000+ 地块的价目表，

其中"-- WHERE price_spaces>=100" 以-- 开头在SQL 中是注释掉，暂不运行的意思，你也可以试试取消注释，看看定价大于100$Space 币的地块有多少？想知道定价最贵的地块价钱是多少？答案是1,0000,0000 $Space, 一个亿的$Space币！ ！

 price_spaces tokenId price owner
100000000 728415 1e+26 \x437b672246e4e2....
100000000 683092 1e+26 \x437b672246e4e2....

具体位置在哪儿，都来观摩一下？根据tokenId 728415 = (y-1)*1000+x 来反向计算，其定位在(415,729), 在这儿～

那个红色「Default Risk」什么意思？应该喊「清税官」在哪里？这位已经Due Tax 3千4百多万了