You need to enable JavaScript to run this app. SQLite下迭代统计关联表名称出现频率的实现方案咨询
优惠活动
大模型
产品
解决方案
定价
更多

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表为空
    1. 统计当前待处理数据中各name_id的出现频次,取最高频的一条:
      SELECT name_id, COUNT(*) as cnt 
      FROM temp_data 
      GROUP BY name_id 
      ORDER BY cnt DESC LIMIT 1;
      
    2. 将查询到的name_id和对应频次插入result表
    3. 找出所有包含该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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.09 20:12:01