ROW_NUMBER 或其他顺序取决于日期 （SQL）

您可以手动将行与“以前的”行进行比较，并基于它创建更改指示器。该指标的稍后求和将为您提供分区号，识别不间断的块。

这个分区号可以用在你的分区子句中。row_number

with tt as (
    select '1a' as bc, 11 as io, '2021-01-01' as date
    union all
    select '1a' as bc, 11 as io, '2021-01-02' as date
    union all
    select '1a' as bc, 12 as io, '2021-01-03' as date
    union all
    select '1a' as bc, 11 as io, '2021-01-04' as date
), t2 as(
  select 
    tt.*,
    case when bc = lag(bc) over (order by date) and io = lag(io) over (order by date) then 0 else 1 end ind
  from tt
), t3 as (
  select 
    t2.*, 
    sum(ind) over ( order by date) pid 
  from t2
)
select 
  bc,
  io, 
  date, 
  row_number() over (partition by pid order by date) rn
from t3

演示（在MySQL中）在这里。

---

编辑：忽略忽略案例条件提及的部分的更改：bcbc

with tt as (
    select '1a' as bc, 11 as io, '2021-01-01' as date
    union all
    select '1b' as bc, 11 as io, '2021-01-02' as date
    union all
    select '1a' as bc, 12 as io, '2021-01-03' as date
    union all
    select '1a' as bc, 11 as io, '2021-01-04' as date
), t2 as(
  select 
    tt.*,
    case when io = lag(io) over (order by date) then 0 else 1 end ind
  from tt
), t3 as (
  select 
    t2.*, 
    sum(ind) over ( order by date) pid 
  from t2
)
select 
  bc,
  io, 
  date, 
  row_number() over (partition by pid order by date) rn
from t3

MySQL演示在这里。

这是一个常见的间隙和孤岛问题：对每个键的连续属性值进行分组（给定一些“类似时间”的维度）。方法是这样的：

• 按时间尺寸排序的每个键计算。row_number
• 按时间维度排序的按每个键和感兴趣的属性进行计算。row_number
• 找出它们的差异 - 这会对连续的相同属性值进行分组（在更改某些属性时，第二个值重置为 1，并且差异增加）。row_number

下面是一个查询：

with src as (
    select inline(array(
      struct('1a', 11, date '2022-01-01'),
      struct('1a', 11, date '2022-01-02'),
      struct('1a', 12, date '2022-01-03'),
      struct('1a', 11, date '2022-01-04')
    )) as (bc, io, dt)
)
, prepared as (
  select
    src.*
    /*Partition by keys*/
    , row_number() over(partition by bc order by dt asc)
        /*Partition by keys AND attributes to track changes and create groups*/
      - row_number() over(partition by bc, io order by dt asc) as rn_diff
  from src
)
select
  bc, io, dt
  /*Partition by keys AND attributes to track changes AND group number*/
  , row_number() over(partition by bc, io, rn_diff order by dt asc) as rn
from prepared
order by dt asc

基于 Postgres 的 dbfiddle（添加了更多属性）。