正在元组范围中查找最大delta

Question

我有以下关系模式

stock_price(symbol: char, date: date, value: int)

我还有一个名为"范围"的表，以的形式列出了一些日期范围

╔════════════╦════════════╗
║    start   ║    end     ║
╠════════════╬════════════╣
║ 2001-10-01 ║ 2001-12-01 ║
║ 2001-12-01 ║ 2001-12-05 ║
║ 2001-12-20 ║ 2001-12-31 ║
╚════════════╩════════════╝

我需要为每个日期范围在值列中找到变化最大的符号（因此为max(max(value)-min(value))）。

输出的一个例子是

╔════════════╦════════════╦════════╦════════════════╦═════════╗
║   start    ║    end     ║ symbol ║  company_name  ║ d_value ║
╠════════════╬════════════╬════════╬════════════════╬═════════╣
║ 2001-10-01 ║ 2001-12-01 ║ AAPL   ║ Apple Inc.     ║ 34.2    ║
║ 2001-12-01 ║ 2001-12-05 ║ MSFT   ║ Microsoft Corp ║ 12.5    ║
║ 2001-12-20 ║ 2001-12-31 ║ GOOG   ║ Alphabet Inc.  ║ 9.3     ║
╚════════════╩════════════╩════════╩════════════════╩═════════╝

Answer 1

这是RANK的一项简单任务：

select start, end, symbol, company_name, d_value
from
 (
   select r.start, r.end, sp.symbol, sp.company_name, 
       max(sp.value)-min(sp.value) as d_value,
       rank() -- rank the maximum difference
       over (partition by r.start, r.end
             order by max(sp.value)-min(sp.value) desc) as rnk          
    from stock_price as sp join range as r
      on sp.date between r.start and r.end
    group by r.start, r.end, sp.symbol, sp.company_name
 ) as dt
where rnk = 1

Answer 2

SELECT
  sub.start,
  sub.end,
  sub.symbol,
  sub.MAX(max_value-min_value) as d_value
FROM (
    SELECT 
       r.start,
       r.end,
       sp.symbol,
       MAX(sp.value) as max_value,
       MIN(p.value) as min_value
    FROM range r 
         LEFT JOIN stock_price sp ON sp.date>=r.start AND sp.date<=r.end
    GROUP BY r.start, r.end, sp.symbol) sub
GROUP BY sub.start, sub.end, sub.symbol