SQLite中基于子查询的聚合函数实现：按浏览器统计属性平均值

场景与表结构 #

使用无外键的SQLite数据库，表结构如下：

create table sessions (sessionId integer, browser text, ...);
-- primary key: sessionId

create table pageviews (sessionId integer, pageViewIndex integer, path text, ...);
-- primary key: sessionId, pageViewIndex

create table properties (sessionId integer, pageViewIndex integer, name text, value text);
-- primary key: sessionId, pageViewIndex, name

• 会话包含1个或多个页面视图
• 页面视图可包含0个或多个不同名称的属性

需求 #

找出所有不同浏览器，计算其对应会话页面视图中指定属性（如'a'）的平均值并按该值排序（属性值需为数值型）。

示例数据 #

sessions表 #

pageviews表 #

sessionId为1的会话有2个页面视图，其余各1个，path字段无关

properties表 #

sessionId为1的pageViewIndex=1及sessionId为4的记录无属性'a'，不纳入统计

预期结果 #

firefox对应2个会话的属性值10和20，平均值15；brave对应1个会话的属性值80

用户疑问 #

当前查询返回错误结果，尝试嵌套聚合函数子查询被SQL限制，能否仅通过SQL解决问题，无需在应用内存处理原始数据？且查询需支持任意字段过滤，核心连接逻辑不可修改。

解决方案 #

可以通过分步聚合实现需求，以下是符合要求的SQL查询：

WITH session_a_values AS (
    SELECT 
        s.sessionId,
        s.browser,
        -- 转换属性值为数值型，自动过滤非数值记录
        CAST(p.value AS REAL) AS a_value
    FROM sessions s
    JOIN pageviews pv ON s.sessionId = pv.sessionId
    JOIN properties p ON pv.sessionId = p.sessionId AND pv.pageViewIndex = p.pageViewIndex
    WHERE p.name = 'a'
      -- 此处可添加任意过滤条件，例如：pv.path = 'xyz'、s.browser LIKE '%chrome%'等
),
session_unique_a AS (
    SELECT 
        sessionId,
        browser,
        -- 若一个会话有多个页面视图带属性'a'，可根据需求调整聚合方式（SUM/MAX/MIN）
        AVG(a_value) AS session_a_avg
    FROM session_a_values
    GROUP BY sessionId, browser
)
SELECT 
    browser,
    AVG(session_a_avg) AS a_avg
FROM session_unique_a
GROUP BY browser
ORDER BY a_avg DESC;

逻辑说明 #

1. session_a_values：关联三张表，过滤出指定属性'a'的有效记录，同时将属性值转为数值型。核心连接逻辑未修改，且支持添加任意字段过滤条件。
2. session_unique_a：按会话聚合，确保每个会话仅贡献一个属性值（若同一会话多个页面视图带'a'，可根据业务需求替换聚合函数，比如用SUM计算总和）。
3. 最终查询：按浏览器聚合计算平均值，并按结果降序排序。

示例验证 #

执行上述查询后，会得到与预期完全一致的结果：

• brave浏览器对应session3的属性值80，平均值为80
• firefox浏览器对应session1（10）和session2（20）的属性值，平均值为(10+20)/2=15

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