mysql 将时间戳四舍五入到最近的半小时，而不忽略丢失的数据

qpgpyjmq 于 2023-05-05 发布在 Mysql

关注(0)|答案(2)|浏览(172)

情况：

想象一下，某个脚本在完成时将当前时间戳和其他一些数据插入到MySQL表中。它每三十分钟执行一次，有时根本不执行，从而在数据中留下空白。

目标：

有一个查询，获取所有数据的时间戳舍入到最近的半小时和空行（所有字段，除了时间戳应该是空的）时，没有数据。

限制条件：

无论是表结构、数据本身还是脚本都不能更改。

问题：

我能想到的唯一能产生预期结果的解决方案是不能扩展的。目前，实际的表大约有50，000行，完成查询已经花费了超过15分钟的时间。

示例：

CREATE TABLE IF NOT EXISTS `statuses` (
    `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `status` INT NOT NULL DEFAULT '0',
    PRIMARY KEY (`timestamp`)
);

INSERT
    IGNORE INTO `statuses` (`timestamp`, `status`)
VALUES
    ('2023-01-01 00:03:34', '164850'),
    ('2023-01-01 00:31:23', '794088'),
    ('2023-01-01 03:31:28', '686754'),
    ('2023-01-01 04:01:15', '684711'),
    ('2023-01-01 05:31:35', '116777'),
    ('2023-01-01 06:01:52', '469332'),
    ('2023-01-01 06:31:55', '816300'),
    ('2023-01-01 08:33:53', '309583'),
    ('2023-01-01 09:03:54', '847976'),
    ('2023-01-01 09:31:33', '812517');

WITH RECURSIVE `timestamps`(`timestamp`) AS (
    SELECT
        (
            SELECT
                FROM_UNIXTIME(
                    UNIX_TIMESTAMP(MIN(`timestamp`)) - MOD(UNIX_TIMESTAMP(MIN(`timestamp`)), 1800)
                )
            FROM
                `statuses`
        )
    UNION
    ALL
    SELECT
        DATE_ADD(`timestamp`, INTERVAL 30 MINUTE)
    FROM
        `timestamps`
    WHERE
        `timestamp` < (
            SELECT
                FROM_UNIXTIME(
                    UNIX_TIMESTAMP(MAX(`timestamp`)) - MOD(UNIX_TIMESTAMP(MAX(`timestamp`)), 1800)
                )
            FROM
                `statuses`
        )
)
SELECT
    `t`.`timestamp`,
    `s`.`status`
FROM
    `timestamps` AS `t`
    LEFT OUTER JOIN `statuses` AS `s` ON `t`.`timestamp` = FROM_UNIXTIME(
        UNIX_TIMESTAMP(`s`.`timestamp`) - MOD(UNIX_TIMESTAMP(`s`.`timestamp`), 1800)
    )
ORDER BY
    `t`.`timestamp` ASC;

mysql

来源：https://stackoverflow.com/questions/76177182/round-timestamps-to-the-nearest-half-hour-without-omitting-missing-data

2条答案

按热度按时间

kmbjn2e31#

您的查询看起来很好。不过，我会先将原始时间戳转换为半小时时间戳，然后在此基础上工作。在递归查询中，我只选择最大时间戳一次。我不知道这是否会有很大的不同。
还有一点我首先聚合数据，以避免重复（例如05：16和05：44，均四舍五入至05：30）。如果保证两个时间戳至少相隔30分钟，则不需要这样做。

WITH RECURSIVE data AS
(
  SELECT
    FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(`timestamp`) / 1800) * 1800) as ts,
    MAX(status) AS status
  FROM statuses
  GROUP BY FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(`timestamp`) / 1800) * 1800)
)
, timestamps (ts, tsmax) AS
(
  SELECT MIN(ts), MAX(ts) FROM data
  UNION ALL
  SELECT ts + INTERVAL 30 MINUTE, tsmax FROM timestamps WHERE ts < tsmax
)
SELECT t.ts, d.status
FROM timestamps t
LEFT OUTER JOIN data d ON d.ts = t.ts
ORDER BY t.ts;

演示：https://dbfiddle.uk/78s6-jzC

赞(0）回复(0）举报 2023-05-05

6bc51xsx2#

我们可以稍微简化递归部分。正如Thorsten Kettner提到的，没有必要在每次迭代中重新选择最大状态日期，我们可以在锚中这样做-我还认为我们甚至不需要对最大日期进行舍入（尽管这是微优化）。
当涉及到外部查询时，我不建议在状态时间戳上应用函数;这是表的主键，我们确实希望对它运行SARGEable predicate ：让我们使用一个半开间隔（我们不需要在这里再次执行unixtime转换）：

with recursive timestamps (ts, max_ts) as (
    select from_unixtime(floor(unix_timestamp(min(ts)) / 1800) * 1800) ts, max(ts) max_ts
    from statuses
    union all
    select ts + interval 30 minute, max_ts from timestamps where ts + interval 30 minute <= max_ts
)
select t.ts, s.status
from timestamps t
left join statuses s on s.ts >= t.ts and s.ts < t.ts + interval 30 minute
order by t.ts

请注意，我将列timestamp重命名为ts，以避免与相应的SQL关键字发生冲突。
如果这还不够好，那么一个替代方案是在日历表中 * 具体化 * 递归查询的结果。
您通常会使用表覆盖很长一段时间。您可以使用递归查询来创建它：

create table timestamp_calendar as 
with recursive timestamps as (
    select '2022-01-01 00:00:00' ts, '2022-01-02 12:00:00' max_ts -- short period for testing
    union all
    select ts + interval 30 minute, max_ts 
    from timestamps
    where ts < max_ts
)
select * from timestamps;

我们可以声明一个主键来使底层索引受益：

alter table timestamp_calendar add primary key (ts);

然后我们可以在查询中使用该表。理想情况下，您提前知道您要查找的日期范围。但如果你不这样做，我们可以带来最小/最大状态日期，并使用它们来预过滤日历表。

select t.ts, s.status
from timestamp_calendar t
inner join (select min(ts) min_ts, max(ts) max_ts from statuses) x 
    on  t.ts >  x.min_ts - interval 30 minute 
    and t.ts <= x.max_ts
left join statuses s 
    on  s.ts >= t.ts 
    and s.ts <  t.ts + interval 30 minute
order by t.ts

这里有一个**demo on DB Fiddle**

赞(0）回复(0）举报 2023-05-05

我来回答

mysql 将时间戳四舍五入到最近的半小时，而不忽略丢失的数据

2条答案

相关问题

热门标签

最新问答