一个函数中的多个SQL查询

Question

I need to return multiple value in a single SQL query on my postgresql database. Here is my query so far:

SELECT AVG("percent"), MIN("percent"), MAX("percent"), AVG("profit"), MIN("profit"), MAX("profit")
FROM public.log_analyticss 
WHERE "buyPlatform" = 'platA' AND
"date" >= '1526356073.6126819'

Data

date             sellPlatform     profit      percent    
----------       ------------     ----------  ----------
1526356073.61    platA            0           10.1
1526356073.62    platA            22          11
1526356073.63    platA            3           7
1526356073.64    platA            1           8
1526356073.65    platA            11          9
1526356073.66    platA            12          10
1526356073.67    platA            13          15

Desired Result

date             sellPlatform     profit      percent    
----------       ------------     ----------  ----------
1526356073.61    platA            0           10.1         //MIN Profit
1526356073.62    platA            22          11           //MAX Profit
1526356073.63    platA            3           7            //MIN Perc
1526356073.67    platA            13          15           //MAX Perc

//Then somehow I want it return AVG as well if that is every possible. Otherwise, I don't mind running another query to do that.

The issue is that I don't just want the MIN and MAX values. I want the entire row of data from which the MIN and MAX value is taken.

I understand that I am asking for an AVG and a MIN/MAX value which is going to return data in two different formats. I know this is might not be possible. But any help on how to most efficiently do this would be extremely helpful.

Right now I am just pulling the entire dataset into my code and calculating the mean, min and max within my code which I know if very bad and also very very slow. The table has about 8 million rows and the dataset that I am grabbing is about 9000 rows so it is very slow the way I am doing it now.

Answer 1

The most efficient way to retrieve the row associated with the minimum / maximum generally doesn't involve the MIN()/MAX() aggregates at all; instead, you can just attach an ORDER BY to your query, and add a LIMIT 1 to fetch only the first record.

This means you need four SELECT statements with four different orderings, but you can factor out the expensive part (the fetch from log_analyticss) into a temp table or a CTE, e.g.:

WITH Data AS (
  SELECT *
  FROM public.log_analyticss 
  WHERE "buyPlatform" = 'platA' AND
    "date" >= '1526356073.6126819'
)
(SELECT 'Min percent', * FROM Data ORDER BY "percent" ASC LIMIT 1)
UNION ALL
(SELECT 'Max percent', * FROM Data ORDER BY "percent" DESC LIMIT 1)
UNION ALL
(SELECT 'Min profit', * FROM Data ORDER BY "profit" ASC LIMIT 1)
UNION ALL
(SELECT 'Max profit', * FROM Data ORDER BY "profit" DESC LIMIT 1)

In your case, a temp table might be better than a CTE, as you can re-use it to compute the averages.

Note that if one of these maximum/minimum values is shared by two different rows, this query would only return one of them. The chosen row is selected effectively at random, but you can attach more fields to the ORDER BY clause to serve as a tie-breaker.

If you actually want both records in this case, you'll need something more like Auston's or Radim's answer, i.e. compute the aggregates first, then join back to the data on the profit and percent columns. You can still make use of a temp table / CTE here to avoid hitting log_analyticss more than once.