How to aggregate values by month
Rogério Carrasqueira
I'm having an issue to make complex queries in django. My problem is, I have a model where I have the sales and I need to make a report showing the sales amount per month, by the way I made this query:
init_date = datetime.date(datetime.now()-timedelta(days=365))
ends_date = datetime.date(datetime.now())
sales = Sale.objects.filter(date_created__range=(init_date,ends_date)).values(date_created__month).aggregate(total_sales=Sum('total_value'))
At the first line I get the today's date past one year
after this I got the today date
at sales I'm trying to between a range get the sales amount grouped by month, but unfortunatelly I was unhappy on this, because this error appeared:
global name 'date_created__month' is not defined
At date_created is the field where I store the information about when the sale was done., the __moth was a tentative to group by this by month.
So, my question: how to do that thing without using a raw sql query and not touching on database independence?
Thanks so much!
Rogério Carrasqueira
---
e-mail: rogerio.ca...@gmail.com
skype: rgcarrasqueira
MSN: rcarra...@hotmail.com
ICQ: 50525616
Tel.: (11) 7805-0074
Franklin Einspruch
%} just in case:
http://docs.djangoproject.com/en/dev/ref/templates/builtins/?from=olddocs#regroup
- Franklin
--
Art, writing, journal: http://einspruch.com
Comics: http://themoonfellonme.com
2010/10/28 Rogério Carrasqueira <rogerio.ca...@gmail.com>:
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To post to this group, send email to django...@googlegroups.com.
> To unsubscribe from this group, send email to
> django-users...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/django-users?hl=en.
>
Rogério Carrasqueira
Thanks for you answer. Unfortunatelly I need to output my results on a JSON file. Do you have any other approach?
Cheers,
Rogério Carrasqueira
---
e-mail: rogerio.ca...@gmail.com
skype: rgcarrasqueira
MSN: rcarra...@hotmail.com
ICQ: 50525616
Tel.: (11) 7805-0074
Rogério Carrasqueira
I tried this way also:
sales = Sale.objects.extra(select={'
but returned {}
Any ideias?
Cheers,
Rogério Carrasqueira
---
e-mail: rogerio.ca...@gmail.com
skype: rgcarrasqueira
MSN: rcarra...@hotmail.com
ICQ: 50525616
Tel.: (11) 7805-0074
Scott Gould
like this (untested):
sales = Sale.objects.filter(date_created__range=(init_date,ends_date))
.values(date_ created__month)
.aggregate(total_sales=Sum('total_value'))
sales_by_month = [(x.year, x.month, [y for y in sales if y.year ==
x.year and y.month == x.month])
for x in set([z.year, z.month for z in sales])]
Should give you a list of (year, month, subqueryset) tuples. Of
course, there might be performance issues with nested list
comprehensions like that depending on the size of the initial
queryset, but at least it's a one-line, SQL-free solution!
On Oct 28, 9:31 am, Rogério Carrasqueira
> Hello!
>
> I'm having an issue to make complex queries in django. My problem is, I have
> a model where I have the sales and I need to make a report showing the sales
> amount per month, by the way I made this query:
>
> init_date = datetime.date(datetime.now()-timedelta(days=365))
> ends_date = datetime.date(datetime.now())
> sales =
> At the first line I get the today's date past one year
> after this I got the today date
>
> at sales I'm trying to between a range get the sales amount grouped by
> month, but unfortunatelly I was unhappy on this, because this error
> appeared:
>
> global name 'date_created__month' is not defined
>
> At date_created is the field where I store the information about when the
> sale was done., the __moth was a tentative to group by this by month.
>
> So, my question: how to do that thing without using a raw sql query and not
> touching on database independence?
>
> Thanks so much!
>
> Rogério Carrasqueira
>
> ---
> skype: rgcarrasqueira
> MSN: rcarrasque...@hotmail.com
Casey S. Greene
You can output to json using templates if this is the only thing holding
you back from a working solution using regroup. You can use
render_to_string with a json template which you can then return with an
HttpResponse. It doesn't take advantage of the serialization library
but it does work.
-- Casey
On 10/28/2010 09:41 AM, Rog�rio Carrasqueira wrote:
> Hi Franklin!
>
> Thanks for you answer. Unfortunatelly I need to output my results on a
> JSON file. Do you have any other approach?
>
> Cheers,
>
> Rog�rio Carrasqueira
>
> ---
> e-mail: rogerio.ca...@gmail.com
> <mailto:rogerio.ca...@gmail.com>
> skype: rgcarrasqueira
> MSN: rcarra...@hotmail.com <mailto:rcarra...@hotmail.com>
> ICQ: 50525616
> Tel.: (11) 7805-0074
>
>
>
> 2010/10/28 Franklin Einspruch <franklin....@gmail.com
> <mailto:franklin....@gmail.com>>
>
> It may not be a complete answer, but you should know about {% regroup
> %} just in case:
>
> http://docs.djangoproject.com/en/dev/ref/templates/builtins/?from=olddocs#regroup
>
> - Franklin
>
> --
> Art, writing, journal: http://einspruch.com
> Comics: http://themoonfellonme.com
>
>
>
>
> 2010/10/28 Rog�rio Carrasqueira <rogerio.ca...@gmail.com
> <mailto:rogerio.ca...@gmail.com>>:
> > Hello!
> >
> > I'm having an issue to make complex queries in django. My problem
> is, I have
> > a model where I have the sales and I need to make a report
> showing the sales
> > amount per month, by the way I made this query:
> >
> > init_date = datetime.date(datetime.now()-timedelta(days=365))
> > ends_date = datetime.date(datetime.now())
> > sales =
> >
> Sale.objects.filter(date_created__range=(init_date,ends_date)).values(date_created__month).aggregate(total_sales=Sum('total_value'))
> >
> > At the first line I get the today's date past one year
> > after this I got the today date
> >
> > at sales I'm trying to between a range get the sales amount
> grouped by
> > month, but unfortunatelly I was unhappy on this, because this error
> > appeared:
> >
> > global name 'date_created__month' is not defined
> >
> > At date_created is the field where I store the information about
> when the
> > sale was done., the __moth was a tentative to group by this by month.
> >
> > So, my question: how to do that thing without using a raw sql
> query and not
> > touching on database independence?
> >
> > Thanks so much!
> >
> > Rog�rio Carrasqueira
> >
> > ---
> > e-mail: rogerio.ca...@gmail.com
> <mailto:rogerio.ca...@gmail.com>
> > skype: rgcarrasqueira
> > MSN: rcarra...@hotmail.com <mailto:rcarra...@hotmail.com>
> > ICQ: 50525616
> > Tel.: (11) 7805-0074
> >
> > --
> > You received this message because you are subscribed to the
> Google Groups
> > "Django users" group.
> > To post to this group, send email to
> django...@googlegroups.com <mailto:django...@googlegroups.com>.
> > To unsubscribe from this group, send email to
> > django-users...@googlegroups.com
> <mailto:django-users%2Bunsu...@googlegroups.com>.
> > For more options, visit this group at
> > http://groups.google.com/group/django-users?hl=en.
> >
>
> --
> You received this message because you are subscribed to the Google
> Groups "Django users" group.
> To post to this group, send email to django...@googlegroups.com
> <mailto:django...@googlegroups.com>.
> To unsubscribe from this group, send email to
> django-users...@googlegroups.com
> <mailto:django-users%2Bunsu...@googlegroups.com>.
Javier Guerra Giraldez
> Thanks for you answer. Unfortunatelly I need to output my results on a JSON
> file. Do you have any other approach?
if the {% regroup %} is what you need, you should know that it's an
application of itertools.groupby()
--
Javier
Mikhail Korobov
You can give http://bitbucket.org/kmike/django-qsstats-magic/src a
try.
It currently have efficient aggregate lookups (1 query for the whole
time series) only for mysql but it'll be great if someone contribute
efficient lookups for other databases :)
On 28 окт, 19:31, Rogério Carrasqueira
> Hello!
>
> I'm having an issue to make complex queries in django. My problem is, I have
> a model where I have the sales and I need to make a report showing the sales
> amount per month, by the way I made this query:
>
> init_date = datetime.date(datetime.now()-timedelta(days=365))
> ends_date = datetime.date(datetime.now())
> sales =
> At the first line I get the today's date past one year
> after this I got the today date
>
> at sales I'm trying to between a range get the sales amount grouped by
> month, but unfortunatelly I was unhappy on this, because this error
> appeared:
>
> global name 'date_created__month' is not defined
>
> At date_created is the field where I store the information about when the
> sale was done., the __moth was a tentative to group by this by month.
>
> So, my question: how to do that thing without using a raw sql query and not
> touching on database independence?
>
> Thanks so much!
>
> Rogério Carrasqueira
>
> ---
sebastien piquemal
You could also give django-cube a try :
http://code.google.com/p/django-cube/
Unlike Mikhail's app, the aggregates are not efficient (because no
optimization is made, I am working on this), but this is more than
enough if you have a reasonable amount of data (less than millions of
rows !!!).
Use the following code, and you should have what you need :
from cube.models import Cube
class SalesCube(Cube):
month = Dimension('date_created__absmonth',
queryset=Sale.objects.filter(date_created__range=(init_date,ends_date)))
@staticmethod
def aggregation(queryset):
return queryset.count()
The advantage is that if you want to add more dimensions (type of sale/
place/month, etc ...), you can do it very easily.
Hope that helps, and don't hesitate to ask me if you can't have it
working (documentation is not very good yet).
On Oct 29, 12:54 am, Mikhail Korobov <kmik...@googlemail.com> wrote:
> Hi Rogério,
>
> You can givehttp://bitbucket.org/kmike/django-qsstats-magic/srca
Rogério Carrasqueira
Thanks for you help, unfortunately on trying
sales = Sale.objects.filter(date_
.values(date_ created__month)
.aggregate(total_sales=Sum('total_value'))
global name 'date_created__month' is not defined
Regards,
Rogério Carrasqueira
---
e-mail: rogerio.ca...@gmail.com
skype: rgcarrasqueira
MSN: rcarra...@hotmail.com
Tel.: (11) 7805-0074
Rogério Carrasqueira
Can you give some clue on how to use your plugin considering my scenario?
Thanks
Rogério Carrasqueira
---
e-mail: rogerio.ca...@gmail.com
skype: rgcarrasqueira
MSN: rcarra...@hotmail.com
Tel.: (11) 7805-0074
Rogério Carrasqueira
Thanks for you reply. I'm a newbie on Django and I must confess unfortunately I don't know everything yet ;-). So I saw that you made a snippet regarding about the use of Django Cube. So, where do I put this snippet: at my views.py? Or should I do another class at my models.py?
Thanks so much!
Regards,
Rogério Carrasqueira
---
e-mail: rogerio.ca...@gmail.com
skype: rgcarrasqueira
MSN: rcarra...@hotmail.com
Tel.: (11) 7805-0074
Rogério Carrasqueira
I've got the solution, putting here for future searchs:
sales = Sale.objects.extra(select={'month':'month(date_created)','year':'year(date_created)'}).values('year','month').annotate(total_month=Sum('total_value'), average_month=Avg('total_value'))
This query works only using MySQL, to use with PGSQL you need to know to work with EXTRACT clauses.
Cheers
Rogério Carrasqueira
---
e-mail: rogerio.ca...@gmail.com
skype: rgcarrasqueira
MSN: rcarra...@hotmail.com
ICQ: 50525616
Tel.: (11) 7805-0074
Mikhail Korobov
With django-qsstats-magic it would be something like this:
stats = QuerySetStats(Sale.objects.all(), 'date_created')
totals = stats.time_series(start, end, 'months',
aggregate=Sum('total_value'))
averages = stats.time_series(start, end, 'months',
aggregate=Avg('total_value'))
Great you've found the solution without any plugins!
On 3 ноя, 23:44, Rogério Carrasqueira <rogerio.carrasque...@gmail.com>
wrote:
>
> Can you give some clue on how to use your plugin considering my scenario?
>
> Thanks
>
> Rogério Carrasqueira
>
> ---
> skype: rgcarrasqueira
> MSN: rcarrasque...@hotmail.com
> ICQ: 50525616
> Tel.: (11) 7805-0074
>
> > .
David Zhou
derek
readily switching between different DB-specific SQL code which is
(presumably) embedded in one's app/project? (As shown in the two
examples presented here)
On Nov 5, 1:10 am, David Zhou <da...@nodnod.net> wrote:
> FWIW, on Postgres DBs, I've done the following:
>
> qs = FooModel.objects.filter(date__gte=start_date,
> date__lt=end_date).extra(select={'datetrunc': "date_trunc('month',
> date)"}).values('datetrunc').annotate(total=Sum("value"))
>
> date_trunc in postgres also accepts "day" and "week" truncations.
>
> -- dz
>
> > Hello Folks!
>
> > I've got the solution, putting here for future searchs:
>
> > sales =
> > Sale.objects.extra(select={'month':'month(date_created)','year':'year(date_created)'}).values('year','month').annotate(total_month=Sum('total_value'),
> > average_month=Avg('total_value'))
>
> > This query works only using MySQL, to use with PGSQL you need to know to
> > work with EXTRACT clauses.
>
> > Cheers
>
> > Rogério Carrasqueira
>
> > ---
> > skype: rgcarrasqueira
> > MSN: rcarrasque...@hotmail.com
> > ICQ: 50525616
> > Tel.: (11) 7805-0074
>
> > Hi Sebastien!
>
> >> Thanks for you reply. I'm a newbie on Django and I must confess
> >> unfortunately I don't know everything yet ;-). So I saw that you made a
> >> snippet regarding about the use of Django Cube. So, where do I put this
> >> snippet: at my views.py? Or should I do another class at my models.py?
>
> >> Thanks so much!
>
> >> Regards,
>
> >> Rogério Carrasqueira
>
> >> ---
> >>> .
> >>>http://groups.google.com/group/django-users?hl=en.
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "Django users" group.
> > To post to this group, send email to django...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > .