Django中基于timestamp的复杂去重查询实现求助

方法1：使用SQL窗口函数（推荐） #

这种时间序列去重场景最适合用窗口函数实现。以PostgreSQL为例，通过LAG()函数获取每个实例的前一条记录时间戳，筛选出与前一个实例间隔超过30分钟的记录后统计数量，就能得到目标结果。

示例SQL语句：

WITH ranked_entities AS (
    SELECT
        timestamp,
        LAG(timestamp) OVER (ORDER BY timestamp) AS prev_timestamp
    FROM entity
    WHERE timestamp >= NOW() - INTERVAL '24 hours'
)
SELECT COUNT(*)
FROM ranked_entities
WHERE prev_timestamp IS NULL OR timestamp - prev_timestamp > INTERVAL '30 minutes';

逻辑说明：

• 用LAG()按时间排序后，给每个实例标记前一个实例的时间戳
• 保留两种记录：第一条实例（无前序记录）、与前序实例间隔超过30分钟的实例
• 最终统计这些保留记录的数量，就是你需要的计数

如果用ORM（比如Django），可以通过窗口函数注解实现：

from django.db.models import F, Window, ExpressionWrapper, DurationField, Q
from django.db.models.functions import Lag
from django.utils import timezone

# 给24小时内的实例标注前序时间戳
annotated_entities = Entity.objects.filter(
    timestamp__gte=timezone.now() - timezone.timedelta(hours=24)
).annotate(
    prev_timestamp=Window(
        expression=Lag('timestamp'),
        order_by=F('timestamp').asc()
    )
)

# 计算时间差并筛选符合条件的实例
filtered = annotated_entities.annotate(
    time_diff=ExpressionWrapper(
        F('timestamp') - F('prev_timestamp'),
        output_field=DurationField()
    )
).filter(
    Q(prev_timestamp__isnull=True) | Q(time_diff__gt=timezone.timedelta(minutes=30))
)

target_count = filtered.count()

方法2：迭代处理（适合小数据量场景） #

如果数据量不大，可以直接排序后遍历过滤：

from datetime import timedelta
from django.utils import timezone

entities = Entity.objects.filter(
    timestamp__gte=timezone.now() - timedelta(hours=24)
).order_by('timestamp')

target_count = 0
last_kept_time = None

for entity in entities:
    if last_kept_time is None or entity.timestamp - last_kept_time > timedelta(minutes=30):
        target_count += 1
        last_kept_time = entity.timestamp

• 数据库官方文档的窗口函数章节：比如PostgreSQL《Window Functions》、MySQL《Window Functions》，是处理这类时间序列分组、去重统计的核心工具
• ORM框架的进阶查询文档：比如Django的《Window functions》《Complex lookups with Q objects》章节，Flask-SQLAlchemy的《Advanced Querying》部分
• 数据库性能调优教程：重点关注时间范围过滤、窗口函数的性能优化技巧，适配大数据量场景

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