how to get last month aggregate value such as sum?

[hengaini2055]

I have a same question about clickhouse, and get answered, clickhouse-discussion

any(MonthValue) OVER (PARTITION BY objectCode ORDER BY toRelativeMonthNum(pt) 
          Range BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prevMonthValue

How can I get this function in ibis-framework?

[cpcloud]

What have you tried?

[hengaini2055]

We can use SQL get the result:

select toStartOfMonth(period) as pt, objectCode, sum(amount) as MonthValue
   ,any(MonthValue) OVER (PARTITION BY objectCode ORDER BY toRelativeMonthNum(pt) Range BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prevMonthValue
from url('https://github.com/user-attachments/files/16830630/clickhouse_posts.csv', 'CSVWithNames',
  'period DateTime64(3),objectCode String,typeName String,amount Float64' )
where objectCode = 'D00000139'
group by pt, objectCode
ORDER BY
     pt,
     objectCode;

When the objectCode is 'D00000147', there are only two months that have MonthValue. The SQL can get the correct results that prevMonthValue is the previous month value, not the previous row value.

select toStartOfMonth(period) as pt, objectCode, sum(amount) as MonthValue
   ,any(MonthValue) OVER (PARTITION BY objectCode ORDER BY toRelativeMonthNum(pt) Range BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prevMonthValue
from url('https://github.com/user-attachments/files/16830630/clickhouse_posts.csv', 'CSVWithNames',
  'period DateTime64(3),objectCode String,typeName String,amount Float64' )
where objectCode = 'D00000147'
group by pt, objectCode
ORDER BY
     pt,
     objectCode;

The code can run in fiddle.clickhouse.com. That's all.
My question is how to write ibis expression to define prevMonthValue.

[hengaini2055]

run code in https://fiddle.clickhouse.com/1e00c1f7-09e2-4706-bb9d-45a24a01781b

[hengaini2055]

The question have been answered by @gforsyth, link to issues 10059