SQLite跨多表ID分析优化及Xkos表批量合并方案咨询

Alright, let's fix that slow bash loop issue once and for all. Dealing with 525 tables manually is a huge waste of time, so we'll use a combination of bash scripting and SQLite's bulk processing to make this efficient. Here are two solid approaches depending on your needs:

Approach 1: Merge All KOS Tables into a Temporary Table (Great for Repeated Queries) #

This method creates a temporary normalized table that flattens all your koX columns into rows, then uses that table to run your stats. It's perfect if you need to run multiple analyses on the same data.

Step 1: Generate the Merge Script #

Create a bash script (generate_merge_script.sh) to auto-generate the SQL needed to combine all 525 tables:

#!/bin/bash

# Set your database name here
DB_NAME="your_database.db"

# Start building the SQL script
echo "CREATE TEMP TABLE temp_all_kos (batch_id INT, ko_id INT);" > merge_kos.sql

# Loop through each KOS table from 1 to 525
for N in {1..525}; do
    TABLE="${N}kos"
    # Start with the first ko column for the table
    SQL_SNIPPET="SELECT batch_id, ko1 FROM ${TABLE} WHERE ko1 IS NOT NULL"
    
    # Add UNION ALL for remaining ko columns in the table
    for X in $(seq 2 $N); do
        SQL_SNIPPET="${SQL_SNIPPET} UNION ALL SELECT batch_id, ko${X} FROM ${TABLE} WHERE ko${X} IS NOT NULL"
    done
    
    # Append to the SQL script (skip UNION ALL for the first table)
    if [ $N -eq 1 ]; then
        echo "${SQL_SNIPPET};" >> merge_kos.sql
    else
        echo "UNION ALL ${SQL_SNIPPET};" >> merge_kos.sql
    fi
done

# Add the final stats query to the script
echo "
-- Calculate counts per ko_id for data1=0 and data1≠0
SELECT
    tk.ko_id,
    COUNT(CASE WHEN sd.data1 = 0 THEN 1 END) AS count_data1_zero,
    COUNT(CASE WHEN sd.data1 != 0 THEN 1 END) AS count_data1_nonzero
FROM temp_all_kos tk
JOIN simulationDetails sd ON tk.batch_id = sd.batch_id
GROUP BY tk.ko_id
ORDER BY tk.ko_id;
" >> merge_kos.sql

# Run the script against your SQLite database
sqlite3 $DB_NAME < merge_kos.sql

Step 2: Adjust for Your Schema #

• If your KOS tables use a different foreign key name instead of batch_id (e.g., simulation_id), update the SELECT and JOIN clauses to match.
• Remove the WHERE koX IS NOT NULL parts only if you want to count NULL values (unlikely for ID fields).

Approach 2: Single Query with Dynamic UNION ALL (Great for One-Time Stats) #

If you only need to run this count once, you can skip the temporary table and generate a single large query that does everything in one go:

Step 1: Generate the Single Query Script #

Create another bash script (generate_count_script.sh):

#!/bin/bash

DB_NAME="your_database.db"

# Start building the count query
echo "
SELECT
    ko_id,
    COUNT(CASE WHEN sd.data1 = 0 THEN 1 END) AS count_data1_zero,
    COUNT(CASE WHEN sd.data1 != 0 THEN 1 END) AS count_data1_nonzero
FROM (
" > count_kos.sql

# Flag to skip UNION ALL for the first table
FIRST_TABLE=1

for N in {1..525}; do
    TABLE="${N}kos"
    SQL_SNIPPET="SELECT batch_id, ko1 AS ko_id FROM ${TABLE} WHERE ko1 IS NOT NULL"
    
    for X in $(seq 2 $N); do
        SQL_SNIPPET="${SQL_SNIPPET} UNION ALL SELECT batch_id, ko${X} AS ko_id FROM ${TABLE} WHERE ko${X} IS NOT NULL"
    done
    
    if [ $FIRST_TABLE -eq 1 ]; then
        echo "    ${SQL_SNIPPET}" >> count_kos.sql
        FIRST_TABLE=0
    else
        echo "    UNION ALL ${SQL_SNIPPET}" >> count_kos.sql
    fi
done

# Close the subquery and add JOIN/GROUP BY logic
echo "
) AS all_kos
JOIN simulationDetails sd ON all_kos.batch_id = sd.batch_id
GROUP BY ko_id
ORDER BY ko_id;
" >> count_kos.sql

# Execute the query
sqlite3 $DB_NAME < count_kos.sql

Key Optimizations to Note #

• Eliminates Bash Overhead: Instead of running 525 separate SQLite queries (each with connection setup/teardown), we run one bulk operation.
• Normalized Data: Flattening columns into rows turns your wide tables into a format that's easy to aggregate with standard SQL.
• Index Usage: Make sure simulationDetails.batch_id has an index (it should if it's the primary key) — this will speed up the JOIN drastically.

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