如何在SQLite中创建新表存储原表的多组统计计算结果？

问题描述 #

我是新用户，希望表述清晰（欢迎反馈）。在SQLite中，我希望在计算出a、b、c、d四个统计值后创建一张新表，相关信息如下：

• 原表：covid2021（由covid表过滤2021年数据得到，初始有35707行）
• 统计值说明：
  • a：过滤条件为TEST_RESULT='POSITIVE'且DIAGNOSIS_CD='U071'，按PTID分组，结果8654行
  • c：过滤条件为TEST_RESULT='NEGATIVE'且DIAGNOSIS_CD='U071'，按PTID分组，结果9087行
  • b：过滤条件为TEST_RESULT='POSITIVE'且DIAGNOSIS_CD<>'U071'，按PTID与DIAGNOSIS_CD分组，结果23行
  • d：过滤条件为TEST_RESULT='NEGATIVE'且DIAGNOSIS_CD<>'U071'，按PTID与DIAGNOSIS_CD分组，结果54行
• 预期：合并四个统计结果后新表行数为17818行（8654+9087+23+54=17818）

请问是否可以创建这样的新表？若可行，具体操作方法是什么？

用户提供的原代码 #

CREATE TABLE covid2021 AS
SELECT *
FROM covid
WHERE RESULT_DATE1 LIKE '%2021%' AND diagdate1 LIKE '%2021%'
ORDER BY PTID
;

/*Finding the output for a,b,c,d*/
/*a*/
SELECT *
FROM covid2021
WHERE TEST_RESULT='POSITIVE' AND DIAGNOSIS_CD='U071' /*AND ENCID <>''*/AND RESULT_DATE1 LIKE '%2021%' AND diagdate1 LIKE '%2021%'
GROUP BY PTID
ORDER BY PTID ASC
;
/*Output: 8654*/

/*c*/
SELECT *
FROM covid2021
WHERE TEST_RESULT='NEGATIVE' AND DIAGNOSIS_CD='U071' AND RESULT_DATE1 LIKE '%2021%' AND diagdate1 LIKE '%2021%'
GROUP BY PTID
ORDER BY PTID
;
/*Output: 9087 */

/*d*/
SELECT *, MAX(RESULT_DATE1), COUNT(*) as Count/*, MIN(RESULT_DATE), MIN(RESULT_TIME)*/
FROM covid2021
WHERE TEST_RESULT='NEGATIVE' AND DIAGNOSIS_CD <>'U071' AND RESULT_DATE1 LIKE '%2021%' AND diagdate1 LIKE '%2021%'
GROUP BY DIAGNOSIS_CD || PTID
ORDER BY PTID
;
/*Output: 54 */

/*b*/
SELECT *, MAX(RESULT_DATE1), COUNT(*) as Count/*, MIN(RESULT_TIME)*/
FROM covid2021
WHERE TEST_RESULT='POSITIVE' AND DIAGNOSIS_CD IS NOT 'U071' AND RESULT_DATE1 LIKE '%2021%' AND diagdate1 LIKE '%2021%'
GROUP BY DIAGNOSIS_CD || PTID
ORDER BY PTID
;
/*Output: 23 */

可行性说明 #

完全可以创建这样的新表，核心思路是通过UNION ALL将四个统计查询的结果合并，再将合并结果写入新表。

原代码优化点 #

1. 冗余条件：covid2021已过滤2021年数据，后续查询无需重复添加年份过滤条件
2. 分组逻辑：GROUP BY DIAGNOSIS_CD || PTID等价于GROUP BY PTID, DIAGNOSIS_CD，后者更符合SQL规范
3. 条件判断：SQLite中判断非等值应使用<>或!=，IS NOT仅用于NULL值判断，因此DIAGNOSIS_CD IS NOT 'U071'需改为DIAGNOSIS_CD <> 'U071'
4. SELECT * 分组问题：SELECT *配合GROUP BY属于非标准写法，建议明确指定字段或使用聚合函数获取分组内的特定值（如原代码中的MAX(RESULT_DATE1)）

具体操作方法 #

