解释之前回答的帖子的解决方案：在 MySQL 中查找中位数
我需要在 MySQL 中计算中值。我在此处看到了解决方案。
但是，我不明白其中的一部分。在此处输入代码提供的解决方案如下：
SELECT x.val from data x, data y
GROUP BY x.val
HAVING SUM(SIGN(1-SIGN(y.val-x.val))) = (COUNT(*)+1)/2
在原始问题的上下文中，data x 和 data y 是什么？通常FROM后跟表名。但是，为什么问题只涉及一张表时却列出了两张表呢？有人可以解释这个解决方案是如何工作的吗？另外，我不明白这部分： HAVING SUM(SIGN(1-SIGN(y.val-x.val))) 。
I need to calculate the median value in MySQL. I saw the solution here.
But, I didn't understand part of it. The solution provided enter code here is as follows:
SELECT x.val from data x, data y
GROUP BY x.val
HAVING SUM(SIGN(1-SIGN(y.val-x.val))) = (COUNT(*)+1)/2
What is data x and data y in the context of the original question? Usually FROM is followed by the table name. But, then why are 2 tables listed when the question refers to only one? Can someone explain how this solution works? Also, I didn't understand this part: HAVING SUM(SIGN(1-SIGN(y.val-x.val))) .
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
在最初的问题中，
data x, data y将表与其自身连接起来，创建笛卡尔积。原始表有 7 行，通过将每一行与其他行连接起来，得到的结果是 49 行。本质上，该函数确定每个值有多少个值小于正在检查的值。然后将该总数与计数的一半 + 1 进行比较，然后选择该值作为中位数。
它通过从其比较的值 (
y.val) 中减去值 (x.val) 来实现此目的。然后，它使用SIGN函数将结果转换为-1、0或1。然后减去该值，然后再次获取SIGN。因此，如果y.val值小于与之比较的x.val值，则最终结果将为1。例如，假设y为3，x为5。如果
y为5，而x为3...最终结果将为0< /code>：对这些比较的结果进行求和，得到一个数字，该数字指示有多少值出现在我们正在检查的值之前。然后，它将这个
SUM与COUNT(*) + 1 / 2进行比较，以找到中间范围...In the original question,
data x, data yjoins the table to itself, creating a cartesian product. The original table had 7 rows, and by joining every row against every other row, the resulting product is 49 rows.Essentially, this function determines for every value how many values are less than the one being examined. It then compares this total to half the count + 1... and then selects that value as the median.
It does this by subtracting the value (
x.val) from the value it is comparing (y.val). It then uses theSIGNfunction to convert the result to-1,0, or1. It then subtracts this value, and then takes theSIGNagain. So if they.valvalue is less than thex.valvalue it that is being compared to, the end result would be a1. For example, let's sayyis3, andxis5.If
ywere5, andxwas3... the end result would be0:Summing the results of these comparisons gives us a number that indicates how many values come before the value that we're examining. It then compares this
SUMagainstCOUNT(*) + 1 / 2to find the middle range...