时间戳

首页 > 解决方案 > 将 3d 时间段分解为每日

sql - 将 3d 时间段分解为每日

问题描述

表 1 显示了不同地区的收入汇总

date           revenue_North_AM  revenue_Asia  revenue_Africa revenue_Oceania
2020-01-01          172               38              10           20
2020-01-04          125              100              30           55 
2020-01-07          202              312              20           85
2020-01-10          212               35               0           35               
2020-01-13          120               50              30           20

我想将 3d 周期分解为每天：table_2

date           revenue_North_AM  revenue_Asia  revenue_Africa revenue_Oceania
2020-01-01          57.33             12.66          3.33          6.66
2020-01-02          57.33             12.66          3.33          6.66
2020-01-03          57.33             12.66          3.33          6.66
2020-01-04          41.66             33.33         10.00         18.33 
2020-01-05          41.66             33.33         10.00         18.33
2020-01-06          41.66             33.33         10.00         18.33
....

作为下一步，我还想将 table_2 格式更改为 table_3：

date           region       revenue
2020-01-01     North_AM      57.33
2020-01-01     Asia          12.66
2020-01-01     Africa         3.33
2020-01-01     Oceania        6.66

从 table_1 到 table_2，我不确定。从table_2到table_3，我在想：

select t2.region, t2.revenue 
from schema.table_2 t2
  cross join lateral (
    values
      (t2.revenue_North_AM, 'North_AM'),
      (t2.revenue_Asia, 'Asia'),
      (t2.revenue_Africa, 'Africa')
      (t2.revenue_Oceania, 'Oceania')
      ) as t3(revenue, region)

但是，使用上面的代码，日期列不包含在交叉连接中。

标签： sqlpostgresql

解决方案

使用您建议的逻辑从表 1 转到表 3：

select t1.date, t3.region, t3.revenue 
from (select gs.date,
             revenue_North_AM / num_days as revenue_North_AM,
             revenue_Asia / num_days as revenue_Asia,
             revenue_Africa / num_days as revenue_Africa,
             revenue_Oceania / num_days as revenue_Oceania
      from (select t1.*,
                   coalesce(lead(date) over (order by date) - interval '1 day', date) as last_date,
                   (lead(date) over (order by date) - date) as num_days
            from table_1 t1
           ) t1 cross join
           generate_series(date, last_date, interval '1 day') gs(date)
     ) t1 cross join lateral
     (values (t1.revenue_North_AM, 'North_AM'),
             (t1.revenue_Asia, 'Asia'),
             (t1.revenue_Africa, 'Africa'),
             (t1.revenue_Oceania, 'Oceania')
     ) t3(revenue, region);

这是一个 db<>fiddle。

推荐阅读