How to aggregate barchart data for volumes of registrations per day from Django auth?
Django auth User model has a date_joined
DateTimeField
. Could this be used to aggregate a list with volumes of registrations per day for days (or other time periods) in a daterange? E.g.: [(21.01, 5), (22开发者_StackOverflow.01, 7), (23.01, 9), ...]
What is the fastest way to do this? E.g. if the date range on the barchart was set to past 3 years.
I looked at http://docs.djangoproject.com/en/dev/topics/db/aggregation/, but it's not apparent how to breakdown objects using their timestamps. This is a common design pattern in statistics.
This question is similar to this one: django: time range based aggregate query
Something like this should work:
MyObject.objects.filter(date_joined__date__range=(weekago,today)).annotate(registrations=Count('id')
Here's my code for generating date-based barcharts with Django and CSS.
def date_barchart(model, date_field, operation='count', days=150, till=datetime.now()):
list = []
i = 0
while i < days:
day = (till - timedelta(days=i), till - timedelta(days=i-1))
date = time.strftime("%Y-%m-%d", day[0].timetuple())
kwargs = {'%s__range' % date_field: day}
qs = model.objects.filter(**kwargs)
if operation == 'count':
total = qs.count()
else:
total = qs.aggregate(Sum('amount'))
total = total['amount__sum']
if total > 0:
list.insert(0, {'date': date, 'total': total, 'round_total': int(total)})
i = i + 1
return list
In my view I add may barchart data to template's context:
'barchart': date_barchart(User, 'date_joined')
or for Satchmo e-commerce
'barchart': date_barchart(Payment, 'time_stamp', 'sum', 150)
template:
{% if barchart %}
<div class="barchart">
<ol>
{% for d in barchart %}
<li><u style="height:{{ d.round_total }}px"><i>{{ d.date }}<br>
<b>{{ d.total }}</b></i></u></li>
{% endfor %}
</ol>
</div>
{% endif %}
and CSS:
.barchart{width:100%;overflow:hidden}
.barchart u{display:inline-block;position:relative;vertical-align:bottom;width:5px;background:#6cc;border-right:1px solid #fff;text-decoration:none}
.barchart u i{display:none;position:absolute;background:#fff;border:1px solid #aaa;padding:5px;border-radius:5px;left:-6px;bottom:10px;width:60px;font-size:10px;font-style:normal;color:#000}
.barchart u:hover{background-color:#333}
.barchart u:hover i{display:inline-block;z-index:500}
.barchart li{margin:0;padding:0;list-style:none;display:inline-block}
Looks as good as Google analytics, but no need to give away my performance data.
精彩评论