SQL sum of multiple rank() statements-CodePudding

I am trying to create a master ranking based on the sum of three ranking values; traffic, conversion, and discount.

This is what I have so far

CREATE TEMPORARY TABLE digitalkpi
(
    ITEM VARCHAR(10),
    TRAFFIC INT,
    CONVERSION FLOAT,
    DISCOUNT FLOAT
);

INSERT INTO digitalkpi
VALUES ('shirt', 10000, 1.7, .50),
       ('pant', 70000, 0.7, .10),
       ('pant', 100, .6, .3),
       ('shirt', 90000, 1.1, .2),
       ('shirt', 2345, .4, .4),
       ('pant', 87543, .2, .3);

SELECT 
    *,
    RANK () OVER (PARTITION BY ITEM ORDER BY TRAFFIC DESC) AS TRAFFICRANK,
    RANK () OVER (PARTITION BY ITEM ORDER BY CONVERSION DESC) AS CONVERSIONRANK,
    RANK () OVER (PARTITION BY ITEM ORDER BY DISCOUNT ASC) AS DISCOUNTRANK,
    TRAFFICRANK   CONVERSIONRANK   DISCOUNTRANK AS TOTALRANK,
FROM 
    digitalkpi

Which is working! but the total rank is showing numbers like 4,5,6.. etc so I would like the final ranking to be 1,2,3.

But when I try to make a rank statement with total rank I am getting an error:

SQL compilation error: window function [RANK() OVER (PARTITION BY DIGITALKPI.ITEM ORDER BY DIGITALKPI.TRAFFIC DESC NULLS FIRST)] may not be nested inside another window function

I'm looking for a way to use the total rank to get a list of items 1,2,3 by shirt and pant.

CodePudding user response：

You cannot define an alias in the select clause and use it in the same select clause.

You can make your query a subquery and then access the expression aliases:

SELECT
  ranked.*,
  trafficrank   conversionrank   discountrank AS totalrank,
  RANK () OVER (PARTITION BY item
                ORDER BY trafficrank   conversionrank   discountrank) AS ranking
FROM
(
  SELECT kpi.*,
    RANK () OVER (PARTITION BY item ORDER BY traffic DESC) AS trafficrank,
    RANK () OVER (PARTITION BY item ORDER BY conversion DESC) AS conversionrank,
    RANK () OVER (PARTITION BY item ORDER BY discount ASC) AS discountrank
  FROM digitalkpi kpi
) ranked