CODEWITHSUNDEEP
sqlsql-servert-sqlsumwindow-functions
Baldie47
Baldie47

Reputation: 1254

Count half of rest of a partition by from position

I'm trying to achieve the following results:

enter image description here

now, the group comes from

 SUM(CASE WHEN seqnum <= (0.5 * seqnum_rev) THEN i.[P&L] END) OVER(PARTITION BY i.bracket_label ORDER BY i.event_id) AS [P&L 50%],

I need that in each iteration it counts the total of rows from the end till position (seq_inv) and sum the amounts in P&L only for the half of it from that position.

for example, when 

seq = 2

seq_inv will be = 13, half of it is 6 so I need to sum the following 6 positions from seq = 2.

when seq = 4 there are 11 positions till the end (seq_inv = 11), so half is 5, so I want to count 5 positions from seq = 4.

I hope this makes sense, I'm trying to come up with a rule that will be able to adapt to the case I have, since the partition by is what gives me the numbers that need to be summed.

I was also thinking if there was something to do with a partition by top 50% or something like that, but I guess that doesn't exist.

Upvotes: 0

Views: 83

Answers (2)

MatBailie
MatBailie

Reputation: 86706

I have the advantage that I've helped him before and have a little extra context.

That context is that this is just the later stage of a very long chain of common table expressions. That means self-joins and/or correlated sub-queries are unfortunately expensive.

Preferably, this should be answerable using window functions, as the data set is already available in the appropriate ordering and partitioning.

My reading is this...

The SUM(5:9) (meaning the sum of rows 5 to row 9, inclusive) is equal to SUM(5:end) - SUM(10:end)

That leads me to this...

WITH
  cumulative AS
(
  SELECT
    *,
    SUM([P&L]) OVER (PARTITION BY bracket_label ORDER BY event_id DESC)  AS cumulative_p_and_l
  FROM
    data
)
SELECT
  *,
  cum_val - LEAD(cumulative_p_and_l, seq_inv/2, 0) OVER (PARTITION BY bracket_label ORDER BY event_id)  AS p_and_l_50_perc,
  cum_val - LEAD(cumulative_p_and_l, seq_inv/4, 0) OVER (PARTITION BY bracket_label ORDER BY event_id)  AS p_and_l_25_perc,
FROM
  cumulative

NOTE: Using , &, % in column names is horrendous, don't do it ;)

EDIT: Corrected the ORDER BY in the cumulative sum.

Upvotes: 2

GMB
GMB

Reputation: 222382

I don't think that window functions can do what you want. You could use a correlated subquery instead, with the following logic:

select
    t.*,
    (
        select  sum(t1.P&L] 
        from mytable t1 
        where t1.seq - t.seq between 0 and t.seq_inv/2
    ) [P&L 50%]
from mytable t

Upvotes: 2

Related Questions