[PonyORM-list] Counting appearances of a row value

[Matthew Bell]

More followup:

SELECT "r"."date", COUNT(DISTINCT "r"."id")
FROM "result" "r"
WHERE "r"."date" >= %(p1)s
GROUP BY "r"."date"
{'p1':datetime.date(2014, 10, 6)}

Is the SQL generated, and it's taking 51 seconds to run.

On 5 November 2014 08:12, Matthew Bell <matthewrobertbell at gmail.com> wrote:

> Of course, as soon as I send the email, I find something that works:
>
> models.select((r.date, models.count(r)) for r in models.Result if r.date
> >= datetime.date.today() - datetime.timedelta(days=10))[:]
>
> Is this the most efficient method?
>
> On 5 November 2014 08:02, Matthew Bell <matthewrobertbell at gmail.com>
> wrote:
>
>> Hi,
>>
>> I have naively tried to execute this query:
>>
>> models.select((r.date, models.count(r.date)) for r in models.Result if
>> r.date >= datetime.date.today() -
>> datetime.timedelta(days=10)).without_distinct()
>>
>> With the aim of counting how many times each date appears. I was doing
>> this with collections.Counter, but this doesn't scale to millions of
>> results (far too much CPU and memory used). What query can I use to make
>> the database do the work?
>>
>> Thanks
>> --
>> Regards,
>>
>> Matthew Bell
>>
>
>
>
> --
> Regards,
>
> Matthew Bell
>



-- 
Regards,

Matthew Bell
-------------- next part --------------
An HTML attachment was scrubbed...
URL: </ponyorm-list/attachments/20141105/20f47466/attachment.html>