SQLite下迭代统计关联表名称出现频率的实现方案咨询
解决方案
针对描述的需求,结合SQLite的特性,推荐以下两种可落地的方案:
方案1:程序+SQL混合实现(最稳定,生产环境优先)
该方案避开SQLite递归CTE的语法限制,逻辑清晰易调试:
- 步骤1:创建两张临时表
- 一张存储待处理的关联表数据,结构和原关联表一致:
CREATE TEMP TABLE temp_data AS SELECT * FROM your_relation_table; - 一张存储最终结果,结构为
name_id, freq:CREATE TEMP TABLE result (name_id INT, freq INT);
- 一张存储待处理的关联表数据,结构和原关联表一致:
- 步骤2:循环执行以下逻辑,直到
temp_data表为空- 统计当前待处理数据中各
name_id的出现频次,取最高频的一条:SELECT name_id, COUNT(*) as cnt FROM temp_data GROUP BY name_id ORDER BY cnt DESC LIMIT 1; - 将查询到的
name_id和对应频次插入result表 - 找出所有包含该
name_id的x_id,删除temp_data中所有这些x_id对应的行:DELETE FROM temp_data WHERE x_id IN ( SELECT DISTINCT x_id FROM temp_data WHERE name_id = 刚才查到的最高频name_id );
- 统计当前待处理数据中各
- 步骤3:查询
result表即可得到按顺序输出的名称和对应频次。
方案2:纯SQL实现(基于递归CTE+窗口函数,适配SQLite 3.33.0及以上版本)
之前遇到的递归CTE无法聚合的问题,可以通过将聚合逻辑嵌套在递归关联子查询中解决,具体实现代码如下:
WITH RECURSIVE -- 给所有去重后的x_id分配唯一序号,用于位掩码标记已处理数据 x_ids AS ( SELECT x_id, ROW_NUMBER() OVER () - 1 as rn FROM (SELECT DISTINCT x_id FROM your_relation_table) ), -- 递归处理主体 recursive_process(processed_mask, step, result_name, result_freq) AS ( -- 初始状态:未处理任何x_id,步数为0 SELECT 0, 0, NULL, NULL UNION ALL SELECT new_mask, step + 1, top_name, top_freq FROM recursive_process -- 计算当前未处理数据中的最高频name_id JOIN ( SELECT name_id as top_name, COUNT(*) as top_freq FROM your_relation_table r2 WHERE NOT (processed_mask & (1 << (SELECT rn FROM x_ids WHERE x_id = r2.x_id))) GROUP BY name_id ORDER BY top_freq DESC LIMIT 1 ) t ON 1=1 -- 计算新的已处理掩码:把所有包含top_name的x_id对应位标记为已处理 CROSS JOIN ( SELECT BIT_OR(1 << rn) as new_mask FROM x_ids WHERE x_id IN ( SELECT DISTINCT x_id FROM your_relation_table WHERE name_id = top_name ) ) m -- 终止条件:没有新的可处理数据 WHERE new_mask != processed_mask ) -- 过滤初始空行,输出最终结果 SELECT result_name as name_id, result_freq as freq FROM recursive_process WHERE step > 0 ORDER BY step;
注意:该方案仅适用于去重后x_id总数不超过64个的场景(受SQLite整数位长限制),如果数据量较大还是推荐使用方案1。
内容的提问来源于stack exchange,提问作者N Meibergen
相关产品推荐
相关产品推荐

