sql server 时间段每天的前几名

原创

mob64ca12df9869 2024-05-03 03:44:27 ©著作权

©著作权归作者所有：来自51CTO博客作者mob64ca12df9869的原创作品，请联系作者获取转载授权，否则将追究法律责任

实现SQL Server时间段每天的前几名

流程图

erDiagram
    CUSTOMERS ||--o| ORDERS : "places"
    ORDERS ||--| ORDER_DETAILS : "contains"

整体流程

步骤	描述
1	创建一个查询，按照时间段和数量排序
2	使用ROW_NUMBER()函数为每一行数据分配一个连续的整数
3	过滤出排名前几的数据

具体步骤

步骤 1：创建查询

-- 创建一个查询，按照时间段和数量排序
SELECT
    DATEPART(YEAR, OrderDate) AS OrderYear,
    DATEPART(MONTH, OrderDate) AS OrderMonth,
    DATEPART(DAY, OrderDate) AS OrderDay,
    SUM(Quantity) AS TotalQuantity
FROM Orders
GROUP BY DATEPART(YEAR, OrderDate), DATEPART(MONTH, OrderDate), DATEPART(DAY, OrderDate)
ORDER BY OrderYear, OrderMonth, OrderDay, TotalQuantity DESC

步骤 2：使用ROW_NUMBER()函数

-- 使用ROW_NUMBER()函数为每一行数据分配一个连续的整数
SELECT
    OrderYear,
    OrderMonth,
    OrderDay,
    TotalQuantity,
    ROW_NUMBER() OVER(ORDER BY TotalQuantity DESC) AS RowNumber
FROM
(
    SELECT
        DATEPART(YEAR, OrderDate) AS OrderYear,
        DATEPART(MONTH, OrderDate) AS OrderMonth,
        DATEPART(DAY, OrderDate) AS OrderDay,
        SUM(Quantity) AS TotalQuantity
    FROM Orders
    GROUP BY DATEPART(YEAR, OrderDate), DATEPART(MONTH, OrderDate), DATEPART(DAY, OrderDate)
) AS SubQuery

步骤 3：过滤出前几名数据

-- 过滤出排名前几的数据
SELECT
    OrderYear,
    OrderMonth,
    OrderDay,
    TotalQuantity
FROM
(
    SELECT
        OrderYear,
        OrderMonth,
        OrderDay,
        TotalQuantity,
        ROW_NUMBER() OVER(ORDER BY TotalQuantity DESC) AS RowNumber
    FROM
    (
        SELECT
            DATEPART(YEAR, OrderDate) AS OrderYear,
            DATEPART(MONTH, OrderDate) AS OrderMonth,
            DATEPART(DAY, OrderDate) AS OrderDay,
            SUM(Quantity) AS TotalQuantity
        FROM Orders
        GROUP BY DATEPART(YEAR, OrderDate), DATEPART(MONTH, OrderDate), DATEPART(DAY, OrderDate)
    ) AS SubQuery
) AS Ranked
WHERE RowNumber <= 5