根据另一个col获取表中每个组的最小值

I have table (Cost for a Product by location and month/date) in the format below and want to get Min of ChangeDate for whenever Cost has changed.

我有以下格式的表格(按位置和月/日计算的产品成本)，我想在成本发生变化时得到最小的变化量。

Cost            Loc     Prod        ChangeDate
--------------------------------------------------------
1.223000000000  5678    12345678    2010-01-03 00:00:00
1.223000000000  5678    12345678    2010-01-31 00:00:00
1.223000000000  5678    12345678    2010-02-28 00:00:00
1.000000000000  5678    12345678    2010-04-04 00:00:00
1.223000000000  5678    12345678    2010-05-02 00:00:00
1.223000000000  5678    12345678    2010-05-30 00:00:00
1.223000000000  5678    12345678    2010-07-04 00:00:00
1.277200000000  5678    12345678    2010-08-01 00:00:00
1.277200000000  5678    12345678    2010-08-29 00:00:00
1.277200000000  5678    12345678    2010-10-03 00:00:00

Expected output is:

预期的输出是:

Cost           Loc  Prod     CostChangeStartDate
------------------------------------------------
1.223000000000 5678 12345678 2010-01-03 00:00:00
1.000000000000 5678 12345678 2010-04-04 00:00:00
1.223000000000 5678 12345678 2010-05-02 00:00:00
1.277200000000 5678 12345678 2010-08-01 00:00:00

I tried using Row_Number() Over(PartitionBy OrderBy) but the problem is since Cost 1.223000000000 is repeated twice, I am unable to partition it properly so getting only 3 records.

我尝试使用Row_Number() / (PartitionBy OrderBy)，但问题是由于代价为1.223000000000重复了两次，因此无法正确地对它进行分区，因此只能获得3条记录。

1 个解决方案

#1

You can get rows that have a different cost to their previous row using LAG:

您可以使用延迟获得与前一行成本不同的行:

select * from (
    select *, lag(cost, 1) over (partition by Loc, Prod order by ChangeDate) prevCost
    from @products
) x
where prevCost is null or cost <> prevCost