在本教程的第一部分中，我们给大家介绍了 Lens 协议，并为其制作了一个初步的看板，分析了包括总交易数量和总用户数量、按天统计的交易数量和独立用户数量、创作者个人资料（Profile）分析、Lens 域名分析、已注册域名搜索等相关内容。让我们继续给这个数据看板添加新的查询和可视化图表。我们将分析并添加以下内容：同一个地址创建多个 Profile、关注数据、发帖数据、评论数据、收藏数据、镜像数据、创作者的操作综合情况、普通用户地址的操作综合情况。

同一个地址创建多个 Profile 分析

Lens 协议允许一个地址创建多个 Profile。我们可以编写一个查询来统计创建了多个 Profile 的地址的数据分布情况。在下面的查询中，我们先用 CTE profile_created 取得所有已创建的 Profile 的数据详情，然后使用 multiple_profiles_addresses 来统计每一个地址创建的 Profile 数量。最后，我们使用 CASE 语句，按每个地址创建的 Profile 的数量对其进行归类，返回综合的统计数据。

with profile_created as (

select vars:to as user_address,

vars:handle as handle_name,

call_block_time,

output_0 as profile_id,

call_tx_hash

from lens_polygon.LensHub_call_createProfile

where call_success = true

),

​

multiple_profiles_addresses as (

select user_address,

count(profile_id) as profile_count

from profile_created

group by 1

order by 2 desc

)

​

select (case when profile_count >= 10 then '10+ Profiles'

when profile_count >= 3 then '5+ Profiles'

when profile_count = 2 then '2 Profiles'

else '1 Profile'

end) as profile_count_type,

count(user_address) as user_address_count,

sum(profile_count) as profile_count

from multiple_profiles_addresses

group by 1

做这类数据统计时，通常我们也需要得到一些 Counter 类型的统计值，比如创建过多个 Profile 的地址总数、这些地址一共创建了多少个 Profile，这些 Profile 在所有已创建的 Profile 中的占比等等。查询这些数据时可以共用上面的 CTE 子查询代码，所以我们对其少做修改，添加了两个额外的 CTE 来统计这些 Counter 类型的数值。为这个查询添加可视化图表并分别加入到数据看板中，显示效果如下：

以上查询在 Dune 上的参考链接：

• ​https://dune.com/queries/1562662​
• ​https://dune.com/queries/1553030​

发帖数据分析

发帖最多的账号数据分析

Lens 的创作者有两种发帖（Post）的方式，一直是直接用自己的账号发布 Post，另一种是委托其他账号或者通过 API 的方式来发布。Post 数据分别保存在 LensHub_call_post 和 LensHub_call_postWithSig 表中。每一个主题 Post 的内容以 JSON 字符串的形式保存在字段 vars 中，包括作者的 ProfileID，帖子内容的 URL 等信息。对于字符串形式的 JSON 内容，我们可以使用:操作符来访问其中的值。下面的查询可以获得部分示范数据：

select call_block_time,

call_tx_hash,

output_0 as post_id,

vars:profileId as profile_id, -- Access element in json string

vars:contentURI as content_url,

vars:collectModule as collection_module,

vars:referenceModule as reference_module,

vars

from lens_polygon.LensHub_call_post

where call_success = true

limit 10

鉴于发帖的 Profile 数量很多，我们可以像前面分析“同一个地址创建多个 Profile”那样，对不同发帖数量的 Profile 做一个分类统计，还可以关注头部用户，即发帖最多的那些账号的数据。这里我们对发帖最多的账号进行分析，同时将这部分账号的发帖数量和总体发帖数量的进行对照，输出 Counter 图表。完整的 SQL 如下：

with post_data as (

select call_block_time,

call_tx_hash,

output_0 as post_id,

vars:profileId as profile_id,

vars:contentURI as content_url,

vars:collectModule as collection_module,

vars:referenceModule as reference_module

from lens_polygon.LensHub_call_post

where call_success = true

union all

select call_block_time,

call_tx_hash,

output_0 as post_id,

vars:profileId as profile_id,

vars:contentURI as content_url,

vars:collectModule as collection_module,

vars:referenceModule as reference_module

from lens_polygon.LensHub_call_postWithSig

where call_success = true

),

