sql - Django 获取 2 小时内的所有模型实例
问题描述
作为 Django 初学者,我遇到了一个非常基本的问题:根据两行的日期差异过滤表。
我有以下模型:
class DataPoint(models.Model):
uid = models.CharField(max_length=128)
timestamp = models.DateTimeField(auto_now_add=True)
我想提取具有相同 UID 并且都在例如 2 小时范围内的数据点。我当然可以使用类似的东西:
DataPoint.objects.filter(uid=uid, timestamp__range=[timezone.now - timedelta(2), timezone.now])
但这只会从 2 小时内返回所有内容now
,但不会动态地给定 DataPoint 的时间戳。
所以这个数据集:
(1) DataPoint: 2021-03-07 12:40
(2) DataPoint: 2021-03-07 11:40
(3) DataPoint: 2021-03-07 10:50
(4) DataPoint: 2021-03-07 08:55
只会返回 (1), (2), (3) 与上面的查询,即使 (4) 仍然被认为是连接的,因为它在 (3) 的 2 小时时间范围内。
一直试图RECURSIVE
让它与 RAW SQL 一起使用,因为我认为这是使用 Django 的唯一方法,但它也没有按预期工作。
WITH RECURSIVE previous AS (
SELECT id, uid_id, timestamp FROM core_datapoints
WHERE id = %s
UNION ALL
SELECT s.id, s.uid_id, s.timestamp
FROM core_datapoints s
WHERE uid_id = s.uid_id AND (timestamp - INTERVAL '2 HOUR') <= s.timestamp
) SELECT id, timestamp FROM core_datapoints WHERE id IN (SELECT id FROM previous);
解决方案
您可以使用lead()
并跟踪(以相反的顺序)第一次休息时间超过 2 小时。然后的想法是枚举这样的中断 - 并选择没有的行:
select c.*
from (select c.*,
sum(case when next_timestamp > timestamp + interval '2 hour' then 1 else 0 end) over (partition by uid_id order by timestamp desc) as cnt_break
from (select c.*,
lead(timestamp) over (partition by uid_id order by timestamp) as next_timestamp
from core_datapoints c
) c
) c
where cnt_break = 0;
注意:您尚未指定数据库——日期/时间函数因数据库而异。我基于您的问题和标准 SQL 中的代码。