kdb+: replace null integer with 0

问题:

Consider the following table:

myTable:

a     b
-------
1     
2
3     10
4     50
5     30

How do I replace the empty cells of b with a zero? So the result would be:

a     b
-------
1     0
2     0
3     10
4     50
5     30

Right now I'm doing:

myTable: update b:{$[x~0Ni;0;x]}'b from myTable

But I am wondering whether there is a better/easier solution for doing this.

回答1:

Using the fill operator (^)

Example Table:

q) tbl:flip`a`b!(2;0N)#10?0N 0N 0N,til 3
    a b
    ---
    0 2
    1 1
    1 1
      1
    1

Fill nulls in all columns with 0:

q)0^tbl
    a b
    ---
    0 2
    1 1
    1 1
    0 1
    1 0

Fill nulls only in selective columns with 0:

q)update 0^b from tbl
    a b
    ---
    0 2
    1 1
    1 1
      1
    1 0

回答2:

In some cases when you want to fill the null values from the previous non-null value, you can use fills functions.

q)tbl:flip`a`b!(2;0N)#10?0N 0N 0N,til 3
a b
---
2 1

  1
1 2
0

update fills a, fills b from tbl
q)a b
---
2 1
2 1
2 1
1 2
0 2

using fills with aggregation

q)tbl:update s:5?`g`a from flip`a`b!(2;0N)#10?0N 0N 0N,til 3
a b s
-----
1   a
  2 a
0 0 g
2 2 g
0   a

q)update fills a, fills b by s from tbl
a b s
-----
1   a
1 2 a
0 0 g
2 2 g
0 2 a