​

posts_summary as (

select count(*) as total_post_count,

count(distinct profile_id) as posted_profile_count

from post_data

),

​

top_post_profiles as (

select profile_id,

count(*) as post_count

from post_data

group by 1

order by 2 desc

limit 1000

)

​

select profile_id,

post_count,

sum(post_count) over () as top_profile_post_count,

total_post_count,

posted_profile_count,

(sum(post_count) over ()) / total_post_count * 100 as top_profile_posts_ratio

from top_post_profiles

inner join posts_summary on true

order by 2 desc

以上 SQL 解读：因为 Post 数据分别保存在两个表里，在 CTE post_data 中，我们使用 union all 将两个表中取出的数据合并到一起。我们通过 posts_summary 来统计所有发帖的 Profile 数量和他们累计发布的 Post 数量。在 top_post_profiles 中，我们按照每个 Profile 的发帖数量最多的 1000 个 Profile 的数据。最后，我们关联查询 top_post_profiles 和 posts_summary，输出发帖最多的账号数据以及它们和总发帖数据的对比。将查询结果可视化并加入数据看板后的显示效果如下：

以上查询在 Dune 上的参考链接：

​https://dune.com/queries/1554541​

每日新发帖数量统计

Lens 用户每日的新发帖数量是观察整体活跃度变化趋势的一个重要指标，我们编写一个查询来统计每天的发帖数量。这个查询中的 post_data CTE 与之前的完全相同，所以我们在下面的代码中省略它的详情。因为我们还希望将每天的发帖数量进行累加返回累计发帖数量，我们定义 post_daily_summary CTE 作为中间步骤，以让 SQL 代码简单易懂。对应的 SQL 如下：

with post_data as (

-- Get post data from LensHub_call_post and LensHub_call_postWithSig tables

),

​

post_daily_summary as (

select date_trunc('day', call_block_time) as block_date,

count(*) post_count,

count(distinct profile_id) as profile_count

from post_data

group by 1

)

​

select block_date,

post_count,

profile_count,

sum(post_count) over (order by block_date) as accumulate_post_count

from post_daily_summary

order by block_date

将查询结果可视化并加入数据看板后的显示效果如下：

以上查询在 Dune 上的参考链接：

​https://dune.com/queries/1555124​

近 30 天发帖最活跃的 Profile 统计

同样，我们可能关心最近一段时间内发帖最活跃的 Profile 的情况。为此我们只需要在前述 post_data CTE 中，分别添加日期过滤条件来筛选最近 30 天内的发帖，然后按日期汇总统计即可。SQL 如下：

with post_data as (

select call_block_time,

call_tx_hash,

output_0 as post_id,

vars:profileId as profile_id,

vars:contentURI as content_url,

vars:collectModule as collection_module,

vars:referenceModule as reference_module

from lens_polygon.LensHub_call_post

where call_success = true

and call_block_time >= now() - interval '30 days'

union all

select call_block_time,

call_tx_hash,

output_0 as post_id,

vars:profileId as profile_id,

vars:contentURI as content_url,

vars:collectModule as collection_module,

vars:referenceModule as reference_module

from lens_polygon.LensHub_call_postWithSig

where call_success = true

and call_block_time >= now() - interval '30 days'

)

​

select profile_id,

count(*) as post_count

from post_data

group by 1

order by 2 desc

limit 100

我们可以分别添加一个柱状图来显示过去 30 天内发帖最多的 100 个账号的发帖数量，同时添加一个 Table 类型的图表来输出详情。相关图表加入数据看板后的显示效果如下：

以上查询在 Dune 上的参考链接：

​https://dune.com/queries/1559981​

评论数据分析

评论最多的账号数据分析

Lens 的评论数据与发帖数据类似，按数据产生来源不同，分别保存在 LensHub_call_comment 和 LensHub_call_commentWithSig 表中。基于 Lens 协议目前的功能，用户必须已经创建了自己的 Profile 才能对其他人创作者对 Post 进行评论。在评论数据表中，是通过评论者的 Profile ID 来进行追踪的。同时，每个创作者的发帖，其编号是从 1 开始累加的。也就是说，不同创作者的发帖，其编号可能相同。我们需要将创作者的 Profile ID 和其 Publication ID 关联起来这样才能得到唯一的编号。SQL 如下：

