rownum sql值获取一个值_sql - 用MySQL计算中值的简单方法

最新推荐文章于 2021-04-03 03:48:45 发布

陵小宇

最新推荐文章于 2021-04-03 03:48:45 发布

阅读量251

点赞数

文章标签： rownum sql值获取一个值

本文链接：https://blog.csdn.net/weixin_35318150/article/details/111925204

版权

sql - 用MySQL计算中值的简单方法

使用MySQL计算中值的最简单(并且希望不是太慢)的方法是什么？我用val来查找平均值，但我很难找到一种计算中位数的简单方法。现在，我将所有行返回给PHP，进行排序，然后选择中间行，但肯定必须有一些简单的方法在单个MySQL查询中执行此操作。

示例数据：

id | val

--------

1 4

2 7

3 2

4 2

5 9

6 8

7 3

排序val给出2 2 3 4 7 8 9，因此中位数应为4，而SELECT AVG(val) = 5。

30个解决方案

197 votes

在MariaDB / MySQL中：

SELECT AVG(dd.val) as median_val

FROM (

SELECT d.val, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum

FROM data d, (SELECT @rownum:=0) r

WHERE d.val is NOT NULL

-- put some where clause here

ORDER BY d.val

) as dd

WHERE dd.row_number IN ( FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2) );

Steve Cohen指出，在第一次传递后，@ runum将包含总行数。这可用于确定中位数，因此不需要第二次通过或连接。

当存在偶数个记录时，AVG(dd.val)和dd.row_number IN(...)用于正确地产生中值。推理：

SELECT FLOOR((3+1)/2),FLOOR((3+2)/2); -- when total_rows is 3, avg rows 2 and 2

SELECT FLOOR((4+1)/2),FLOOR((4+2)/2); -- when total_rows is 4, avg rows 2 and 3

最后，MariaDB 10.3.3+包含一个MEDIAN函数

velcrow answered 2019-04-09T04:07:45Z

53 votes

我刚刚在评论中在网上找到了另一个答案：

对于几乎所有SQL中的中位数：

median_row = floor(count / 2)

确保列的索引编制良好，索引用于过滤和排序。验证解释计划。

select count(*) from table --find the number of rows

计算“中位数”行数。也许使用：median_row = floor(count / 2)。

然后从列表中选择它：

select val from table order by val asc limit median_row,1

这应该只返回您想要的值的一行。

雅各

TheJacobTaylor answered 2019-04-09T04:09:40Z

27 votes

我发现接受的解决方案不适用于我的MySQL安装，返回一个空集，但这个查询在我测试的所有情况下都适用于我：

SELECT x.val from data x, data y

GROUP BY x.val

HAVING SUM(SIGN(1-SIGN(y.val-x.val)))/COUNT(*) > .5

LIMIT 1

zookatron answered 2019-04-09T04:10:24Z

14 votes

不幸的是，TheJacobTaylor和velcro的答案都没有为当前版本的MySQL返回准确的结果。

Velcro从上面得到的答案很接近，但是对于具有偶数行的结果集，它没有正确计算。中位数被定义为1)奇数集上的中间数，或2)偶数集上的两个中间数的平均值。

所以，这里的velcro解决方案修补了处理奇数和偶数集：

SELECT AVG(middle_values) AS 'median' FROM (

SELECT t1.median_column AS 'middle_values' FROM

(

SELECT @row:=@row+1 as `row`, x.median_column

FROM median_table AS x, (SELECT @row:=0) AS r

WHERE 1

-- put some where clause here

ORDER BY x.median_column

) AS t1,

(

SELECT COUNT(*) as 'count'

FROM median_table x

WHERE 1

-- put same where clause here

) AS t2

-- the following condition will return 1 record for odd number sets, or 2 records for even number sets.

WHERE t1.row >= t2.count/2 and t1.row <= ((t2.count/2) +1)) AS t3;

要使用它，请按照以下3个简单步骤操作：

将上述代码中的“median_table”(2次出现)替换为您的表的名称

将“median_column”(3次出现)替换为您要查找中位数的列名称

如果您有WHERE条件，请将“WHERE 1”(2次出现)替换为where条件

bob answered 2019-04-09T04:12:08Z

9 votes

我提出了一个更快的方法。

获取行数：

SELECT max(val) FROM (SELECT val FROM data ORDER BY val limit @middlevalue) x;

然后在排序的子查询中取中间值：

SELECT max(val) FROM (SELECT val FROM data ORDER BY val limit @middlevalue) x;

