Блог

Я хочу запрашивать количество определенных столбцов каждые 10 минут до текущего времени.

Предположим, что фактические данные следующие.

 | access_no |     in_datetime     |
-----------------------------------
|   1       | 2021-09-16 09:10:30 |
|-----------|---------------------|
|   2       | 2021-09-16 09:15:05 |
|-----------|---------------------|
|   3       | 2021-09-16 10:03:23 |
|-----------|---------------------|
|   4       | 2021-09-16 11:13:53 |
|-----------|---------------------|
|   5       | 2021-09-16 11:30:10 |
|-----------|---------------------|
 

В это время желаемый выходной результат выглядит следующим образом.

 
| hh:mm     | au  |
-------------------
|   09:10   |  2  |
|-----------|-----|
|   09:20   |  0  |
|-----------|-----|
|   09:30   |  0  |
|-----------|-----|
|   09:40   |  0  |
|-----------|-----|
|   09:50   |  0  |
|-----------|-----|
|   10:00   |  1  |
|-----------|-----|
|   10:10   |  1  |
|-----------|-----|
|   10:20   |  0  |
|-----------|-----|
|   10:30   |  1  |
|-----------|-----|
 

И вопрос, который я сделал до сих пор, заключается в следующем.

 SELECT
   case 
        when substr(in_datetime,15,2) < '10' then concat(substr(in_datetime,12,2),':00')
        when substr(in_datetime,15,2) >='10' and substr(in_datetime,15,2)<'20' then concat(substr(in_datetime,12,2),':10')
      when substr(in_datetime,15,2) >='20' and substr(in_datetime,15,2)<'30' then concat(substr(in_datetime,12,2),':20')
      when substr(in_datetime,15,2) >='30' and substr(in_datetime,15,2)<'40' then concat(substr(in_datetime,12,2),':30')
      when substr(in_datetime,15,2) >='40' and substr(in_datetime,15,2)<'50' then concat(substr(in_datetime,12,2),':40')
      when substr(in_datetime,15,2) >='50' and substr(in_datetime,15,2)<'60' then concat(substr(in_datetime,12,2),':50')
   END as hhmm,
   count(access_no) as au
FROM tbl_access

where date(out_datetime) = str_to_date('20210916', '%Y%m%d')
and in_datetime is not null

group by substr(in_datetime,1,10) ,
   case 
        when substr(in_datetime,15,2)<'10' then concat(substr(in_datetime,12,2),':00')
        when substr(in_datetime,15,2) >='10' and substr(in_datetime,15,2)<'20' then concat(substr(in_datetime,12,2),':10')
      when substr(in_datetime,15,2) >='20' and substr(in_datetime,15,2)<'30' then concat(substr(in_datetime,12,2),':20')
      when substr(in_datetime,15,2) >='30' and substr(in_datetime,15,2)<'40' then concat(substr(in_datetime,12,2),':30')
      when substr(in_datetime,15,2) >='40' and substr(in_datetime,15,2)<'50' then concat(substr(in_datetime,12,2),':40')
      when substr(in_datetime,15,2) >='50' and substr(in_datetime,15,2)<'60' then concat(substr(in_datetime,12,2),':50')
   END
 

Однако в результате этого запроса значение времени, когда данные отсутствуют, не извлекается, как показано ниже.

 
| hh:mm     | au  |
-------------------
|   09:10   |  2  |
|-----------|-----|
|   10:00   |  1  |
|-----------|-----|
|   10:10   |  1  |
|-----------|-----|
|   10:30   |  1  |
|-----------|-----|
 

Как я могу получить время, когда нет данных до текущего времени?

Спасибо вам всем!

Если я правильно понял ваш вопрос, то это можно решить с помощью таблицы календаря.

Вы можете создать список раз с помощью рекурсивного cte, а затем отформатировать их с помощью DATE_FORMAT() для отображения только hh:mm в соответствии с существующим запросом.

 with recursive times as (
    select '1970-01-01 00:00' t 
    union all
    select t   interval 10 minute 
    from times 
    where t  <= '1970-01-01 23:40'
 )
 select 
     DATE_FORMAT(t, '%H:%i') 'hh:mm'
 from 
     times
 

Выход:

Затем вы можете присоединить это к существующему запросу, чтобы каждый раз возвращать строку.