优化PostgreSQL查询：提升可读性与DRY特性的方法咨询

Great question! Your original query gets the job done, but all that repeated window function logic makes it tough to read and maintain. Let's refactor it to follow the DRY (Don't Repeat Yourself) principle and boost clarity—here's a step-by-step breakdown:

Key Pain Points in the Original Query #

• The complex COUNT(*) FILTER (...) OVER(...) expression is repeated four times (twice for positive calculations, twice for negative). Any future tweaks to this logic would require updating it in multiple spots, which is error-prone.
• Long JSON path expressions clutter the code and make it hard to parse at a glance.
• The CASE statement for avoiding division by zero is overly verbose.

Optimized Query (Using CTE for Reusability) #

We'll use a Common Table Expression (CTE) to extract repeated calculations, alias long expressions, and simplify the division logic:

WITH daily_survey_stats AS (
    SELECT
        date(survey_results.created_at) AS survey_date,
        -- Alias repeated JSON path expressions for readability
        scores#>>'{medic,social,total}' AS total_score,
        coalesce(raw#>>'{survey, denied}', 'f') AS survey_denied,
        -- Calculate total valid responses ONCE per date
        COUNT(*) FILTER (
            WHERE total_score IN ('high','medium','low') OR survey_denied = 'true'
        ) OVER (ORDER BY survey_date) AS total_valid_responses,
        -- Count positive responses ONCE per date
        COUNT(*) FILTER (
            WHERE total_score IN ('high', 'medium')
        ) OVER (ORDER BY survey_date) AS positive_count,
        -- Count negative responses ONCE per date
        COUNT(*) FILTER (
            WHERE total_score = 'low'
        ) OVER (ORDER BY survey_date) AS negative_count
    FROM survey_results
)
SELECT DISTINCT
    survey_date,
    -- Calculate positive percentage with clean division-by-zero safety
    ROUND(
        positive_count * 1.0 / COALESCE(NULLIF(total_valid_responses, 0), 1) * 100,
        2
    ) AS positive,
    -- Calculate negative percentage using the same precomputed values
    ROUND(
        negative_count * 1.0 / COALESCE(NULLIF(total_valid_responses, 0), 1) * 100,
        2
    ) AS negative
FROM daily_survey_stats
ORDER BY survey_date ASC;

What Changed & Why #

1. CTE for Reusable Logic:

   • All window function calculations are moved to the daily_survey_stats CTE, so each count is computed once and referenced later—no more duplicate code.
   • Aliasing long JSON paths (total_score, survey_denied) makes filter conditions concise and easy to understand.

2. Simplified Division-by-Zero Handling:

   • Replaced the bulky CASE statement with COALESCE(NULLIF(total_valid_responses, 0), 1). This does exactly what the original logic did: if total_valid_responses is 0, we use 1 to avoid division by zero; otherwise, we use the actual count.

3. Cleaner Final Select:

   • The main query now just references precomputed values from the CTE, making the percentage calculations straightforward and easy to audit.
   • We kept DISTINCT since we're grouping by date, but the heavy lifting of aggregations is handled in the CTE.

Alternative: Subquery Version (If CTEs Aren't Preferred) #

If you prefer derived tables over CTEs, you can achieve the same result with this variant:

SELECT DISTINCT
    survey_date,
    ROUND(positive_count * 1.0 / COALESCE(NULLIF(total_valid_responses, 0), 1) * 100, 2) AS positive,
    ROUND(negative_count * 1.0 / COALESCE(NULLIF(total_valid_responses, 0), 1) * 100, 2) AS negative
FROM (
    SELECT
        date(survey_results.created_at) AS survey_date,
        scores#>>'{medic,social,total}' AS total_score,
        coalesce(raw#>>'{survey, denied}', 'f') AS survey_denied,
        COUNT(*) FILTER (
            WHERE total_score IN ('high','medium','low') OR survey_denied = 'true'
        ) OVER (ORDER BY date(survey_results.created_at)) AS total_valid_responses,
        COUNT(*) FILTER (WHERE total_score IN ('high', 'medium')) OVER (ORDER BY date(survey_results.created_at)) AS positive_count,
        COUNT(*) FILTER (WHERE total_score = 'low') OVER (ORDER BY date(survey_results.created_at)) AS negative_count
    FROM survey_results
) AS subquery
ORDER BY survey_date ASC;

Both versions follow DRY principles, are far easier to read, and will be simpler to maintain as your requirements evolve.

内容的提问来源于stack exchange，提问作者Mateusz Urbański