使用sparklyr按timestamp+ID查找缺失行及缺失位置

针对你需要按elemuid分组定位缺失timestamp的需求，我整理了两个常用的实现方案，不管是用SQL还是Python都能轻松解决～

方案一：SQL实现（以PostgreSQL为例） #

如果你的数据存在数据库里，用SQL窗口函数+连续时间生成的方式最直接。核心思路是先拿到每个ID的时间范围，生成该范围内所有分钟级的连续时间，再和原表左连接，找出没有匹配上的时间就是缺失的。

以下是针对你测试数据的SQL代码：

WITH id_time_ranges AS (
    -- 先获取每个elemuid的最小和最大timestamp
    SELECT
        elemuid,
        MIN(timestamp) AS min_ts,
        MAX(timestamp) AS max_ts
    FROM your_table
    GROUP BY elemuid
),
continuous_times AS (
    -- 为每个ID生成时间范围内的所有分钟级连续时间
    SELECT
        r.elemuid,
        generate_series(r.min_ts, r.max_ts, '1 minute'::interval) AS missing_ts
    FROM id_time_ranges r
)
-- 左连接原表，筛选出原表中不存在的时间
SELECT
    ct.elemuid,
    ct.missing_ts AS timestamp
FROM continuous_times ct
LEFT JOIN your_table t ON ct.elemuid = t.elemuid AND ct.missing_ts = t.timestamp
WHERE t.timestamp IS NULL
ORDER BY ct.elemuid, ct.missing_ts;

执行后就能得到你期望的结果：

elemuid timestamp
1232 2018-02-10 22:59:00
1674 2018-02-10 22:36:00
1674 2018-02-10 22:38:00

如果是其他数据库（比如MySQL），可以用递归CTE来生成连续时间，逻辑是一样的，只是生成连续时间的语法略有不同。

方案二：Python Pandas实现 #

如果你的数据在本地，用Pandas处理更灵活。核心是按ID分组后，生成该组完整的时间序列，再对比原数据找出缺失项。

代码示例如下：

import pandas as pd

# 构造测试数据
data = {
    'elemuid': [1232, 1232, 1232, 1674, 1674, 1674, 1674],
    'timestamp': [
        '2018-02-10 23:00:00',
        '2018-02-10 23:01:00',
        '2018-02-10 22:58:00',
        '2018-02-10 22:40:00',
        '2018-02-10 22:39:00',
        '2018-02-10 22:37:00',
        '2018-02-10 22:35:00'
    ]
}
df = pd.DataFrame(data)
# 把timestamp转为datetime类型
df['timestamp'] = pd.to_datetime(df['timestamp'])

# 定义函数，找出每个分组的缺失timestamp
def find_missing_ts(group):
    # 生成该组的完整时间序列（分钟频率）
    full_ts = pd.date_range(start=group['timestamp'].min(), end=group['timestamp'].max(), freq='T')
    # 找出不在原组中的时间
    missing_ts = full_ts[~full_ts.isin(group['timestamp'])]
    # 返回结果DataFrame
    return pd.DataFrame({'elemuid': [group.name]*len(missing_ts), 'timestamp': missing_ts})

# 按elemuid分组处理，合并结果
missing_df = df.groupby('elemuid').apply(find_missing_ts).reset_index(drop=True)
# 按ID和时间排序
missing_df = missing_df.sort_values(['elemuid', 'timestamp']).reset_index(drop=True)

print(missing_df)

运行后输出的结果就是你需要的缺失timestamp列表：

elemuid           timestamp
0     1232 2018-02-10 22:59:00
1     1674 2018-02-10 22:36:00
2     1674 2018-02-10 22:38:00

这两个方案都能精准按ID定位缺失的timestamp，你可以根据自己的使用场景选择～

内容的提问来源于stack exchange，提问作者user60856839