Hello,
I'd like to the use the MySQL function YEARWEEK (and INTERVAL) to aggregate
timed data, as it looks more efficient than a double date comparison
(start-date;end-date).
How would it best be done with django?
My current SQL query, that counts various types of mails exchanged over a
period of ~10 years per week, looks like
*SELECT
YEARWEEK( `datetime` - INTERVAL 7 DAY , 7 ) AS `yearWeek` ,
COUNT( IF( `thdFinal` = 1, 1, NULL ) ) AS `finalsCount` ,
COUNT( IF( `id` = `thdRoot_id` , 1, NULL ) ) AS `rootCount` ,
COUNT( * ) AS `totalCount` ,
`datetime`
FROM `data_mail`
WHERE `project_id` = %s AND `mailType` = '%s'
GROUP BY `yearWeek` *
I currently call it with a django.db raw() call, but am wondering if
there's any more efficient way, provided django already powers all my other
requests.
* # get sql results
from django.db import connection
cursor = connection.cursor()
cursor.execute(query, q_params)
#turn sql result to array of named lists per columns
names = [col[0] for col in cursor.description]
raw = map(list, zip(*cursor.fetchall()))
res = {names[i]:raw[i] for i in range(len(names))}*
Efficiency is an issue as I have a very big database.
Any idea is welcome, thanks in advance!
Bertrand.
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
