取每天的最大值并总结mysql

I have a table with 3 columns (server varchar(20), transactions int(20), timestamp timestamp(0)). There are multiple servers that log the transactions they get hit with over the course of a day. I want to write a query that sums the max transaction (per day) over the course of a multiple months and display a column for each month.

I believe I am close with the following broken query, but the problem is that it takes the max value of the whole month rather than getting the sum of the max of each day in the month.

SELECT IFNULL(server, 'TOTAL') AS Server,
       FORMAT(SUM(MAX(CASE WHEN DATE(TIMESTAMP) >= DATE('2014-10-01')
                  AND DATE(TIMESTAMP) < DATE('2014-11-01') THEN transactions ELSE 0 END),0)) AS 'Oct - 2014',
       FORMAT(SUM(MAX(CASE WHEN DATE(TIMESTAMP) >= DATE('2014-11-01')
                  AND DATE(TIMESTAMP) < DATE('2014-12-01') THEN transactions ELSE 0 END),0)) AS 'Nov - 2014',
       FORMAT(SUM(MAX(CASE WHEN DATE(TIMESTAMP) >= DATE('2014-12-01')
                  AND DATE(TIMESTAMP) < DATE('2015-01-01') THEN transactions ELSE 0 END),0)) AS 'Dec - 2014',
       '' AS 'Total'
FROM transactionstable
GROUP BY Server WITH ROLLUP;

1个回答

You want nested group bys, first by date and server to get the maximum and then by server to get the sum(). A sketch of the query is:

select server, sum(maxt)
from (select date(timestamp) as d, server, max(transactions) as maxt
      from transactiontable tt
      where date(timestamp) between @TIMESTAMP1 and @TIMESTAMP2
      group by date(timestamp), server
     ) t
group by server;

EDIT:

Just do a conditional aggregation in the outer query:

select server, sum(maxt),
       sum(case when year(timestamp) = 2014 and month(timestamp) = 11 then maxt end) as sum_201411,
       sum(case when year(timestamp) = 2014 and month(timestamp) = 12 then maxt end) as sum_201412,
       sum(case when year(timestamp) = 2015 and month(timestamp) = 1 then maxt end) as sum_201501
from (select date(timestamp) as d, server, max(transactions) as maxt
      from transactiontable tt
      where date(timestamp) between @TIMESTAMP1 and @TIMESTAMP2
      group by date(timestamp), server
     ) t
group by server;

展开翻译

译文



您想要嵌套的group by,首先按日期和服务器获取最大值,然后由服务器获取 sum() </代码>。 查询的草图是:</ p>

 选择服务器,sum(maxt)
from(选择日期(时间戳)为d,服务器,最大(事务)为maxt
来自 transactiontable tt
其中@ TIMESTAMP1和@ TIMESTAMP2之间的日期(时间戳)
按日期分组(时间戳),服务器
)t
group by server;
</ code> </ pre>

编辑:</ p>

只需在外部查询中进行条件聚合:</ p>

 选择服务器,sum(maxt),
sum( 当年份(时间戳)= 2014年和月份(时间戳)= 11然后maxt结束时为sum_201411,
总和(年份(时间戳)= 2014年和月份(时间戳)= 12然后maxt结束时的情况)为sum_201412,
sum(年份(时间戳)= 2015和月(时间戳)= 1然后maxt结束时的情况)作为sum_201501
from(选择日期(时间戳)为d,服务器,最大(交易)为maxt
来自transactiontable tt
其中 日期(时间戳)@ TIMESTAMP1和@ TIMESTAMP2
按日期分组(时间戳),服务器
)t
group by server;
</ code> </ pre>
</ div >

douliu8327
douliu8327 这有效! 我对能够在一个月内获得每天最大值并为每台服务器总结它持怀疑态度。 非常感谢你的时间。
大约 5 年之前 回复
dongmu3457
dongmu3457 。 。 。 如果需要,它可以很容易地适应多列。
大约 5 年之前 回复
doumaji6215
doumaji6215 感谢您及时的回复。 我希望每个月都有一个单独的列来显示过去3个月的总和(最大值)。 你的答案只会显示一个特定范围的总和(最大值)吗?
大约 5 年之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问