KDB+ Learning Notes (3):Calculation between rows

How to do calculation between roles within groups in KDB+/Q elegantly

It is easy to do calculation between columns for KDB+ tables. Similar to SQL or like ‘mutate’ in the dplyr package of R, we can define the new columns easily. However, calculations between rows can be tough for me as a beginner. Below, I listed some of the methods I tried.

Loop over each group

If the table is not so large, we can loop over each group.

to do the calculation would be very slow and can be comlipated.

Adding new columns to faciliate calculation

A bit thinking steps needed regarding specific problem

The example we use to illustrate this problem is as follows:

The supply and demand of commodies vary on different days. The table includes information on total supply and specified demand of investors in the market. The market runs in the first-come-first-serve way. We would want to know which of the buyers’ orders will be fulfilled and which are not.

In brief, this is the fill model calculation within each commodity name and date. And we need to

  1. fulfill the first several in full amount, when the sum of the first few rows are less than the supply
  2. partially fill the demand, when the cumulative sum is less than the demand but not large enough
  3. set the fill number as 0 when there is no availability in the market

The corresponding calculation will be

  1. calculate cumulative sum of demand in the market
  2. compare cumulative sums and supply in the market
  3. deal with the partial fill

Converge within the group

This is an elegant way to compute the capped sum with in the group using the converge “\”

The example we use to illustrate this problem is as follows:

If the increment between the rows is small, keep the previous value and adopt the new value only when the increment is significant enough.

The use of the converge wrapper is in this link https://code.kx.com/q/ref/accumulators/

Cello dropout

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store