Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

postgresql - Selecting multiple max() values using a single SQL statement

I have a table that has data that looks something like this:

data_type, value
World of Warcraft, 500
Quake 3, 1500
Quake 3, 1400
World of Warcraft, 1200
Final Fantasy, 100
Final Fantasy, 500

What I want to do is select the maximum of each of these values in a single statement. I know I can easily do something like

select data_type, max(value)
from table
where data_type = [insert each data type here for separate queries]
group by data_type

But what I want it to display is is

select data_type, 
  max(value) as 'World of Warcraft', 
  max(value) as 'Quake 3', 
  max(value) as 'Final Fantasy'

So I get the max value of each of these in a single statement. How would I go about doing this?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Once again, for more than just a few "data types", I suggest to use crosstab():

SELECT * FROM crosstab(
     $$SELECT DISTINCT ON (1, 2)
              'max' AS "type", data_type, val
       FROM   tbl
       ORDER  BY 1, 2, val DESC$$

    ,$$VALUES ('Final Fantasy'), ('Quake 3'), ('World of Warcraft')$$)
AS x ("type" text, "Final Fantasy" int, "Quake 3" int, "World of Warcraft" int)

Returns:

type | Final Fantasy | Quake 3 | World of Warcraft
-----+---------------+---------+-------------------
max  | 500           | 1500    |    1200

More explanation for the basics:
PostgreSQL Crosstab Query

Dynamic solution

The tricky thing is to make this completely dynamic: to make it work for

  • an unknown number of columns (data_types in this case)
  • with unknown names (data_types again)

At least the type is well known: integer in this case.

In short: that's not possible with current PostgreSQL (including 9.3). There are approximations with polymorphic types and ways to circumvent the restrictions with arrays or hstore types. May be good enough for you. But it's strictly not possible to get the result with individual columns in a single SQL query. SQL is very rigid about types and wants to know what to expect back.

However, it can be done with two queries. The first one builds the actual query to use. Building on the above simple case:

SELECT $f$SELECT * FROM crosstab(
     $$SELECT DISTINCT ON (1, 2)
              'max' AS "type", data_type, val
       FROM   tbl
       ORDER  BY 1, 2, val DESC$$

    ,$$VALUES ($f$     || string_agg(quote_literal(data_type), '), (') || $f$)$$)
AS x ("type" text, $f$ || string_agg(quote_ident(data_type), ' int, ') || ' int)'
FROM  (SELECT DISTINCT data_type FROM tbl) x

This generates the query you actually need. Run the second one inside the same transaction to avoid concurrency issues.

Note the strategic use of quote_literal() and quote_ident() to sanitize all kinds of illegal (for columns) names and prevent SQL injection.

Don't get confused by multiple layers of dollar-quoting. That's necessary for building dynamic queries. I put it as simple as possible.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...