一、提出问题
在postgre数据库,遇到了数组内嵌套数组的jsonb存储格式,如下:
SELECT *
FROM public.keyword_trend
查询结果:
其中,history一栏[[“2014-05-11”,70],[“2014-05-18”,69]…]中,每个数组包含两项,分别为日期和得分,我们希望查询最近得分(2019年2,3,4月平均得分)环比增长大于100%的keyword。
二、解决方法
先上代码:
with t1 as (
select keyword,to_date(h->>0,'YYYY MM DD') as day,(h->>1)::integer as num
from keyword_trend as t, jsonb_array_elements(history) as h
), t2 as(
select keyword,extract(year from day)::integer as year,
extract(month from day)::integer as month,
extract(day from day)::integer as day,num
from t1
), t3 as (
select keyword,year,avg(num)
from t2
where year in (2018,2019) and month in (2,