主页 / dba / 问题 / 318579
Accepted
Rollo
Asked: 2022-10-24 01:54:40 +0800 CST

如何在 MySQL 中按周/月/年和时间范围 8hr/11hr/14hr/17hr/20hr 分组

  • 772

我需要将 2 个表（Key = analysis_id <=> id）分组，其中我的输出应显示上周、月或年，按时间范围分组。我有大约 4000 个来自用户的输入。

我有的表格示例

 +----------+-------------+-------------+    +------+-----+------------------------+
 | user_id  | Analyze_id  | result      |    |id    | arm |  date                  |
 +----------+-------------+-------------+    +------+-----+------------------------+
 | 1        | 9000        | 0.753478    |    | 9000 | "R" | 2022-10-30 06:38:29    |
 | 2        | 8553        | 0.603724    |    | 8553 | "L" | 2022-10-22 11:48:42    |
 | 3        | 9886        | 0.931123    |    | 9886 | "R" | 2022-10-01 14:48:40    |
 | 4        | 4831        | 0.755645    |    | 4831 | "L" | 2022-10-01 05:18:14    |
 | 5        | 2458        | 0.662494    |    | 2458 | "R" | 2022-10-01 05:18:12    |
 +----------+-------------+-------------+    +------+-----+------------------------+

时间范围：

  • 8h 包括 6h 到 9h29
  • 11h 包括 9h30 到 12h29
  • 14h 包括 12h30 到 15h29
  • 17h 包括 15h30 到 18h29
  • 20h 包括 18h30 到 23h59

目前，我可以正确地进行 INNER JOIN 并显示“上周或月或年”（我的选择），但我不知道如何在时间范围内对当前输出进行分组。我用 GROUP BY 想象它，但我不知道如何在这些情况下使用它。我对 MySQL 真的很陌生（2 个月前开始工作）。我不知道这是否可能，但我认为我需要一个变量，其中是我的投入的推动或库存。我也播种了子查询，但我不知道如何在这些情况下使用它们。

 SELECT table1_features.user_id, analysis_id, table2.arm, table1_features.result*8 AS score, 
 table2.date 
 FROM table1_features 
 INNER JOIN table2 ON table1_features.analysis_id = table2.id 
 WHERE  date >= CURRENT_TIMESTAMP() - INTERVAL 1 month AND table1_features.user_id = 5 
 ORDER BY date DESC

我的查询的输出是：

  const outputQuery = 
    [
     {user_id: 9000, score: 6.027824, date: '2022-10-30 06:38:29', boolean: "R"},
     {user_id: 8553, score: 4.829792, date: '2022-10-22 11:48:42', boolean: "L"},
     {user_id: 9886, score: 7.448984, date: '2022-10-01 14:48:40', boolean: "R"},
     {user_id: 4831, score: 6.04516,  date: '2022-10-01 17:18:14', boolean: "L"},
     {user_id: 2458, score: 5.299952, date: '2022-10-01 21:18:12', boolean: "R"}
    ]

我的输出应该看起来像这样，我不知道在 MySQL 中有什么可能：

  const result = 
    [
      //8hr time range
      [
       {user_id: 9000, score: 6.027824, date: '2022-10-30 06:38:29', boolean: "R"}
      ],
      //11hr time range
      [
       {user_id: 8553, score: 4.829792, date: '2022-10-22 11:48:42', boolean: "L"}
      ],
      //14hr time range
      [
       {user_id: 9886, score: 7.448984, date: '2022-10-01 14:48:40', boolean: "R"}
      ],
      //17hr time range
      [
       {user_id: 4831, score: 6.04516,  date: '2022-10-01 17:18:14', boolean: "L"}
      ],
      //20hr time range
      [
       {user_id: 2458, score: 5.299952, date: '2022-10-01 21:18:12', boolean: "R"}
      ]
    ]
mysql

1 个回答

  1. Best Answer

    给定的数据和预期的结果不匹配。

    8h  includes 6h    to 9h29
11h includes 9h30  to 12h29
14h includes 12h30 to 15h29
17h includes 15h30 to 18h29
20h includes 18h30 to 23h59

    日期“2022-10-01 05:18:14”和“2022-10-01 05:18:12”将不属于上述任何组。

    一个案例可能会给你预期的结果：

    SELECT t1.analysis_id, 
       t2.arm, 
       t1.`result`* 8 AS score, 
       t2.`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 
ORDER BY `date` DESC

    https://dbfiddle.uk/we0nIOA2

    注意，我删除了条件AND table1_features.user_id = 5。如果可能，不要使用关键字和保留字 ，例如 DATE 重命名，否则您必须使用反引号

    • 0

相关问题