1. RANK

• 分组创建rank， row_number()是没有并列的

SELECT  [order_details_id]
      ,[order_id]
      ,[pizza_id]
      ,[quantity]
      ,[order_date]
      ,[unit_price]
      ,[total_price]
      ,[pizza_name]
      ,ROW_NUMBER() OVER(partition by order_id order by total_price desc) rank_by_id
  FROM [sales].[dbo].[pizza_sales]

image.png

2. 前后和当前行的总和

image.png

• 建表语句

CREATE TABLE day_sales (
    ID INT,
    sale_day DATE,
    total_sale INT
);

INSERT INTO day_sales (ID, Date, Sales)
VALUES
    (1, '2022-06-22', 603),
    (2, '2022-06-21', 478),
    (3, '2022-06-20', 679),
    (4, '2022-06-19', 443),
    (5, '2022-06-18', 540),
    (6, '2022-06-17', 740),
    (7, '2022-06-16', 850),
    (8, '2022-06-15', 604),
    (9, '2022-06-14', 339),
    (10, '2022-06-13', 905);

• 指定窗口的范围，它按照日期升序排序，并包含了当前行及其前后一行的数据。

SELECT *, SUM(Sales) OVER (ORDER BY DATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS Rolling_Sum
FROM [sales].[dbo].[day_sales];

3. First value和 last value

• 获取分组日期的第一个是数值和最后一个数值

SELECT *, FIRST_VALUE(Sales) OVER(PARTITION BY State order by Date) First_sales
,LAST_VALUE(Sales) OVER(PARTITION BY State order by Date rows between unbounded preceding and unbounded following) Last_sales
FROM [sales_info_01]

image.png

4. 移动平均值 moving average

• 使用Partition by 来计算滚动平均值

select *, avg(sales) over(order by dateTime rows between 2 preceding and current row) as three_days_rolling_avgs,
avg(sales) over (order by dateTime rows between 6  preceding and current row) as seven_days_rolling_avgs