主页 / 问题 / 1532953
Accepted
Acinit
Asked:2023-07-28 17:18:16 +0800 CST2023-07-28 17:18:16 +0800 CST

浮点问题，熵

  • 772

想象一下以下情况

我们有 4 列

  • 不含增值税金额 ->sum_wo_tax
  • 含增值税金额 ->sum_w_tax
  • 决定是否缴纳增值税 ->is_wo_nds
  • 这笔金额是否被排除在外（我们是否考虑在内）->excluded_from_delivery

我们要计算订单中扣除排除项目的系数，有一个公式

sum(decode(t.is_wo_nds, 1, t.sum_wo_tax, t.sum_w_tax) * abs(t.excluded_from_delivery)) 
/
sum(decode(t.is_wo_nds, 1, decode(t.sum_wo_tax,0,1,t.sum_wo_tax), decode(t.sum_w_tax,0,1,t.sum_w_tax))) as coef

我们来测试一下。

我们假设有3个位置，第一个为100，第二个为100，第三个为0，排除第一个和第三个，结果我们在金额中减去了100卢布200，等于一个系数0.5的

select sum(decode(t.is_wo_nds, 1, t.sum_wo_tax, t.sum_w_tax) * abs(t.excluded_from_delivery)) 
/
sum(decode(t.is_wo_nds, 1, decode(t.sum_wo_tax,0,1,t.sum_wo_tax), decode(t.sum_w_tax,0,1,t.sum_w_tax))) as coef
              from (select 0 as is_wo_nds,
                           100 as sum_wo_tax,
                           100 as sum_w_tax,
                           1 as excluded_from_delivery
                           from dual
                    union all
                    select 0 as is_wo_nds,
                           100 as sum_wo_tax,
                           100 as sum_w_tax,
                           0 as excluded_from_delivery
                           from dual
                    union all
                    select 0 as is_wo_nds,
                           0 as sum_wo_tax,
                           0 as sum_w_tax,
                           1 as excluded_from_delivery
                           from dual) t

我们得到

coef
.4975124378109452736318407960199004975124

这很奇怪，但它应该有效。0.5

sum_w_tax如果我们删除所有处理并且我们确信只有并且excluded_from_delivery那就是，将参与我们的计算

select sum(t.sum_w_tax * abs(t.excluded_from_delivery)) /
                   sum(t.sum_w_tax) as coef
              from (select 0 as is_wo_nds,
                           100 as sum_wo_tax,
                           100 as sum_w_tax,
                           1 as excluded_from_delivery
                           from dual
                    union all
                    select 0 as is_wo_nds,
                           100 as sum_wo_tax,
                           100 as sum_w_tax,
                           0 as excluded_from_delivery
                           from dual
                    union all
                    select 0 as is_wo_nds,
                           0 as sum_wo_tax,
                           0 as sum_w_tax,
                           1 as excluded_from_delivery
                           from dual) t

我们得到

coef
.5

解决方案本身就是舍入，但对于要舍入哪个符号没有明确的答案，因为例外可能是sum_w_tax它趋于无穷大的位置（相对于sum_w_tax它趋于 0 的位置）。

db<>fiddle（如果需要）

他的行为有这么奇怪吗decode？

UPD1

如果我们重写正在构建的所有内容会怎样case when？

SELECT 
    SUM(CASE 
            WHEN t.is_wo_nds = 1 THEN t.sum_wo_tax * ABS(t.excluded_from_delivery)
            ELSE t.sum_w_tax * ABS(t.excluded_from_delivery)
        END
    ) / 
    SUM(CASE 
            WHEN t.is_wo_nds = 1 THEN 
                CASE 
                    WHEN t.sum_wo_tax = 0 THEN 1
                    ELSE t.sum_wo_tax
                END
            ELSE 
                CASE 
                    WHEN t.sum_w_tax = 0 THEN 1
                    ELSE t.sum_w_tax
                END
        END
    ) as coef
              from (select 0 as is_wo_nds,
                           100 as sum_wo_tax,
                           100 as sum_w_tax,
                           1 as excluded_from_delivery
                           from dual
                    union all
                    select 0 as is_wo_nds,
                           100 as sum_wo_tax,
                           100 as sum_w_tax,
                           0 as excluded_from_delivery
                           from dual
                    union all
                    select 0 as is_wo_nds,
                           0 as sum_wo_tax,
                           0 as sum_w_tax,
                           1 as excluded_from_delivery
                           from dual) t

结果是一样的

coef
.4975124378109452736318407960199004975124

那些。原则上，条件块会对数字做一些事情吗？

更新了数据库<>小提琴

oracle

2 个回答

  1. 就在这里就在这里

    sum(decode(t.is_wo_nds, 1, decode(t.sum_wo_tax,0,1,t.sum_wo_tax), decode(t.sum_w_tax,0,1,t.sum_w_tax))) as coef

    为什么有单位？

    100/201=.4975124378109452736318407960199004975124

    • 1
  2. Best Answer
    SELECT
    SUM(
        CASE
            WHEN t.is_wo_nds = 1 THEN t.sum_wo_tax
            ELSE t.sum_w_tax
        END * t.excluded_from_delivery
    ) / NULLIF(SUM(
        CASE
            WHEN t.is_wo_nds = 1 THEN t.sum_wo_tax
            ELSE t.sum_w_tax
        END
    ), 0) AS coef
FROM (
    SELECT 0 AS is_wo_nds, 100 AS sum_wo_tax, 100 AS sum_w_tax, 1 AS excluded_from_delivery FROM dual
    UNION ALL
    SELECT 0 AS is_wo_nds, 100 AS sum_wo_tax, 100 AS sum_w_tax, 0 AS excluded_from_delivery FROM dual
    UNION ALL
    SELECT 0 AS is_wo_nds, 0 AS sum_wo_tax, 0 AS sum_w_tax, 1 AS excluded_from_delivery FROM dual
) t;

    根据评论得出了这个选项。

    从简单到复杂。
    首先，我们重新设计了逻辑：
    SUM(t.sum_w_tax * (1 - t.excluded_from_delivery)) / SUM(t.sum_w_tax)

    让它尽可能简单。1-后来删除了（在评论中）。

    通过CASE表达式，我们根据增值税决定因素 的值选择适当的金额is_wo_nds。如果is_wo_nds等于1，则取sum_wo_tax，否则 — sum_w_tax。

    被零除

    最强大的技巧是除以零的规定。这里一切都可以通过NULLIF(SUM(…), 0)除法表达式中的分母来简化。如果除去排除商品的订单金额为零，则除法结果为NULL，可以避免除零的错误。

    • 1

相关问题