You need to enable JavaScript to run this app. SQLite五表关联分组求和查询结果异常及多维统计需求实现
优惠活动
大模型
产品
解决方案
定价
更多

SQLite五表关联分组求和查询结果异常及多维统计需求实现

问题产生原因

之前的查询结果出现重复求和,核心是多表直接联接触发笛卡尔积:

  • 单个Job_ID在TAB1_CATEGORY中对应多条分类记录,在TAB2中对应多条日志记录
  • 两个一对多的关联关系和基础表直接内连接时,会生成行交叉匹配的结果:
    • Job3有2条分类记录、1条TAB2日志,关联后生成2行匹配,原本9的点击值被重复计算2次,得到错误的18
    • Job4有2条分类记录、2条TAB2日志,关联后生成4行匹配,原本9的点击、4的申请值都被重复计算2次,得到错误的18、8
调整后的查询实现

通过预聚合拆分不同粒度的统计值,再做关联避免笛卡尔积,配合窗口函数实现公司维度的统计,完整SQL如下:

WITH job_tab2_agg AS (
    -- 预聚合每个Job的Tab2维度统计值
    SELECT 
        job_id,
        SUM(clicks) AS total_clicks,
        SUM(applications) AS total_apps
    FROM TAB2
    GROUP BY job_id
),
job_category_agg AS (
    -- 预聚合每个Job的分类维度统计值,内置first_level过滤条件
    SELECT 
        tc.job_id,
        COUNT(DISTINCT c.second_level) AS distinct_second_level_cnt
    FROM TAB1_CATEGORY tc
    JOIN CATEGORY c ON tc.category_id = c.id
    WHERE c.first_level IN ('ggg', 'sss')
    GROUP BY tc.job_id
)
SELECT 
    t1.job_id,
    t1.company_id,
    jta.total_clicks AS "SUM(t2.clicks)",
    jta.total_apps AS "SUM(t2.applications)",
    jca.distinct_second_level_cnt AS "COUNT(DISTINCT c.second_level)",
    -- 窗口函数计算同公司下的总申请量,保持行粒度为单Job
    SUM(jta.total_apps) OVER (PARTITION BY t1.company_id) AS "Total Appl per company"
FROM TAB1 t1
JOIN SOURCE s ON t1.source_id = s.id
JOIN job_tab2_agg jta ON t1.job_id = jta.job_id
JOIN job_category_agg jca ON t1.job_id = jca.job_id
WHERE s.name = 'mmm'
执行结果

完全匹配预期的输出:

job_idcompany_idSUM(t2.clicks)SUM(t2.applications)COUNT(DISTINCT c.second_level)Total Appl per company
32229024
42229424

内容的提问来源于stack exchange,提问作者pooq

相关产品推荐
方舟 Agent Plan

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

最近更新时间:2026.11.13 01:36:04