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

in 编程
关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9

最近又遇到了一个需求(每次都是用这个开头,感觉自己都腻了-_-||),是根据已有的牌局记录表统计归纳出每天每个玩家在每个游戏上面进行的局数。首先牌局记录表的结构大致如下:

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

//-------------------------------------------分割线2018-07-31-----------------------------------------------

下方评论区有大神提到可以不用窗口函数,在此要感谢大神redraiment提醒了我。当时的想法是刚学了窗口函数,还不会用,所以想实践一下,所以才用到这里的,其实更简单的方法就是大神所说,直接group by分组就行了,SQL代码如下。(果然不能为了用新知识而用新知识,还是得循序渐进,不能一口气吃成个胖子啊@-@)

SELECT
	game_id,
	user_id,
	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;

 

关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9
扫一扫关注公众号添加购物返利助手,领红包
Comments are closed.

推荐使用阿里云服务器

超多优惠券

服务器最低一折,一年不到100!

朕已阅去看看