SQL多等级（）语句的总和

发布于 2025-02-13 15:06:02 字数 1050 浏览 0 评论 0原文

我正在尝试根据三个排名值的总和创建一个主排名；流量，转换和折扣。

到目前为止，这就是我

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

所拥有的！但是总排名显示的数字是4,5,6。等等，因此我希望最终排名为1,2,3。

但是，当我尝试以总排名进行排名语句时，我会遇到一个错误：

sql汇编错误：窗口函数[rank（）over（digitalkpi.item order digitalkpi.traffic desc nulls nulls nulls digitalkpi.item order corme）]]不可能嵌套在另一个窗口函数中

我正在寻找一种使用总等级来获得一个的方法衬衫和裤子1,2,3的物品清单。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

快乐很简单 2025-02-20 15:06:02

您不能在选择子句中定义别名，并在同一选择子句中使用它。

您可以使查询成为子查询，然后访问表达式别名：

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

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

回复收藏 0 原文

~没有更多了~