我使用随机数的5x10e6数据集对此进行了测试，它将在10秒内找到中位数。

Reggie Edwards answered 2019-04-09T04:13:21Z

7 votes

MySQL文档中对此页面的评论有以下建议：

-- (mostly) High Performance scaling MEDIAN function per group

-- Median defined in http://en.wikipedia.org/wiki/Median

-- by Peter Hlavac

-- 06.11.2008

-- Example Table:

DROP table if exists table_median;

CREATE TABLE table_median (id INTEGER(11),val INTEGER(11));

COMMIT;

INSERT INTO table_median (id, val) VALUES

(1, 7), (1, 4), (1, 5), (1, 1), (1, 8), (1, 3), (1, 6),

(2, 4),

(3, 5), (3, 2),

(4, 5), (4, 12), (4, 1), (4, 7);

-- Calculating the MEDIAN

SELECT @a := 0;

SELECT

id,

AVG(val) AS MEDIAN

FROM (

SELECT

id,

val

FROM (

SELECT

-- Create an index n for every id

@a := (@a + 1) mod o.c AS shifted_n,

IF(@a mod o.c=0, o.c, @a) AS n,

o.id,

o.val,

-- the number of elements for every id

o.c

FROM (

SELECT

t_o.id,

val,

FROM

table_median t_o INNER JOIN

(SELECT

id,

COUNT(1) AS c

FROM

table_median

GROUP BY

) t2

ON (t2.id = t_o.id)

ORDER BY

t_o.id,val

) o

) a

WHERE

IF(

-- if there is an even number of elements

-- take the lower and the upper median

-- and use AVG(lower,upper)

c MOD 2 = 0,

n = c DIV 2 OR n = (c DIV 2)+1,

-- if its an odd number of elements

-- take the first if its only one element

-- or take the one in the middle

IF(

c = 1,

n = 1,

n = c DIV 2 + 1

)

) a

GROUP BY

id;

-- Explanation:

-- The Statement creates a helper table like

-- n id val count

-- ----------------

-- 1, 1, 1, 7

-- 2, 1, 3, 7

-- 3, 1, 4, 7

-- 4, 1, 5, 7

-- 5, 1, 6, 7

-- 6, 1, 7, 7

-- 7, 1, 8, 7

-- 1, 2, 4, 1

-- 1, 3, 2, 2

-- 2, 3, 5, 2

-- 1, 4, 1, 4

-- 2, 4, 5, 4

-- 3, 4, 7, 4

-- 4, 4, 12, 4

-- from there we can select the n-th element on the position: count div 2 + 1

Sebastian Paaske Tørholm answered 2019-04-09T04:13:49Z

4 votes

建立魔术贴的答案，对于那些你必须做一个由另一个参数分组的东西的中位数：

row_number， @s：= IF(@s = grp_field，@ s，grp_field)AS sec，d.val FROM data d，(SELECT @rownum：= 0，@ s：= 0)r ORDER BY grp_field，d.val)作为t1 JOIN( SELECT grp_field，count(*)as total_rows 来自数据d GROUP BY grp_field)作为t2ON t1.grp_field = t2.grp_field在哪里t1.row_number = floor(total_rows / 2)+1;

Doug answered 2019-04-09T04:14:30Z

4 votes

上面的大多数解决方案仅适用于表中的一个字段，您可能需要获得查询中许多字段的中位数(第50个百分位数)。

我用这个：

SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(

GROUP_CONCAT(field_name ORDER BY field_name SEPARATOR ','),

',', 50/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS `Median`

FROM table_name;

您可以将上面示例中的“50”替换为任何百分位数，效率非常高。

只要确保你有足够的内存用于GROUP_CONCAT，你可以改为：

SET group_concat_max_len = 10485760; #10MB max length

更多细节：[http://web.performancerasta.com/metrics-tips-calculating-95th-99th-or-any-percentile-with-single-mysql-query/]

Nico answered 2019-04-09T04:15:25Z

4 votes

我在HackerRank上找到了以下代码，它非常简单，适用于每种情况。

SELECT M.MEDIAN_COL FROM MEDIAN_TABLE M WHERE

(SELECT COUNT(MEDIAN_COL) FROM MEDIAN_TABLE WHERE MEDIAN_COL < M.MEDIAN_COL ) =

(SELECT COUNT(MEDIAN_COL) FROM MEDIAN_TABLE WHERE MEDIAN_COL > M.MEDIAN_COL );

Prashant Srivastav answered 2019-04-09T04:15:53Z

3 votes

您可以使用此处找到的用户定义函数。

Alex Martelli answered 2019-04-09T04:16:20Z

3 votes

关注奇数值计数 - 在这种情况下给出中间两个值的平均值。

SELECT AVG(val) FROM

( SELECT x.id, x.val from data x, data y

GROUP BY x.id, x.val

HAVING SUM(SIGN(1-SIGN(IF(y.val-x.val=0 AND x.id != y.id, SIGN(x.id-y.id), y.val-x.val)))) IN (ROUND((COUNT(*))/2), ROUND((COUNT(*)+1)/2))

) sq

Franz K. answered 2019-04-09T04:16:47Z

2 votes

我的代码，没有表或其他变量有效：

SELECT

((SUBSTRING_INDEX(SUBSTRING_INDEX(group_concat(val order by val), ',', floor(1+((count(val)-1) / 2))), ',', -1))

(SUBSTRING_INDEX(SUBSTRING_INDEX(group_concat(val order by val), ',', ceiling(1+((count(val)-1) / 2))), ',', -1)))/2

as median

FROM table;

Oscar Canon answered 2019-04-09T04:17:14Z

2 votes

或者，您也可以在存储过程中执行此操作：

DROP PROCEDURE IF EXISTS median;

DELIMITER //

CREATE PROCEDURE median (table_name VARCHAR(255), column_name VARCHAR(255), where_clause VARCHAR(255))

BEGIN

-- Set default parameters

IF where_clause IS NULL OR where_clause = '' THEN

SET where_clause = 1;

END IF;

-- Prepare statement

SET @sql = CONCAT(

"SELECT AVG(middle_values) AS 'median' FROM (

SELECT t1.", column_name, " AS 'middle_values' FROM

(

SELECT @row:=@row+1 as `row`, x.", column_name, "

FROM ", table_name," AS x, (SELECT @row:=0) AS r

WHERE ", where_clause, " ORDER BY x.", column_name, "

) AS t1,

(

SELECT COUNT(*) as 'count'

FROM ", table_name, " x

WHERE ", where_clause, "

) AS t2

-- the following condition will return 1 record for odd number sets, or 2 records for even number sets.

WHERE t1.row >= t2.count/2

AND t1.row <= ((t2.count/2)+1)) AS t3

");

-- Execute statement

PREPARE stmt FROM @sql;

EXECUTE stmt;

END//

DELIMITER ;

-- Sample usage:

-- median(table_name, column_name, where_condition);

CALL median('products', 'price', NULL);

bob answered 2019-04-09T04:17:42Z

2 votes

SELECT

SUBSTRING_INDEX(

GROUP_CONCAT(field ORDER BY field),

',',

((

ROUND(

LENGTH(GROUP_CONCAT(field)) -

LENGTH(

REPLACE(

GROUP_CONCAT(field),

',',

)

) / 2) + 1

)),

',',

-1

)

FROM

table

以上似乎对我有用。

Nochum Sossonko answered 2019-04-09T04:18:09Z

1 votes

我使用了两种查询方法：

第一个得到计数，分钟，最大值和平均值

第二个(准备好的声明)带有“LIMIT @ count / 2,1”和“ORDER BY ..”子句以获得中值

它们包含在函数defn中，因此可以从一次调用返回所有值。

如果您的范围是静态的并且您的数据不经常更改，则预先计算/存储这些值并使用存储的值而不是每次从头开始查询可能更有效。

btk answered 2019-04-09T04:19:08Z

1 votes

因为我只需要一个中位数和百分位数的解决方案，我根据这个帖子中的发现做了一个简单而且非常灵活的功能。我知道如果我找到易于包含在我项目中的“现成”功能，我会感到很开心，所以我决定快速分享：

function mysql_percentile($table, $column, $where, $percentile = 0.5) {

$sql = "

SELECT `t1`.`".$column."` as `percentile` FROM (

SELECT @rownum:=@rownum+1 as `row_number`, `d`.`".$column."`

FROM `".$table."` `d`, (SELECT @rownum:=0) `r`

".$where."

ORDER BY `d`.`".$column."`

) as `t1`,

(

SELECT count(*) as `total_rows`

FROM `".$table."` `d`

".$where."

) as `t2`

WHERE 1

AND `t1`.`row_number`=floor(`total_rows` * ".$percentile.")+1;

$result = sql($sql, 1);

if (!empty($result)) {

return $result['percentile'];

} else {

return 0;

}

用法非常简单，例如我当前的项目：

...

$table = DBPRE."zip_".$slug;

$column = 'seconds';

$where = "WHERE `reached` = '1' AND `time` >= '".$start_time."'";

$reaching['median'] = mysql_percentile($table, $column, $where, 0.5);

$reaching['percentile25'] = mysql_percentile($table, $column, $where, 0.25);

$reaching['percentile75'] = mysql_percentile($table, $column, $where, 0.75);

...

bezoo answered 2019-04-09T04:19:44Z

1 votes

这是我的方式。当然，你可以把它放到一个程序中:-)

SET @median_counter = (SELECT FLOOR(COUNT(*)/2) - 1 AS `median_counter` FROM `data`);

SET @median = CONCAT('SELECT `val` FROM `data` ORDER BY `val` LIMIT ', @median_counter, ', 1');

PREPARE median FROM @median;

EXECUTE median;

你可以避免变量@median_counter，如果你替换它：

SET @median = CONCAT( 'SELECT `val` FROM `data` ORDER BY `val` LIMIT ',

(SELECT FLOOR(COUNT(*)/2) - 1 AS `median_counter` FROM `data`),

', 1'

);

PREPARE median FROM @median;

EXECUTE median;

pucawo answered 2019-04-09T04:20:19Z

1 votes

我在下面介绍的解决方案仅在一个查询中工作，无需创建表，变量甚至子查询。此外，它允许您在分组查询中获得每个组的中位数(这是我需要的！)：

SELECT `columnA`,

SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(`columnB` ORDER BY `columnB`), ',', CEILING((COUNT(`columnB`)/2))), ',', -1) medianOfColumnB

