如何在MySQL中按时间帧8 8hr/11 8hr/14 8hr/17 8hr/20 8hr分组时按布尔值进行分组

Question

我需要分组2表(Key = analysis_id <=> id)，其中我的输出应该显示最后一个星期或月份或年份，按时间框架分组，并按bolean "R“或"L”分组。我有大约4000输入用户。

我有表格的例子

 +----------+-------------+-------------+    +------+---------+------------------------+
 |                 t1                   |    |                 t2                      |
 +----------+-------------+-------------+    +------+---------+------------------------+
 | user_id  | Analyze_id  | result      |    |  id  | boolean |  date                  |
 +----------+-------------+-------------+    +------+---------+------------------------+
 | 1588     | 9001        | 0.753478    |    | 9001 | "R"     | 2022-10-30 06:38:29    |
 | 1588     | 9000        | 0.758452    |    | 9000 | "L"     | 2022-10-30 06:39:30    |

 | 1588     | 8554        | 0.853724    |    | 8554 | "R"     | 2022-10-22 11:48:42    |
 | 1588     | 8553        | 0.603724    |    | 8553 | "L"     | 2022-10-22 11:47:35    |
 
 | 1588     | 9887        | 0.931123    |    | 9887 | "R"     | 2022-10-01 14:48:40    |
 | 1588     | 9886        | 0.756321    |    | 9886 | "L"     | 2022-10-01 14:01:57    |

 | 1588     | 4832        | 0.755645    |    | 4832 | "R"     | 2022-10-01 17:18:14    |
 | 1588     | 4831        | 0.987445    |    | 4831 | "L"     | 2022-10-01 17:17:24    |

 | 1588     | 2458        | 0.662494    |    | 2458 | "R"     | 2022-10-01 21:18:12    |
 | 1588     | 2458        | 0.864524    |    | 2458 | "L"     | 2022-10-01 21:17:12    |
 +----------+-------------+-------------+    +------+---------+------------------------+

时间范围：

• 8h包括6h至9h29
• 11小时包括9小时30至12小时29小时
• 14h包括12小时30至15小时29小时
• 17h包括15h30至18h29
• 20h包括18h30至23h59

现在，我达到了处理连接+时间框架和平均分组。问题是，我的代码是按时间框架分组的，而不考虑布尔值"R“或"L”，所以结果的输出是将"R“和"L”混合在一起，所有这些都是加并做平均值的，所以我需要分开。

SELECT 
    max(t1.user_id) AS user_id,     
    max(t2.boolean) AS boolean, 
    AVG(t1.`result`* 8) AS result, 
    max(t2.`date`) max_date,
    case 
        when  TIME(`date`) between '06:00:00' and '09:29:00' then '08h'
        when  TIME(`date`) between '09:30:00' and '12:29:00' then '11h'
        when  TIME(`date`) between '12:30:00' and '15:29:00' then '14h'
        when  TIME(`date`) between '15:30:00' and '18:29:00' then '17h'
        when  TIME(`date`) between '18:30:00' and '23:59:00' then '20h'
    end as 'time_intervals'
FROM table1_features t1
INNER JOIN table2 t2 ON t1.analysis_id = t2.id 
WHERE  `date` >= CURRENT_TIMESTAMP() - INTERVAL 1 month AND t1.user_id = 1588
GROUP BY time_intervals
ORDER BY max_date ASC

实际输出:

 +----------+-------------+------------------------------+   
 |           Actual output                               |   
 +----------+-------------+------------------------------+   
 | user_id  |   result    |   boolean   | time_intervals |
 +----------+-------------+------------------------------+   
 | 1588     |  0.753478   |      R      |       08h      |  
 | 1588     |  0.603724   |      R      |       14h      |
 | 1588     |  0.931123   |      R      |       11h      |
 | 1588     |  0.755645   |      R      |       17h      |
 | 1588     |  0.662494   |      R      |       20h      |
 +----------+-------------+------------------------------+     

预期输出：

 +----------+-------------+------------------------------+   
 |           Actual output                               |   
 +----------+-------------+------------------------------+   
 | user_id  |   result    |   boolean   | time_intervals |
 +----------+-------------+------------------------------+   
 | 1588     |  0.753478   |      R      |       08h      |
 | 1588     |  0.753478   |      L      |       08h      |
 | 1588     |  0.603724   |      R      |       14h      |
 | 1588     |  0.603724   |      L      |       14h      |
 | 1588     |  0.931123   |      R      |       11h      |
 | 1588     |  0.931123   |      L      |       11h      |
 | 1588     |  0.755645   |      R      |       17h      |
 | 1588     |  0.755645   |      L      |       17h      |
 | 1588     |  0.662494   |      R      |       20h      |
 | 1588     |  0.662494   |      L      |       20h      |
 +----------+-------------+------------------------------+  

或者类似的东西，我有AVR的结果，每个时间帧，并由Bolean。

Answer 1

正如我在评论中所建议的，您需要更改

max(t2.boolean) AS boolean和t2.boolean，并在group by子句GROUP BY time_intervals,t2.boolean中添加t2.boolean。

最后的查询如下所示：

SELECT 
    max(t1.user_id) AS user_id,     
    t2.boolean, 
    AVG(t1.`result`* 8) AS result, 
    max(t2.`date`) max_date,
    case 
        when  TIME(`date`) between '06:00:00' and '09:29:00' then '08h'
        when  TIME(`date`) between '09:30:00' and '12:29:00' then '11h'
        when  TIME(`date`) between '12:30:00' and '15:29:00' then '14h'
        when  TIME(`date`) between '15:30:00' and '18:29:00' then '17h'
        when  TIME(`date`) between '18:30:00' and '23:59:00' then '20h'
    end as 'time_intervals'
FROM table1_features t1
INNER JOIN table2 t2 ON t1.analysis_id = t2.id 
WHERE  `date` >= CURRENT_TIMESTAMP() - INTERVAL 1 month AND t1.user_id = 1588
GROUP BY time_intervals,t2.boolean
ORDER BY max_date ASC