SQL子查询计算关联表平均值：avg_num_button_link_clicks结果异常排查

正在学习SQL，尝试计算特定test_variants记录对应的test_tracking_data_sessions、test_tracking_data_forms和test_tracking_data_buttons_and_links表的以下指标：平均页面停留时间、平均跳出率、平均下滑百分比、平均表单提交数、平均按钮与链接点击数。前四项指标返回结果正确，但第五项avg_num_button_link_clicks错误返回3而非预期的0.33。

原查询语句 #

SELECT 
  AVG(time_on_page) AS avg_time_on_page,
  AVG(CASE WHEN bounced THEN 1 ELSE 0 END) AS avg_bounce_rate,
  AVG(scrolled_down_percentage) AS avg_scrolled_down_percentage,
  AVG(num_forms_submitted) AS avg_num_forms_submitted,
  SUM(num_button_link_clicks) / COUNT(DISTINCT data_id) AS avg_num_button_link_clicks
FROM (
  SELECT 
    ttds.id AS data_id,
    ttds.time_on_page,
    ttds.bounced,
    ttds.scrolled_down_percentage,
    COUNT(DISTINCT CASE WHEN ttdf.id IS NOT NULL THEN ttdf.id END) AS num_forms_submitted,
    COUNT(DISTINCT CASE WHEN ttdbl.id IS NOT NULL THEN ttdbl.id END) AS num_button_link_clicks
  FROM test_tracking_data_sessions ttds
  LEFT JOIN test_tracking_data_forms ttdf ON ttdf.data_id = ttds.id AND ttdf.submitted
  LEFT JOIN test_tracking_data_buttons_and_links ttdbl ON ttdbl.data_id = ttds.id
  WHERE ttds.variant_id = 1
  GROUP BY ttds.id
) subquery;

测试数据 #

INSERT INTO test_variants (id, name, test_id, page_id, is_control, variant_no, css_code, js_code, is_redirect, traffic_allocation, created_at) VALUES
  (1, 'Control I', 1, 1, true, 0, NULL, NULL, FALSE, 30, NOW());

INSERT INTO test_tracking_data_sessions (id, test_id, variant_id, goal_id, session_start, session_end, time_on_page, bounced, scrolled_down_percentage, device_type_id) VALUES
  ('bf5a5afe-82d2-4007-92b8-2985bc4c6cea', 1, 1, 1, '2023-02-01 10:00:00', '2023-02-01 10:10:00', 2000, true, 0, 3),
  ('00c7fc33-d189-4bd8-b293-447adf618919', 1, 1, 1, '2023-02-02 11:00:00', '2023-02-02 11:10:00', 10000, false, 30, 3),
  ('5e9cc392-f336-410d-83fb-726f95971259', 1, 1, 1, '2023-02-03 11:30:00', '2023-02-03 11:40:00', 18000, false, 90, 1);

INSERT INTO test_tracking_data_forms (data_id, name, submitted)
VALUES 
    ('bf5a5afe-82d2-4007-92b8-2985bc4c6cea', 'Form 1', true),
    ('00c7fc33-d189-4bd8-b293-447adf618919', 'Form 2', false),
    ('5e9cc392-f336-410d-83fb-726f95971259', 'Form 3', true);

INSERT INTO test_tracking_data_buttons_and_links (data_id, name, clicks)
VALUES 
    ('bf5a5afe-82d2-4007-92b8-2985bc4c6cea', 'Button 1', 1),
    ('bf5a5afe-82d2-4007-92b8-2985bc4c6cea', 'Link 1', 1),
    ('bf5a5afe-82d2-4007-92b8-2985bc4c6cea', 'Link 2', 0),
    ('00c7fc33-d189-4bd8-b293-447adf618919', 'Button 1', 0),
    ('00c7fc33-d189-4bd8-b293-447adf618919', 'Link 1', 0),
    ('00c7fc33-d189-4bd8-b293-447adf618919', 'Link 2', 0),
    ('5e9cc392-f336-410d-83fb-726f95971259', 'Button 1', 1),
    ('5e9cc392-f336-410d-83fb-726f95971259', 'Link 1', 0),
    ('5e9cc392-f336-410d-83fb-726f95971259', 'Link 2', 0);

