mysql 每月中值

发布于 2024-11-28 20:49:46 字数 1464 浏览 2 评论 0原文

我正在从数据库中进行一些查询来制作图表,可能需要的图表是每月一组值的中位数。我知道如何获取整组数据的中位数,但由于某种原因,我无法使用 GROUP BY MONTH(..) 来分隔月份,因此它返回每月的中位数。

这是我拥有的数据,我希望每个月都能获得每个月的中值,无论构建类型如何。

'Development', 1013.0164, 'June'
'Development', 1170.8999, 'July'
'Development', 671.2837, 'August'
'Flash Assets', 2961.3832, 'June'
'Flash Assets', 6662.2335, 'July'
'Flash Assets', 3902.5000, 'August'
'Release', 54.5499, 'June'
'Release', 62.4832, 'July'
'Release', 398.8500, 'August'
'Repackage', 1360.0834, 'June'
'Repackage', 6286.8505, 'July'
'Repackage', 1274.7833, 'August'
'Component', 16378.0161, 'June'
'Component', 6063.5482, 'July'
'Component', 23663.2496, 'August'
'Source Diff', 1503.8834, 'June'
'Source Diff', 1051.4500, 'July'
'Source Diff', 73.7002, 'August'

我想以此结束,

June, XXXX
July, XXXX
August, XXXX

谢谢。

编辑:当前查询

这是我现在用来获取总体中位数的查询,我不知道如何将其转换为每月获取它。

 SELECT t.Data AS 'Median' FROM
 (SELECT CEIL(COUNT(*)/2.0) as 'Middle', s.Data as 'Data' FROM
 (SELECT bt.name as 'Labels', 
 SUM(TIME_TO_SEC(TIMEDIFF(bs.eventtime, b.submittime))/60.0) 
 AS 'Data', MONTHNAME(b.submittime) FROM builds b 
 JOIN buildstatuses bs ON bs.buildid = b.id 
 JOIN buildtypes bt ON bt.id = b.buildtype 
 WHERE MONTH(b.submittime) BETWEEN MONTH(CURDATE())-2 AND MONTH(CURDATE()) 
 AND bs.status LIKE 'Started HANDLER' 
 GROUP BY b.buildtype, MONTH(b.submittime) ORDER BY 'Data' ) s )t;

I am doing some queries from a database to make graphs, and a graph that may be needed is the median of a set of values per month. I know how to get the median of the whole set of our data, but for some reason I can't get GROUP BY MONTH(..) to separate the months so it returns the median per month.

Here is the data that I have and I'd like for every month to get the median value for each month regardless of build type.

'Development', 1013.0164, 'June'
'Development', 1170.8999, 'July'
'Development', 671.2837, 'August'
'Flash Assets', 2961.3832, 'June'
'Flash Assets', 6662.2335, 'July'
'Flash Assets', 3902.5000, 'August'
'Release', 54.5499, 'June'
'Release', 62.4832, 'July'
'Release', 398.8500, 'August'
'Repackage', 1360.0834, 'June'
'Repackage', 6286.8505, 'July'
'Repackage', 1274.7833, 'August'
'Component', 16378.0161, 'June'
'Component', 6063.5482, 'July'
'Component', 23663.2496, 'August'
'Source Diff', 1503.8834, 'June'
'Source Diff', 1051.4500, 'July'
'Source Diff', 73.7002, 'August'

I'd like to end up with this,

June, XXXX
July, XXXX
August, XXXX

Thanks.

EDIT: Current Query

This is the query that I am using right now to get the overall median, I am not sure how to translate this to getting it per month.

 SELECT t.Data AS 'Median' FROM
 (SELECT CEIL(COUNT(*)/2.0) as 'Middle', s.Data as 'Data' FROM
 (SELECT bt.name as 'Labels', 
 SUM(TIME_TO_SEC(TIMEDIFF(bs.eventtime, b.submittime))/60.0) 
 AS 'Data', MONTHNAME(b.submittime) FROM builds b 
 JOIN buildstatuses bs ON bs.buildid = b.id 
 JOIN buildtypes bt ON bt.id = b.buildtype 
 WHERE MONTH(b.submittime) BETWEEN MONTH(CURDATE())-2 AND MONTH(CURDATE()) 
 AND bs.status LIKE 'Started HANDLER' 
 GROUP BY b.buildtype, MONTH(b.submittime) ORDER BY 'Data' ) s )t;

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

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

发布评论

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

评论(2

半葬歌 2024-12-05 20:49:46

如果您的值始终为浮点型...您可以尝试此操作 -

假设 Data 是值列

select mth,
substring_index
(
  substring_index
  (
    group_concat(Data order by Data), ',', 
    count(*)/2+1
  ), 
  ',', -1
)
from your_tables
group by mth;
  • group_concat 将以升序对月份的值进行排序,
  • 然后使用第一个substring_index 将从第一个值返回到中间值 +1 个位置,
  • 然后下一个 substring_index 将帮助您获得最正确的值(即中位数)

if your value is always in float ... you can try this --

assume Data is the value column

select mth,
substring_index
(
  substring_index
  (
    group_concat(Data order by Data), ',', 
    count(*)/2+1
  ), 
  ',', -1
)
from your_tables
group by mth;
  • group_concat will sort the values for month in ascending order
  • then use of first substring_index will return from first value to middle value +1 more position
  • then next substring_index will help you to ge tthe right most value (which is the median)
何以心动 2024-12-05 20:49:46

人们在mysql的聚合函数讨论页面结束与临时表。在那里搜索“中位数”。

Peolple at the mysql's aggregate functions discussion page ends up with temporary tables. Search for "median" there.

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