如何在Redshift SQL中实现带条件的移动最小值计算

要在Redshift SQL中实现类似Excel条件判断逻辑的移动最小值计算，可使用以下查询语句：

SELECT *,
  MIN(avg_ttm) OVER (PARTITION BY ASIN ORDER BY ASIN,
  CASE WHEN ageband_on_cost_acquisition_date = 'Current' THEN 1 
    WHEN ageband_on_cost_acquisition_date = '[31-60]' THEN 2 
    WHEN ageband_on_cost_acquisition_date = '[61-90]' THEN 3 
    WHEN ageband_on_cost_acquisition_date = '[91-120]' THEN 4 
    WHEN ageband_on_cost_acquisition_date = '[121-150]' THEN 5 
    WHEN ageband_on_cost_acquisition_date = '[151-180]' THEN 6 
    WHEN ageband_on_cost_acquisition_date = '[181-270]' THEN 7 
    WHEN ageband_on_cost_acquisition_date = '[271-360]' THEN 8 
    WHEN ageband_on_cost_acquisition_date = 'Over 360' THEN 9 
    END ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS proposed_rate
FROM ccogs;

逻辑说明 #

• 按ASIN字段分区，确保每个ASIN的数据独立计算移动最小值
• 通过CASE表达式将ageband_on_cost_acquisition_date的文本时间区间转换为数字排序键（1到9），保证排序符合时间先后逻辑
• 窗口函数MIN(avg_ttm)的计算范围限定为当前行及前一行，最终得到的proposed_rate就是这两行中avg_ttm的最小值

内容的提问来源于stack exchange，提问作者Ravindra Reddy