计算百分比随时间的变化

Question

我已经创建了结构和示例数据这里。我不知道如何计算时间的变化。

我想要的结果集是：

a   | % growth
abc | 4.16
def | 0.83
hig | -0.2

% change是(最后一个值-第一个值)/天：

a   | % growth
abc | (30-5) / 6
def | (6-1) / 6
hig | (4-5) / 5

我在努力：

SELECT a.*, 
       b.val, 
       c.val 
FROM   (SELECT a, 
               Min(dt) AS lowerDt, 
               Max(dt) AS upperDt 
        FROM   tt 
        GROUP  BY a) a 
       LEFT JOIN tt b 
              ON b.dt = a.lowerdt 
                 AND b.a = a.a 
       LEFT JOIN tt c 
              ON c.dt = a.upperdt 
                 AND b.a = a.a 

如果可能的话，我想避免做CTE。

Answer 1

你不想要min和max，你真的想要first和last。

我这样做的一种方法是使用ROW_NUMBER()来告诉我从开始到结束的位置。然后使用MAX(CASE WHEN pos=1 THEN x ELSE null END)获取我想要的值。

SELECT
  a,
  MAX(CASE WHEN pos_from_first = 1 THEN dt  ELSE NULL END)  AS first_date,
  MAX(CASE WHEN pos_from_final = 1 THEN dt  ELSE NULL END)  AS final_date,
  MAX(CASE WHEN pos_from_first = 1 THEN val ELSE NULL END)  AS first_value,
  MAX(CASE WHEN pos_from_final = 1 THEN val ELSE NULL END)  AS final_value,
  100
  *
  CAST(MAX(CASE WHEN pos_from_final = 1 THEN val ELSE NULL END) AS DECIMAL(9,6))
  /
  CAST(MAX(CASE WHEN pos_from_first = 1 THEN val ELSE NULL END) AS DECIMAL(9,6))
  -
  100                                                       AS perc_change
FROM
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY a ORDER BY dt  ASC) AS pos_from_first,
    ROW_NUMBER() OVER (PARTITION BY a ORDER BY dt DESC) AS pos_from_final,
    *
  FROM
    tt
)
  AS ordered
GROUP BY
  a

http://sqlfiddle.com/#!6/ad95d/11