如何通过单条SQL查询统计各产品日均销售额

我有一张日订单（Daily Orders）表：

该表存储每个产品每日的销售数据。
当前实现存在明显的性能问题：属于典型的N+1查询场景，循环遍历每个产品单独发起SQL拉取全量日订单明细，再在应用内存中计算平均值，逻辑冗余、执行效率极低，还存在资源泄漏隐患。
现有问题代码如下：

// Create an empty list that will contain all products
List<Product> products = new List<Product>();

try  {
    // create database connection
    if (con.State != ConnectionState.Open)
        con.Open();

    // fill list with all products
    SqlDataAdapter MasterItems = new SqlDataAdapter("SELECT * FROM [AIMS].[dbo].[MasterItems]", con);
    DataTable tabMasterItems = new DataTable();
    MasterItems.Fill(tabMasterItems);
    foreach (DataRow row in tabMasterItems.Rows)
    {
        Product product = new Product(row["ItemNmbr"].ToString());
        product.Description = row["ItemDesc"].ToString();
        product.Type = row["ItemType"].ToString();
        product.ShWgt = Convert.ToInt32(row["ItemShWgt"]);
        product.Class = row["ItemClass"].ToString();
        product.QtyDec = Convert.ToInt32(row["ItemQtyDec"]);
        products.Add(product);
    }
    MasterItems.Dispose();
    tabMasterItems.Dispose();

    foreach (Product product in products)
    {
        // get average orders daily
        SqlDataAdapter DailyOrders = new SqlDataAdapter("SELECT * FROM [AIMS].[dbo].[TrxDailyOrders] WHERE ItemNmbr=" + product.No, con);
        DataTable tabDailyOrders = new DataTable();
        DailyOrders.Fill(tabDailyOrders);
        foreach (DataRow row in tabDailyOrders.Rows)
        {       
            // 原逻辑计划把所有日订单存入列表再计算平均值，写法冗余不合理
        }
    }
    DailyOrders.Dispose();
    tabDailyOrders.Dispose();

    // display data in datagridview
    dataGridView1.DataSource = tabDailyOrders;
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}
finally
{
    con.Close();
}

目标是通过单条SQL直接获取各产品的日销售额平均值，用更合理的方式实现统计需求。

核心优化原则 #

• 彻底消除N+1查询：不要循环单产品发请求，通过表关联一次拉取全量需要的数据
• 计算逻辑下推到数据库：用SQL内置聚合函数直接在数据库层完成平均值计算，不需要拉取全量明细到应用内存，大幅减少网络传输和内存占用
• 用using块自动释放数据库资源，修正原代码中循环内创建的SqlDataAdapter、DataTable无法被正确Dispose的资源泄漏问题
• 杜绝字符串拼接SQL的写法，避免SQL注入风险

单条统计SQL实现 #

通过LEFT JOIN关联产品主表和日订单表，按产品维度分组聚合，一次返回所有产品的基础信息+日均销售数据：

SELECT 
    m.ItemNmbr,
    m.ItemDesc,
    m.ItemType,
    m.ItemShWgt,
    m.ItemClass,
    m.ItemQtyDec,
    -- 把下方OrderAmount替换为你表中实际存储日销售额的字段名即可
    ISNULL(AVG(d.OrderAmount), 0) AS AvgDailySales,
    -- 如果需要同时统计日均订单量，替换OrderQty为对应字段
    ISNULL(AVG(d.OrderQty), 0) AS AvgDailyOrderQty
FROM [AIMS].[dbo].[MasterItems] m
LEFT JOIN [AIMS].[dbo].[TrxDailyOrders] d 
    ON m.ItemNmbr = d.ItemNmbr
-- 如果需要统计指定时间范围的数据，在这里加过滤条件，例如统计近90天：
-- WHERE d.OrderDate >= DATEADD(DAY, -90, GETDATE())
GROUP BY 
    m.ItemNmbr,
    m.ItemDesc,
    m.ItemType,
    m.ItemShWgt,
    m.ItemClass,
    m.ItemQtyDec

用LEFT JOIN是为了保留没有任何订单记录的产品，ISNULL会把这类产品的统计值从NULL转为0，符合常规业务展示需求。

优化后的C#实现 #

不需要手动维护Product列表循环填充，直接执行上述聚合查询，拿到结果后直接绑定到DataGridView即可，代码量减少70%以上，性能提升明显：

try
{
    if (con.State != ConnectionState.Open)
        con.Open();

    const string sql = @"
        SELECT 
            m.ItemNmbr,
            m.ItemDesc,
            m.ItemType,
            m.ItemShWgt,
            m.ItemClass,
            m.ItemQtyDec,
            ISNULL(AVG(d.OrderAmount), 0) AS AvgDailySales,
            ISNULL(AVG(d.OrderQty), 0) AS AvgDailyOrderQty
        FROM [AIMS].[dbo].[MasterItems] m
        LEFT JOIN [AIMS].[dbo].[TrxDailyOrders] d 
            ON m.ItemNmbr = d.ItemNmbr
        GROUP BY 
            m.ItemNmbr,
            m.ItemDesc,
            m.ItemType,
            m.ItemShWgt,
            m.ItemClass,
            m.ItemQtyDec
    ";

    using (SqlDataAdapter da = new SqlDataAdapter(sql, con))
    {
        DataTable resultTable = new DataTable();
        da.Fill(resultTable);
        dataGridView1.DataSource = resultTable;
    }
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}
finally
{
    con.Close();
}

额外优化建议 #

• 如果日订单表数据量超过10万级，给TrxDailyOrders表的ItemNmbr、OrderDate字段加联合索引，查询速度会提升数倍
• 后续如果需要加筛选条件（比如按时间、产品分类过滤），直接在SQL里加参数即可，不要用字符串拼接的方式传值

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