mysql：如何计算按天/小时分组的分钟行

6ovsh4lw 于 2021-06-23 发布在 Mysql

关注(0)|答案(1)|浏览(436)

我的 select 效果很好，但每小时的计数相同：
sqlfiddle:http://sqlfiddle.com/#!9/bae01d/32年

Player  | Day | Hour | Minutes | % online
player1 |  27 |    0 |      60 |      100
player1 |  27 |    1 |      60 |      100
player1 |  27 |    2 |      60 |      100
player1 |  27 |    3 |      60 |      100
player1 |  27 |    4 |      60 |      100
...

活动表只有在线玩家的逐分钟条目，离线玩家将丢失。这就是连接的所在，存根表包含24行，持续0-23小时。
如何重写此查询，使玩家在线时每小时的总分钟数是正确的总分钟数？

select 
    player, 
    day(datetime) as Day,
    stub.hour,
    hour(datetime) as Hour, 
    count(player) as minutes,
    ((count(player) / 60) * 100) as percent_of_hr
 FROM stub_hours as stub
 LEFT JOIN activity
  on Hour=stub.hour
    GROUP BY date_format( datetime, '%Y%m%d%h' ), Day, Hour, player
    ORDER BY player, datetime desc, Day, hour, minutes desc;

mysql DateTime

来源：https://stackoverflow.com/questions/50066907/mysql-how-to-count-minute-rows-grouped-by-day-hour

1条答案

按热度按时间

qacovj5a1#

我相信这应该是你需要的：

SELECT
  sh.player,
  sh.day,
  sh.hour,
  act.count_minutes minutes,
  ((act.count_minutes / 60) * 100) AS percent_of_hr
FROM (SELECT
  player,
  MONTH(datetime) amonth,
  DAY(datetime) aday,
  HOUR(datetime) ahour,
  COUNT(*) count_minutes
FROM activity
GROUP BY player,
         MONTH(datetime),
         DAY(datetime),
         HOUR(datetime)) act
RIGHT JOIN (SELECT DISTINCT
  a.player,
  s.hour,
  DAY(a.datetime) day,
  MONTH(a.datetime) month
FROM activity a
CROSS JOIN stub_hours s) sh
  ON act.ahour = sh.hour
  AND act.player = sh.player
  AND act.aday = sh.day
  AND act.amonth = sh.month
ORDER BY sh.player, sh.day, sh.hour;

http://sqlfiddle.com/#!9/84fc1d/70年
如果需要不同的输出，请告诉我。

赞(0）回复(0）举报 2021-06-23

我来回答

mysql：如何计算按天/小时分组的分钟行

1条答案

相关问题

热门标签

最新问答