时间戳

首页 > 解决方案 > SQL Windowing Ranks 函数

sql - SQL Windowing Ranks 函数

问题描述

 SELECT
        *
    FROM (
        SELECT 

            Product,
            SalesAmount,
            ROW_NUMBER() OVER (ORDER BY SalesAmount DESC) as RowNum,
            RANK() OVER (ORDER BY SalesAmount DESC) as RankOf2007,
            DENSE_RANK() OVER (ORDER BY SalesAmount DESC) as DRankOf2007
        FROM (
            SELECT

                c.EnglishProductName as Product,
                SUM(a.SalesAmount) as SalesAmount,
                b.CalendarYear as CalenderYear
            FROM FactInternetSales a
            INNER JOIN DimDate b
                ON  a.OrderDateKey=b.DateKey
            INNER JOIN DimProduct c
                ON  a.ProductKey=c.ProductKey

            WHERE   b.CalendarYear IN (2007)
            GROUP BY c.EnglishProductName,b.CalendarYear
            ) Sales
        ) Rankings
    WHERE [RankOf2007] <= 5
    ORDER BY [SalesAmount] DESC

我目前正在根据销售额的总和以降序方式对产品进行排序，并根据 2007 年每个产品的销售额总和获得排名，如果产品 1 在当年的销售额最高，则排名第一，依此类推。

我的数据库数据的图像

目前我的数据库表看起来像图像中提到的那个（除了 RankOf2008 和 DRankOf2008 列），我想在 2008 年对 2007 年的前 5 名产品进行排名（如果 2007 年的前 5 名产品中的任何一个是空值） 2008 年未售出）在同一张表中，并排列，如上图所示。

标签： sqlsql-servertsql

解决方案

可能是你需要这样的东西。

首先获取所有产品的排名，然后按年份划分，即产品的年度排名并在 CTE 的帮助下获取所需的数据。

WITH cte
AS (
    SELECT *
    FROM (
        SELECT Product
            ,SalesAmount
            ,CalenderYear
            ,ROW_NUMBER() OVER (
                PARTITION BY CalenderYear ORDER BY SalesAmount DESC
                ) AS RowNum
            ,RANK() OVER (
                PARTITION BY CalenderYear ORDER BY SalesAmount DESC
                ) AS RankOf2007
            ,DENSE_RANK() OVER (
                PARTITION BY CalenderYear ORDER BY SalesAmount DESC
                ) AS DRankOf2007
        FROM (
            SELECT c.EnglishProductName AS Product
                ,SUM(a.SalesAmount) AS SalesAmount
                ,b.CalendarYear AS CalenderYear
            FROM FactInternetSales a
            INNER JOIN DimDate b ON a.OrderDateKey = b.DateKey
            INNER JOIN DimProduct c ON a.ProductKey = c.ProductKey
            --WHERE b.CalendarYear IN (2007)
            GROUP BY c.EnglishProductName
                ,b.CalendarYear
            ) Sales
        ) Rankings
        --WHERE [RankOf2007] <= 5
        --ORDER BY [SalesAmount] DESC
    )
SELECT a.*
    ,b.DRankOf2007 AS [DRankOf2008]
    ,b.RankOf2007 AS [RankOf2008]
FROM cte a
LEFT JOIN cte b ON a.Product = b.Product
    AND b.CalenderYear = 2008
WHERE a.CalenderYear = 2007
    AND a.[RankOf2007] <= 5

推荐阅读