当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Selecting highest value from with a grouped by query

问题描述:

I have a query:

select substr(name,7,50) as location, points,sum(if (p1=r1,10,-10))as total from

dq.data

group by points,location order by location,total desc

Which produces this data:

FRANCE |0|2|0|0|0|0|1 110.0

FRANCE |0|2|1|0|1|2|1 100.0

FRANCE |0|2|0|0|0|1|1 100.0

FRANCE |0|2|1|0|0|1|1 100.0

FRANCE |0|2|0|1|1|2|1 100.0

FRANCE |0|2|0|0|1|1|1 100.0

GERMANY |1|0|2|2|2|1|0 120.0

GERMANY |1|0|2|2|2|0|0 110.0

GERMANY |1|0|2|2|2|2|0 110.0

GERMANY |1|0|2|2|2|0|2 110.0

GERMANY |1|0|2|2|2|1|1 110.0

I want to get to highest total and the related points for each location.

I should end up with:

FRANCE |0|2|0|0|0|0|1 110.0

GERMANY |1|0|2|2|2|1|0 120.0

I believe I need to use a subquery and MAX(total), but I cant get this to work.

In the subquery, I want to select points, but I dont want to group by it which is obviously not allowed.

How do I go about doing this?

网友答案:

Your instincts are correct. You can do this by calculating the max total and then joining this back to the original data:

select t.*
from (select substr(name,7,50) as location, points,sum(if (p1=r1,10,-10))as total
      from dq.data 
      group by points,location
     ) t join
     (select location, max(total) as maxtotal
      from (select substr(name,7,50) as location, points,sum(if (p1=r1,10,-10))as total
            from dq.data 
            group by points,location
           ) t
      group by location
     ) tsum
     on t.location = tsum.location and t.total = tsum.maxtotal

Note that this version will return duplicates if there are ties at the top.

I am not intimately familiar with google-biggquery. If it supports the "with" statement, then you can simplify the query, by doing:

with t as (select substr(name,7,50) as location, points,sum(if (p1=r1,10,-10))as total
           from dq.data 
           group by points,location
          )
select t.*
from t join
     (select location, max(total) as maxtotal
      from t
      group by location
     ) tsum
     on t.location = tsum.location and t.total = tsum.maxtotal

If it supports windows functions (such as row_number()), then you can eliminate the explicit join altogether.

网友答案:

I had a similar problem recently, solved it similar to this:

SELECT substr(name,7,50) as location, points,sum(if (p1=r1,10,-10))as total
FROM ( 
   SELECT * FROM dq.data ORDER BY location,sum(if (p1=r1,10,-10)) desc 
) tmp
GROUP BY points,location;

Not sure if it will work as my db was MySQL, but it's a nice intuitive solution. Order the subquery the way you want the summary rows to fall out.

分享给朋友:
您可能感兴趣的文章:
随机阅读: