0

I need to group 2 tables (Key = analysis_id <=> id) where my output should show the last week or month or year, grouped by a Time range. I have around 4000 inputs from users.

Example of tables I have

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

Time Range:

  • 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

for the moment I reach to do the INNER JOIN correctly and to show the "last week or month or year" (of my choice) but I don't know how to group my current output in time ranges. I imagine it with GROUP BY but I don't know how to use it in these cases. I'm really new to MySQL (started working 2 months ago). I don't know if it's possible but think I need a variable where is a push or stock of my inputs. I sow also the sub-queries but I don't know how to use them for these cases.

 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

And the output of my query is :

  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"}
    ]

My output should look something like these, I don't know what is possible in 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"}
      ]
    ]
1
  • Was this cross-posted?
    – Rick James
    Oct 23, 2022 at 21:51

1 Answer 1

0

The given data and expected result doesn't match.

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

The dates '2022-10-01 05:18:14' and '2022-10-01 05:18:12' will not be part of any of the above groups.

A case might give you the expected result:

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

Note, I removed the condition AND table1_features.user_id = 5 . Do not use Keywords and Reserved Words such as DATE rename it if possible otherwise you have to use backticks

11
  • You might want to put '08h' for the first time interval so that you could sort by that as well? I think the OP wants to group by time_intervals also? Just a couple of thoughts!
    – Vérace
    Oct 23, 2022 at 14:51
  • Hi thanks a lot i will try these out!! And thanks for the feedback and you are right the timestamps hours where bad i changed them :)
    – Rollo
    Oct 24, 2022 at 8:53
  • i don't have enought points to give you :) !!
    – Rollo
    Oct 24, 2022 at 8:54
  • @Rollo, if this answer solves your problem, please accept as answer and/or upvote.
    – user212533
    Oct 25, 2022 at 13:48
  • Hi thanks a lot for your answer and sorry for late reply. Your code for the moment is adding a new column 'time_intervals' and adding an extra info (super interesting) but is not grouping them by timeRange like the expected output i show in my question
    – Rollo
    Oct 31, 2022 at 8:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.