sql金额替换并分摊

Question

急
sql金额减小并分摊到明细
本月订单金额为37847，现在需要把金额减小再平摊到每个订单明细
如：本月有1879张订单 总金额为37847，每张订单下面有不同数量及价格的商品。现在需要将整月的订单金额降低到29867，并分摊到每个品种上，最终分摊下来总金额为29867，不论按总订单量或按明细查 金额误差都不能超过1
需要代码

Answer 1

基于Monster 组和GPT的调写：

-- 先计算本月订单总金额
SELECT SUM(amount) AS total_amount
FROM orders
WHERE month = '2023-04';

-- 假设上面的查询结果为 37847，那么我们可以按以下方式将金额减小并分摊到明细上
WITH cte AS (
  -- 将订单按金额从小到大排序，并为每个订单分配一个序号
  SELECT *,
         ROW_NUMBER() OVER (ORDER BY amount) AS row_num
  FROM orders
  WHERE month = '2023-04'
),
cte2 AS (
  -- 计算每个订单需要分配的金额
  SELECT *,
         amount - (37847 - 29867) / COUNT(*) OVER () AS adjusted_amount
  FROM cte
),
cte3 AS (
  -- 将每个订单的金额分摊到该订单的明细上
  SELECT *,
         adjusted_amount / COUNT(*) OVER (PARTITION BY order_id) AS final_amount
  FROM cte2
)
-- 更新订单明细表中的金额
UPDATE order_details
SET amount = cte3.final_amount
FROM cte3
WHERE order_details.order_id = cte3.order_id
  AND order_details.product_id = cte3.product_id;

用了三个公用表表达式（CTE）来完成任务：

• 第一个 CTE 用于给每个订单分配一个序号，以便后续处理。
• 第二个 CTE 用于计算每个订单需要分配的金额。我们将总金额减去目标金额（29867），然后将其平均分配给每个订单。
• 第三个 CTE 用于将每个订单的金额分摊到其明细上。我们使用窗口函数计算每个订单有多少个明细，并将订单的金额平均分配给每个明细。

最后用 UPDATE 语句将最终的金额更新到订单明细表中。 WHERE 子句中，将订单 ID 和产品 ID 与 CTE 中的值进行匹配，以确保更新正确的行。