SQL Server 选择行以及时间戳与之接近的行

qpgpyjmq 于 2022-11-28 发布在其他

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

我有一个包含时间序列数据的表，希望根据某些“第一”条件选择一些行，以及时间戳与根据第一个条件选择的行接近的行。
示例表：
| 时间|瓦尔|
| - -|- -|
| 2022年11月1日09时55分|一个|
| 2022年11月1日09：55：57|一个|
| 2022年11月1日10时00分|一个|
| 2022年11月1日10时00分10秒|一个|
| 2022年11月1日10时00分20秒|一个|
| 2022年11月1日10时00分25秒|2个|
| 2022年11月1日10时00分30秒|一个|
| 2022年11月1日10时00分57秒|一个|
| 2022年11月1日10时20分15秒|一个|
| 2022年11月1日10时20分35秒|五个|
| 2022年11月1日10时20分55秒|一个|
| 2022年11月1日10时21分01秒|一个|
| 2022年11月1日10时21分30秒|一个|
从这个表中，我想要SELECT * FROM table WHERE val > 1加上与这些行接近的ts的任何行，例如a）在+/- 30秒时差内或b）在同一“绝对”分钟内（例如，从时间戳分钟的第00秒到第59秒）。
因此，作为选项a）的结果，我想得到：
| 时间|瓦尔|注解|
| - -|- -|- -|
| 2022年11月1日10时00分|一个|2022年11月1日10：00：25 30秒内的数据|
| 2022年11月1日10时00分10秒|一个|2022年11月1日10：00：25 30秒内的数据|
| 2022年11月1日10时00分20秒|一个|2022年11月1日10：00：25 30秒内的数据|
| 2022年11月1日10时00分25秒|2个|2022年11月1日10：00：25 30秒内的数据|
| 2022年11月1日10时00分30秒|一个|2022年11月1日10：20：35 30秒内的数据|
| 2022年11月1日10时20分15秒|一个|2022年11月1日10：20：35 30秒内的数据|
| 2022年11月1日10时20分35秒|五个|2022年11月1日10：20：35 30秒内的数据|
| 2022年11月1日10时20分55秒|一个|2022年11月1日10：20：35 30秒内的数据|
或者在选项B）的情况下，所以作为选项a）的结果，我想得到：
| 时间|瓦尔|注解|
| - -|- -|- -|
| 2022年11月1日10时00分|一个|与2022年11月1日同一分钟10：00：25|
| 2022年11月1日10时00分10秒|一个|与2022年11月1日同一分钟10：00：25|
| 2022年11月1日10时00分20秒|一个|与2022年11月1日同一分钟10：00：25|
| 2022年11月1日10时00分25秒|2个|与2022年11月1日同一分钟10：00：25|
| 2022年11月1日10时00分30秒|一个|与2022年11月1日同一分钟10：20：35|
| 2022年11月1日10时00分57秒|一个|与2022年11月1日同一分钟10：20：35|
| 2022年11月1日10时20分15秒|一个|与2022年11月1日同一分钟10：20：35|
| 2022年11月1日10时20分35秒|五个|与2022年11月1日同一分钟10：20：35|
| 2022年11月1日10时20分55秒|一个|与2022年11月1日同一分钟10：20：35|
我尝试使用子查询和DATEDIFF来实现选项a），但似乎不起作用，因为在子查询中不可能使用DATEDIFF和类似的函数。

SELECT * FROM table t WHERE ABS(DATEDIFF(second, t.ts,  (SELECT ts FROM table WHERE val > 1))) <= 30

如果只使用SQL和大型表，最好的方法是什么？

sql-server

来源：https://stackoverflow.com/questions/74530977/select-rows-and-those-with-timestamps-close-to-it

2条答案

按热度按时间

z9zf31ra1#

看看您的第一个需求（一个问题实际上应该只关注一个问题），可以实现这一点的一种方法是使用 exists 半连接，将每个ts日期与 exists 条件中导出的合格最小和最大日期进行比较。
你的第二个（或任何其他）标准可以以非常相似的方式工作。

select t.*
from t
where exists (
  select * from t t2
    cross apply (values(
      case when val > 1 then DateAdd(second, -30, ts) end,
      case when val > 1 then DateAdd(second, 30, ts) end)
    )r(rmin,rmin)
  where t.ts >= rmin and t.ts <= rmin
) order by ts;

请参见演示Fiddle

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

oewdyzsn2#

我还不太清楚您想要什么，但下面是一个使用DATEDIFF计算时间戳之间分钟差的示例（请参见下面的fiddle）：
创建表：

CREATE TABLE events (
  event_ts DATETIME,
  event_value INTEGER
);

用数据填充（我添加了一行以更好地说明查询）：

INSERT INTO events VALUES
  ('2022-11-01 10:00:00', 1),
  ('2022-11-01 10:00:10', 1),
  ('2022-11-01 10:00:20', 1),
  ('2022-11-01 10:00:25', 2),
  ('2022-11-01 10:00:30', 1),
  ('2022-11-01 10:00:55', 1),
  ('2022-11-01 10:20:15', 1),
  ('2022-11-01 10:20:35', 5),
  ('2022-11-01 10:20:55', 1),
  ('2022-11-01 10:30:55', 6)

查询：

WITH events_filtered AS (
  SELECT *
  FROM events
  WHERE event_value > 1
)

SELECT
  *,
  DATEDIFF(mi, e1.event_ts, e2.event_ts) AS event_minute_difference
FROM events_filtered AS e1
INNER JOIN events_filtered AS e2
ON e1.event_ts < e2.event_ts

为了清楚起见，我将这两个条件分为CTE和final查询（但是您可以也可能应该将其包含在同一个查询中）。
| 事件_ts|事件值|事件_ts|事件值|事件分钟差|
| - -|- -|- -|- -|- -|
| 2022年11月1日10时00分25.000秒|2个|2022年11月1日10时20分35.000秒|五个|二十个|
| 2022年11月1日10时00分25.000秒|2个|2022年11月1日10时30分55.000秒|六个|三十|
| 2022年11月1日10时20分35.000秒|五个|2022年11月1日10时30分55.000秒|六个|10个|
如果不真正了解更多有关问题的信息，我就无法对性能（以及改进性能的机会）进行评论。

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

我来回答

SQL Server 选择行以及时间戳与之接近的行

2条答案

相关问题

热门标签

最新问答