请求优化SQLite查询：生成月度企业统计数据

表结构 #

CREATE TABLE IF NOT EXISTS month (
  name TEXT PRIMARY KEY, -- "YYYY-MM" format for uniqueness
  threadId TEXT UNIQUE,
  createdAtOriginal DATETIME,
  createdAt DATETIME DEFAULT CURRENT_TIMESTAMP, -- auto-populated
  updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP -- auto-populated on creation
);

CREATE TABLE IF NOT EXISTS company (
  name TEXT,
  monthName TEXT,
  commentId TEXT UNIQUE,
  createdAtOriginal DATETIME,
  createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
  updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP, 
  PRIMARY KEY (name, monthName),
  FOREIGN KEY (monthName) REFERENCES month(name)
);

统计需求 #

需生成符合以下结构的统计数据数组：

export interface LineChartMultipleData {
  monthName: string;
  firstTimeCompaniesCount: number;
  newCompaniesCount: number;
  oldCompaniesCount: number;
  allCompaniesCount: number;
}

统计规则：

• 为每一组连续降序的月份对（如['2024-03','2024-02']）生成一条数据，monthName为较新的当月
• firstTimeCompaniesCount：当月存在且从未在更早月份出现的企业数量
• newCompaniesCount：当月存在但上月不存在的企业数量
• oldCompaniesCount：当月存在且上月也存在的企业数量
• allCompaniesCount：当月所有企业数量
• 最旧的月份无需生成数据

优化后的SQL查询 #

WITH company_first_occurrence AS (
  SELECT 
    name,
    MIN(monthName) AS first_month
  FROM company
  GROUP BY name
),
month_pairs AS (
  SELECT 
    m1.name AS current_month,
    m2.name AS previous_month
  FROM month m1
  JOIN month m2 
    ON strftime('%Y%m', m1.name) = strftime('%Y%m', m2.name) + 1
),
current_month_companies AS (
  SELECT 
    monthName,
    name
  FROM company
)
SELECT
  mp.current_month AS monthName,
  COUNT(DISTINCT CASE WHEN cfc.first_month = mp.current_month THEN cmc.name END) AS firstTimeCompaniesCount,
  COUNT(DISTINCT CASE WHEN cmc.name NOT IN (SELECT name FROM current_month_companies WHERE monthName = mp.previous_month) THEN cmc.name END) AS newCompaniesCount,
  COUNT(DISTINCT CASE WHEN cmc.name IN (SELECT name FROM current_month_companies WHERE monthName = mp.previous_month) THEN cmc.name END) AS oldCompaniesCount,
  COUNT(DISTINCT cmc.name) AS allCompaniesCount
FROM month_pairs mp
JOIN current_month_companies cmc ON cmc.monthName = mp.current_month
LEFT JOIN company_first_occurrence cfc ON cmc.name = cfc.name
GROUP BY mp.current_month
ORDER BY mp.current_month DESC;

优化说明 #

1. CTE复用计算结果：

   • company_first_occurrence：提前计算每个企业的首次出现月份，避免重复扫描全表判断首次出现
   • month_pairs：通过日期函数直接关联连续的月份对，替代循环查询单月数据的方式
   • current_month_companies：复用当月企业列表，减少子查询重复计算

2. 利用索引提升性能：

   • 依赖company表已定义的(name, monthName)主键索引，加速分组和关联查询
   • month表的name主键索引会自动生效，提升月份配对的查询效率

3. 聚合逻辑合并：

   • 用COUNT(DISTINCT CASE...)在一次分组聚合中完成多维度统计，避免多次扫描全表

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