postgresql 基于其他表中的列添加起始行和下一行的值

tnkciper  于 2023-10-18  发布在  PostgreSQL
关注(0)|答案(1)|浏览(131)

我有两张table。
表A**
| 产品|植物|股票|
| --|--|--|
| 11248 |ABC12| 65 |
| 98490 |GHI34| 54 |
| 11248 |DEF56| 25 |

表B

| 产品|植物|周|销售|生产|
| --|--|--|--|--|
| 11248 |ABC12| 0 | 7894 | 38410 |
| 11248 |ABC12|......这是什么?|......这是什么?|......这是什么?|
| 11248 |ABC12| 11 | 6887 | 84351 |
首先,我想添加表A中第0周的唯一股票价值,如下表所示
| 产品|植物|周|销售|生产|股票|
| --|--|--|--|--|--|
| 11248 |ABC12| 0 | 7894 | 38410 | 65 |
第二,使用操作(Production+Stock)-Sales计算本周剩余时间的股票,并得到如下表。
| 产品|植物|周|销售|生产|股票|
| --|--|--|--|--|--|
| 11248 |ABC12| 0 | 7894 | 38410 | 65 |
| 11248 |ABC12| 1 | 6587 | 87427 |(87427+65)-6587= 80905|
| 11248 |ABC12|......这是什么?|......这是什么?|......这是什么?|......这是什么?|
| 11248 |ABC12| 11 | 6887 | 84351 |(84351+Stock_row10)-6887=NewStock|
我尝试两个查询
第一个查询:

  1. with Table_A1 (Product, Plant, Stock) as (select Product, Plant, Stock from TABLE_A)
  2. select Product,
  3. Plant,
  4. Week,
  5. Sales,
  6. Production,
  7. from TABLE_B;
  8. case
  9. when Table_A1.Product = TABLE_B.Product and w.Plant=TABLE_B.Plant and TABLE_B.Week=0 then TABLE_A.Stock
  10. when Table_A1.Product = TABLE_B.Product and w.Plant=TABLE_B.Plant and TABLE_B.Week=1 then (TABLE_B.Production+TABLE_A.Stock)-TABLE_B.Sales
  11. end ;

但我得到了错误“错误:在“from”处或附近出现错误“
第二个查询:

  1. ALTER TABLE TABLE_B
  2. ADD Stock INT;
  3. SELECT * from TABLE_B
  4. inner join TABLE_A ON TABLE_A.Product=TABLE_B.Product
  5. case
  6. when Table_A1.Product = TABLE_B.Product and w.Plant=TABLE_B.Plant and TABLE_B.Week=0 then TABLE_A.Stock
  7. end;

但我得到了错误“错误:在'CASE'处或附近出现错误“。有什么需要帮忙的吗?

mo49yndu

mo49yndu1#

为此使用一个窗口函数。
您的用例是复杂的,因为您想要的结果显示初始库存是第一周结束时的库存。我以前见过这种情况,我将第一周的销售和生产结转,以调整它们从以后的库存计算中排除:

  1. with nets as (
  2. select a.product, a.plant, b.week,
  3. b.sales, b.production,
  4. sum(b.sales) over w as running_sales,
  5. sum(b.production) over w as running_production,
  6. first_value(b.sales) over w as first_week_sales,
  7. first_value(b.production) over w as first_week_production,
  8. a.stock as first_week_stock
  9. from table_a a
  10. join table_b b on (b.product, b.plant) = (a.product, a.plant)
  11. window w as (partition by a.product, a.plant order by b.week)
  12. )
  13. select product, plant, week, sales, production,
  14. running_production + first_week_stock - first_week_production
  15. - running_sales + first_week_sales as stock
  16. from nets;

工作fiddle.

展开查看全部

相关问题