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_id | company_id | SUM(t2.clicks) | SUM(t2.applications) | COUNT(DISTINCT c.second_level) | Total Appl per company |
|---|---|---|---|---|---|
| 3 | 222 | 9 | 0 | 2 | 4 |
| 4 | 222 | 9 | 4 | 2 | 4 |
内容的提问来源于stack exchange,提问作者pooq
相关产品推荐
相关产品推荐