方法一：直接创建新表（合并四个统计结果） #

-- 创建合并后的统计新表
CREATE TABLE covid_stats_combined AS
-- 统计值a：阳性且诊断码U071，按PTID分组
SELECT 
  PTID,
  DIAGNOSIS_CD,
  TEST_RESULT,
  MAX(RESULT_DATE1) AS latest_result_date, -- 取分组内最新结果日期
  COUNT(*) AS record_count -- 分组内记录总数
FROM covid2021
WHERE TEST_RESULT='POSITIVE' AND DIAGNOSIS_CD='U071'
GROUP BY PTID
UNION ALL
-- 统计值c：阴性且诊断码U071，按PTID分组
SELECT 
  PTID,
  DIAGNOSIS_CD,
  TEST_RESULT,
  MAX(RESULT_DATE1) AS latest_result_date,
  COUNT(*) AS record_count
FROM covid2021
WHERE TEST_RESULT='NEGATIVE' AND DIAGNOSIS_CD='U071'
GROUP BY PTID
UNION ALL
-- 统计值b：阳性且诊断码非U071，按PTID、DIAGNOSIS_CD分组
SELECT 
  PTID,
  DIAGNOSIS_CD,
  TEST_RESULT,
  MAX(RESULT_DATE1) AS latest_result_date,
  COUNT(*) AS record_count
FROM covid2021
WHERE TEST_RESULT='POSITIVE' AND DIAGNOSIS_CD <> 'U071'
GROUP BY PTID, DIAGNOSIS_CD
UNION ALL
-- 统计值d：阴性且诊断码非U071，按PTID、DIAGNOSIS_CD分组
SELECT 
  PTID,
  DIAGNOSIS_CD,
  TEST_RESULT,
  MAX(RESULT_DATE1) AS latest_result_date,
  COUNT(*) AS record_count
FROM covid2021
WHERE TEST_RESULT='NEGATIVE' AND DIAGNOSIS_CD <> 'U071'
GROUP BY PTID, DIAGNOSIS_CD;

方法二：先建空表再插入数据（适合自定义表结构场景） #

-- 创建结构匹配的空表（字段类型根据实际情况调整）
CREATE TABLE covid_stats_combined (
  PTID TEXT,
  DIAGNOSIS_CD TEXT,
  TEST_RESULT TEXT,
  latest_result_date TEXT,
  record_count INTEGER
);

-- 插入统计值a的结果
INSERT INTO covid_stats_combined
SELECT PTID, DIAGNOSIS_CD, TEST_RESULT, MAX(RESULT_DATE1), COUNT(*)
FROM covid2021
WHERE TEST_RESULT='POSITIVE' AND DIAGNOSIS_CD='U071'
GROUP BY PTID;

-- 插入统计值c的结果
INSERT INTO covid_stats_combined
SELECT PTID, DIAGNOSIS_CD, TEST_RESULT, MAX(RESULT_DATE1), COUNT(*)
FROM covid2021
WHERE TEST_RESULT='NEGATIVE' AND DIAGNOSIS_CD='U071'
GROUP BY PTID;

-- 插入统计值b的结果
INSERT INTO covid_stats_combined
SELECT PTID, DIAGNOSIS_CD, TEST_RESULT, MAX(RESULT_DATE1), COUNT(*)
FROM covid2021
WHERE TEST_RESULT='POSITIVE' AND DIAGNOSIS_CD <> 'U071'
GROUP BY PTID, DIAGNOSIS_CD;

-- 插入统计值d的结果
INSERT INTO covid_stats_combined
SELECT PTID, DIAGNOSIS_CD, TEST_RESULT, MAX(RESULT_DATE1), COUNT(*)
FROM covid2021
WHERE TEST_RESULT='NEGATIVE' AND DIAGNOSIS_CD <> 'U071'
GROUP BY PTID, DIAGNOSIS_CD;

结果验证 #

执行完创建操作后，可通过以下语句验证行数是否符合预期：

SELECT COUNT(*) FROM covid_stats_combined;

预期返回结果为17818。

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