时间戳

首页 > 解决方案 > 如何进行 SQL 查询以选择具有动态间隔的行

mysql - 如何进行 SQL 查询以选择具有动态间隔的行

问题描述

我有一个具有以下结构的表货币历史：

+----+-------------+---------------+---------------------+
| id | currency_id | price_usd     | created_at          |
+----+-------------+---------------+---------------------+
|  1 |           1 | 8719.79000000 | 2019-11-12 15:52:06 |
|  2 |           2 |  185.36000000 | 2019-11-12 15:52:06 |
|  3 |           3 |    0.27157609 | 2019-11-12 15:52:06 |
|  4 |           4 |  287.40000000 | 2019-11-12 15:52:06 |
|  5 |           5 |    1.00000000 | 2019-11-12 15:52:06 |
|  1 |           1 | 8719.79000000 | 2019-11-12 15:58:06 |
|  2 |           2 |  185.36000000 | 2019-11-12 15:58:06 |
|  3 |           3 |    0.27157609 | 2019-11-12 15:58:06 |
|  4 |           4 |  287.40000000 | 2019-11-12 15:58:06 |
|  5 |           5 |    1.00000000 | 2019-11-12 15:58:06 |
+----+-------------+---------------+---------------------+

现在我需要对这个表做一些日期过滤，例如，我需要在过去 7 天每小时选择 1 行。列created_at是动态的。我怎样才能以正确的方式做到这一点？

UPD：mysql 数据库，InnoDB。

UPD2：预期结果是：

+----+-------------+---------------+---------------------+
| id | currency_id | price_usd     | created_at          |
+----+-------------+---------------+---------------------+
|  1 |           1 | 8719.79000000 | 2019-11-12 15:52:06 |
|  2 |           1 | 8719.79000000 | 2019-11-12 16:52:06 |
|  3 |           1 | 8719.79000000 | 2019-11-12 17:52:06 |
|  4 |           1 | 8719.79000000 | 2019-11-12 18:52:06 |
|  5 |           1 | 8719.79000000 | 2019-11-12 19:52:06 |
|  6 |           1 | 8719.79000000 | 2019-11-12 20:58:06 |
|  7 |           1 | 8719.79000000 | 2019-11-12 21:58:06 |
|  8 |           1 | 8719.79000000 | 2019-11-12 22:58:06 |
|  9 |           1 | 8719.79000000 | 2019-11-12 23:58:06 |
| 10 |           1 | 8719.79000000 | 2019-11-13 00:58:06 |
+----+-------------+---------------+---------------------+

行间间隔不少于1小时

标签： mysqlsql

解决方案

您可以使用row_number()日期函数。

select ch.*
from (select ch.*,
             row_number() over (partition by date(created_at), hour(created_at) order by created_at) as seqnum
      from currency_history ch
      where created_at >= curdate() - interval 6 day
     ) ch
where seqnum = 1;

推荐阅读