当前位置： 文江博客话题详情

为什么SQL中没有Product聚合函数？

发布于 2024-09-27 05:32:23 字数 257 浏览 1 评论 0原文

我正在寻找类似于 SUM 工作方式的 SELECT PRODUCT(table.price) FROM table GROUP BY table.sale 之类的内容。

我是否错过了文档中的某些内容，或者真的没有 PRODUCT 功能？

如果是这样，为什么不呢？

注意：我在postgres、mysql和mssql中查找了该函数，但没有找到，所以我假设所有sql都不支持它。

原文

Im looking for something like SELECT PRODUCT(table.price) FROM table GROUP BY table.sale similar to how SUM works.

Have I missed something on the documentation, or is there really no PRODUCT function?

If so, why not?

Note: I looked for the function in postgres, mysql and mssql and found none so I assumed all sql does not support it.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（10）

断舍离 2024-10-04 05:32:24

T-SQL 中有一个巧妙的技巧（不确定它是否是 ANSI），它允许将一组行中的字符串值连接到一个变量中。看起来它也适用于乘法：

declare @Floats as table (value float)
insert into @Floats values (0.9)
insert into @Floats values (0.9)
insert into @Floats values (0.9)

declare @multiplier float = null

select 
    @multiplier = isnull(@multiplier, '1') * value
from @Floats

select @multiplier

这可能比 log/exp 解决方案在数值上更稳定。

There is a neat trick in T-SQL (not sure if it's ANSI) that allows to concatenate string values from a set of rows into one variable. It looks like it works for multiplying as well:

declare @Floats as table (value float)
insert into @Floats values (0.9)
insert into @Floats values (0.9)
insert into @Floats values (0.9)

declare @multiplier float = null

select 
    @multiplier = isnull(@multiplier, '1') * value
from @Floats

select @multiplier

This can potentially be more numerically stable than the log/exp solution.

回复 原文
甜妞爱困 2024-10-04 05:32:24

我认为这是因为没有一个编号系统能够容纳许多产品。由于数据库是为大量记录而设计的，1000 个数字的乘积将是巨大的，并且在浮点数的情况下，传播的误差将是巨大的。

另请注意，使用日志可能是一个危险的解决方案。虽然从数学上来说 log(a*b) = log(a)*log(b)，但它可能不会出现在计算机中，因为我们处理的不是实数。如果您计算 2^(log(a)+log(b)) 而不是 a*b，您可能会得到意想不到的结果。例如：

SELECT 9999999999*99999999974482, EXP(LOG(9999999999)+LOG(99999999974482))

在 Sql Server 中返回

999999999644820000025518, 9.99999999644812E+23

所以我的观点就是当你尝试做这个产品时，一定要小心，并且要进行大量的测试。

I think that is because no numbering system is able to accommodate many products. As databases are designed for large number of records, a product of 1000 numbers would be super massive and in case of floating point numbers, the propagated error would be huge.

Also note that using log can be a dangerous solution. Although mathematically log(a*b) = log(a)*log(b), it might not be in computers as we are not dealing with real numbers. If you calculate 2^(log(a)+log(b)) instead of a*b, you may get unexpected results. For example:

SELECT 9999999999*99999999974482, EXP(LOG(9999999999)+LOG(99999999974482))

in Sql Server returns

999999999644820000025518, 9.99999999644812E+23

So my point is when you are trying to do the product do it carefully and test is heavily.

回复 原文
思念满溢 2024-10-04 05:32:24

处理此问题的一种方法（如果您使用脚本语言）是使用 group_concat 函数。
例如，SELECT group_concat(table.price) FROM table GROUP BY table.sale

这将返回一个字符串，其中包含同一销售值的所有价格，并以逗号分隔。
然后使用解析器可以获得每个价格，并进行乘法运算。 （在 php 中你甚至可以使用 array_reduce 函数，事实上在 php.net 手册中 你会得到一个合适的例子）。

干杯

One way to deal with this problem (if you are working in a scripting language) is to use the group_concat function.
For example, SELECT group_concat(table.price) FROM table GROUP BY table.sale

This will return a string with all prices for the same sale value, separated by a comma.
Then with a parser you can get each price, and do a multiplication. (In php you can even use the array_reduce function, in fact in the php.net manual you get a suitable example).

Cheers

回复 原文
紫南 2024-10-04 05:32:24

另一种方法基于以下事实：笛卡尔积的基数是特定集合的基数的乘积;-)

