从mysql表中获取最近30天的数据，并按日期对其进行分组 - Fetching last 30 days data from mysql table and grouping them by date

Fetching last 30 days data from mysql table and grouping them by date

以下是我的表看起来像的样子

| total_hit   |success_hit  | date_time             |
|      12     |          12 | 01-11-2009 07:32:44   | 
|      12     |          11 | 01-11-2009 08:33:49   | 
|      12     |          10 | 01-11-2009 09:08:24   | 
|      12     |          11 | 01-12-2009 10:33:57   | 
|      12     |          12 | 01-12-2009 11:37:34   | 
|      12     |          11 | 01-12-2009 12:23:49   |

我使用以下查询从表中获取过去30天的数据：

SELECT *
FROM my_table
WHERE DATE(date_time) >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

我想做的是根据日期对返回的数据进行分组，并计算成功率(success_hit/total_hit)*100类似的东西

Date: 01-11-2009 Percentage: x
Date: 01-12-2009 Percentage: y

有什么想法吗？

select (success/total)*100 as perc_success, Date(date_time) from 
       (select sum(success_hit) as success, sum(total_hit) as total, 
            date_time from my_table
            where DATE(date_time) >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) 
            group by Date(date_time)  ) 
tbl

这应该有效！！！

试试这个

SELECT (sum(success_hit)/sum(total_hit)*100) AS `Percentage`,
       DATE(date_time) AS `Date`
FROM my_table
WHERE DATE(date_time) >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
GROUP BY DATE(date_time);