表结构 #

CREATE TABLE IF NOT EXISTS test_variants(
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  test_id INTEGER NOT NULL,
  page_id INTEGER NOT NULL,
  is_control BOOLEAN NOT NULL,
  variant_no INTEGER NOT NULL, -- control must be 0
  css_code TEXT,
  js_code TEXT,
  is_redirect BOOLEAN NOT NULL,
  traffic_allocation INTEGER NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  last_modified TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS test_tracking_data_sessions(
    id UUID PRIMARY KEY ,
    test_id INTEGER,
    variant_id INTEGER REFERENCES test_variants(id) ON DELETE CASCADE, -- control is also technically a variant
    goal_id INTEGER,
    session_start TIMESTAMP NOT NULL,
    session_end TIMESTAMP NOT NULL,
    time_on_page INTEGER NOT NULL,
    bounced BOOLEAN NOT NULL,
    scrolled_down_percentage INTEGER,
    device_type_id INTEGER 
);

CREATE TABLE IF NOT EXISTS test_tracking_data_forms(
    id SERIAL PRIMARY KEY,
    data_id UUID REFERENCES test_tracking_data_sessions(id) ON DELETE CASCADE,
    name VARCHAR(50) NOT NULL,
    submitted BOOLEAN NOT NULL
);

CREATE TABLE IF NOT EXISTS test_tracking_data_buttons_and_links(
    id SERIAL PRIMARY KEY,
    data_id UUID REFERENCES test_tracking_data_sessions(id) ON DELETE CASCADE,
    name VARCHAR(50) NOT NULL,
    clicks INTEGER NOT NULL
);

错误原因 #

1. 多表JOIN导致笛卡尔积：同时LEFT JOIN test_tracking_data_forms和test_tracking_data_buttons_and_links会产生重复行，比如一个session对应1条表单记录和3条按钮记录，JOIN后会生成3行数据，干扰后续统计。
2. 统计逻辑错误：子查询中用COUNT(DISTINCT ttdbl.id)统计的是该session下的按钮/链接记录条数，而非实际点击数总和；外层SUM(num_button_link_clicks)将3个session的记录条数（各3条）相加得到9，除以3个session后得到3，与预期的点击数总和（1+1+0 + 0+0+0 +1+0+0 =3）除以3得到0.33不符。

修正后的查询 #

SELECT 
  AVG(time_on_page) AS avg_time_on_page,
  AVG(CASE WHEN bounced THEN 1 ELSE 0 END) AS avg_bounce_rate,
  AVG(scrolled_down_percentage) AS avg_scrolled_down_percentage,
  AVG(num_forms_submitted) AS avg_num_forms_submitted,
  AVG(num_button_link_clicks) AS avg_num_button_link_clicks
FROM (
  SELECT 
    ttds.id AS data_id,
    ttds.time_on_page,
    ttds.bounced,
    ttds.scrolled_down_percentage,
    -- 统计提交的表单数量
    (SELECT COUNT(DISTINCT id) FROM test_tracking_data_forms WHERE data_id = ttds.id AND submitted) AS num_forms_submitted,
    -- 统计按钮/链接的总点击数
    (SELECT COALESCE(SUM(clicks), 0) FROM test_tracking_data_buttons_and_links WHERE data_id = ttds.id) AS num_button_link_clicks
  FROM test_tracking_data_sessions ttds
  WHERE ttds.variant_id = 1
) subquery;

排查思路 #

1. 查看子查询结果：单独执行子查询，检查每个data_id对应的num_button_link_clicks值，会发现每个session都返回3，这说明统计的是记录数而非点击数。
2. 检查JOIN逻辑：分析多表JOIN后的行数，发现行数远大于session总数，确认是笛卡尔积导致的重复数据。
3. 核对统计目标：明确需要统计的是点击数总和，而非记录条数，调整统计逻辑为SUM(clicks)而非COUNT(DISTINCT id)。
4. 拆分聚合逻辑：对每个关联表单独做子查询聚合，避免多表JOIN带来的笛卡尔积问题。

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