PostgreSQL 分离json数组来统计用户局数
【摘要】 一需求：根据已有的牌局记录表统计归纳出每天每个玩家在每个游戏上面进行的局数。首先牌局记录表的结构大致如下：其中log_date表示当局日期，game_id表示当局属于哪个游戏，points是一个json类型的值，里面记录着参与这局游戏的玩家信息与得分情况，现有的原始数据如下：因为points是一个json数组，里面保存着用户的得分和user_id这些信息，因此可以考虑把points变为多行来...
一需求：根据已有的牌局记录表统计归纳出每天每个玩家在每个游戏上面进行的局数。首先牌局记录表的结构大致如下：
其中log_date表示当局日期，game_id表示当局属于哪个游戏，points是一个json类型的值，里面记录着参与这局游戏的玩家信息与得分情况，现有的原始数据如下：
因为points是一个json数组，里面保存着用户的得分和user_id这些信息，因此可以考虑把points变为多行来实现分离每个user_id的效果，如图所示：
把每个user_id都分离之后就可以利用窗口函数统计每个user_id对应的局数了，SQL语句如下：
SELECT
game_id,
user_id,
SUM (COUNT(*)) OVER (PARTITION BY game_id, user_id) AS round_count
FROM
(
SELECT
log_date,
game_id,
(
json_array_elements (points :: json) ->> 'user_id'
) AS user_id
FROM
game_record
) AS A
GROUP BY
game_id,
user_id;其中，over字句使得sum(count(*))函数被当作一个窗口函数处理，并在game_id,user_id都相等的行集上进行计算，得出局数，结果如下：
【声明】本内容来自华为云开发者社区博主，不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息，否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
作者其他文章
云夭夭2018/08/07 02:03:521楼编辑删除举报
云夭夭2018/08/07 02:04:232楼编辑删除举报