CODEWITHSUNDEEP
mysqlsqlvertica
Arpit Srivastava
Arpit Srivastava

Reputation: 121

How to rank multiple columns?

I have the data in the below format:

vendor  metric1 metric2 metric3 metric4 metric5 metric6 metric7
a       69      11      53      94      50      45      39
b       54      80      3       63      23      24      57
c       59      41      100     10      53      6       94
d       92      92      1       44      49      84      55
e       86      89      66      79      27      22      67

Assume that these metrics are affecting vendor performance and i have to find out which 3 metrics are affecting the most. e.g for vendor "a" i need the names of metric4,metric1,metric3 in that order as these 3 have maximum values

Thanks in advance.

Upvotes: 3

Views: 151

Answers (2)

Greg Viers
Greg Viers

Reputation: 3523

Use unions to normalize the data, then a CTE to rank them by vendor:

WITH cte
AS (
    SELECT vendor, value, ROW_NUMBER() OVER (
            PARTITION BY vendor ORDER BY value DESC
    ) AS rank
    FROM (
        SELECT vendor, 1 AS metric, metric1 AS value FROM mytable UNION ALL
        SELECT vendor, 2 AS metric, metric2 AS value FROM mytable UNION ALL
        SELECT vendor, 3 AS metric, metric3 AS value FROM mytable UNION ALL
        SELECT vendor, 4 AS metric, metric4 AS value FROM mytable UNION ALL
        SELECT vendor, 5 AS metric, metric5 AS value FROM mytable UNION ALL
        SELECT vendor, 6 AS metric, metric6 AS value FROM mytable UNION ALL
        SELECT vendor, 7 AS metric, metric7 AS value FROM mytable
    ) AS DerivedByUnion
)
SELECT vendor, value, rank 
FROM cte
WHERE rank <= 3
ORDER BY NAME, rank ;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

In Vertica, the easiest way is going to be to unpivot the data and then use row_number() and perhaps re-aggregate:

select vendor,
       max(case when seqnum = 1 then which end) as topmetric,
       max(case when seqnum = 2 then which end) as secondmetric,
       max(case when seqnum = 3 then which end) as thirdmetric
from (select vendor, which,
             row_number() over (partition by vendor order by metric desc) as seqnum
      from ((select vendor, 'metric1' as which, metric1 as metric from t) union all
            (select vendor, 'metric2' as which, metric2 from t) union all
            (select vendor, 'metric3' as which, metric3 from t) union all
            (select vendor, 'metric4' as which, metric4 from t) union all
            (select vendor, 'metric5' as which, metric5 from t) union all
            (select vendor, 'metric6' as which, metric6 from t) union all
            (select vendor, 'metric7' as which, metric7 from t)
           ) v
     ) v
group by vendor;

A massive case statement is another approach. But, it is prone to error, complicated, and then even more complicated if the values are not distinct or possibly NULL.

Upvotes: 1

Related Questions