doufei2007 2018-05-15 19:03
浏览 61


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'


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.

  • 写回答

3条回答 默认 最新

  • doucheng5705 2018-05-15 20:21

    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)
    (SELECT 'Max percent', * FROM Data ORDER BY "percent" DESC LIMIT 1)
    (SELECT 'Min profit', * FROM Data ORDER BY "profit" ASC LIMIT 1)
    (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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?



  • ¥30 求一下解题思路,完全不懂。
  • ¥30 关于#硬件工程#的问题:求一下解题思路
  • ¥15 运筹学对偶单纯行法构造扩充问题
  • ¥20 XP系统的老电脑一开机就提示找不到rundll.exe,付费求解
  • ¥15 milvus查询出来的score怎么转换成0-1之间的相似性
  • ¥15 多ip服务器站群如何搭建l2tp服务器
  • ¥15 lvgl V9移植到linux开发板
  • ¥15 VB.net中在窗体中创建一个button控件来关闭窗体,但是提示错误,我该怎么办
  • ¥15 网上下载好的程序但是arduinoIDE编程报错,运行不了,哪里出错了,能具体给改一下吗
  • ¥15 Sharepoint JS开发 付费技术指导