时间戳

首页 > 解决方案 > 仅当存在一对值时，Postgres 才对来自 json 的值求和

sql - 仅当存在一对值时，Postgres 才对来自 json 的值求和

问题描述

我的 json 字段看起来像这样

{
"Ui": [
    {
        "element": "TG1",
        "mention": "in",
        "time": 123
    },
    {
        "element": "TG1",
        "mention": "out",
        "time": 125
    },        
    { "element": "TG2",
        "mention": "in",
        "time": 251
    },
    {
        "element": "TG2",
        "mention": "out",
        "time": 259
    },        
    { "element": "TG2",
        "mention": "in",
        "time": 251
    }
]
 }

我正在尝试获取每个元素的时间差总和，如下所示

| element  |   Timespent   |
|  TG1     |     2         |
|  TG2     |     8         |

理想情况下，问题是每个“in”元素都应该有一个“out”元素，这在上面的示例中显然不是这种情况。我只想计算这对值的差异，并忽略任何与 in 不对应的值。我该怎么做？

以下是我用来获取时差的方法

select element, sum(time) as time_spent
from my_table
cross join lateral (
  select
    value->>'element' as element,
    case value->>'mention' when 'in' then -(value->>'time')::numeric else (value->>'time')::numeric end as time
  from json_array_elements(json_column->'Ui')) as elements
group by 1
order by 1

标签： sqljsonpostgresqljsonb

解决方案

我不确定你的 json_column 属性 - 你需要按它分组，以免在行之间混合值，所以我将它包含在 CTE 部分的窗口聚合中。但是你的结果中没有它，所以我在最后的 qry 中也跳过了它。简而言之 - 您可以检查订单号是否为偶数并等于最大订单号，然后跳过它：

select json_column
, e->>'element' element
, case when
 mod(lag(i) over (partition by json_column::text ,e->>'element' order by i),2) = 0
 and
 max(i) over (partition by json_column::text ,e->>'element') = i
then true else false end "skip"
, case when e->>'mention' = 'in' then -(e->>'time')::int else (e->>'time')::int end times
from my_table, json_array_elements(json_column->'Ui') with ordinality o(e,i)
)
select element, sum (times)
from logic
where not skip
group by element
;
 element | sum
---------+-----
 TG1     |   2
 TG2     |   8
(2 rows)

推荐阅读