postgresql 如何将计算数据与月份连接？

oxosxuxt 于 2023-06-29 发布在 PostgreSQL

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

我有地区和月份：

80 | San Diego         |      1
 80 | San Diego         |      2
 80 | San Diego         |      3
 80 | San Diego         |      4
 80 | San Diego         |      5
 80 | San Diego         |      6
 80 | San Diego         |      7
 80 | San Diego         |      8
 80 | San Diego         |      9
 80 | San Diego         |     10
 80 | San Diego         |     11
 80 | San Diego         |     12
 74 | Tampa             |      1
 74 | Tampa             |      2
 74 | Tampa             |      3
 74 | Tampa             |      4
 74 | Tampa             |      5
 74 | Tampa             |      6
 74 | Tampa             |      7
 74 | Tampa             |      8
 74 | Tampa             |      9
 74 | Tampa             |     10
 74 | Tampa             |     11
 74 | Tampa             |     12
 64 | Denver            |      1
 64 | Denver            |      2
 64 | Denver            |      3
 64 | Denver            |      4
 64 | Denver            |      5
 64 | Denver            |      6
 64 | Denver            |      7
 64 | Denver            |      8
 64 | Denver            |      9
 64 | Denver            |     10
 64 | Denver            |     11
 64 | Denver            |     12

我也计算了一下：

74 | Tampa             |     12 | 100
 74 | Tampa             |      6 |  80
 80 | San Diego         |      7 |  50

如何连接这些表以获得：

80 | San Diego         |      1  |
 80 | San Diego         |      2  |
 80 | San Diego         |      3  |
 80 | San Diego         |      4  |
 80 | San Diego         |      5  |  50
 80 | San Diego         |      6  |
 80 | San Diego         |      7  |
 80 | San Diego         |      8  |
 80 | San Diego         |      9  |
 80 | San Diego         |     10  |
 80 | San Diego         |     11  |
 80 | San Diego         |     12  |
 74 | Tampa             |      1  |
 74 | Tampa             |      2  |
 74 | Tampa             |      3  |
 74 | Tampa             |      4  |
 74 | Tampa             |      5  |
 74 | Tampa             |      6  |  80
 74 | Tampa             |      7  |
 74 | Tampa             |      8  |
 74 | Tampa             |      9  |
 74 | Tampa             |     10  |
 74 | Tampa             |     11  |
 74 | Tampa             |     12  | 100

正如你所看到的，Denver应该从第一个结果集中过滤出来。

postgresql

来源：https://stackoverflow.com/questions/76546209/how-to-join-calculated-data-with-months

1条答案

按热度按时间

sqougxex1#

使用窗口函数计算每个区域的计算总和（c CTE）以及总和为0的过滤器区域。这是丹佛的下降。我假设第一个表名为t，第二个表名为-s。

with c as
(
 select t.*, s.calc_sum, 
        sum(s.calc_sum) over (partition by t.region_id) total
 from t left outer join s using(region_id, "month")
)
select region_id, region_name, "month", calc_sum 
from c where total > 0 order by region_id desc, "month";

参见DB Fiddle demo
这里使用group by。

with c1 as
(
 select t.*, s.calc_sum
 from t left outer join s using(region_id, "month")
),
c2 as
(
  select region_id
  from c1
  group by region_id having (sum(calc_sum) > 0)
)
select c1.*
from c1 inner join c2 using (region_id)
order by region_id desc, "month";

DB Fiddle demo

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

我来回答

postgresql 如何将计算数据与月份连接？

1条答案

相关问题

热门标签

最新问答