select call_block_time,

call_tx_hash,

output_0 as comment_id, -- 评论编号

vars:profileId as profile_id_from, -- 评论者的 Profile ID

vars:contentURI as content_url, -- 评论内容链接

vars:pubIdPointed as publication_id_pointed, -- 被评论的 Publication ID

vars:profileIdPointed as profile_id_pointed, -- 被评论的创作者的 Profile ID

vars:profileIdPointed || '-' || vars:pubIdPointed as unique_publication_id -- 组合生成唯一编号

from lens_polygon.LensHub_call_comment

where call_success = true

limit 10

我们同样通过定义额外的 CTE 来获取总的评论数据，从而可以在同一个查询中输出 Counter 图表，对比评论最多的 1000 个账号的评论数据和所有账号的评论数据。将查询结果可视化并加入到数据看板后的显示效果如下：

以上查询在 Dune 上的参考链接：

​https://dune.com/queries/1560028​

评论最多的 Publication 统计

每个评论都是针对一个具体的对象（Publication）（这里作者认为应该就是 Post，如有理解错误敬请指正）。分析被评论最多的 Publication 就具有一定的价值。我们编写一个查询来统计前 500 个被评论最多的 Publication，同时将其与所有评论数据进行对比。SQL 如下：

with comment_data as (

-- get comment data from LensHub_call_comment and LensHub_call_commentWithSig tables

)

​

select profile_id_pointed,

publication_id_pointed,

unique_publication_id,

count(*) as comment_count

from comment_data

group by 1, 2, 3

order by 4 desc

limit 500

如法炮制，我们添加额外的 CTE 来获取全部评论的数据，并将上面统计的前 500 个评论最多的 Publication 的数据与全局数据进行对比。添加相应的可视化图表到数据看板，效果如下：

以上查询在 Dune 上的参考链接：

​https://dune.com/queries/1560578​

镜像数据分析

镜像数据与评论数据高度相似，用户也必须先创建自己的 Profile 才能镜像其他人的 Publication。我们分别编写两个查询，统计出镜像操作最多的前 1000 个账号数据和前 500 个被镜像最多的 Publication 数据。同样将它们跟整体镜像数据进行对比。加入数据看板后的效果如下图所示：

以上查询在 Dune 上的参考链接：

• ​https://dune.com/queries/1561229​
• ​https://dune.com/queries/1561558​

收藏数据分析

Lens 的收藏数据同样分别保存在 LensHub_call_collect 和 LensHub_call_collectWithSig 这两个表里。与评论或镜像数据有所不同的是，收藏一个 Publication 时并不要求收藏者拥有自己的 Lens Profile。也就是说，任何地址（用户）都可以收藏其他 Profile 下的 Publication。所以我们要通过收藏者的地址来跟踪具体的收藏操作。特别之处在于，在 LensHub_call_collect 表中并没有保存收藏者的地址数据，LensHub_call_collectWithSig 表中则有这个数据。我们需要从 LensHub_call_collect 表关联到 transactions 表，获取当前操作收藏的用户地址。SQL 示例如下：

select call_block_time,

t.`from` as collector,

c.profileId as profile_id,

c.pubId as publication_id,

c.profileId || '-' || c.pubId as unique_publication_id,

c.output_0 as collection_id

from lens_polygon.LensHub_call_collect c

inner join polygon.transactions t on c.call_tx_hash = t.hash -- 关联交易表获取用户地址

where call_block_time >= '2022-05-18' -- Lens 合约的发布日期，提升查询效率

and block_time >= '2022-05-18'

and c.call_success = true

limit 10

由于交易表记录相当庞大，查询耗时将明显增加。一个经验法则是，能避免针对原始数据表（transactions, logs, traces）的 join 操作就尽量避免。

收藏数据分析 SQL 的其他部分跟前面的例子基本相同，这里不再赘述。同样，我们也针对被收藏最多的 Publication 进行统计分析。相关可视化图片加入数据看板后显示效果如下图所示：

