sql - 在 SQL Server 中通过列更改进行排名

Question

下面是我想根据每个产品的 Install_Status 和 Dates 列更改来排名的原始表。

询问：

;WITH CTE AS 
(
   SELECT  
       Product, Install_Status, Date_Inserted,
       DENSE_RANK() OVER (PARTITION BY Product ORDER BY rank) rank
   FROM
       (SELECT 
            Product, Install_Status, Date_Inserted,
            ROW_NUMBER() OVER (ORDER BY Product, Date_Inserted) -
               ROW_NUMBER() OVER (PARTITION BY Product, Install_Status ORDER BY Product, Date_Inserted ASC) AS rank
        FROM 
            [dbo].[Product_table]) t
)
SELECT * 
FROM CTE
ORDER BY Product, Date_Inserted;

此 SQL 查询返回以下结果：

但是，我没有按日期获得产品 A 的适当排名。我的预期结果如下：

标签： sqlsql-server

Answer 1

你似乎想计算变化。对于此用途lag()和累积总和：

select t.*,
       sum(case when prev_install_status = install_status then 0 else 1 end) over
           (partition by product order by date_inserted) as rank
from (select t.*,
             lag(install_status) over (partition by product order by date_inserted) as prev_install_status
      from t
     ) t;