python按日期水平切割mysql,python – 带有MySQL后端的Django – 按时间范围分组

我有这个简单的模型：

models.py

class Ping(models.Model):

online = models.BooleanField()

created = models.DateTimeField(db_index=True, default=timezone.now)

def __str__(self):

return f'{self.online}, {self.created}'

它给了我以下结果：

mysql [lab]> SELECT * FROM myapp_ping;

+----+--------+----------------------------+

| id | online | created |

+----+--------+----------------------------+

| 1 | 1 | 2018-08-02 13:34:09.435292 |

| 2 | 1 | 2018-08-02 13:35:09.520200 |

| 3 | 0 | 2018-08-02 13:36:09.540638 |

| 4 | 0 | 2018-08-02 13:37:10.529783 |

| 5 | 1 | 2018-08-02 13:38:09.779012 |

| 6 | 1 | 2018-08-02 13:39:09.650365 |

| 7 | 1 | 2018-08-02 13:40:09.625543 |

| 8 | 1 | 2018-08-02 13:41:09.892196 |

| 9 | 1 | 2018-08-02 13:42:09.802186 |

| 10 | 1 | 2018-08-02 13:43:09.864551 |

| 11 | 1 | 2018-08-02 13:44:09.960962 |

| 12 | 1 | 2018-08-02 13:45:09.891947 |

| 13 | 0 | 2018-08-02 13:46:09.141727 |

| 14 | 0 | 2018-08-02 13:47:09.142030 |

| 15 | 0 | 2018-08-02 13:48:09.160942 |

| 16 | 0 | 2018-08-02 13:49:09.152879 |

| 17 | 0 | 2018-08-02 13:50:09.280246 |

| 18 | 1 | 2018-08-02 13:51:09.363184 |

| 19 | 1 | 2018-08-02 13:52:09.405863 |

| 20 | 1 | 2018-08-02 13:53:09.403251 |

+----+--------+----------------------------+

20 rows in set (0.00 sec)

有没有办法得到类似于此的输出(在线的范围是假的)：

停机时间：

from | to | duration

2018-08-02 13:36:09 | 2018-08-02 13:37:10 | 1 minute and 1 second

2018-08-02 13:46:09 | 2018-08-02 13:50:09 | 4 minutes and 0 seconds

我不确定这是否可以用Django ORM完成,或者它需要一个原始的MySQL查询来使用像CASE或IF语句这样的东西？

更新：8月8日星期三15:13:15 UTC 2018

所以我从@AKX answer获得了两种解决方案的概念证明：

models.py

class PingManager(models.Manager):

def downtime_python(self):

queryset = super().get_queryset().filter(created__gt=timezone.now() - timezone.timedelta(days=30))

offline = False

ret = []

for entry in queryset:

if not entry.online and not offline:

offline = True

_ret = {'start': str(entry.created)}

if entry.online and offline:

_ret.update({'end': str(entry.created)})

ret.append(_ret)

offline = False

return ret

def downtime_sql(self):

queryset = super().get_queryset().filter(created__gt=timezone.now() - timezone.timedelta(days=30))

offline = queryset.filter(online=False).order_by('created').first()

last = queryset.order_by('created').last()

ret = []

if offline:

online = queryset.filter(created__gt=offline.created, online=True).order_by('created').first()

ret.append({'start': str(offline.created), 'end': str(online.created)})

while True:

offline = queryset.filter(created__gt=online.created, online=False).order_by('created').first()

if offline:

online = queryset.filter(created__gt=offline.created, online=True).order_by('created').first()

if (online and offline) and online.created < last.created:

ret.append({'start': str(offline.created), 'end': str(online.created)})

continue

else:

break

return ret

class Ping(models.Model):

online = models.BooleanField()

created = models.DateTimeField(db_index=True, default=timezone.now)

objects = PingManager()

def __str__(self):

return f'{self.online}, {self.created}'

问题：

>我应该为此创建静态方法,还是自定义管理器是正确的解决方案？

>如果两个计算都在内存中运行,为什么执行次数之间有如此巨大的差异？有没有办法改进并使它更pythonic python等效方法？

测试：

# python manage.py shell

Python 3.6.5 (default, Apr 10 2018, 17:08:37)

Type 'copyright', 'credits' or 'license' for more information

IPython 6.5.0 -- An enhanced Interactive Python. Type '?' for help.

In [1]: from myapp.models import Ping

In [2]: Ping.objects.downtime_sql()[0]

Out[2]:

{'start': '2018-07-13 16:32:16.009356+00:00',

'end': '2018-07-13 16:33:15.942784+00:00'}

In [3]: Ping.objects.downtime_python()[0]

Out[3]:

{'start': '2018-07-13 16:32:16.009356+00:00',

'end': '2018-07-13 16:33:15.942784+00:00'}

In [4]: Ping.objects.downtime_sql() == Ping.objects.downtime_python()

Out[4]: True

In [5]: import timeit