⚠ 警告：此示例仅供娱乐，相当学术性，请勿在生产中使用它！ （除此之外，它仅适用于正整数且几乎很小的整数）⚠

with recursive t(c) as (
  select unnest(array[2,5,7,8])
), p(a) as (
  select array_agg(c) from t
  union all
  select p.a[2:]
  from p
  cross join generate_series(1, p.a[1])
)
select count(*) from p where cardinality(a) = 0;

Another approach based on fact that the cardinality of cartesian product is product of cardinalities of particular sets ;-)

⚠ WARNING: This example is just for fun and is rather academic, don't use it in production! (apart from the fact it's just for positive and practically small integers)⚠

with recursive t(c) as (
  select unnest(array[2,5,7,8])
), p(a) as (
  select array_agg(c) from t
  union all
  select p.a[2:]
  from p
  cross join generate_series(1, p.a[1])
)
select count(*) from p where cardinality(a) = 0;
回复 原文
帝王念 2024-10-04 05:32:24

该问题可以使用现代 SQL 功能（例如窗口函数和 CTE）来解决。一切都是标准 SQL，并且与基于对数的解决方案不同，不需要从整数世界切换到浮点世界，也不需要处理非正数。只需对行进行编号并在递归查询中评估产品，直到没有行为止：

   with recursive t(c) as (
     select unnest(array[2,5,7,8])
   ), r(c,n) as (
     select t.c, row_number() over () from t
   ), p(c,n) as (
     select c, n from r where n = 1
     union all
     select r.c * p.c, r.n from p join r on p.n + 1 = r.n
   )
   select c from p where n = (select max(n) from p);

由于您的问题涉及按销售列分组，所以事情变得有点复杂，但仍然可以解决：

   with recursive t(sale,price) as (
     select 'multiplication', 2 union
     select 'multiplication', 5 union
     select 'multiplication', 7 union
     select 'multiplication', 8 union
     select 'trivial', 1 union
     select 'trivial', 8 union
     select 'negatives work', -2 union
     select 'negatives work', -3 union
     select 'negatives work', -5 union
     select 'look ma, zero works too!', 1 union
     select 'look ma, zero works too!', 0 union
     select 'look ma, zero works too!', 2
   ), r(sale,price,n,maxn) as (
     select t.sale, t.price, row_number() over (partition by sale), count(1) over (partition by sale)
     from t
   ), p(sale,price,n,maxn) as (
     select sale, price, n, maxn
     from r where n = 1
     union all
     select p.sale, r.price * p.price, r.n, r.maxn
     from p
     join r on p.sale = r.sale and p.n + 1 = r.n
   )
   select sale, price
   from p
   where n = maxn
   order by sale;

结果：

sale,price
"look ma, zero works too!",0
multiplication,560
negatives work,-30
trivial,8

在 Postgres 上测试。

The problem can be solved using modern SQL features such as window functions and CTEs. Everything is standard SQL and - unlike logarithm-based solutions - does not require switching from integer world to floating point world nor handling nonpositive numbers. Just number rows and evaluate product in recursive query until no row remain:

   with recursive t(c) as (
     select unnest(array[2,5,7,8])
   ), r(c,n) as (
     select t.c, row_number() over () from t
   ), p(c,n) as (
     select c, n from r where n = 1
     union all
     select r.c * p.c, r.n from p join r on p.n + 1 = r.n
   )
   select c from p where n = (select max(n) from p);

