Query with rolling values from last record

ksqlDB
1

I’m trying to make this kind of thing work in ksqlDB query:

address, balance, change_id
a1,1,first                    -> a1,1,first
null,null,second              -> a1,1,second
a1,2,third                    -> a1,2,third
null,null,fourth              -> a1,2,fourth

In “descriptive SQL” terms:

SELECT
  topic.address,
  topic.balance,
  (SELECT LATEST(topic.change_id) FROM topic WHERE topic.address IS NULL) AS change_id
FROM topic
WITHIN 1 MINUTE
WHERE topic.address IS NOT NULL

Is something like this possible? Note that there’s no group key, as the latest entry is just that, the latest entry not tied to specific addresses here.

2

@almindor I’m not 100% certain I understand the desired behavior from your example, however, does the LATEST_BY_OFFSET aggregate function solve your need?

3

Yes, after a lot of trying I was able to do this using a triple join + group by combination. It’s not optimal since the joins add up to the window time but it works.

1 Like
4

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.