FROM `tableC`

-- some where clause if you want

GROUP BY `columnA`;

它的工作原理是智能使用group_concat和substring_index。

但是，要允许大的group_concat，您必须将group_concat_max_len设置为更高的值(默认情况下为1024个char)。您可以像这样设置它(对于当前的sql会话)：

SET SESSION group_concat_max_len = 10000;

-- up to 4294967295 in 32-bits platform.

group_concat_max_len的更多信息：[https://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_group_concat_max_len]

didier2l answered 2019-04-09T04:21:08Z

1 votes

Velcrow回答的另一个重复，但使用单个中间表并利用用于行编号的变量来获取计数，而不是执行额外的查询来计算它。同时开始计数，以便第一行是第0行，以允许简单地使用Floor和Ceil来选择中间行。

SELECT Avg(tmp.val) as median_val

FROM (SELECT inTab.val, @rows := @rows + 1 as rowNum

FROM data as inTab, (SELECT @rows := -1) as init

-- Replace with better where clause or delete

WHERE 2 > 1

ORDER BY inTab.val) as tmp

WHERE tmp.rowNum in (Floor(@rows / 2), Ceil(@rows / 2));

Steve Cohen answered 2019-04-09T04:21:37Z

1 votes

安装并使用这个mysql统计函数：[http://www.xarg.org/2012/07/statistical-functions-in-mysql/]

之后，计算中位数很容易：

SELECT median(x)FROM t1

Leonardo Nicolas answered 2019-04-09T04:22:18Z

1 votes

这种方式似乎包括偶数和奇数，没有子查询。

SELECT AVG(t1.x)

FROM table t1, table t2

GROUP BY t1.x

HAVING SUM(SIGN(t1.x - t2.x)) = 0

yuhanluo answered 2019-04-09T04:22:46Z

1 votes

通常，我们可能需要不仅针对整个表计算Median，而且针对我们的ID计算聚合。换句话说，计算表中每个ID的中位数，其中每个ID都有许多记录。 (良好的性能和适用于许多SQL +修复偶数和赔率的问题，更多关于不同中位数方法的性能[https://sqlperformance.com/2012/08/t-sql-queries/median])

SELECT our_id, AVG(1.0 * our_val) as Median

FROM

( SELECT our_id, our_val,

COUNT(*) OVER (PARTITION BY our_id) AS cnt,

ROW_NUMBER() OVER (PARTITION BY our_id ORDER BY our_val) AS rn

FROM our_table

) AS x

WHERE rn IN ((cnt + 1)/2, (cnt + 2)/2) GROUP BY our_id;

希望能帮助到你

Danylo Zherebetskyy answered 2019-04-09T04:23:16Z

0 votes

如果MySQL有ROW_NUMBER，则MEDIAN(受此SQL Server查询启发)：

WITH Numbered AS

(

SELECT *, COUNT(*) OVER () AS Cnt,

ROW_NUMBER() OVER (ORDER BY val) AS RowNum

FROM yourtable

)

SELECT id, val

FROM Numbered

WHERE RowNum IN ((Cnt+1)/2, (Cnt+2)/2)

;

如果您有偶数条目，则使用IN。

如果你想找到每组的中位数，那么你的OVER子句中只有PARTITION BY组。

抢

Rob Farley answered 2019-04-09T04:24:01Z

0 votes

在阅读了之前的所有内容后，他们与我的实际要求不符，所以我实现了自己的一个不需要任何程序或复杂的语句，只是我GROUP_CONCAT我希望获得MEDIAN并应用COUNT DIV的列中的所有值 BY 2我从列表中间提取值，如下面的查询所示：

(POS是我想要获得其中位数的列的名称)

(query) SELECT

SUBSTRING_INDEX (

GROUP_CONCAT(pos ORDER BY CAST(pos AS SIGNED INTEGER) desc SEPARATOR ';')

, ';', COUNT(*)/2 )

, ';', -1 ) AS `pos_med`

FROM table_name

GROUP BY any_criterial

我希望这可能对某些人有用，就像本网站上的许多其他评论一样。

ggarri answered 2019-04-09T04:24:44Z

0 votes

知道确切的行数，您可以使用此查询：

SELECT AS VAL FROM

= ceiling( / 2.0) - 1

ZhekaKozlov answered 2019-04-09T04:25:19Z

0 votes

我有一个包含大约10亿行的数据库，我们需要这些行来确定集合中的中位数年龄。排序十亿行很难，但是如果你聚合可以找到的不同值(年龄范围从0到100)，你可以对这个列表进行排序，并使用一些算术魔法来找到你想要的任何百分位，如下所示：

with rawData(count_value) as

(

select p.YEAR_OF_BIRTH

from dbo.PERSON p

overallStats (avg_value, stdev_value, min_value, max_value, total) as

(

select avg(1.0 * count_value) as avg_value,

stdev(count_value) as stdev_value,

min(count_value) as min_value,

max(count_value) as max_value,

count(*) as total

from rawData

aggData (count_value, total, accumulated) as

(

select count_value,

count(*) as total,

SUM(count(*)) OVER (ORDER BY count_value ROWS UNBOUNDED PRECEDING) as accumulated

FROM rawData

group by count_value

)

select o.total as count_value,

o.min_value,

o.max_value,

o.avg_value,

o.stdev_value,

MIN(case when d.accumulated >= .50 * o.total then count_value else o.max_value end) as median_value,

MIN(case when d.accumulated >= .10 * o.total then count_value else o.max_value end) as p10_value,

MIN(case when d.accumulated >= .25 * o.total then count_value else o.max_value end) as p25_value,

MIN(case when d.accumulated >= .75 * o.total then count_value else o.max_value end) as p75_value,

MIN(case when d.accumulated >= .90 * o.total then count_value else o.max_value end) as p90_value

from aggData d

cross apply overallStats o

GROUP BY o.total, o.min_value, o.max_value, o.avg_value, o.stdev_value

;

此查询取决于您的数据库支持窗口函数(包括ROWS UNBOUNDED PRECEDING)，但如果您没有这个，将aggData CTE与其自身连接并将所有先前总计聚合到“累积”列中用于确定 value包含指定的优先级。上述样品计算p10，p25，p50(中位数)，p75和p90。

-克里斯

Chris Knoll answered 2019-04-09T04:26:03Z

0 votes

取自：[http://mdb-blog.blogspot.com/2015/06/mysql-find-median-nth-element-without.html]

我会建议另一种方式，没有加入，但是使用字符串

我没有用大数据表检查它，但是小/中等表它运行得很好。

这里的好处是，它也可以通过GROUPING工作，因此它可以返回几个项目的中位数。

这是测试表的测试代码：

DROP TABLE test.test_median

CREATE TABLE test.test_median AS

SELECT 'book' AS grp, 4 AS val UNION ALL

SELECT 'book', 7 UNION ALL

SELECT 'book', 2 UNION ALL

SELECT 'book', 9 UNION ALL

SELECT 'book', 8 UNION ALL

SELECT 'book', 3 UNION ALL

SELECT 'note', 11 UNION ALL

SELECT 'bike', 22 UNION ALL

SELECT 'bike', 26

以及查找每个组的中位数的代码：

SELECT grp,

SUBSTRING_INDEX( SUBSTRING_INDEX( GROUP_CONCAT(val ORDER BY val), ',', COUNT(*)/2 ), ',', -1) as the_median,

GROUP_CONCAT(val ORDER BY val) as all_vals_for_debug

FROM test.test_median

GROUP BY grp

输出：

grp | the_median| all_vals_for_debug

bike| 22 | 22,26

book| 4 | 2,2,3,4,7,8,9

note| 11 | 11

mr.baby123 answered 2019-04-09T04:27:12Z

0 votes

在某些情况下，中位数计算如下：

当按值排序时，“中位数”是数字列表中的“中间”值。对于偶数计数集，中位数是两个中间值的平均值。我为此创建了一个简单的代码：

$midValue = 0;

$rowCount = "SELECT count(*) as count {$from} {$where}";

$even = FALSE;

$offset = 1;

$medianRow = floor($rowCount / 2);

if ($rowCount % 2 == 0 && !empty($medianRow)) {

$even = TRUE;

$offset++;

$medianRow--;

}

$medianValue = "SELECT column as median

{$fromClause} {$whereClause}

ORDER BY median

LIMIT {$medianRow},{$offset}";

$medianValDAO = db_query($medianValue);

while ($medianValDAO->fetch()) {

if ($even) {

$midValue = $midValue + $medianValDAO->median;

}

else {

$median = $medianValDAO->median;

}

if ($even) {

$median = $midValue / 2;

}

return $median;

返回的$ median将是必需的结果:-)

jitendrapurohit answered 2019-04-09T04:27:57Z

0 votes

按维度分组的中位数：

SELECT your_dimension, avg(t1.val) as median_val FROM (

SELECT @rownum:=@rownum+1 AS `row_number`,

IF(@dim <> d.your_dimension, @rownum := 0, NULL),

@dim := d.your_dimension AS your_dimension,

d.val

FROM data d, (SELECT @rownum:=0) r, (SELECT @dim := 'something_unreal') d

WHERE 1

-- put some where clause here

ORDER BY d.your_dimension, d.val

) as t1

INNER JOIN

(

SELECT d.your_dimension,

count(*) as total_rows

FROM data d

WHERE 1

-- put same where clause here

GROUP BY d.your_dimension

) as t2 USING(your_dimension)

WHERE 1

AND t1.row_number in ( floor((total_rows+1)/2), floor((total_rows+2)/2) )

GROUP BY your_dimension;

Vladimir_M answered 2019-04-09T04:28:25Z

0 votes

根据@ bob的答案，这可以概括查询，使其能够返回多个中位数，并按某些条件分组。

想一想，例如，汽车中二手车的中位销售价格，按年份分组。

SELECT

period,

AVG(middle_values) AS 'median'

FROM (

SELECT t1.sale_price AS 'middle_values', t1.row_num, t1.period, t2.count

FROM (

SELECT

@last_period:=@period AS 'last_period',

@period:=DATE_FORMAT(sale_date, '%Y-%m') AS 'period',

IF (@period<>@last_period, @row:=1, @row:=@row+1) as `row_num`,

x.sale_price

FROM listings AS x, (SELECT @row:=0) AS r

WHERE 1

-- where criteria goes here

ORDER BY DATE_FORMAT(sale_date, '%Y%m'), x.sale_price

) AS t1

LEFT JOIN (

SELECT COUNT(*) as 'count', DATE_FORMAT(sale_date, '%Y-%m') AS 'period'

FROM listings x

WHERE 1

-- same where criteria goes here

GROUP BY DATE_FORMAT(sale_date, '%Y%m')

) AS t2

ON t1.period = t2.period

) AS t3

WHERE

row_num >= (count/2)

AND row_num <= ((count/2) + 1)

GROUP BY t3.period

ORDER BY t3.period;

Ariel Allon answered 2019-04-09T04:29:07Z

陵小宇

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
rownum sql值获取一个值_sql - 用MySQL计算中值的简单方法

sql - 用MySQL计算中值的简单方法使用MySQL计算中值的最简单(并且希望不是太慢)的方法是什么？我用val来查找平均值，但我很难找到一种计算中位数的简单方法。现在，我将所有行返回给PHP，进行排序，然后选择中间行，但肯定必须有一些简单的方法在单个MySQL查询中执行此操作。示例数据：id | val--------1 42 73 24 25 96 ...
复制链接

扫一扫