As your question involves grouping by sale column, things got little bit complicated but it's still solvable:

   with recursive t(sale,price) as (
     select 'multiplication', 2 union
     select 'multiplication', 5 union
     select 'multiplication', 7 union
     select 'multiplication', 8 union
     select 'trivial', 1 union
     select 'trivial', 8 union
     select 'negatives work', -2 union
     select 'negatives work', -3 union
     select 'negatives work', -5 union
     select 'look ma, zero works too!', 1 union
     select 'look ma, zero works too!', 0 union
     select 'look ma, zero works too!', 2
   ), r(sale,price,n,maxn) as (
     select t.sale, t.price, row_number() over (partition by sale), count(1) over (partition by sale)
     from t
   ), p(sale,price,n,maxn) as (
     select sale, price, n, maxn
     from r where n = 1
     union all
     select p.sale, r.price * p.price, r.n, r.maxn
     from p
     join r on p.sale = r.sale and p.n + 1 = r.n
   )
   select sale, price
   from p
   where n = maxn
   order by sale;

Result:

sale,price
"look ma, zero works too!",0
multiplication,560
negatives work,-30
trivial,8

Tested on Postgres.

回复 原文
入怼 2024-10-04 05:32:24

这是一个适合任何有需要的人的 Oracle 解决方案

with data(id, val) as(
select 1,1.0 from dual union all
select 2,-2.0 from dual union all
select 3,1.0 from dual union all
select 4,2.0 from dual 
),
neg(val , modifier) as(
select exp(sum(ln(abs(val)))), case when mod(count(*),2) = 0 then 1 Else -1 end
from data
where val <0
)
,
pos(val) as (
select exp(sum(ln(val)))
from data
where val >=0
)
select (select val*modifier from neg)*(select val from pos) product from dual

Here is an oracle solution for anyone who needs it

with data(id, val) as(
select 1,1.0 from dual union all
select 2,-2.0 from dual union all
select 3,1.0 from dual union all
select 4,2.0 from dual 
),
neg(val , modifier) as(
select exp(sum(ln(abs(val)))), case when mod(count(*),2) = 0 then 1 Else -1 end
from data
where val <0
)
,
pos(val) as (
select exp(sum(ln(val)))
from data
where val >=0
)
select (select val*modifier from neg)*(select val from pos) product from dual
回复 原文
白芷 2024-10-04 05:32:23

对于 MSSQL，您可以使用它。它可以用于其他平台：它只是数学和对数的聚合。

SELECT
    GrpID,
    CASE
       WHEN MinVal = 0 THEN 0
       WHEN Neg % 2 = 1 THEN -1 * EXP(ABSMult)
       ELSE EXP(ABSMult)
    END
FROM
    (
    SELECT
       GrpID, 
       --log of +ve row values
       SUM(LOG(ABS(NULLIF(Value, 0)))) AS ABSMult,
       --count of -ve values. Even = +ve result.
       SUM(SIGN(CASE WHEN Value < 0 THEN 1 ELSE 0 END)) AS Neg,
       --anything * zero = zero
       MIN(ABS(Value)) AS MinVal
    FROM
       Mytable
    GROUP BY
       GrpID
    ) foo

取自我的回答：SQL Server 查询 - 分组乘法

For MSSQL you can use this. It can be adopted for other platforms: it's just maths and aggregates on logarithms.

SELECT
    GrpID,
    CASE
       WHEN MinVal = 0 THEN 0
       WHEN Neg % 2 = 1 THEN -1 * EXP(ABSMult)
       ELSE EXP(ABSMult)
    END
FROM
    (
    SELECT
       GrpID, 
       --log of +ve row values
       SUM(LOG(ABS(NULLIF(Value, 0)))) AS ABSMult,
       --count of -ve values. Even = +ve result.
       SUM(SIGN(CASE WHEN Value < 0 THEN 1 ELSE 0 END)) AS Neg,
       --anything * zero = zero
       MIN(ABS(Value)) AS MinVal
    FROM
       Mytable
    GROUP BY
       GrpID
    ) foo

Taken from my answer here: SQL Server Query - groupwise multiplication

回复 原文
傻比既视感 2024-10-04 05:32:23

我不知道为什么没有，但是（要多注意负数）你可以使用对数和指数来执行以下操作：-

select exp (sum (ln (table.price))) from table ...

I don't know why there isn't one, but (take more care over negative numbers) you can use logs and exponents to do:-

select exp (sum (ln (table.price))) from table ...
回复 原文
听闻余生 2024-10-04 05:32:23

