程序员经验分享-hwhale

mysql选择时间跨度之间的行数

2023-12-27

我试图获取特定时间或时间跨度之间的总行数。 基本上，我们可以说下表：

CREATE TABLE IF NOT EXISTS `downloads` (
`id` int(7) NOT NULL AUTO_INCREMENT,
`stuff_id` int(7) NOT NULL,
`user_id` int(7) NOT NULL,
`dl_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

每当有人下载​​某些内容时，就会填充此表。

所以我真正需要的是获取在一段时间内（例如 24 小时）下载次数超过 100 次的用户 (user_id) 列表。不是在过去 24 小时内，而是在那个确切的时间段内，即使是在去年的圣诞节期间 =)

有什么想法吗？


好吧，我意识到我有点晚了，但我还是想发布我的答案:-)

您所需要的可以使用子查询来完成，但这可能需要很长时间才能在大型表上完成......

思考这个问题时，我想到了两种不同的方法。

其中之一已在其他答案中处理过，它的工作原理是从特定时间点开始，查看此时开始的间隔，然后查看紧随其后的相等持续时间的间隔。这会产生清晰、易于理解的结果，并且可能是所需要的（例如，每个日历日用户的下载量不得超过 100 次）。然而，这完全会忽略用户在午夜之前的一小时内进行 99 次下载并在新的一天的第一个小时内进行另外 99 次下载的情况。

因此，如果所需的结果更多的是“前十名下载者列表”，那么这是另一种方法。这里的结果乍一看可能不太容易理解，因为一次下载可能会计入多个间隔。这是因为间隔将（并且需要）重叠。

这是我的设置。我已经根据您的语句创建了表并添加了两个索引：

CREATE INDEX downloads_timestamp on downloads (dl_date);
CREATE INDEX downloads_user_id on downloads (user_id);

我插入表中的数据：

SELECT * FROM downloads;
+----+----------+---------+---------------------+
| id | stuff_id | user_id | dl_date             |
+----+----------+---------+---------------------+
|  1 |        1 |       1 | 2011-01-24 09:00:00 |
|  2 |        1 |       1 | 2011-01-24 09:30:00 |
|  3 |        1 |       1 | 2011-01-24 09:35:00 |
|  4 |        1 |       1 | 2011-01-24 10:00:00 |
|  5 |        1 |       1 | 2011-01-24 11:00:00 |
|  6 |        1 |       1 | 2011-01-24 11:15:00 |
|  7 |        1 |       1 | 2011-01-25 09:15:00 |
|  8 |        1 |       1 | 2011-01-25 09:30:00 |
|  9 |        1 |       1 | 2011-01-25 09:45:00 |
| 10 |        1 |       2 | 2011-01-24 08:00:00 |
| 11 |        1 |       2 | 2011-01-24 12:00:00 |
| 12 |        1 |       2 | 2011-01-24 12:01:00 |
| 13 |        1 |       2 | 2011-01-24 12:02:00 |
| 14 |        1 |       2 | 2011-01-24 12:03:00 |
| 15 |        1 |       2 | 2011-01-24 12:00:00 |
| 16 |        1 |       2 | 2011-01-24 12:04:00 |
| 17 |        1 |       2 | 2011-01-24 12:05:00 |
| 18 |        1 |       2 | 2011-01-24 12:06:00 |
| 19 |        1 |       2 | 2011-01-24 12:07:00 |
| 20 |        1 |       2 | 2011-01-24 12:08:00 |
| 21 |        1 |       2 | 2011-01-24 12:09:00 |
| 22 |        1 |       2 | 2011-01-24 12:10:00 |
| 23 |        1 |       2 | 2011-01-25 14:00:00 |
| 24 |        1 |       2 | 2011-01-25 14:12:00 |
| 25 |        1 |       2 | 2011-01-25 14:25:00 |
+----+----------+---------+---------------------+
25 rows in set (0.00 sec)

正如您所看到的，所有下载都发生在昨天或今天，并且由两个不同的用户执行。

现在，我们必须注意的是：（从数学上讲）在“2011-01-24 0:00”和“2011-01-25 23”之间存在无限多个 24 小时间隔（或任何其他持续时间的间隔） :59:59'。但由于服务器的精度是一秒，因此可以归结为 86,400 个间隔：

First interval:  2011-01-24 0:00:00 -> 2011-01-25 0:00:00
Second interval: 2011-01-24 0:00:01 -> 2011-01-25 0:00:01
Third interval: 2011-01-24 0:00:02 -> 2011-01-25 0:00:02
   .
   .
   .
86400th interval: 2011-01-24 23:59:59 -> 2011-01-25 23:59:59

因此，我们可以使用循环来迭代所有这些时间间隔，并计算每个用户和每个时间间隔的下载次数。当然，并非所有间隔都对我们感兴趣，因此我们可以通过使用表中的时间戳作为“间隔的开始”来跳过其中一些间隔。

这就是以下查询的作用。它使用表中的每个下载时间戳作为“间隔开始”，添加间隔持续时间，然后查询该间隔期间每个用户的下载数量。

SET @duration = '24:00:00';
SET @limit = 5;
SELECT * FROM 
    (SELECT t1.user_id, 
            t1.dl_date startOfPeriod, 
            ADDTIME(t1.dl_date,@duration) endOfPeriod, 
           (SELECT COUNT(1) 
            FROM downloads t2 
            WHERE t1.user_id = t2.user_id 
            AND t1.dl_date <= t2.dl_date 
            AND ADDTIME(t1.dl_date,@duration) >= t2.dl_date) count
     FROM downloads t1) t3 
WHERE count > @limit;

结果如下：

+---------+---------------------+---------------------+-------+
| user_id | startOfPeriod       | endOfPeriod         | count |
+---------+---------------------+---------------------+-------+
|       1 | 2011-01-24 09:00:00 | 2011-01-25 09:00:00 |     6 |
|       1 | 2011-01-24 09:30:00 | 2011-01-25 09:30:00 |     7 |
|       1 | 2011-01-24 09:35:00 | 2011-01-25 09:35:00 |     6 |
|       1 | 2011-01-24 10:00:00 | 2011-01-25 10:00:00 |     6 |
|       2 | 2011-01-24 08:00:00 | 2011-01-25 08:00:00 |    13 |
|       2 | 2011-01-24 12:00:00 | 2011-01-25 12:00:00 |    12 |
|       2 | 2011-01-24 12:01:00 | 2011-01-25 12:01:00 |    10 |
|       2 | 2011-01-24 12:02:00 | 2011-01-25 12:02:00 |     9 |
|       2 | 2011-01-24 12:03:00 | 2011-01-25 12:03:00 |     8 |
|       2 | 2011-01-24 12:00:00 | 2011-01-25 12:00:00 |    12 |
|       2 | 2011-01-24 12:04:00 | 2011-01-25 12:04:00 |     7 |
|       2 | 2011-01-24 12:05:00 | 2011-01-25 12:05:00 |     6 |
+---------+---------------------+---------------------+-------+
12 rows in set (0.00 sec)
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

sql

mysql

mysql选择时间跨度之间的行数 的相关文章

  • MySQL 错误 1172 - 结果包含多行

    在存储过程中运行查询时 我从 MySQL 收到此错误 错误代码 1172 结果包含多行 我理解错误 我正在做一个SELECT INTO var list 因此查询需要返回单行 当我使用LIMIT 1 or SELECT DISTINCT 错

  • MySQL 排序顺序 - 排序规则？

    我在对 MySQL 中的 char 字段进行排序时遇到困难 问题是重音字符与非重音字符混淆 例如 Abc bd Acc 我认为这可能与整理有关 所以我将表格的排序规则更改为utf8 ut8 bin 看完之后这个帖子 https stacko

  • Reporting Services 在哪里存储其日志文件

    最相关的谷歌结果似乎表明 为了访问日志 我们必须将您自己的日志表部署到数据库并制作报告服务写入它 http technet microsoft com en us library ms157403 aspx 简而言之 Reporting S

  • 如何通过子 POJO 的属性过滤复合 ManyToMany POJO？

    我有两个像这样的房间实体 Entity public class Teacher implements Serializable PrimaryKey autoGenerate true public int id ColumnInfo n

  • mysql 中的二进制、十六进制和八进制值

    我对在 mysql 数据库中使用二进制 十六进制和八进制系统非常感兴趣 首先 请给我一个建议 为什么我们在存储信息时需要它们 因为信息太多 或者为什么 另外 哪种类型的值必须存储在标记系统中 另外这里还有像 这是例子 gt SELECT 5

  • 非常大的字段会对 MySQL 数据库产生负面影响吗？

    我目前正在使用 Django 构建一个网站 并希望托管用户生物样式页面 该页面可能长达几 KB 这些字段不一定需要搜索 但在查找用户名时确实需要提供 将这些数据存储在数据库中会产生负面影响吗 如果我使用带有数据库链接的静态文本文件 我的服务

  • 如何在 SEQUELIZE (nodeJS) 中创建触发器？

    我正在尝试使用sequelize 创建一个触发器 主要思想是创建一个实例CONFIG创建后USER USER MODEL module exports function sequelize DataTypes var User sequel

  • 将庞大数据库从亚马逊RDS导出到本地mysql

    我在 Amazon RDS 上有一个 mysql 数据库 大约 600GB 数据 我需要将其移回本地专用服务器 但我不知道从哪里开始 每次我尝试初始化 sqldump 时它都会冻结 有没有办法将其移至 S3 甚至可能在开始下载之前将其分成更

  • SQL Server 连接其他表中不存在的位置

    Service Asset AssetService Id Name Id Name AssetId ServiceId

  • MySQL/PDO::quote() 尽管使用 PDO::PARAM_INT 参数，但仍在整数周围加上引号

    无论我传递给什么值 数据类型对 它都会出现 pdo gt quote value type 它总是将其引用为字符串 echo pdo gt quote foo PDO PARAM STR foo as expected echo pdo g

  • 获取带有计数的不同记录

    我有一张桌子personid and msg列 personid msg 1 msg1 2 msg2 2 msg3 3 msg4 1 msg2 我想得到总计msg对于每个personid 我正在尝试这个查询 select distinct

  • 删除数据库中的行后如何重新排序ID

    我正在使用 C 来制作具有 sql 数据库的程序 在数据库中我有一个名为Workers 它有一个自动增量和主键ID column 当我删除一条记录时 ID 之间会出现间隙 删除记录后如何重新排序 ID UPDATE 我要做的就是找到记录后将

  • posts_search 中的自定义查询

    如何使用此查询作为我的自定义搜索查询 add filter posts search my search is perfect 20 2 function my search is perfect search wp query sWord

  • 数字表与递归 CTE 生成一系列数字

    为什么使用数字表比使用递归 CTE 动态生成它们要快得多 在我的机器上 给定一张桌子numbers单列n 主键 包含从1到100000的数字 查询如下 select n from numbers 大约需要 400 毫秒才能完成 使用递归 C

  • 随机组合 MySQL 数据库中的两个单词

    我有一个包含名词和形容词的数据库 例如 id type word 1 noun apple 2 noun ball 3 adj clammy 4 noun keyboard 5 adj bloody ect 我想创建一个查询 它将抓取 10

  • Mysql插入表后不显示右单引号(’)

    我有一个名为 测试 的表 我插入了一行 其中包含unicode字符右单引号 0x2019在名称字段中 SQL insert into Testing values Sno Name Address insert into Testing v

  • Spark SQL 中的 SQL LIKE

    我正在尝试使用 LIKE 条件在 Spark SQL 中实现联接 我正在执行连接的行看起来像这样 称为 修订 Table A 8NXDPVAE Table B 4 8 NXD V 在 SQL Server 上执行联接 A revision

  • 我是否需要在外键上指定 ON DELETE NO ACTION？

    我有以下与 SQL Server 2012 一起使用的 DDL CREATE TABLE Subject SubjectId INT IDENTITY 1 1 NOT NULL Name NVARCHAR 50 Not NULL CONST

  • 如何获取自定义订单的结果？ [关闭]

    Closed 这个问题需要细节或清晰度 help closed questions 目前不接受答案 代替ASC or DESC 我希望我的查询结果采用特定的自定义顺序 例如 如果我想要的结果不是 A B C D 而是 P A L H 该怎么

  • 是否可以引用同一个表中的不同列？

    如果博客有一个 类别 表 如下所示 CREATE TABLE categories id INTEGER PRIMARY KEY AUTO INCREMENT parent id INTEGER NOT NULL name VARCHAR

随机推荐

热门标签