In [6]: timeit.timeit(stmt=Ping.objects.downtime_python, number=1)

Out[6]: 5.720254830084741

In [7]: timeit.timeit(stmt=Ping.objects.downtime_sql, number=1)

Out[7]: 0.25946347787976265

解决方法:

扩展我的评论：

I’m not sure even SQL case/if statements can get you that result, since the result rows depend on previous rows. This is easy to do procedurally in Python though.

>显而易见的方法是循环遍历Ping.objects.all()(或Ping.objects.iterator())并跟踪在线变量以形成所需的“条纹”.这有一个缺点,你真的需要循环每个对象,这最终将是缓慢的(和/或耗尽你的记忆).

>使用更多查询但内存更少的更复杂的方法是找到第一个脱机的Ping对象,然后找到再次联机的下一个(时间上)Ping对象 – 这将形成一个条纹.然后冲洗并重复此操作,直到用完Ping物体进行检查.

编辑

所以是的,这是一个(相当优雅,如果你不介意我说)方法2的具体实现(在https://github.com/akx/so51656477找到完整的测试回购)：

class PingQuerySet(models.QuerySet):

def streaks(self):

queryset = self.values_list('created', 'online').order_by('created')

entry = queryset.first()

while entry:

next_entry = queryset.filter(created__gt=entry[0], online=(not entry[1])).first()

yield (entry, next_entry)

entry = next_entry

它是一个2元组元组的生成器：((start_timestamp,start_online),(end_timestamp,end_online)| None).

例如,要在过去10天内获得上/下或下/下对,

for start, end in Ping.objects.filter(created__gt=now() - timedelta(days=10)).streaks():

print(start, end)

会印出类似的东西

[...snip...]

(datetime.datetime(2018, 8, 8, 8, 10, 12, 943500), False) (datetime.datetime(2018, 8, 8, 10, 10, 12, 943500), True)

(datetime.datetime(2018, 8, 8, 10, 10, 12, 943500), True) (datetime.datetime(2018, 8, 8, 11, 10, 12, 943500), False)

(datetime.datetime(2018, 8, 8, 11, 10, 12, 943500), False) (datetime.datetime(2018, 8, 8, 11, 40, 12, 943500), True)

(datetime.datetime(2018, 8, 8, 11, 40, 12, 943500), True) (datetime.datetime(2018, 8, 8, 12, 40, 12, 943500), False)

(datetime.datetime(2018, 8, 8, 12, 40, 12, 943500), False) (datetime.datetime(2018, 8, 8, 16, 40, 12, 943500), True)

(datetime.datetime(2018, 8, 8, 16, 40, 12, 943500), True) (datetime.datetime(2018, 8, 8, 17, 40, 12, 943500), False)

(datetime.datetime(2018, 8, 8, 17, 40, 12, 943500), False) (datetime.datetime(2018, 8, 8, 18, 10, 12, 943500), True)

(datetime.datetime(2018, 8, 8, 18, 10, 12, 943500), True) (datetime.datetime(2018, 8, 8, 19, 40, 12, 943500), False)

(datetime.datetime(2018, 8, 8, 19, 40, 12, 943500), False) (datetime.datetime(2018, 8, 8, 23, 10, 12, 943500), True)

(datetime.datetime(2018, 8, 8, 23, 10, 12, 943500), True) (datetime.datetime(2018, 8, 9, 0, 10, 12, 943500), False)

(datetime.datetime(2018, 8, 9, 0, 10, 12, 943500), False) (datetime.datetime(2018, 8, 9, 3, 10, 12, 943500), True)

(datetime.datetime(2018, 8, 9, 3, 10, 12, 943500), True) (datetime.datetime(2018, 8, 9, 3, 40, 12, 943500), False)

(datetime.datetime(2018, 8, 9, 3, 40, 12, 943500), False) (datetime.datetime(2018, 8, 9, 5, 10, 12, 943500), True)

(datetime.datetime(2018, 8, 9, 5, 10, 12, 943500), True) (datetime.datetime(2018, 8, 9, 5, 40, 12, 943500), False)

(datetime.datetime(2018, 8, 9, 5, 40, 12, 943500), False) (datetime.datetime(2018, 8, 9, 7, 10, 12, 943500), True)

(datetime.datetime(2018, 8, 9, 7, 10, 12, 943500), True) None

一些说明：

>最后一个结束值可能是None,这意味着机器仍然处于启动或关闭状态(取决于启动元组的状态值).

>如果您只关心机器停机的时间,只需忽略启动元组状态值为True的对.

>由于这是一个生成器,你可以在你有足够的数据时停止迭代它,它不会再进一步​​查询.

>由于这是一个QuerySet扩展方法,您可以根据需要添加其他过滤器(只要它们不在线过滤).例如,如果你有一个主机字段,Ping.objects.filter(host =’example.com’).streaks().

标签：python,mysql,django,django-models

来源： https://codeday.me/bug/20190527/1161922.html