以上查询在 Dune 上的参考链接：

​https://dune.com/queries/1560847​

​https://dune.com/queries/1561009​

关注数据分析

关注最多的 Profile 数据

Lens 协议的关注数据仍然是分别保存在 LensHub_call_follow 和 LensHub_call_followWithSig 两个表里。任何地址（用户）都可以关注其他 Profile。与收藏类似，LensHub_call_follow 表里没有保存关注者的地址，所以我们也需要通过关联到 transactions 表来获取当前操作收藏的用户地址。另外，关注还有一个特殊的地方，就是一个交易里面可以同时批量关注多个 Profile。LensHub_call_follow 表中，被关注的 Profile 数据保存在数组类型字段 profileIds 里，这个相对容易处理。而表 LensHub_call_followWithSig 中，则是 JSON 字符串格式里面的数组值。其中字段 vars 的一个实例如下（部分内容做了省略）：

{"follower":"0xdacc5a4f232406067da52662d62fc75165f21b23","profileIds":[21884,25271,39784],"datas":["0x","0x","0x"],"sig":"..."}

要从 JSON 字符串中读取数组值并不容易，无法直接将字符串格式的数组值[21884,25271,39784]转换为数组，所以这里我们要借用 from_json() 方法，将 JSON 字符串映射为一个结构体（Struct），然后从结构体里面提取数组。示例代码如下：

select from_json(vars, 'struct<follower:string,profileIds:array<long>>') AS vars_struct

from lens_polygon.LensHub_call_followWithSig

where array_size(output_0) > 1

limit 10

我们通过结构体的映射，将其中的 follower 影视为一个字符串类型，将 profileIds 映射为长整型数组 array<long>，忽略 json 中的其他无关内容。这样我们就可以使用 struct_name.element_name 的形式访问结构体中的元素，其中 profileIds 是一个数组。然后我们可以使用提取数组元素的方法来读取 profileIds 里面的单个数值。具体语法为使用 lateral view explode(profile_ids) as profile_id 将数组拆分为多行，关联到主查询，然后使用给定的别名 profile_id 来访问拆分得到的数组元素值。关于 lateral view clause 的更多信息，可以参考 LATERAL VIEW Clause。

读取关注详情的完整 SQL 代码如下：

with follow_data as (

select follower, profile_id

from (

select vars_struct.follower as follower, -- read element from struct

vars_struct.profileIds as profile_ids

from (

-- belowe we map json string to a struct with data type

select from_json(vars, 'struct<follower:string,profileIds:array<long>>') AS vars_struct

from lens_polygon.LensHub_call_followWithSig

)

union all

select t.`from` as follower, f.profileIds as profile_ids

from lens_polygon.LensHub_call_follow f

inner join polygon.transactions t on f.call_tx_hash = t.hash

where call_block_time >= '2022-05-18' -- Lens launch date

and block_time >= '2022-05-18'

) f

lateral view explode(profile_ids) as profile_id

)

​

select * from follow_data

limit 100

同样，我们也在上面的查询基础上添加获取全部关注数据的 CTE 定义，从而可以在取得最多关注的 Profile 列表时，将其与整体关注数量进行对比。查询结果可视化并加入数据看板后的效果如下：

以上查询在 Dune 上的参考链接：

​https://dune.com/queries/1554454​

按关注数量范围统计 Profile 分布

我们看到几乎绝大部分 Profile 都有被关注，我们可以用一个查询来对各 Profile 的关注量的分布情况做一个分析。SQL 代码如下：

with follow_data as (

-- Get follow data from table LensHub_call_follow and LensHub_call_followWithSig

),

​

profile_follower as (

select profile_id,

count(follower) as follower_count

from follow_data

group by 1

)

​

select (case when follower_count >= 10000 then '10K+ Followers'

when follower_count >= 1000 then '1K+ Followers'

when follower_count >= 100 then '100+ Followers'

when follower_count >= 50 then '50+ Followers'

when follower_count >= 10 then '10+ Followers'

when follower_count >= 5 then '5+ Followers'

else '1 - 5 Followers'

end) as follower_count_type,

count(profile_id) as profile_count

from profile_follower

group by 1

