如果我需要创建一个报告,说明一个月的新用户,我需要通过整个文档来比较用户是在一个月而不在另一个月.
消息文件:
[{"recipIEnt":1,"user":4,"created_at":"2016-11-10","content":"Duis aliquam convallis nunc.","is_sender_user":true},{"recipIEnt":1,"user":18,"created_at":"2016-12-10","content":"Proin eu mi.","is_sender_user":false},"created_at":"2016-11-20","content":"In hac habitasse platea dictumstm.","user":20,"created_at":"2016-12-14","content":"Donec ut dolor.","user":13,"created_at":"2016-12-06","content":"Nulla mollis molestIE lorem. Quisque ut erat. Curabitur gravida nisi at nibh.","is_sender_user":true}]
最好创建一个User表并创建一个JsONB消息字段来存储您的消息.或者它的方式我可以使用JsONB查询创建我的报告?
解决方法 正如Samuil Petrov提到你可以在Jsonb字段上创建索引,我建议在created_at和user的月份部分创建索引create INDEX td002_si3 ON testData002 (substring(doc->>'created',8),(doc->>'user'));
用这个查询
SELECT substring(doc ->> 'created',8) AS m,ARRAY_AGG(disTINCT doc ->> 'user') AS users FROM testData002 GROUP BY substring(doc ->> 'created',8)
将通过索引扫描为您提供每月用户
GroupAggregate (cost=0.28..381.52 rows=3485 wIDth=50) Group Key: ""substring""((doc ->> 'created'::text),8) -> Index Scan using td002_si3 on testdata002 (cost=0.28..294.28 rows=3500 wIDth=50)
使用生成的测试数据
create table testData002 as select row_number() OVER () as ID,Jsonb_build_object('created',dt::DATE,'user',(random()*1000)::INT) as doc from generate_serIEs(1,10),generate_serIEs('2016-01-01'::TIMESTAMP,'2016-12-15'::TIMESTAMP,'1 day'::INTERVAL) as dt;总结
以上是内存溢出为你收集整理的postgresql – 以下情况的最佳替代方案是什么?全部内容,希望文章能够帮你解决postgresql – 以下情况的最佳替代方案是什么?所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)