mysql 是否具有相当于 Oracle 的“分析功能”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11806528/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does mysql have the equivalent of Oracle's "analytic functions"?
提问by ripper234
I'm looking for analytical function like PARTITION BYin MySQL (see the docsfor more info)
我正在寻找像PARTITION BYMySQL一样的分析函数（有关更多信息，请参阅文档）
Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group.
分析函数根据一组行计算聚合值。它们与聚合函数的不同之处在于它们为每个组返回多行。
Does it exist?
它存在吗？
采纳答案by aleroot
No, it is one of the main lack of MySQL, compared to others DBMS like MSSQL, Oracle, PostgreSQL. I strongly doubt to see Window Functions in MySQL in any future, especially after Oracle acquisition of MySQL ...
不，与 MSSQL、Oracle、PostgreSQL 等其他 DBMS 相比，这是 MySQL 的主要缺陷之一。我强烈怀疑将来会在 MySQL 中看到窗口函数，尤其是在 Oracle 收购 MySQL 之后......
Update 04/2018
更新 04/2018
MySQL 8.0 now supports window functions.
MySQL 8.0 现在支持窗口函数。
回答by Seaux
just wanted to tell you that you can use variables in MySQL to mimic analytic functions. SUM OVER, for example, could be done as follows:
只是想告诉你，你可以在 MySQL 中使用变量来模拟分析函数。例如，SUM OVER 可以按如下方式完成：
SELECT amount,
@sum := @sum + amount as sum
FROM tbl
JOIN (SELECT @sum := 0) s
If you want to PARTITION BY, it's possible but just a bit more complicated. Basically, you add another @variableto watch the account (or whatever you want to partition by), order by account (or your variable), and then reset the @sumwhen the account changes. As follows:
如果你愿意PARTITION BY，这是可能的，但稍微复杂一点。基本上，您添加另一个@variable来监视帐户（或任何您想分区的对象），按帐户（或您的变量）排序，然后@sum在帐户更改时重置。如下：
SELECT account,
amount,
(case when @account != account then @sum := amount else @sum := @sum + amount end) as sum,
(case when @account != account then @account := account else @account end) as _
FROM (SELECT * FROM tbl ORDER BY account)
JOIN (SELECT @sum := 0) s
JOIN (SELECT @account := '') a
You'll note two major changes that had to be done to accomplish the partition effect:
您会注意到为了实现分区效果必须进行的两个主要更改：
The main table (
tbl) is encased in a sub select with anORDER BYclause. This is necessary because when MySQL goes to do the@accountvariable testing the values need to already be ordered. If this didn't happen, you'd get incorrect sum values as well as account values.There is an 'extra' column aliased
as _. You can ignore this column when using results, but the order of the@accountcheck and change needs to be after the@sumcheck and change.Also with this, you could choose to reorder your columns if you didn't mind account being last. This is done by taking out the first
accountcolumn since it duplicates with the last_column and then obviously renamed the aliased_toaccount.
主表 (
tbl) 包含在带有ORDER BY子句的子选择中。这是必要的，因为当 MySQL 进行@account变量测试时，需要已经对值进行排序。如果没有发生这种情况，您将获得错误的总和值以及帐户值。有一个别名为 的“额外”列
as _。使用结果时可以忽略此列，但@account检查和更改的顺序需要在@sum检查和更改之后。此外，如果您不介意帐户是最后一个，您可以选择重新排序您的列。这是通过取出第一
account列来完成的，因为它与最后一_列重复，然后显然将别名重命名_为account.
Resources:
资源：
回答by Kirby
回答by Joe Stampf
There is a commercial product for SQL Server that provides in-database analytic functions and it can be connected to an oracle or MySQL database via 'linked servers'/odbc - here is an article describing it: http://westclintech.com/Blog/tabid/132/EntryId/88/Using-XLeratorDB-with-MySQL-and-other-RDBMS-s.aspx
SQL Server 有一个商业产品，它提供数据库内分析功能，它可以通过“链接服务器”/odbc 连接到 oracle 或 MySQL 数据库 - 这是一篇描述它的文章：http: //westclintech.com/Blog /tabid/132/EntryId/88/Using-XLeratorDB-with-MySQL-and-other-RDBMS-s.aspx
This requires having a Windows o/s and SQL Server 2005 or later (which the Express version is free)
这需要有 Windows 操作系统和 SQL Server 2005 或更高版本（Express 版本是免费的）
