PostgreSQL 分离json数组来统计用户局数

一需求：根据已有的牌局记录表统计归纳出每天每个玩家在每个游戏上面进行的局数。首先牌局记录表的结构大致如下：

其中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都相等的行集上进行计算，得出局数，结果如下：