mysql - 返回过去 24 小时内每小时的计数，用零填充空白-6ren

mysql - 返回过去 24 小时内每小时的计数，用零填充空白

转载作者：行者123 更新时间：2023-11-29 02:10:28

25

4

我正在尝试获取一个记录集，其中包含过去 24 小时内每小时的销售额，并用零填充空白时间。

我的示例数据集如下所示，包含两个测试行:

id | saleID            | amount      | created
---|-------------------|-------------|-------------
1  | 6032b317-1533...  | 20000       | 2019-01-10 23:56:40
2  | 43556fg6-5344...  | 60000       | 2019-01-11 18:06:32

比如当前时间是18:10，那么我要查找的结果是:

| hourOrderID | saleHour | saleHourTotal |
| ----------- | -------- | ------------- |
| 1           | 18:00    | 1             |
| 2           | 17:00    | 0             |
| 3           | 16:00    | 0             |
| 4           | 15:00    | 0             |
| 5           | 14:00    | 0             |
| 6           | 13:00    | 0             |
| 7           | 12:00    | 0             |
| 8           | 11:00    | 0             |
| 9           | 10:00    | 0             |
| 10          | 9:00     | 0             |
| 11          | 8:00     | 0             |
| 12          | 7:00     | 0             |
| 13          | 6:00     | 0             |
| 14          | 5:00     | 0             |
| 15          | 4:00     | 0             |
| 16          | 3:00     | 0             |
| 17          | 2:00     | 0             |
| 18          | 1:00     | 0             |
| 19          | 0:00     | 0             |
| 20          | 23:00    | 1             |
| 21          | 22:00    | 0             |
| 22          | 21:00    | 0             |
| 23          | 20:00    | 0             |
| 24          | 19:00    | 0             |
| 25          | 18:00    | 0             |

请注意；今天 18:00-19:00 之间有 1 个销售，昨天 18:00-19:00 之间有 0 个。这是我的问题。

我实际得到的结果是:

| hourOrderID | saleHour | saleHourTotal |
| ----------- | -------- | ------------- |
| 1           | 18:00    | 1             |
| 2           | 17:00    | 0             |
| 3           | 16:00    | 0             |
| 4           | 15:00    | 0             |
| 5           | 14:00    | 0             |
| 6           | 13:00    | 0             |
| 7           | 12:00    | 0             |
| 8           | 11:00    | 0             |
| 9           | 10:00    | 0             |
| 10          | 9:00     | 0             |
| 11          | 8:00     | 0             |
| 12          | 7:00     | 0             |
| 13          | 6:00     | 0             |
| 14          | 5:00     | 0             |
| 15          | 4:00     | 0             |
| 16          | 3:00     | 0             |
| 17          | 2:00     | 0             |
| 18          | 1:00     | 0             |
| 19          | 0:00     | 0             |
| 20          | 23:00    | 1             |
| 21          | 22:00    | 0             |
| 22          | 21:00    | 0             |
| 23          | 20:00    | 0             |
| 24          | 19:00    | 0             |
| 25          | 18:00    | 1             | <----- this isn't yesterday, it's today

我一直在处理的查询是:

SELECT
    aux.id AS hourOrderID,
    CONCAT(aux.dh, ':00') AS saleHour,
    COALESCE(COUNT(ets.saleID), 0) AS saleHourTotal
FROM eventTicketSales AS ets 
RIGHT JOIN (
    SELECT 1 AS id, HOUR(UTC_TIMESTAMP()) AS dh UNION
    SELECT 2 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 1 HOUR)) AS dh UNION
    SELECT 3 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 2 HOUR)) AS dh UNION
    SELECT 4 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 3 HOUR)) AS dh UNION
    SELECT 5 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 4 HOUR)) AS dh UNION
    SELECT 6 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 5 HOUR)) AS dh UNION
    SELECT 7 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 6 HOUR)) AS dh UNION
    SELECT 8 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 7 HOUR)) AS dh UNION
    SELECT 9 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 8 HOUR)) AS dh UNION
    SELECT 10 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 9 HOUR)) AS dh UNION
    SELECT 11 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 10 HOUR)) AS dh UNION
    SELECT 12 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 11 HOUR)) AS dh UNION
    SELECT 13 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 12 HOUR)) AS dh UNION
    SELECT 14 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 13 HOUR)) AS dh UNION
    SELECT 15 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 14 HOUR)) AS dh UNION
    SELECT 16 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 15 HOUR)) AS dh UNION
    SELECT 17 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 16 HOUR)) AS dh UNION
    SELECT 18 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 17 HOUR)) AS dh UNION
    SELECT 19 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 18 HOUR)) AS dh UNION
    SELECT 20 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 19 HOUR)) AS dh UNION
    SELECT 21 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 20 HOUR)) AS dh UNION
    SELECT 22 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 21 HOUR)) AS dh UNION
    SELECT 23 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 22 HOUR)) AS dh UNION
    SELECT 24 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 23 HOUR)) AS dh UNION
    SELECT 25 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 24 HOUR)) AS dh
) AS aux ON HOUR(ets.created) = aux.dh
         AND ets.created > DATE_SUB(UTC_TIMESTAMP, INTERVAL 24 HOUR)
