SQL技术咨询：计算计数平均值、占比及拖车相关统计问题

一、如何计算计数的平均值？ #

要计算计数的平均值，核心是先分组统计单维度的计数，再对这些计数结果求平均。比如要统计各分组记录数的平均值，需先通过GROUP BY分组，用COUNT()得到每组的数量，再在外层查询中用AVG()计算这些数值的平均值。

二、如何计算占比？ #

占比的核心逻辑是目标群体数量 ÷ 总群体数量，常用两种实现方式：

• 子查询方式：分别计算目标数和总数，再做除法
• 窗口函数方式：用SUM() OVER()直接获取总数，避免嵌套子查询
  注意：整数除法需要乘1.0转换为浮点型，避免结果被取整。

三、业务需求具体实现 #

1. 代码正确性验证（计算str_lod_trlr_id对应trkg_id数量） #

你提供的这段代码是冗余且无意义的：

select str_lod_trlr_id, avg(item)
from
(
select str_lod_trlr_id ,count(trkg_id) as item
from tablename group by str_lod_trlr_id) a
group by str_lod_trlr_id

内层查询已经按str_lod_trlr_id分组，得到每个拖车对应的trkg_id数量（item）；外层再次按同一字段分组后对item求平均，每个分组仅包含一个数值，最终结果和内层的item完全一致，多此一举。

如果需求是获取每个拖车的物品数量，直接用内层查询即可：

select str_lod_trlr_id, count(trkg_id) as item_count
from tablename 
group by str_lod_trlr_id

如果需求是所有拖车的平均物品数量，外层去掉分组直接求平均：

select avg(item) as avg_item_per_trailer
from
(
select str_lod_trlr_id, count(trkg_id) as item
from tablename 
group by str_lod_trlr_id
) a

2. 统计拥有专属拖车的物品占比 #

假设“专属拖车”定义为仅关联单个物品的拖车（即大件物品独占一个拖车），可以用窗口函数实现：

select 
    (count(distinct case when trailer_item_count = 1 then trkg_id end) * 1.0) 
    / count(distinct trkg_id) as exclusive_item_ratio
from
(
select 
    trkg_id,
    str_lod_trlr_id,
    -- 计算当前拖车关联的物品总数
    count(trkg_id) over(partition by str_lod_trlr_id) as trailer_item_count
from tablename
) a

如果业务中有明确标记专属拖车的字段（比如is_exclusive = 1），可以简化为：

select 
    (count(case when is_exclusive = 1 then trkg_id end) * 1.0) 
    / count(trkg_id) as exclusive_item_ratio
from tablename

3. 统计关联物品数>1的拖车的平均关联物品数 #

先筛选出物品数大于1的拖车，再对这些拖车的物品数求平均：

select avg(item_count) as avg_item_for_multi_trailers
from
(
select str_lod_trlr_id, count(trkg_id) as item_count
from tablename 
group by str_lod_trlr_id
-- 筛选出物品数>1的拖车
having count(trkg_id) > 1
) a

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