KDB Q分区内表分组：基于空分区mydb存储多表的技术问询

Let's walk through this step by step—first getting your tables into the correct partitions of mydb, then handling within-partition grouping operations. Here's what you need to do:

First, make sure your partitioned database structure is set up, then filter/write each table to its assigned date partition.

Initialize the Partition Structure #

Assume mydb is your target database directory (adjust the path to match your system):

// Set your database path
dbPath:`:mydb/

// Create the required partition directories (if they don't exist)
system"mkdir -p ",dbPath,"2018.01.01"
system"mkdir -p ",dbPath,"2018.01.02"
system"mkdir -p ",dbPath,"2018.01.03"

Prepare and Write Tables #

Your original table generation code creates dates spanning a 25-day range, but you want each table stored in a single date partition. Let's adjust the tables to only contain the target date, then write them:

npertable:10000000; 

// Generate table1 with only 2018.01.01 dates
table1:([]date:npertable?2018.01.01;acc:npertable?`C123`C132`C321`C121`C131;c:npertable?til 100); 
// Generate table2 with only 2018.01.02 dates
table2:([]date:npertable?2018.01.02;acc:npertable?`C123`C132`C321`C121`C131;c:npertable?til 100); 
// Generate table3 with only 2018.01.03 dates
table3:([]date:npertable?2018.01.03;acc:npertable?`C123`C132`C321`C121`C131;c:npertable?til 100);

// Write each table to its corresponding partition
`dbPath`2018.01.01/table1 set table1
`dbPath`2018.01.02/table2 set table2
`dbPath`2018.01.03/table3 set table3

If you need to keep your original multi-date tables but only store the target date slice in each partition, filter first:

// Example: Filter table1 to only 2018.01.01 data before writing
table1_partitioned:select from table1 where date=2018.01.01
`dbPath`2018.01.01/table1 set table1_partitioned

Once your data is partitioned, you can group within individual partitions or across all partitions (while retaining per-partition results).

Group Within a Single Partition #

Load the database first, then target the specific partition table:

// Load the partitioned database
\l mydb

// Group table1 in the 2018.01.01 partition by `acc`, e.g., sum `c`
select sum_c:sum c, count_rows:count i by acc from `2018.01.01/table1

Group Across All Partitions (Per-Partition Results) #

To run the same grouping logic on every partition and collect results, use a helper function with each:

// Get list of all partition directories
partitions:key `:mydb/

// Define a reusable grouping function
group_by_acc:{[part_path]
    // Get the table name in the partition (assuming one table per partition)
    tab_name:first key part_path;
    // Run grouping on the table
    grouping_result:select sum_c:sum c, count_rows:count i by acc from part_path,tab_name;
    // Add the partition date to the result for clarity
    partition_date:"D"$string first key part_path;
    update partition_date:partition_date from grouping_result
}

// Apply the function to all partitions
all_partition_results:group_by_acc each partitions

// If you want to combine all results into a single table
combined_results:raze all_partition_results

Key Notes #

• Ensure your partition directory names match the date field values in the tables (Kdb+ relies on this for efficient partitioned queries).
• If all partitions use the same table name (e.g., data), you can simplify cross-partition grouping while keeping per-partition context:

  // For uniform table names, get per-partition groups
  select sum_c:sum c by acc, date from data where date within 2018.01.01 2018.01.03
  

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