From: | Emanuel Calvo <3manuek(at)esdebian(dot)org> |
---|---|
To: | droberts <david(dot)roberts(at)riverbed(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Approach to extract top records from table based upon aggregate |
Date: | 2015-11-03 00:59:54 |
Message-ID: | CAE--4s8c3ZsFi0Wi8sV_KA=pSJUAJpuDxxtc8Y+g6dcfo=07Eg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2015-11-02 19:14 GMT-03:00 droberts <david(dot)roberts(at)riverbed(dot)com>:
> Hi, I have a table that contains call records. I'm looking to get only
> records for users who made the most calls over a particular time duration
> in
> an efficient way.
>
> calls()
>
> time, duration, caller_number, dialed_number
>
>
>
> -- query to get top 10 callers
> select caller_number, count(1) from calls group by caller_number order by
> calls desc limit 10
>
> --my current query to get those callers
>
> select * from call where caller_number in (above query)
>
>
> It works but I was hoping for something a little more efficient if anyone
> has an idea.
>
>
I think that almost every time based tables, should be partitioned. Also,
depending on your workload you can create lazy views over the last entries
in calls table during a particular time frame.
Probably in this particular case, you will want to dig into more underneath
design in order to get the best performance.
Doing a lazy view with that query, you can use the top n of it and get less
callers if you need to (or more if you want to expand the feature).
Hope it helps,
--
--
Emanuel Calvo
Twitter: 3manuek
LinkedIn: http://ar.linkedin.com/in/ecbcbcb/
From | Date | Subject | |
---|---|---|---|
Next Message | Victor Blomqvist | 2015-11-03 06:10:16 | Re: Drop or alter column under load give ERROR #42804 structure of query does not match function result type: |
Previous Message | Jason O'Donnell | 2015-11-02 22:56:12 | Re: How to search a string inside a json structure |