MySQL - 最后一天插入的平均百分比值

Question

我在 MySQL 中有一个表:

+-----------------+-------------+------+-----+---------------------+-------+
| Field           | Type        | Null | Key | Default             | Extra |
+-----------------+-------------+------+-----+---------------------+-------+
| period_duration | datetime    | NO   | PRI | 0000-00-00 00:00:00 |       |
| duration        | varchar(6)  | YES  |     | NULL                |       |
| sample          | varchar(2)  | YES  |     | NULL                |       |
| corner          | varchar(10) | YES  |     | NULL                |       |
| country         | varchar(60) | NO   | PRI |                     |       |
| roaming_partner | varchar(60) | NO   | PRI |                     |       |
| pdp_in_total    | int(8)      | YES  |     | NULL                |       |
| pdp_in_ok       | int(8)      | YES  |     | NULL                |       |
| pdp_in_not_ok   | int(8)      | YES  |     | NULL                |       |
| pdp_in_ok_rate  | int(8)      | YES  |     | NULL                |       |
+-----------------+-------------+------+-----+---------------------+-------+

这个表每小时都​​有新的插入，这使得每个 roaming_partner 有许多不同的 period_duration:

+---------------------+----------+--------+----------+---------+-----------------------+--------------+-----------+---------------+----------------+
| period_duration     | duration | sample | corner   | country | roaming_partner       | pdp_in_total | pdp_in_ok | pdp_in_not_ok | pdp_in_ok_rate |
+---------------------+----------+--------+----------+---------+-----------------------+--------------+-----------+---------------+----------------+
| 2014-12-16 14:00:00 | 3600     | 1      | GPRS_OUT | USA     | Operator1             |          796 |       787 |             9 |             99 |
| 2014-12-16 15:00:00 | 3600     | 1      | GPRS_OUT | USA     | Operator1             |         1748 |      1706 |            42 |             98 |
| 2014-12-16 16:00:00 | 3600     | 1      | GPRS_OUT | USA     | Operator1             |            7 |         7 |             0 |            100 |

“ok_rate”是百分比。

我需要创建一个 SELECT 来显示每个 country、roaming_partner 和 pdp_in_ok_rate 24 次插入，这些插入的平均 pdp_in_ok_rate%。

就像我希望我的 SQL 查询说:“这是你表中每个运算符(operator)在最后 24 次插入中的平均 pdp_in_ok_rate。不是在整个表中，而是在最后这些中。”

有人可以帮帮我吗？

Answer 1

这是否符合您的要求？

select country, roaming_partner, avg(pd_in_ok_rate) as avgrate
from table t
where period_duration >= date_sub(now(), interval -1 day)
group by country, roaming_partner;

这不会为您提供平均细节。如果您还需要详细信息，可以将其用作子查询

select t.*, cr.avgrate
from table t join
     (select country, roaming_partner, avg(pd_in_ok_rate) as avgrate
      from table t
      where period_duration >= date_sub(now(), interval -1 day)
      group by country, roaming_partner
     ) cr
     on t.country = cr.country and t.roaming_partner = cr.roaming_partner
where period_duration >= date_sub(now(), interval -1 day);