postgresql 基于多个表计算费用的SQL查询

我正在做一个大的（不是真实的的）任务，管理几个国家的开支。我已经计算了每个城镇的投资能力，现在我需要计算建造这些飞船的预算。任务如下：
我们有以下表格（有表格城镇和飞船，但任务很清楚没有他们在这里）。我们需要计算多少钱是需要完成每一种类型的船可用于生产。所以，我们有不同类型的宇宙飞船，每种类型需要不同类型的部件（见表Spaceship_required_part）。每个城镇都生产几种类型的零件（见表Spaceship_part_in_town）.我们需要计算，成本是多少（请参见“飞船部件”中的成本、“城镇中的飞船部件”中的阶段，和数量）来建造每一种可用类型的宇宙飞船。可用的意思是所需的部件可以在给定的城市中找到。我们计算一个给定城市的预算（我可以自己为其他城市计算）。
第一个
我知道如何使用编程语言解决这个任务，但我的SQL技能不是很好。我知道首先我需要检查我们可以用镇上可用的部件建造什么飞船。这可以通过使用所需部件的计数器来完成（amount）和镇上可用的零件（count(spaceship_part_id)）。然后我需要用公式(100-stage)*cost/100计算建造每艘飞船所需的总和。
然而，我不知道如何用SQL代码编写这个。我正在用PostgreSQL编写。

数据模型如下所示：

要以最少的建造成本建造宇宙飞船，我们可以：
步骤1.计算零件的build_cost =（100 -阶段）* 成本/ 100;对于每个部分，根据阶段对建造成本进行排序，这样我们就可以最小化宇宙飞船的总成本。
步骤2.基于build_cost，我们按所需数量计算零件的total_cost（以便与spaceship_required_part.amount进行比较），并在part_sources中记录零件的来源，part_sources是CSV格式的(city_id, stage, build_cost),...
第3步。一旦我们有可用的零件和总数量和成本计算，我们加入它与飞船_所需的_部分，以获得如下结果：

spaceship_id|spaceship_part_id|amount|total_cost|part_sources         |
------------+-----------------+------+----------+---------------------+
           1|                1|     2|      50.0|(4,80,20),(3,70,30)  |
           1|                2|     1|     120.0|(1,40,120)           |
           2|                2|     2|     260.0|(1,40,120),(2,30,140)|
           2|                3|     1|     180.0|(2,40,180)           |
           3|                3|     2|     360.0|(2,40,180),(4,40,180)|

上面告诉我们，要建造：

太空船#1，我们需要从城市#4和城市#3采购的部件#1 x 2;来自城市1的部件#2 x 1;总成本= 50 + 120 = 170，或
spceeship#2，我们需要从城市#1和城市#2采购的部件#2 x 2;部件#3 x 1来自城市#2;总成本= 160 + 180 = 340，或
太空船#3，我们需要来自城市#2和城市#4的零件#3 x 2;总成本= 360。

在第一次迭代之后，我们可以更新spaceship_part_in_town并从spaceship_required_part中删除第一艘宇宙飞船，然后再次运行查询以获取要构建的第二艘宇宙飞船及其部件源代码。

with cte_part_sources as (
select spt.spaceship_part_id,
       spt.city_id,
       sp.cost,       
       spt.stage,
       (100.0-spt.stage)*sp.cost/100.0 as build_cost,
       row_number() over (partition by spt.spaceship_part_id order by spt.stage desc) as cost_rank
  from spaceship_part_in_town spt
  join spaceship_part sp
    on spt.spaceship_part_id = sp.id),
cte_parts as (
select spaceship_part_id,
       city_id,
       cost_rank,
       cost,
       stage,
       build_cost,
       cost_rank as total_qty,
       sum(build_cost) over (partition by spaceship_part_id order by cost_rank) as total_cost,
       string_agg('(' || city_id || ',' || stage || ',' || build_cost || ')',',') over (partition by spaceship_part_id order by cost_rank) as part_sources
  from cte_part_sources)
select srp.spaceship_id,
       srp.spaceship_part_id,
       srp.amount,
       p.total_cost,
       p.part_sources
  from spaceship_required_part srp
  left
  join cte_parts p
    on srp.spaceship_part_id = p.spaceship_part_id
   and srp.amount = p.total_qty;

编辑：

已添加db fiddle

postgresql 基于多个表计算费用的SQL查询

1条答案

相关问题

热门标签

最新问答