postgresql SQL：如何清理这些数据并动态地将它们转换成列

1cklez4t 于 2023-06-22 发布在 PostgreSQL

关注(0)|答案(1)|浏览(158)

在我的数据库中，我有一个列（progress_history），它捕获了如下值：

eg1: {'2022-11-01': 44038.91099579527, '2022-12-05': 44038.91099579527, '2023-01-21': 20623.839917193636, '2023-02-01': 20623.839917193636, '2023-02-25': 20623.839917193636}
eg2: {'2023-05-26T12:53:50.708195+00:00': 0.0, '2023-05-26T12:53:51.034034+00:00': 0}

这将捕获不同时间段的度量值
我试图实现的是，从今天开始的最后6个月，代码应该查看不同的月份并捕获与该月份相关的值。
例如，在eg1中，对于Nov'22，它应该给予44038.91，等等&在eg2中，对于May'23，它应该给出50.71（月份的最低日期值）。
尝试以下代码，仅当在每月的第一天捕获度量时才给我值。而且，我无法动态地获得月份。使用特里诺DB.

with kpis as (
    select
        k.kpi,
        k.progress_history,
        replace(k.progress_history, '''', '"') as progress_cleaned
    from
        kpis as k
)
select
    kpi,
    json_extract(
        progress_cleaned,
        '$."2022-10-01"',
        '$."2022-11-01"',
        '$."2022-12-01"',
        '$.error()'
    )
    emits(
        "2022-10-01" double,
        "2022-11-01" double,
        "2022-12-01" double,
        error_column varchar(2000000)
    )
from kpis
;

postgresql

来源：https://stackoverflow.com/questions/76439472/sql-how-to-clean-this-data-and-dynamically-convert-them-into-columns

1条答案

按热度按时间

kupeojn61#

第一步是将字符串转换为progress_history列。您的CTE正确地做到了这一点（尽管恕我直言，您应该在存储数据时这样做，以便在数据库中始终具有有效的JSON）。剩下的就是从JSON中提取键值对到标准列的转换，以及 key 的日期（时间戳）操作。我不知道你从哪里得到了 json_extract的语法用户... emits（可能是对MySQl文档的无效引用），但它不是来自Postgres;两者都不存在。要提取键值对，请使用函数jsonb_each()。要获取每个月的最低值，请结合使用window函数row_number()和date_trunc（）函数。综合起来得出：（参见demo）

with kpis_hist(kpi, progress_history) as
     ( select k.kpi
            , replace(k.progress_history, '''', '"')::jsonb   
        from kpis k
     ) --select * from kpis_hist
select kpi "KPI"
     , to_char( date_trunc('month', key), 'Mon''YY')  "Month" 
     , round(value, 2)  "Value" 
  from (select k.kpi
             , js.key::timestamptz  
             , js.value::numeric 
             , row_number() over (partition by k.kpi, date_trunc('month', js.key::timestamptz)
                                      order by js.key) rn
          from kpis_hist k
             , jsonb_each(k.progress_history) js
        ) sq 
 where rn = 1       
 order by kpi;

注：演示的数据更改。对于exp 2，将预期值更改为123.46，因为预期值50.71不是键值对的值，它是密钥（时间戳）的一部分。

赞(0）回复(0）举报 2023-06-22

我来回答

postgresql SQL：如何清理这些数据并动态地将它们转换成列

1条答案

相关问题

热门标签

最新问答