Metabase中子子查询WHERE子句如何使用列计算值

表结构 #

azure_tickets表 #

azure_tickets_history_releases_eav表 #

需求 #

统计特定国家（如CZ）对应system_tag下，状态为“On Stage”的所有工单的平均天数。

现有SQL代码 #

SELECT 
    `Table A`.`Tags A1` AS                  `Tags A`, 
    `Question 172`.`Azure System Tags` AS   `Tags B`, 
    `Question 172`.`Days On Stage` AS       `Days On Stage`
FROM (
    SELECT DISTINCT regexp_substr(system_tags, concat({{country}}, '_released_[^;]*')) as "Tags A1"
    FROM            azure_tickets 
    WHERE           azure_tickets.system_tags LIKE concat("%", {{country}}, "_released_%")
    ) 
    `Table A`
    
    LEFT JOIN (
        SELECT 
            regexp_substr(`Table B`.`system_tags`, concat({{country}}, '_released_[^;]*')) AS `Azure System Tags`,
            SUM(`Table A`.`days_count`) / COUNT(`Table A`.`days_count`) AS                    `Days On Stage`
        FROM (
            SELECT  `azure_tickets_history_releases_eav`.`ticket_id`, 
                    `azure_tickets_history_releases_eav`.`days_count`
            FROM    `azure_tickets_history_releases_eav`
            WHERE   `ticket_id` IN (
                        SELECT  `azure_tickets`.`ticket_id`
                        FROM    `azure_tickets`
                        WHERE   `azure_tickets.system_tags` LIKE concat("%", 'CZ_released_2022/12/14', "%") AND 
                                `azure_tickets`.`state` IN ("For release", 'Closed') AND
                                `azure_tickets`.`team_project` NOT IN ('mobile-team','cloud-infrastructure','bart-team','ipf-team','integration-backoffice-team','web-measurements-team','devops-team','ecommerce-sla','qaa-team')) AND
                    `country` = {{country}} AND
                    `status` = "On Stage"
                    )
                    `Table A`
                
        JOIN `azure_tickets` AS `Table B` ON `Table A`.`ticket_id` = `Table B`.`ticket_id`
        ) 
        
        `Question 172` ON `Table A`.`Tags A1` = `Question 172`.`Azure System Tags`

当前执行结果 #

核心问题 #

现有SQL中第23行硬编码了标签值CZ_released_2022/12/14，尝试替换为动态列值（如Table A.Tags A1）时，会触发“Unknown column 'Table A.Tags A1' in 'where clause'”错误——这是因为子查询无法直接引用外层查询的列，多层嵌套的结构放大了这个作用域问题。

解决建议 #

无需保留原有复杂的子查询嵌套结构，可简化逻辑直接关联两张表，提取对应国家的标签后分组计算平均值：

修改后的SQL #

SELECT
    -- 提取当前国家对应的system_tag
    regexp_substr(at.system_tags, concat({{country}}, '_released_[^;]*')) AS `Country Tag`,
    -- 计算平均天数，用AVG()替代手动SUM/COUNT
    AVG(ath.days_count) AS `Average Days On Stage`
FROM azure_tickets at
-- 若需保留所有国家标签（即使无On Stage记录），将JOIN改为LEFT JOIN
JOIN azure_tickets_history_releases_eav ath
    ON at.ticket_id = ath.ticket_id
WHERE
    -- 筛选包含目标国家标签的工单
    at.system_tags LIKE concat('%', {{country}}, '_released_%')
    -- 筛选工单状态
    AND at.status IN ('For Release', 'Closed')
    -- 排除指定项目组
    AND at.team_project NOT IN ('mobile-team','cloud-infrastructure','bart-team','ipf-team','integration-backoffice-team','web-measurements-team','devops-team','ecommerce-sla','qaa-team')
    -- 筛选历史记录中的On Stage状态
    AND ath.status = 'On Stage'
-- 按提取的国家标签分组统计
GROUP BY `Country Tag`

逻辑说明 #

1. 直接关联表：通过ticket_id直接关联工单表和历史记录表，避免多层子查询的作用域冲突。
2. 动态提取标签：用regexp_substr实时提取目标国家对应的system_tag，无需提前生成标签列表再关联。
3. 整合筛选条件：将所有过滤规则（国家标签、工单状态、项目组排除、历史状态）统一放在WHERE子句中，逻辑更清晰。
4. 分组计算平均值：用AVG()函数直接计算分组后的平均天数，比手动SUM/COUNT更简洁且不易出错。
5. 可选调整：如果需要保留所有存在国家标签的工单（即使无On Stage记录），将JOIN改为LEFT JOIN，并使用AVG(COALESCE(ath.days_count, 0))将NULL值转为0计算平均值。

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