计算 Postgresql 中连续范围的百分比变化

debugcn 发表于 Dev

亚历克斯·尤苏波夫

我需要计算连续范围内的价格百分比变化。例如，如果价格开始向上或向下移动，并且我有一系列减少或增加的值，我需要获取该序列的第一个和最后一个值并计算变化。

我正在使用窗口滞后函数来计算方向，我的问题 - 我无法为序列生成唯一的 RANK 来计算百分比变化。我厌倦了 RANK、ROW_NUMBER 等的组合，但没有运气。

这是我的查询


WITH partitioned AS (
  SELECT
    *,
    lag(price, 1) over(ORDER BY time) AS lag_price

  FROM prices 
),
sequenced AS (
  SELECT
    *,
    CASE 
      WHEN price > lag_price THEN 'up'
      WHEN price < lag_price THEN 'down'
      ELSE 'equal'
    END
     AS direction
  FROM partitioned

),
ranked AS (
  SELECT
    *,
    -- Here's is the problem
    -- I need to calculate unique rnk value for specific sequence
    DENSE_RANK() OVER ( PARTITION BY direction ORDER BY time) + ROW_NUMBER() OVER ( ORDER BY time DESC) AS rnk
    -- DENSE_RANK() OVER ( PARTITION BY seq ORDER BY time),
    -- ROW_NUMBER() OVER ( ORDER BY seq, time DESC),
    -- ROW_NUMBER() OVER ( ORDER BY seq),
    -- RANK() OVER ( ORDER BY seq)

  FROM sequenced
),
changed AS (
  SELECT *,
    FIRST_VALUE(price) OVER(PARTITION BY rnk ) first_price,
    LAST_VALUE(price) OVER(PARTITION BY rnk ) last_price,
    (LAST_VALUE(price) OVER(PARTITION BY rnk ) / FIRST_VALUE(price) OVER(PARTITION BY rnk ) - 1) * 100 AS percent_change
    FROM ranked

)
SELECT
    *
FROM changed
ORDER BY time DESC;

和带有示例数据的 SQLFiddle

亚历克斯·尤苏波夫

如果有人对这里的解决方案感兴趣，请组建另一个论坛：

  with ct1 as /* detecting direction: up, down, equal */
    (
      select
          price, time,
          case 
            when lag(price) over (order by time) < price then 'down'
            when lag(price) over (order by time) > price then 'up'
            else 'equal' 
          end as dir
      from
          prices
    )
    , ct2 as /* setting reset points */
    (
      select
          price, time,  dir,
          case 
              when coalesce(lag(dir) over (order by time), 'none') <> dir
              then 1 else 0
          end as rst
      from
          ct1
    )
    , ct3 as /* making groups */
    (
      select
          price, time, dir,
          sum(rst) over (order by time) as grp
      from
          ct2
    )
    select /* calculates min, max price per group */
        price, time, dir,
        min(price) over (partition by grp) as min_price,
        max(price) over (partition by grp) as max_price
    from
        ct3
    order by
        time desc;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。