1

I need to calculate a weighted average using the following table. I have to calculate this number for metric_1 up to metric_10. All the metric columns have a value from 1 to 5.

------------------------------------------------------
id | var      | weight | metric_1 | metric_2 | week
------------------------------------------------------
1  | 9        | 0.56   | 4        | 2        | 8
2  | 3        | 1      | 3        | 1        | 7
3  | 7        | 0.98   | 3        | 5        | 8
------------------------------------------------------

Here's the query I'm using. I need same query for each metric, from 1 to 10.

SELECT
    weight, sample_size, detractor,
    (promoter * 100) / weight AS promoter_p,
    (promoter - detractor) * 100 / weight AS score
FROM
    (
        SELECT
            COUNT(surveys.id) AS sample_size,
            SUM(weight),
            SUM(CASE WHEN var BETWEEN 9 AND 10 THEN pond END) AS promoter,
            SUM(CASE WHEN var BETWEEN 0 AND 6  THEN pond END) AS detractor,
            MAX(week)
        FROM surveys
        WHERE (var BETWEEN 0 AND 10) AND (metric_1 BETWEEN 1 AND 5)
    ) t

I there a way to combine those queries instead of having one query for each metric? The only difference is the WHERE clause:

        WHERE (...) AND (metric_1 BETWEEN 1 AND 5) /* metric_2, ...10 */
  • Which version of Postgres? – ypercubeᵀᴹ Mar 6 '15 at 16:35
  • @ypercube I'm using 9.4 – Dilbert Vanwinklespout Mar 6 '15 at 17:20
  • If that's correct: All the metric columns have a value from 1 to 5., then you don't have a problem, because all results are the same. Please clarify: how selective are the predicates metric_n BETWEEN 1 AND 5? – Erwin Brandstetter Mar 7 '15 at 4:19
  • @ErwinBrandstetter yes, each metric column can only have a value in the range of 1 to 5. But I don't understand how that changes anything? – Dilbert Vanwinklespout Mar 8 '15 at 20:07
  • The predicate metric_1 BETWEEN 1 AND 5 is TRUE for every row and therefore irrelevant. The result is the same for every metric_n. – Erwin Brandstetter Mar 8 '15 at 22:17
0

metric columns are redundant in survey table. we can create a new metric type column to differentiate multiple metrics and to normalize survey table. we can even do this normalization in inline view.

following sample query may explain this. it use SurveyNormalized inline view and it take aggregate group by metricType.

CREATE TABLE #Surveys(id int, VAR int, [weight] float, metric_1 int, metric_2 int, [week] int)

INSERT INTO #Surveys VALUES(1, 9, 0.56 , 4, 2, 8), (2, 3, 1, 3, 1, 7), (3, 7, 0.98 , 3, 5, 8)

SELECT
    weight, sample_size, detractor,
    (promoter * 100) / weight AS promoter_p,
    (promoter - detractor) * 100 / weight AS score,
    t.metricType
FROM
    (
        SELECT
            COUNT(id) AS sample_size,
            SUM(weight) [weight],
            SUM(CASE WHEN var BETWEEN 9 AND 10 THEN 1 END) AS promoter,
            SUM(CASE WHEN var BETWEEN 0 AND 6  THEN 2 END) AS detractor,
            MAX(week) wk,
            metricType
        FROM (
                SELECT id, var, weight, metric_1 AS metric, 'metric_1' AS metricType, week
                FROM #Surveys
                WHERE (var BETWEEN 0 AND 10) AND (metric_1 BETWEEN 1 AND 5)
                UNION
                SELECT id, var, weight, metric_2 AS metric, 'metric_2' AS metricType, week
                FROM #Surveys
                WHERE (var BETWEEN 0 AND 10) AND (metric_2 BETWEEN 1 AND 5)
            )SurveyNormalised
            GROUP BY SurveyNormalised.metricType

    ) t
  • That's very cool. Thanks. But I don't understand something: The user can have a different response for each metric type. How do we account for that? So mertric_1 can be 4, and metric_2 can be 6, etc... I think I'm missing something... – Dilbert Vanwinklespout Mar 6 '15 at 16:15
  • do you mean where clause in inline SurveyNormalised view? – vmds Mar 6 '15 at 16:42
  • Ahh, never mind! I understand now. Thanks. Wouldn't it be more efficient to move the WHERE (var BETWEEN 0 AND 10) outside of the select clauses, just below the GROUP BY, since it's applicable to the entire query? – Dilbert Vanwinklespout Mar 6 '15 at 17:19
0

Another way to solve this:

WITH cte AS 
  ( SELECT 
        q.query_id,
        COUNT(d.id) AS sample_size,
        SUM(d.weight) AS weight,
        SUM(CASE WHEN d.var BETWEEN 9 AND 10 THEN d.pond END) AS promoter,
        SUM(CASE WHEN d.var BETWEEN 0 AND 6  THEN d.pond END) AS detractor,
        MAX(d.week) AS max_week
    FROM
        generate_series(1, 3)      -- change 3 to the number of different queries
          AS q (query_id) 
      CROSS JOIN
        surveys AS s
      LEFT JOIN LATERAL
        ( SELECT 1, s.*
          WHERE (metric_1 BETWEEN 1 AND 5)  --- query 1
        UNION ALL
          SELECT 2, s.*
          WHERE (metric_2 BETWEEN 3 AND 7)  --- query 2
        UNION ALL
          SELECT 3, s.*
          WHERE (metric_3 BETWEEN 6 AND 9)  --- query 3
        --- etc
        ) 
          AS d (query_id, id, var, weight, metric_1, metric_2, week) 
        ON d.query_id = q.query_id
    WHERE 
        (s.var BETWEEN 0 AND 10)
    GROUP BY
        q.query_id
    ) 
SELECT
    query_id,
    weight, sample_size, detractor, max_week,
    (promoter * 100) / weight AS promoter_p,
    (promoter - detractor) * 100 / weight AS score
FROM
    cte
ORDER BY
    query_id ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.