CODEWITHSUNDEEP
sqlpostgresql
Alex Yusupov
Alex Yusupov

Reputation: 1077

Calculate percent changes in contiguous ranges in Postgresql

I need to calculate price percent change in contiguous ranges. For example if price start moving up or down and I have sequence of decreasing or increasing values I need to grab first and last value of that sequence and calculate the change.

I'm using window lag function to calculate direction, my problem- I can't generate unique RANK for the sequences to calculate percent changes. I tired combination of RANK, ROW_NUMBER, etc. with no luck.

Here's my query 


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;

and SQLFiddle with sample data

enter image description here

Upvotes: 1

Views: 209

Answers (1)

Alex Yusupov
Alex Yusupov

Reputation: 1077

If anyone interested here's solution, form another forum:

  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;

Upvotes: 1

Related Questions