如何获取嵌套查询返回表的大小？SQL中.size方法失效求解决方案

首先得明确你原来的SQL为什么行不通：你的两个子查询都加了GROUP BY name，这会返回两行数据（对应nic_stat和agent_app_names两个表），而不是单个标量值。SQL里没有.size这种针对多行结果的引用方式，而且你没法直接对两行结果做减法——这就好比你拿着两个包含两个元素的列表，想直接用列表减列表，逻辑上就不成立。

下面分两种场景给你解决办法：

场景1：想分别计算每个表在两天的大小差值 #

如果需要得到每个表单独的大小变化，可以把两个子查询的结果通过表名关联起来，然后逐行计算差值：

SELECT 
    t1.table_name,
    t1.size - t2.size AS size_difference
FROM (
    -- 获取2017-12-21每个表的平均大小
    SELECT 
        tn.name AS table_name,
        ROUND(AVG(ts.data / ts.avglen), 0) AS size
    FROM x.ddp_table_stats AS ts
    INNER JOIN x.ddp_table_names AS tn ON ts.tableid = tn.id
    WHERE ts.date = '2017-12-21' 
      AND tn.name IN ('nic_stat', 'agent_app_names')
    GROUP BY tn.name
) t1
INNER JOIN (
    -- 获取2017-12-19每个表的平均大小
    SELECT 
        tn.name AS table_name,
        ROUND(AVG(ts.data / ts.avglen), 0) AS size
    FROM x.ddp_table_stats AS ts
    INNER JOIN x.ddp_table_names AS tn ON ts.tableid = tn.id
    WHERE ts.date = '2017-12-19' 
      AND tn.name IN ('nic_stat', 'agent_app_names')
    GROUP BY tn.name
) t2 ON t1.table_name = t2.table_name;

场景2：想计算两个表的总大小差值 #

如果需要的是两个表在两天的总大小变化，可以用条件聚合的方式，把两天的数据放在同一个查询里处理，更简洁高效：

SELECT 
    -- 计算21号的总大小减去19号的总大小
    SUM(CASE WHEN ts.date = '2017-12-21' THEN daily_size ELSE 0 END)
    - SUM(CASE WHEN ts.date = '2017-12-19' THEN daily_size ELSE 0 END) AS total_size_difference
FROM (
    -- 先按表名和日期分组，计算每天每个表的平均大小
    SELECT 
        tn.name,
        ts.date,
        ROUND(AVG(ts.data / ts.avglen), 0) AS daily_size
    FROM x.ddp_table_stats AS ts
    INNER JOIN x.ddp_table_names AS tn ON ts.tableid = tn.id
    WHERE ts.date IN ('2017-12-19', '2017-12-21')
      AND tn.name IN ('nic_stat', 'agent_app_names')
    GROUP BY tn.name, ts.date
) aggregated_data;

或者如果你坚持用子查询的方式，也可以把每个子查询的结果聚合为总和再做减法：

SELECT 
    (SELECT SUM(daily_size)
     FROM (
         SELECT ROUND(AVG(ts.data / ts.avglen), 0) AS daily_size
         FROM x.ddp_table_stats AS ts
         JOIN x.ddp_table_names AS tn ON ts.tableid = tn.id
         WHERE ts.date = '2017-12-21' 
           AND tn.name IN ('nic_stat', 'agent_app_names')
         GROUP BY tn.name
     ) t1)
     -
    (SELECT SUM(daily_size)
     FROM (
         SELECT ROUND(AVG(ts.data / ts.avglen), 0) AS daily_size
         FROM x.ddp_table_stats AS ts
         JOIN x.ddp_table_names AS tn ON ts.tableid = tn.id
         WHERE ts.date = '2017-12-19' 
           AND tn.name IN ('nic_stat', 'agent_app_names')
         GROUP BY tn.name
     ) t2) AS total_size_difference;

核心逻辑就是：先把多行的聚合结果处理成可计算的形式——要么按维度关联后逐行计算，要么聚合为单一值后做减法，SQL才能正确执行你的需求。

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