从Django查询数据 [英] Querying data from Django
问题描述
这是我的模型结构如下所示:
class Visitor(models.Model):
id = models .AutoField(primary_key = True)
类会话(models.Model):
id = models.AutoField(primary_key = True)
visit = models.ForeignKey(Visitor)
sequence_no = models.IntegerField(null = False)
class Track(models.Model):
id = models.AutoField(primary_key = True)
session = models ForeignKey(Session)
action = models.ForeignKey(Action)
when = models.DateTimeField(null = False,auto_now_add = True)
sequence_no = models.IntegerField(null = False)
class Action(models.Model):
id = models.AutoField(primary_key = True)
url = models.CharField(max_length = 65535,null = False)
host = models.IntegerField(null = False)
如你所见,每个访客
有多个会话
;每个会话
有多个轨道
,每个轨道
有一个动作
。轨道总是通过会话
和 sequence_no
升序排列。 访问者
网站上的平均时间(即特定 Action.host
)是 Track.when
(时间)最高和最低 Track.sequence_no
除以会话数量
访客
。
我需要计算网站上访问者的平均时间,这是每个访问者在 Action.site
除以访问者数量。
我可以使用SQL查询这个,但我希望将我的查询作为Djangonic保持尽可能,仍然非常迷失与复杂的查询。
对于一个具体的 Action
您可以收集有关会话的有趣数据:
from django.db.models import Min,Max
from yourapp.models import *
host = 1#我想你想为每个站点计算
sessions = list(Session.objects.filter(
track__action__host = host,
).annotate(
start = Min('track__when'),
end = Max('track__when'),
).values('visit_id','start'结束'))
你会得到以下一行:
[
{'visit_id':1,'start':datetime(...),'end':datetime(...)},
{'visit_id':1,'start':datetime ),'end':datetime(...)},
{'visit_id':2,'start':datetime(...),'end':datetime(...)},
....
]
现在只是获得所需结果的一个问题来自数据:
number_of_visitors = len(set(s ['visit_id'] for s in sessions))
pre>
total_time = sum((s ['end'] - s ['start'])会话中s的total_seconds()
average_time_spent = total_time / number_of_visitors
另一种方法是使用两个查询而不是一个,并避免使用
len(set(...))
片段:sessions = Session.objects.filter(
track__action__host = host,
)。注释(
start = Min('track__when'),
end = Max('track__when'),
)
number_of_visitors = sessions.values('visit_id ).distict()。count()
total_time = sum((s ['end'] - s ['start'])total_seconds()
在会话中的s('start' ,'end'))
没有办法做实际的计算字段,禁止提供的汇总,所以您可以在原始SQL中执行此操作,或者按照这样的代码执行。
至少提出的解决方案尽可能使用Django的ORM。
Here's what my model structure looks like:
class Visitor(models.Model): id = models.AutoField(primary_key=True) class Session(models.Model): id = models.AutoField(primary_key=True) visit = models.ForeignKey(Visitor) sequence_no = models.IntegerField(null=False) class Track(models.Model): id = models.AutoField(primary_key=True) session = models.ForeignKey(Session) action = models.ForeignKey(Action) when = models.DateTimeField(null=False, auto_now_add=True) sequence_no = models.IntegerField(null = False) class Action(models.Model): id = models.AutoField(primary_key=True) url = models.CharField(max_length=65535, null=False) host = models.IntegerField(null=False)
As you can see, each
Visitor
has multipleSessions
; eachSession
has multipleTracks
and eachTrack
has oneAction
. Tracks are always ordered ascendingly by thesession
and thesequence_no
. AVisitors
average time on an site (i.e. a particularAction.host
) is the difference inTrack.when
(time) between the highest and lowestTrack.sequence_no
divided by the number ofSessions
of thatVisitor
.I need to calculate the average time of visitors on the site which be the sum of the time for each visitor on the
Action.site
divided by the number of visitors.I could query this using SQL but I'd like to keep my query as Djangonic as possible and I'm still very lost with complex queries.
解决方案For a specific
Action
object you can gather interesting data about Sessions:from django.db.models import Min, Max from yourapp.models import * host = 1 # I suppose you want to calculate for each site sessions = list(Session.objects.filter( track__action__host=host, ).annotate( start=Min('track__when'), end=Max('track__when'), ).values('visit_id', 'start', 'end'))
You will get something in the line of:
[ { 'visit_id': 1, 'start': datetime(...), 'end': datetime(...) }, { 'visit_id': 1, 'start': datetime(...), 'end': datetime(...) }, { 'visit_id': 2, 'start': datetime(...), 'end': datetime(...) }, .... ]
Now it's only a matter of getting the desired result from the data:
number_of_visitors = len(set(s['visit_id'] for s in sessions)) total_time = sum((s['end'] - s['start']).total_seconds() for s in sessions) average_time_spent = total_time / number_of_visitors
Another way is to use two queries instead of one, and avoid the
len(set(...))
snippet:sessions = Session.objects.filter( track__action__host=host, ).annotate( start=Min('track__when'), end=Max('track__when'), ) number_of_visitors = sessions.values('visit_id').distict().count() total_time = sum((s['end'] - s['start']).total_seconds() for s in sessions.values('start', 'end'))
There is NO WAY to do actual calculated fields barring the provided aggregations, so either you do it in raw SQL or you do in code like this.
At least the proposed solution uses Django's ORM as far as possible.
这篇关于从Django查询数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!