Улей, упорядочивание строк с использованием переменной задержки

Question

У меня есть следующая таблица улья:

product |  price
   A    |   100
   B    |   102
   C    |   220
   D    |   240
   E    |   242
   F    |   410

Для каждой строки я хотел бы разделить более низкую цену на текущую цену, если результат больше 0,9, я хотел бы увеличить номер строки. Если результат меньше 0,9, тогда для этой строки номер строки должен быть равен 1, а текущая цена становится более низкой, а затем повторяется.

Результат должен выглядеть так:

product |  price | row_number
   A    |   100  |     1
   B    |   102  |     2
   C    |   220  |     1
   D    |   240  |     2
   E    |   242  |     3
   F    |   410  |     1

Так как:

lower price = 100: product A get 1 as row_number
100/102 >= 0.9: product B get 2 as row_number
100/220 < 0.9: product C get 1 as row_number, lower price = 220
220/240 >= 0.9: product D get 2 as row_number
220/242 >= 0.9: product E get 3 as row_number
220/410 < 0.9: product F get 1 as row_number, lower price = 410

Я думал о создании временного_прибора, только что упорядоченного по цене:

product |  price | temp_row_number
   A    |   100  |     1
   B    |   102  |     2
   C    |   220  |     3
   D    |   240  |     4
   E    |   242  |     5
   F    |   410  |     6

А потом:

Select
   product,
   price,
   case
     when lag(price,temp_row_number-1,0)/price over() >= 0.9 then lag(price,temp_row_number-1,0)
     else price
   end as test
from my_table

Это восстановит:

product |  price | test
   A    |   100  | 100
   B    |   102  | 100
   C    |   220  | 220
   D    |   240  | 240
   E    |   242  | 242
   F    |   410  | 410

Но в идеале я хотел бы получить

product |  price | test
   A    |   100  | 100
   B    |   102  | 100
   C    |   220  | 220
   D    |   240  | 220
   E    |   242  | 220
   F    |   410  | 410

Таким образом, я мог вычислить row_number row с помощью функции row_number() по порядку продукта и цены и получить ожидаемый результат.

Answer 1

С CTE КАК
(выберите продукт, цену, (случай, когда цена между 100 и 200, затем 1, когда цена между 200 и 300, затем 2, когда цена между 300 и 400, затем 3 КОНЕЦ) AS RN

         FROM #test) 

ВЫБЕРИТЕ Product,Price, ROW_NUMBER() OVER (PARTITION BY RN ORDER BY RN) ОТ CTE ORDER BY Product