GROUP BY aux.id, aux.dh
ORDER BY hourOrderID

你可以fiddle with my bits here :o

我希望最后一小时是前一天的，但我的大脑认为它不能再继续了，需要帮助。

我尝试在第 25 小时添加另一个 UNION，还尝试将时间范围延长到 25 小时，但结果与我想要的相去甚远:

AND ets.created > DATE_SUB(UTC_TIMESTAMP, INTERVAL 25 HOUR

请建议可以更改哪些内容以使其按预期工作。

最佳答案

我听取了评论区@Strawberry 的建议，决定做这个应用层，现在很有意义，因为它更容易阅读。我不后悔尝试 SQL 版本，因为我学到了一些东西。

我在下面发布了我使用的解决方案，它是用 PHP 编写的，适用于任何需要类似东西的人。如果有人有比这更有效的解决方案，请在评论中分享。

查询:

SELECT
    CONCAT(DATE_FORMAT(created, '%Y-%m-%d %H'), ':00:00') AS saleHour,
    SUM(amount) AS totalSales
FROM eventTicketSales
WHERE created BETWEEN DATE_SUB(UTC_TIMESTAMP(), INTERVAL 24 HOUR) AND UTC_TIMESTAMP
GROUP BY HOUR(created);

结果:

saleHour                 totalSales
2019-01-11 17:00:00      10000
2019-01-10 23:00:00      20000

存储在$salesIn24Hours

逻辑:

$hours = 24 + 1;
for ($i=0; $i<$hours; $i++) {
    $date = date('Y-m-d H', strtotime('-'.$i.' HOUR')).':00:00';
    $key = array_search($date, array_column($salesIn24Hours, 'saleHour'));
    if ($key !== FALSE) {
        echo $date . ' ' . $salesIn24Hours[$key]['totalSales'] . '<br>';
    } else {
        echo $date . ' 0' . '<br>';
    }
}

结果:

2019-01-11 21:00:00  0
2019-01-11 20:00:00  0
2019-01-11 19:00:00  0
2019-01-11 18:00:00  0
2019-01-11 17:00:00  10000
2019-01-11 16:00:00  0
2019-01-11 15:00:00  0
2019-01-11 14:00:00  0
2019-01-11 13:00:00  0
2019-01-11 12:00:00  0
2019-01-11 11:00:00  0
2019-01-11 10:00:00  0
2019-01-11 09:00:00  0
2019-01-11 08:00:00  0
2019-01-11 07:00:00  0
2019-01-11 06:00:00  0
2019-01-11 05:00:00  0
2019-01-11 04:00:00  0
2019-01-11 03:00:00  0
2019-01-11 02:00:00  0
2019-01-11 01:00:00  0
2019-01-11 00:00:00  0
2019-01-10 23:00:00  20000
2019-01-10 22:00:00  0
2019-01-10 21:00:00  0

关于mysql - 返回过去 24 小时内每小时的计数，用零填充空白，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/54152396/

25

4

0

文章推荐： MySQL 5.5 - 计算每天有条件的开放项目

文章推荐： android - 使用 Android AlertDialog

文章推荐： c# - 如何解决 Entity Framework 中 system.data 配置的问题

php 计数 vs sql 计数 vs 计数字段？演出
关闭。这个问题是opinion-based .它目前不接受答案。想要改进这个问题？更新问题，以便 editing this post 可以用事实和引用来回答它. 关闭 9 年前。 Improve
javascript 计数
我有点卡在 JavaScript 逻辑上来完成这个任务。基本上如果我给出一个数字(比如 30) 我想在两边都显示 5。所以 25 26 27 28 29 30 31 32 33 34 35 这部
你能发现这段代码中的逻辑错误吗？计数
我编写的程序有问题。我无法获得输入字符串的正确字数，但我获得了正确的最长字符数。我不知道为什么，但这是我的代码。我正在做的是将一个字符串传递给一个函数，该函数将字符串中的所有字母大写。然后，该函数逐个
Android错误循环 "for"计数
我有功能 public ArrayList vyberNahodnaPismena() { String[] seznamPismen = {"A", "Á", "B", "C", "Č",
postgresql 计数
这可以在 PGSQL 中完成吗？我有一个我创建的 View ，其中主机名、ip 和数据中心来自一个表，ifdesc 和 if stats 来自另一个表。 View 输出如下所示: hostname |
mysql查询左连接+计数
我想要一组来自订单文件的数据，这些数据可以为我提供客户编号、订单编号、产品、数量、价格以及每个订单的订单详细信息文件中的行数。我在最后一部分遇到问题。 Select Header.CustNo, He
sql查询问题/计数
我有属于街道的房子。一个用户可以买几套房子。我如何知道用户是否拥有整条街道？ street table with columns (id/name) house table with columns
Mongodb如何聚合不同值的出现次数(计数)？
我有一套有 200 万个主题标签。然而，只有大约 200k 是不同的值。我想知道哪些主题标签在我的数据中重复得更多。我用它来查找每个主题标签在我的数据集上重复了多少次: db.hashtags.ag
Mongodb同时在多个字段上聚合(计数)
我有如下文件: { "_id" : "someuniqueeventid", "event" : "event_type_1", "date" : ISODate("2014-
使用多重连接的 SQL 计数
我有以下三个相互关联的表: 主持人(有多个 session ) session (有多个进程) 过程表结构如下: 主机表 - id, name session 表 - id, host_id, na
每个类别的 SQL 计数
我需要根据 2 个字段对行进行计数以进行分组。动物(一) id group_id strain_id death_date death_cause status --
从加入组中的 LINQ 计数
我有一个 LINQ 语句，我正在努力改正，所以可能这一切都错了。我的目标是查询一个表并加入另一个表以获取计数。地点标识、显示 ProfilePlaces ID、PlaceID、通话、聆听基本上P
Excel:计数 "Not equal to"
我无法编写 Countifs 来完成我想要的。我每个月都会运行一份 claim 报告，其中包含大量按列组织的数据，并每月将其导出到 Excel 中。在一个单独的选项卡上，我有引用此数据复制到的选项卡的
sql - 根据列值对行进行排名/计数
我有一些数据采用此 sqlfilddle 中描述的格式:http://sqlfiddle.com/#!4/b9cdf/2 基本上，一个包含用户 ID 和事件发生时间的表。我想做的是根据用户发生事件的时
SQL 计数 where 子句
我有以下 SQL 语句: SELECT [l.LeagueId] AS LeagueId, [l.LeagueName] AS NAME, [lp.PositionId] FROM
SQL 平均(计数(*))？
我试图找出一个值在列中出现的平均次数，根据另一列对其进行分组，然后对其进行计算。我有 3 张 table ，有点像这样 DVD ID | NAME 1 | 1 2 | 1 3
SQL - 用 Where 计数
我有一个非常简单的 SQL 问题。我有一个包含以下列的数据库表: 零件号销售类型(为简单起见，称之为销售类型 1、2、3、4、5) 我希望编写一个包含以下三列的查询: 零件号 Sales Type
包含零值的 SQL 计数
我创建了以下存储过程，用于计算选定位置的特定范围之间每天的记录数: [dbo].[getRecordsCount] @LOCATION as INT, @BEGIN as datetime, @END
日期列的 SQL 计数
我有一个包含一组列的表，其中一个是日期列。我需要计算该列的值引用同一个月的次数。如果一个月内，该计数的总和超过 3，则返回。例如: ____________________ | DATE |
XML 计数 TXT
看XXX数据如下: lala XXX = EL String [XXX] | TXT String | MMS String 为此，XXX数据yppz是由 lala

首页

博学

6Ren·AI

商城

mysql - 返回过去 24 小时内每小时的计数，用零填充空白