Re: How pull

On 23/09/2007, Matt Magoffin <postgresql(dot)org(at)msqr(dot)us> wrote:
> Hello,
>
> My SQL skills are limited and I'm struggling with a query where I want to
> return a single item of an aggregate join. The query looks like this:
>
> select
> (case
> when agg.avg_rating is null then 0.0
> when agg.avg_rating < 0.75 then 0.5
> when agg.avg_rating < 1.25 then 1.0
> when agg.avg_rating < 1.75 then 1.5
> when agg.avg_rating < 2.25 then 2.0
> when agg.avg_rating < 2.75 then 2.5
> when agg.avg_rating < 3.25 then 3.0
> when agg.avg_rating < 3.75 then 3.5
> when agg.avg_rating < 4.25 then 4.0
> when agg.avg_rating < 4.75 then 4.5
> else 5.0
> end) as avg_rating,
> count(item.itemid) as item_count
> from media_item item
> inner join (
> select rating.mediaitem_userrating_hjid as ritemid,
> avg(rating.rating) as avg_rating
> from media_item_rating rating, media_item item
> where rating.mediaitem_userrating_hjid = item.itemid
> group by rating.mediaitem_userrating_hjid
> ) as agg
> on item.itemid = agg.ritemid
> group by avg_rating
> order by avg_rating desc
>
> and a sample of results is this:
>
> avg_rating | item_count
> ------------+------------
> 5.0 | 21
> 4.0 | 33
> 3.0 | 13
> 2.0 | 4
> 1.0 | 1
>
> What I want as well is the ID of the item (and possibly it's avg_rating
> value) from the "agg" join with the highest avg_rating for each output
> row... something like this
>
> avg_rating | item_count | item_id | item_rating
> ------------+-----------------------------------
> 5.0 | 21 | 109890 | 4.9
> 4.0 | 33 | 89201 | 4.1
> 3.0 | 13 | 119029 | 2.8
> 2.0 | 4 | 182999 | 2.2
> 1.0 | 1 | 1929 | 1.0
>
> So the intention in this example is that item #109890 has an average
> rating of 4.9 and that is the highest rating within the > 4.75 rating
> group.
>
> If anyone had any tips I'd greatly appreciate it.
>

create ranking function to make queries look simpler:

create or replace function ranking_group(numeric) returns numeric as
$$ select case
when $1 < 0.3456 then 'quite small'
...
end $$ language sql immutable;
(I'd make it STRICT, but you allow null rankings)

1st way: DISTINCT ON + subquery

select *, (select count(*) from rating where rating_group(rating) =
subq.rating_group ) as rating_group_size
from (
select distinct on (rating_group)
rating_group(r.rating),
r.item_id as best_rated_item_id,
r.rating as best_rating
from rating r
order by rating_group desc, r.rating desc
) subq;

2nd way (faster - actually 2 x faster)
using FIRST aggregate to calculate all in one pass

create function first(numeric,numeric) returns numeric as 'select $1'
language sql immutable strict;
create function first(integer,integer) returns integer as 'select $1'
language sql immutable strict;
create aggregate first( integer ) ( SFUNC = first, STYPE = integer );
create aggregate first ( numeric ) ( SFUNC = first, STYPE = numeric );

select
rating_group(rating),
count(*) as num_ratings,
first(item_id) as best_rated_item_id,
first(rating) as best_rating
from ( select * from rating order by rating desc ) ordered_ratings
group by rating_group
order by rating_group desc;

note: if you can, get rid of null ratings. what are they supposed to
mean? they make things a bit more complicated.

--
Filip Rembiałkowski