Обсуждение: Grouping by day, limiting amounts
<font size="-1"><font face="Tahoma">Hi,<br /><br /> I didn't really know what subject I should give.<br /><br /> I have atable like this one:<small><br /><br /></small></font></font><small><font face="Tahoma">2006.10.01. Bela 10<br />2006.10.01. Aladar 9<br /> 2006.10.01. Cecil 8<br /> 2006.10.01. Dezso 7<br /> 2006.10.01. Elemer 6<br /> 2006.10.02. Bela 11<br /> 2006.10.02. Aladar 10<br /> 2006.10.02. Cecil 9<br /> 2006.10.02. Dezso 8<br /> 2006.10.02. Ferenc 7<br /> 2006.10.03. Bela 6<br /> 2006.10.03. Aladar 5<br/> 2006.10.03. Cecil 4<br /> 2006.10.03. Dezso 3<br /> 2006.10.03. Jozef 2<br /><br /> The first columnis a date, the second is a name, the third is the number of votes that the name received on that day.<br /><br /> Iwould like to select the 3 (or 10) names with the most votes for each day.<br /><br /> Any suggestions on how can it bedone easily?<br /><br /> Thanks,<br /><br /> Zizi<br /></font></small>
am Thu, dem 19.10.2006, um 13:51:55 +0200 mailte Mezei Zoltán folgendes:
> Hi,
>
> I didn't really know what subject I should give.
>
> I have a table like this one:
>
> 2006.10.01. Bela 10
> 2006.10.01. Aladar 9
> 2006.10.01. Cecil 8
> 2006.10.01. Dezso 7
> 2006.10.01. Elemer 6
> 2006.10.02. Bela 11
> 2006.10.02. Aladar 10
> 2006.10.02. Cecil 9
> 2006.10.02. Dezso 8
> 2006.10.02. Ferenc 7
> 2006.10.03. Bela 6
> 2006.10.03. Aladar 5
> 2006.10.03. Cecil 4
> 2006.10.03. Dezso 3
> 2006.10.03. Jozef 2
>
> The first column is a date, the second is a name, the third is the number of
> votes that the name received on that day.
>
> I would like to select the 3 (or 10) names with the most votes for each day.
>
> Any suggestions on how can it be done easily?
I would write a Set-returning function. First, select distinct all
dates, and interate over this to select the names and number of votes
for this date, ordered by the votes descending and limit this to 3 oder
10 or whatever.
simplified:
create function .... (IN c int, OUT d date, OUT n text, OUT x int) returns setof record ...
declare temp_date date; rec record;
begin for select into temp_date distinct date from table loop for select into rec name, votes from table
wheredatum=temp_date order by 2 limit by $1 loop n := rec.name;
... return next
...
HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
am Thu, dem 19.10.2006, um 6:55:41 -0700 mailte chester c young folgendes: > --- "A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote: > > > > I would like to select the 3 (or 10) names with the most votes for > > each day. > > > > > > Any suggestions on how can it be done easily? > > Am I misunderstanding, or can you write a simple: > > select * from <table> where <date>='2006-10-10' > limit 5 order by <vote_ct> desc; From the first mail: "I would like to select the 3 (or 10) names with the most votes for each day." _FOR_ _EACH_ _DAY_. Your query returns the values only for one day... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
I came into this thread late, so I don't know what you schema looks like. I have a table that has a timestamp with time zone field and other data. I can write a query that give me a count per day SELECT count(*) AS count, date_trunc( 'day', hour) AS day FROM hist_callhealth GROUP BY day; This will give me a count per day, to get the top X SELECT count, day FROM (SELECT count(*) AS count, date_trunc( 'day', hour) AS day FROM hist_callhealth GROUP BY day) AS foo ORDER BY count desc limit X; Hope this helps, Woody IGLASS Networks -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of A. Kretschmer Sent: Thursday, October 19, 2006 10:18 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Grouping by day, limiting amounts am Thu, dem 19.10.2006, um 6:55:41 -0700 mailte chester c young folgendes: > --- "A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote: > > > > I would like to select the 3 (or 10) names with the most votes for > > each day. > > > > > > Any suggestions on how can it be done easily? > > Am I misunderstanding, or can you write a simple: > > select * from <table> where <date>='2006-10-10' > limit 5 order by <vote_ct> desc; >From the first mail: "I would like to select the 3 (or 10) names with the most votes for each day." _FOR_ _EACH_ _DAY_. Your query returns the values only for one day... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.orgso that your message can get through to the mailing list cleanly
On Thu, Oct 19, 2006 at 01:51:55PM +0200, Mezei Zolt??n wrote:
>
> Hi,
> I didn't really know what subject I should give.
> I have a table like this one:
> 2006.10.01. Bela 10
> 2006.10.01. Aladar 9
> 2006.10.01. Cecil 8
> 2006.10.01. Dezso 7
> 2006.10.01. Elemer 6
> 2006.10.02. Bela 11
> 2006.10.02. Aladar 10
> 2006.10.02. Cecil 9
> 2006.10.02. Dezso 8
> 2006.10.02. Ferenc 7
> 2006.10.03. Bela 6
> 2006.10.03. Aladar 5
> 2006.10.03. Cecil 4
> 2006.10.03. Dezso 3
> 2006.10.03. Jozef 2
> The first column is a date, the second is a name, the third is the
> number of votes that the name received on that day.
> I would like to select the 3 (or 10) names with the most votes for
> each day.
> Any suggestions on how can it be done easily?
It'd be easy with windowing functions, but unfortunately we don't have
those...
SELECT * FROM (SELECT DISTINCT date FROM table) AS dates , (SELECT date, name, votes FROM table
WHERE table.date = dates.date ORDER BY votes DESC LIMIT 3 )
;
Note that this has to scan the table twice (well, the second subquery
will likely use an index on date). If you have another table that has
the dates in it already, you can use that instead of the first subquery.
If you know that every day has a row, you could also replace the first
subquery with a generate_series().
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)