kdb+中列名不匹配时按条件将数据集行添加至另一数据集

Hey there, let's walk through how to replicate your Excel workflow in kdb+ efficiently. I'll start with sample data matching your structure, then break down each step to align exactly with your requirements.

Step 1: Define Sample Data #

First, let's create test datasets that match your schema and example values:

// Sample data1 with columns: id1, id2, exc1, exc2, exc3, exc4
data1:([] id1:`aa2`bb; id2:`12ac`23; exc1:45 23; exc2:54 33; exc3:53 23; exc4:65 12)

// Sample data2 with columns: kid1, id2, sf1, sf2, sf3, sf4, exc1, exc2
data2:([] kid1:`aa2`aa2`bb; id2:`12ac`ads2`23; sf1:55 55 10; sf2:6 7 11; sf3:55 56 12; sf4:66 67 13; exc1:45 45 23; exc2:54 54 33)

Step 2: Generate Link Columns #

We'll create the composite link column in both datasets to identify unique rows:

// Add link column to data1 (format: "id1 | id2")
data1:update link:string[id1]," | ",string[id2] from data1

// Add link column to data2 (format: "kid1 | id2")
data2:update link:string[kid1]," | ",string[id2] from data2

Step 3: Identify Missing Rows #

Filter data2 to get rows that don't exist in data1 (based on the link column):

// Isolate rows in data2 that aren't present in data1
missingRows:select from data2 where link not in data1.link

Step 4: Populate Missing Columns (exc3, exc4) #

Next, we'll pull the exc3 and exc4 values from data1 where kid1 matches id1:

// Create a keyed table from data1 to speed up lookups (keyed by id1)
data1Keyed:`id1 xkey select id1, exc3, exc4 from data1

// Left join missingRows with data1Keyed to get exc3/exc4 values
missingRows:missingRows lj data1Keyed

// Rename kid1 to id1 to match data1's column name
missingRows:rename `id1!`kid1 from missingRows

Step 5: Merge and Clean Up #

Finally, align the column order of missingRows with data1, then append them together:

// Reorder columns to match data1's structure
missingRows:select id1, id2, exc1, exc2, exc3, exc4, link from missingRows

// Append missing rows to the original data1
finalData:raze data1, missingRows

// Optional: Drop the temporary link column if no longer needed
finalData:drop `link from finalData

Verification #

Running the above code will give you finalData with the missing row from data2 added, and exc3/exc4 populated from the matching id1 row in data1. For our sample data, the result will include the row aa2 ads2 45 54 53 65 (pulling exc3=53, exc4=65 from the existing aa2 row in data1).

Notes for Large Datasets #

• Using keyed tables (xkey) ensures fast lookups even with large volumes of data.
• If there's a chance kid1 doesn't exist in data1, the left join will leave exc3/exc4 as null—you can add a step to fill these with default values (e.g., update exc3:0^exc3, exc4:0^exc4 from missingRows) if needed.

内容的提问来源于stack exchange，提问作者user8415577