mysql 按八小时时间间隔计数

vcudknz3 于 2023-03-28 发布在 Mysql

关注(0)|答案(3)|浏览(154)

我使用这个查询来计算表中的记录，它工作了，我每隔八个小时执行一次计算，但是现在我有一个问题，我不能从晚上11点计算到第二天早上7点，我必须计算三次，一次从07：00到15：00，一次从15：00到23：00，一次从23：00到07：00，但是最后一次我不能让它工作。每天，每月，每年，我成功了，但是我错过了最后一步的最后一个时间块，有人能帮助我也能够计算这个吗？

select 
    date_format(created_at, '%Y-%m-%d %H:00:00') as date_and_hour,
    count(*) as total_ticket_Id,
    case when count(*) >= 4 then 4 else count(*) end as up_ticket_Id,   
    case when count(*) > 4 then count(*) - 4 else 0 end as down_ticket_Id
from tickets
where created_at between '2022-01-01 07:00:00' and '2022-12-31 15:00:00'
    and cast(date_format(created_at, '%H') as SIGNED)  between 7 and 15
group by date_format(created_at, '%Y-%m-%d %H:00:00');

运行建议的查询，我得到了这个结果，但它不是按时间组划分的，我应该这样做，我必须计算三个单独的组，从07：00到15：00，15：00到23：00的8小时组和从晚上11点到早上7点的一个组，其中一列中每小时计数4，其余的在另一列中，其中一列是总数

date_and_hour   total_ticket_Id up_ticket_Id    down_ticket_Id
18/05/2022 00:00    50  4   46
19/05/2022 00:00    51  4   47
20/05/2022 00:00    7   4   3
20/05/2022 07:00    31  4   27
20/05/2022 15:00    17  4   13
21/05/2022 00:00    8   4   4
21/05/2022 07:00    14  4   10
21/05/2022 15:00    14  4   10
21/05/2022 23:00    6   4   2
22/05/2022 07:00    18  4   14
22/05/2022 15:00    14  4   10
23/05/2022 05:00    3   3   0
23/05/2022 07:00    38  4   34
23/05/2022 15:00    10  4   6
24/05/2022 01:00    2   2   0
24/05/2022 07:00    31  4   27
24/05/2022 15:00    23  4   19
24/05/2022 23:00    5   4   1
25/05/2022 07:00    29  4   25
25/05/2022 15:00    17  4   13
26/05/2022 01:00    3   3   0
26/05/2022 07:00    19  4   15
26/05/2022 15:00    12  4   8
27/05/2022 00:00    4   4   0
27/05/2022 07:00    27  4   23
27/05/2022 15:00    20  4   16
27/05/2022 23:00    7   4   3
28/05/2022 10:00    13  4   9
28/05/2022 15:00    11  4   7
28/05/2022 23:00    12  4   8
29/05/2022 08:00    19  4   15
29/05/2022 15:00    19  4   15
30/05/2022 03:00    6   4   2
30/05/2022 07:00    27  4   23
30/05/2022 15:00    15  4   11
30/05/2022 23:00    6   4   2
31/05/2022 07:00    26  4   22

如果我用这段代码导出，它不会给予我结果
如果我用这段代码导出，它不会给予我结果，我会在查询中看到它们，但如果我以任何格式导出，我什么也看不到，如果我试图创建一个视图，它不会创建它

SELECT
  createdHour,
  hourGroup,
  COUNT(*) total_ticket_Id,
  LEAST(COUNT(*), 4) up_ticket_Id,
  IF(COUNT(*) > 4, COUNT(*) - 4, 0) down_ticket_Id
FROM (
  SELECT
    7 * (EXTRACT(HOUR FROM created_at) >= 7 AND EXTRACT(HOUR FROM created_at) < 15) +
    15 * (EXTRACT(HOUR FROM created_at) >= 15 AND EXTRACT(HOUR FROM created_at) < 23) +
    23 * (EXTRACT(HOUR FROM created_at) >= 23 OR EXTRACT(HOUR FROM created_at) < 7) AS hourGroup,
    CONCAT(DATE(created_at), ' ', LPAD((SELECT hourGroup), 2, '0'), ':00:00') createdHour
  FROM tickets
) groupedPerInterval
GROUP BY createdHour, hourGroup

