mariadb 从表的updated_at timestamp列中获取过去X天的MIN聚合字段

ndasle7k 于 2022-11-08 发布在其他

关注(0)|答案(1)|浏览(110)

给定以下表结构和示例数据：

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的金额中会添加一些额外的内容）。

mariadb

来源：https://stackoverflow.com/questions/72604590/get-min-aggregate-field-from-table-for-the-past-x-days-from-updated-at-timestamp

1条答案

按热度按时间

ztmd8pv51#

目前还不清楚如果有多个external_id值会发生什么。您似乎只希望返回一行？（如果不是这样，请改进示例，以便在有多个不同的external_id值时包含所需的结果。）
如果您只想返回一个值，则只需ORDER BY <something> LIMIT 1
但是，我将***假设***您只需要一个值***每个external_id***。

WITH
  filtered_sorted
AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY external_id
                           ORDER BY CASE WHEN sub_id <> 0 THEN 0 ELSE 1 END,
                                    amount
                      )
                        AS rn
  FROM
    records  AS r
  WHERE
    updated_at >= (SELECT COALESCE(MAX(updated_at), DATE(NOW()) - INTERVAL 30 DAY)
                     FROM records
                    WHERE updated_at  <= DATE(NOW()) - INTERVAL 30 DAY
                      AND external_id  = r.external_id
                  )
)
SELECT
  *
FROM
  filtered_sorted
WHERE
  rn = 1

这是基于这样的概念，即30天前***当天或之前***的最近一行仍然有效，应包括在最低金额的考虑中。

对于每个external_id ...
忽略updated_at***之后***30天前开始的所有记录
返回最近剩余的updated_at（可能正好是30天前一天的开始时间）
如果找不到这样的行，则返回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

赞(0）回复(0）举报 2022-11-08

我来回答

mariadb 从表的updated_at timestamp列中获取过去X天的MIN聚合字段

1条答案

相关问题

热门标签

最新问答