给定以下表结构和示例数据:
CREATE TABLE IF NOT EXISTS `records` (
`id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
`external_id` int unsigned NOT NULL,
`sub_id` int unsigned DEFAULT 0,
`amount` bigint unsigned NOT NULL,
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) DEFAULT CHARSET=utf8;
INSERT INTO `records` (`external_id`, `sub_id`,`amount`, `updated_at`) VALUES
(1, 0, 160, '2022-01-13 16:00:00'),
(1, 1001, 150, '2022-01-13 16:40:00'),
(1, 1002, 170, '2022-06-13 16:40:00'),
(1, 1003, 170, '2022-06-13 16:40:00');
对于给定的external_id
,我尝试使用时间戳字段updated_at
获取过去X(假设30天)的amount
的MIN
值,并具有以下约束:
- 如果过去30天内没有记录(更改),则最新记录仍然有效,
- 给定
external_id
的每个新记录将“取消并替换”先前的记录, - 如果同时存在
sub_id = 0
和sub_id <> 0
的记录,则对于相同的给定external_id
,sub_id <> 0
的记录将优先。
因此,对上述数据的查询应返回150
。
一把小提琴和我尝试:https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=e4ddd6b55dbccf607633c1cf7d9cd4ef
附加信息(稍后编辑)
为了让您更好地了解整个概念,请参阅:每次对amount
字段进行更新时,都会在records
表中创建一条新记录(以创建历史日志)。
我的任务是查询过去30天的MIN
金额。一些external_id
记录也包含sub_id
。无论是否存在sub_id
,都会创建external_id
的记录。包含sub_id
的金额通常更大(product_id
的金额中会添加一些额外的内容)。
1条答案
按热度按时间ztmd8pv51#
目前还不清楚如果有多个
external_id
值会发生什么。您似乎只希望返回一行?(如果不是这样,请改进示例,以便在有多个不同的external_id
值时包含所需的结果。)如果您只想返回一个值,则只需
ORDER BY <something> LIMIT 1
但是,我将***假设***您只需要一个值***每个
external_id
***。这是基于这样的概念,即30天前***当天或之前***的最近一行仍然有效,应包括在最低金额的考虑中。
external_id
...updated_at
***之后***30天前开始的所有记录updated_at
(可能正好是30天前一天的开始时间)updated_at
之后的所有行那么
ROW_NUMBER()
优先选择sub_id
不为零的行,然后选择amount
最小的行。WHERE
子句之后external_id
应该有它自己的行号序列 (用PARTITION BY
external_id '实现)sub_id <> 0
的行应排在具有sub_id = 0
* 的任何行之前(通过ORDER BY CASE WHEN sub_id <> 0 THEN 0 ELSE 1 END
实现)*amount
值的行应排在前面 (通过'ORDER BY amount实现。)然后,只返回分配的行号为1的行
external_id
分区updated_at
筛选sub_id <> 0
、amount
排序external_id
一行)*Demo on dbfiddle.uk