Rollo's questions

管理员 4.8.1
MySQL

我有 3 个与 tabata 数据相关的表：

• tabata_exercice_serie
• tabata_exercice_chunk
• tabata_module

我的问题

在tabata_module中，我有一列“exercice_ids”，其中包含来自两个表“tabata_exercice_ serie ”和“tabata_exercice_ chunk ”的 ID 数组，因为它们都有 ID (int)，我不知道它是哪个表。

如何使用 MySQL 在 adminer 中创建一个带有前缀的自增列？？

目前我想创建一个 AI ID。为了解决我的问题，我目前的解决方案是为“tabata_exercice_ serie ”和“tabata_exercice_ chunk ”设置一个特定的 id 。例如：

• “tabata_exercice_ serie ”：E1、E2、E3、E4、E5 等
• “tabata_exercice_块：EC1、EC2、EC3、EC4、EC5 等

我的问题是，当我单击默认值并输入前缀“E”时，我有这些错误日志 (img2)。我认为这可能是列类型，我也尝试了所有列类型，但始终是相同的错误.

你如何在 AI 中添加前缀值..？？ChatGPT 告诉我使用CONCAT("E",id)，但我发现它没用，因为它不会自动添加前缀，每次我在表中有新输入时，我都需要使用该功能。

如果您有其他方法可以解决这些问题，我也很感兴趣。谢谢

我有这些功能查询，我需要将 UTC 中的时间戳更改为不同时区的时间戳

    SELECT max(user_id) AS user_id,        
            AVG((model_v3)*40) AS avg_score,
            SUM((model_v3)*40) AS sum_score,
            COUNT(*) AS total_inputs,
            min(`date_cet`) AS oldest_date,       
            max(`date_cet`) AS newest_date,
            case
                when TIME(`date_cet`) between '03:00:00' and '07:59:59' then 6 
                when TIME(`date_cet`) between '08:00:00' and '11:59:59' then 10
                when TIME(`date_cet`) between '12:00:00' and '15:59:59' then 14
                when TIME(`date_cet`) between '16:00:00' and '19:59:59' then 18 
                when TIME(`date_cet`) between '20:00:00' and '02:59:59' then 22
            end as 'time_intervals_hours'
    FROM phonation_features
    WHERE `date_cet` >= CURRENT_TIMESTAMP() - interval 10000 DAY AND        user_id = 5
    GROUP BY time_intervals_hours 
    ORDER BY time_intervals_hours ASC

我处理查询的唯一方法是使用CONVERT_TZ()如下：

    SELECT 
           max(user_id) AS user_id,        
           AVG((model_v3)*40) AS avg_score,
           SUM((model_v3)*40) AS sum_score,
           COUNT(*) AS total_inputs,
           min(convert_tz(date_cet, 'UTC', 'Europe/Paris')) AS oldest_date,       
           max(convert_tz(date_cet, 'UTC', 'Europe/Paris')) AS newest_date,
           case
               when TIME(convert_tz(date_cet, 'UTC', 'Europe/Paris')) between '00:00:00' and '05:59:59' then 3
               when TIME(convert_tz(date_cet, 'UTC', 'Europe/Paris')) between '06:00:00' and '09:29:59' then 8
               when TIME(convert_tz(date_cet, 'UTC', 'Europe/Paris')) between '09:30:00' and '12:29:59' then 11
               when TIME(convert_tz(date_cet, 'UTC', 'Europe/Paris')) between '12:30:00' and '15:29:59' then 14
               when TIME(convert_tz(date_cet, 'UTC', 'Europe/Paris')) between '15:30:00' and '18:29:59' then 17
               when TIME(convert_tz(date_cet, 'UTC', 'Europe/Paris')) between '18:30:00' and '23:59:59' then 20
           end as 'time_intervals'
    FROM phonation_features
    WHERE convert_tz(date_cet, 'UTC', 'Europe/Paris') >= CURRENT_TIMESTAMP() - interval 5000 DAY AND user_id = 5
    GROUP BY time_intervals 
    ORDER BY time_intervals ASC

有没有办法使用生成变量GROUP BY？

我想创建一个timestamp_timeZone可以在所有查询中重复使用的变量。

这可能吗？

我需要生成一个 POST/查询，允许使用时区（→欧洲/雅典、→欧洲/布鲁塞尔、→欧洲/伦敦或→欧洲/巴黎。）发送有效载荷，并将响应更新的时间戳+时区。

我需要将 2 个表（Key = analysis_id <=> id）分组，其中我的输出应该显示最后一周或一个月或一年，按时间框架分组并按布尔值 "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
• 11h 包括 9h30 到 12h29
• 14h 包括 12h30 到 15h29
• 17h 包括 15h30 到 18h29
• 20h 包括 18h30 到 23h59

目前，我要处理加入 + 时间范围和按组计算的平均值。问题是我的代码是按时间范围分组而不考虑布尔值“R”或“L”，因此结果的输出是将“R”和“L”混合在一个组中，并进行平均和 i需要分开

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      |
 +----------+-------------+------------------------------+  

或者类似的东西，我有每个时间框架和 Bolean 的 AVR 结果。

我需要将 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"}
      ]
    ]