<div class="alert alert-danger" role="alert"><h1>Errore</h1><p><strong>Query SQL:</strong>  <a href="#" class="copyQueryBtn" data-text="SELECT
  createdHour
  hourGroup
  COUNT(*) total_ticket_Id
  LEAST(COUNT(*)
  IF(COUNT(*) &gt
FROM (
  SELECT
    7 * (EXTRACT(HOUR FROM created_at) &gt
    15 * (EXTRACT(HOUR FROM created_at) &gt
    23 * (EXTRACT(HOUR FROM created_at) &gt
    CONCAT(DATE(created_at)
  FROM tickets
) groupedPerInterval
GROUP BY createdHour

Result csv:

<div class="alert alert-danger" role="alert"><h1>Error</h1><p><strong>SQL Query:</strong> <a href="#" class="copyQueryBtn" data -text="SELECT
   createdHour
   hourGroup
   COUNT(*) total_ticket_Id
   LEAST(COUNT(*)
   IF(COUNT(*) &gt
FROM (
   SELECT
     7 * (EXTRACT(HOUR FROM created_at) &gt
     15 * (EXTRACT(HOUR FROM created_at) &gt
     23 * (EXTRACT(HOUR FROM created_at) &gt
     CONCAT(DATE(created_at)
   FROM tickets
) groupedPerInterval
GROUP BY createdHour
<a href="./url.php?url=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F8.0%2Fen%2Fselect.html" target="mysql_doc"><img src=" class="icon ic_b_edit">&nbsp
<p>
<code class="sql"><pre>
SELECT
   createdHour
   hourGroup
   COUNT(*) total_ticket_Id
   LEAST(COUNT(*)
   IF(COUNT(*) &gt
FROM (
   SELECT
     7 * (EXTRACT(HOUR FROM created_at) &gt
     15 * (EXTRACT(HOUR FROM created_at) &gt
     23 * (EXTRACT(HOUR FROM created_at) &gt
     CONCAT(DATE(created_at)
   FROM tickets
) groupedPerInterval
GROUP BY createdHour
</pre></code>
</p>
<p>
     <strong>Message from MySQL: </strong><a href="./url.php?url=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F8.0%2Fen%2Fserver-error- reference.html" target="mysql_doc"><img src="themes/dot.gif" title="Documentation" alt="Documentation" class="icon ic_b_help"></a>
</p>
<code>#1046 - No database selected</code><br></div>

mysql

来源：https://stackoverflow.com/questions/75825441/counts-by-eight-hour-time-interval

3条答案

按热度按时间

idfiyjo81#

您可以调整How to group datetime into intervals of 3 hours in mysql中的解决方案
不同的是，您需要在07：00之前的特殊情况时间将它们包含在前一天中。

SELECT
    CASE 
        WHEN HOUR(created_at) < 7 THEN DATE(DATE_SUB(created_at, INTERVAL 1 DAY))
        ELSE DATE(created_at)
    END AS date,
    CASE
        WHEN HOUR(created_at) < 7 OR HOUR(CREATED_AT) = 23 THEN '23-6'
        WHEN HOUR(created_at) < 15 THEN '8-14'
        ELSE '15-22'
    END AS hours,
    count(*) as total_ticket_Id,
    case when count(*) >= 4 then 4 else count(*) end as up_ticket_Id,   
    case when count(*) > 4 then count(*) - 4 else 0 end as down_ticket_Id
FROM tickets
GROUP BY date, hours

赞(0）回复(0）举报 2023-03-28

cs7cruho2#

试试这个：

SELECT 
    date_format(MIN(created_at), '%Y-%m-%d %H:00:00') as date_and_hour,
    count(*) as total_ticket_Id,
    case when count(*) >= 4 then 4 else count(*) end as up_ticket_Id,   
    case when count(*) > 4 then count(*) - 4 else 0 end as down_ticket_Id
FROM tickets
WHERE created_at between '2022-01-01 07:00:00' and '2022-12-31 15:00:00'
GROUP BY (((DAYOFYEAR(created_at)-1)*24) + HOUR(created_at) - 7) >> 3;

赞(0）回复(0）举报 2023-03-28

k5ifujac3#

更新以计算所有3个总计列并显示日期。

SELECT
  createdHour,
  hourGroup,
  COUNT(*) total_ticket_Id,
  LEAST(COUNT(*), 4) up_ticket_Id,
  IF(COUNT(*) > 4, COUNT(*) - 4, 0) down_ticket_Id
FROM (
  SELECT
    7 * (EXTRACT(HOUR FROM created_at) >= 7 AND EXTRACT(HOUR FROM created_at) < 15) +
    15 * (EXTRACT(HOUR FROM created_at) >= 15 AND EXTRACT(HOUR FROM created_at) < 23) +
    23 * (EXTRACT(HOUR FROM created_at) >= 23 OR EXTRACT(HOUR FROM created_at) < 7) AS hourGroup,
    CONCAT(DATE(created_at), ' ', LPAD((SELECT hourGroup), 2, '0'), ':00:00') createdHour
  FROM tickets
) groupedPerInterval
GROUP BY createdHour, hourGroup

赞(0）回复(0）举报 2023-03-28

我来回答

mysql 按八小时时间间隔计数

3条答案

相关问题

热门标签

最新问答