PostgreSQL单查询获取多统计值及对应完整行数据需求

To efficiently retrieve both the average values and the full rows corresponding to min/max profit and percent (without pulling all 9000 rows into your code), you can use a combination of common table expressions (CTEs) and union operations. Here's an optimized solution:

WITH agg_stats AS (
    -- Calculate all required aggregates in a single pass over the filtered data
    SELECT 
        AVG(profit) AS avg_profit,
        MIN(profit) AS min_profit,
        MAX(profit) AS max_profit,
        AVG(percent) AS avg_percent,
        MIN(percent) AS min_percent,
        MAX(percent) AS max_percent
    FROM public.log_analyticss 
    WHERE buyPlatform = 'platA' AND date >= '1526356073.6126819'
),
-- Get rows for each extreme value
min_profit_rows AS (
    SELECT 
        date,
        sellPlatform,
        profit,
        percent,
        'MIN Profit' AS stat_type
    FROM public.log_analyticss
    WHERE buyPlatform = 'platA' AND date >= '1526356073.6126819'
    AND profit = (SELECT min_profit FROM agg_stats)
),
max_profit_rows AS (
    SELECT 
        date,
        sellPlatform,
        profit,
        percent,
        'MAX Profit' AS stat_type
    FROM public.log_analyticss
    WHERE buyPlatform = 'platA' AND date >= '1526356073.6126819'
    AND profit = (SELECT max_profit FROM agg_stats)
),
min_percent_rows AS (
    SELECT 
        date,
        sellPlatform,
        profit,
        percent,
        'MIN Percent' AS stat_type
    FROM public.log_analyticss
    WHERE buyPlatform = 'platA' AND date >= '1526356073.6126819'
    AND percent = (SELECT min_percent FROM agg_stats)
),
max_percent_rows AS (
    SELECT 
        date,
        sellPlatform,
        profit,
        percent,
        'MAX Percent' AS stat_type
    FROM public.log_analyticss
    WHERE buyPlatform = 'platA' AND date >= '1526356073.6126819'
    AND percent = (SELECT max_percent FROM agg_stats)
),
average_summary AS (
    -- Add a row with the average values
    SELECT 
        NULL AS date,
        NULL AS sellPlatform,
        (SELECT avg_profit FROM agg_stats) AS profit,
        (SELECT avg_percent FROM agg_stats) AS percent,
        'Averages' AS stat_type
)
-- Combine all results
SELECT * FROM min_profit_rows
UNION ALL
SELECT * FROM max_profit_rows
UNION ALL
SELECT * FROM min_percent_rows
UNION ALL
SELECT * FROM max_percent_rows
UNION ALL
SELECT * FROM average_summary;

How This Works: #

1. agg_stats CTE: Computes all your required aggregate values (averages, mins, maxes) in one scan of the filtered dataset. This avoids redundant calculations.
2. Extreme Row CTEs: Each CTE retrieves the full rows matching the min/max values from agg_stats. If multiple rows have the same min/max value, this will return all of them (add LIMIT 1 if you only want one).
3. average_summary: Adds a single row with the average values for easy reference.
4. UNION ALL: Combines all the results into a single result set, with a stat_type column to identify what each row represents.

Optimization Tips: #

• Indexing: Create an index on (buyPlatform, date) to speed up the initial filter of your dataset. This will let PostgreSQL quickly locate the 9000 relevant rows without scanning the entire 8 million-row table:

  CREATE INDEX idx_log_analyticss_buyplatform_date ON public.log_analyticss (buyPlatform, date);
  

• Handling Duplicates: If you only want one row per extreme (even if multiple rows share the same min/max), add LIMIT 1 to each of the extreme row CTEs.
• Alternative Split Query: If you prefer the averages as separate values rather than a row, split this into two queries: one for the aggregates (agg_stats) and one for the extreme rows. This might be slightly faster if you don't need to combine them into a single result set.

Example Output: #

For your sample data, this query will return:

This approach is far more efficient than pulling all rows into your code, as the database handles all the computation and filtering directly on the server.

内容的提问来源于stack exchange，提问作者Nevin Jethmalani