将以上查询结果使用一个 Pie chart 饼图进行可视化。加入到数据看板后到显示效果如下图所示：

以上查询在 Dune 上的参考链接：

​https://dune.com/queries/1554888​

每日新增关注数量统计

Lens 用户每日的新增关注数量也是观察整体活跃度变化的一个重要指标，我们编写一个查询来统计每天的发帖数量。这个查询中的 follow_data CTE 与之前的完全相同。查询处理方式也与前面讲过的每日发帖数量统计高度相似，这里不再详述细节。给查询结果添加可视化图表并将其加入数据看板，显示效果如下：

以上查询在 Dune 上的参考链接：

​https://dune.com/queries/1555185​

创作者操作综合分析

结合前述内容可以看出，创作者（拥有 Profile 的用户）可以发帖（Post）、评论（Comment）或者镜像（Mirror）其他创作者的数据，而普通用户（未创建 Profile）则可以关注（Follow）创作者和收藏创作者发布的作品（Publication）。所以我们可以将创作者可以操作的数据合并到一起来进行综合分析。

我们定义一个 action_data CTE，在其内部使用嵌套定义 CTE 的方式将相关数据集中到一起，其中 post_data、comment_data 和 mirror_data 都分别跟前面相关查询里面的定义完全相同。我们使用 union all 将以上数据合并到一起，同时分布指定对应的动作类型，生成一个用于分类的字段 action_type。然后我们只需按照分类字段进行汇总统计即可计算出每种操作类型的交易数量和相应的 Profile 数量。SQL 示例如下：

with action_data as (

with post_data as (

-- get post data from relevant tables

),

comment_data as (

-- get comment data from relevant tables

),

mirror_data as (

-- get mirror data from relevant tables

)

select 'Post' as action_type, * from post_data

union all

select 'Mirror' as action_type, * from mirror_data

union all

select 'Comment' as action_type, * from comment_data

)

​

select action_type,

count(*) as transaction_count,

count(distinct profile_id) as profile_count

from action_data

group by 1

我们可以用相似的方法，新建一个按日期汇总每日各种操作数量的查询。示例代码如下：

with action_data as (

-- same as above query

)

​

select date_trunc('day', call_block_time) as block_date,

action_type,

count(*) as transaction_count

from action_data

group by 1, 2

order by 1, 2

将以上查询结果可视化并加入数据看板，显示效果如下：

以上查询在 Dune 上的参考链接：

• ​https://dune.com/queries/1561822​
• ​https://dune.com/queries/1561898​

普通用户操作综合分析

与创作者类似，我们可以将普通用户可执行的关注和收藏操作合并到一起进行分析。我们同样编写两个查询，分别统计总体的操作分布和按日期的操作数量。查询里面的 action_data 数据同样来源于前面介绍过的收藏查询和关注查询，其 SQL 示例如下：

with action_data as (

with follow_data as (

-- get follow data from relevant tables

),

collect_data as (

-- get collect data from relevant tables

)

​

select 'Follow' as action_type, * from follow_data

union all

select 'Collect' as action_type, * from collect_data

)

除了数据来源不同，这两个查询与创作者操作综合分析基本相同。将查询结果可视化并加入数据看板，显示效果如下：

以上查询在 Dune 上的参考链接：

• ​https://dune.com/queries/1562000​
• ​https://dune.com/queries/1562178​

总结与作业

非常好！我们已经完成了对 Lens 协议的整体分析。不过，由于篇幅问题，仍然有很多值得分析的指标我们尚未涉及，包括但不限于：三种 NFT 的相关数据分析、创作者的收益分析、Profile 账号的转移情况分析等。这部分留给大家去继续探索。

请结合教程内容，继续完善你自己的 Lens 协议数据看板，你可以 Fork 本教程的查询去修改，可以按自己的理解做任何进一步的扩展。请大家积极动手实践，创建数据看板并分享到社区。我们将对作业完成情况和质量进行记录，之后追溯为大家提供一定的奖励，包括但不限于 Dune 社区身份，周边实物，API 免费额度，POAP，各类合作的数据产品会员，区块链数据分析工作机会推荐，社区线下活动优先报名资格以及其他 Sixdegree 社区激励等。