您现在的位置是:首页 >  编程分享 > DBSERVICEDBSERVICE

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

Valiben 转载 编程分享 2018-01-03

简介 有的牌局记录表统计归纳出每天每个玩家在每个游戏上面进行的局数。首先牌局记录表的结构大致如下: ...


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

其中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;

 

参考地址:https://my.oschina.net/u/3400816/blog/1919711

Tags:


本篇评论 —— 揽流光,涤眉霜,清露烈酒一口话苍茫。


    声明:参照站内规则,不文明言论将会删除,谢谢合作。


      最新评论



ABOUT ME

Name:袅袅牧童 | Arkin

Job:Web全栈技术工程师

WeChat:nnmutong

Email:nnmutong@icloud.com

标签云