SQL 标准中没有 PRODUCT 设置函数。不过，它似乎是一个有价值的候选者（与 CONCATENATE 集合函数不同：它不太适合 SQL，例如生成的数据类型将涉及多值，并会在第一范数方面带来问题形式）。

SQL 标准旨在整合 1990 年左右 SQL 产品的功能，并为未来的开发提供“思想领导”。简而言之，它们记录了 SQL 的作用以及 SQL 应该做什么。 PRODUCT 集合函数的缺失表明，在 1990 年，没有供应商值得将其纳入其中，并且学术界也没有兴趣将其引入标准中。

当然，供应商总是寻求添加自己的功能，如今通常作为标准的扩展而不是无关紧要的。我不记得在我使用过的任何 SQL 产品中看到过 PRODUCT 集函数（甚至不需要一个函数）。

无论如何，使用 log 和 exp 标量函数（以及处理负值的逻辑）和 SUM set 函数，解决方法都相当简单；有关一些示例代码，请参阅 @gbn 的答案。不过，我从来不需要在业务应用程序中执行此操作。

总之，我最好的猜测是 SQL 最终用户对 PRODUCT 集函数没有需求；此外，任何有学术兴趣的人都可能会发现这种解决方法是可以接受的（即不会重视 PRODUCT 集函数提供的语法糖）。

出于兴趣，SQL Server 领域确实需要新的集合函数，但窗口函数种类（以及标准 SQL 也是如此）。有关更多详细信息，包括如何参与进一步推动需求，请参阅 Itzik Ben-Gan 的博客。

There is no PRODUCT set function in the SQL Standard. It would appear to be a worthy candidate, though (unlike, say, a CONCATENATE set function: it's not a good fit for SQL e.g. the resulting data type would involve multivalues and pose a problem as regards first normal form).

The SQL Standards aim to consolidate functionality across SQL products circa 1990 and to provide 'thought leadership' on future development. In short, they document what SQL does and what SQL should do. The absence of PRODUCT set function suggests that in 1990 no vendor though it worthy of inclusion and there has been no academic interest in introducing it into the Standard.

Of course, vendors always have sought to add their own functionality, these days usually as extentions to Standards rather than tangentally. I don't recall seeing a PRODUCT set function (or even demand for one) in any of the SQL products I've used.

In any case, the work around is fairly simple using log and exp scalar functions (and logic to handle negatives) with the SUM set function; see @gbn's answer for some sample code. I've never needed to do this in a business application, though.

In conclusion, my best guess is that there is no demand from SQL end users for a PRODUCT set function; further, that anyone with an academic interest would probably find the workaround acceptable (i.e. would not value the syntactic sugar a PRODUCT set function would provide).

Out of interest, there is indeed demand in SQL Server Land for new set functions but for those of the window function variety (and Standard SQL, too). For more details, including how to get involved in further driving demand, see Itzik Ben-Gan's blog.

回复 原文
煮茶煮酒煮时光 2024-10-04 05:32:23

您可以执行乘积聚合函数，但您必须自己进行数学计算，如下所示...

SELECT
    Exp(Sum(IIf(Abs([Num])=0,0,Log(Abs([Num])))))*IIf(Min(Abs([Num]))=0,0,1)*(1-2*(Sum(IIf([Num]>=0,0,1)) Mod 2)) AS P
FROM
   Table1

来源：http:// Productfunctionsql.codeplex.com/

You can perform a product aggregate function, but you have to do the maths yourself, like this...

SELECT
    Exp(Sum(IIf(Abs([Num])=0,0,Log(Abs([Num])))))*IIf(Min(Abs([Num]))=0,0,1)*(1-2*(Sum(IIf([Num]>=0,0,1)) Mod 2)) AS P
FROM
   Table1

Source: http://productfunctionsql.codeplex.com/

回复 原文
~没有更多了~

关于作者

素罗衫

暂无简介

0 文章
0 评论
20 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

lioqio

文章 0 评论 0

Single

文章 0 评论 0

禾厶谷欠

文章 0 评论 0

alipaysp_2zg8elfGgC

文章 0 评论 0

qq_N6d4X7

文章 0 评论 0

放低过去

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文