时间戳

首页 > 解决方案 > 计算用零替换缺失行的预定义值的平均值和标准偏差

sql - 计算用零替换缺失行的预定义值的平均值和标准偏差

问题描述

我有一个简单的表格，其中包含产品记录及其一年中每天的总销售额（只有 3 列 - 产品、日期、销售额）。因此，例如，如果产品 A 每天都售出，它就会有365记录。同样，如果产品 B 仅售出 50 天，则该表将仅50包含该产品的行 - 每一天销售一个。

我需要计算全年的日均销售额和标准差，这意味着，对于产品 B，我需要有额外365-50=315的零销售额条目才能正确计算该年度的日均和标准差。

有没有办法在 SQL 中有效且动态地执行此操作？

谢谢

标签： sqlsql-serverstandard-deviation

解决方案

我们可以生成 366 行并将销售数据加入其中：

WITH rg(rn) AS (
   SELECT 1 AS rn
   UNION ALL
   SELECT a.rn + 1 AS rn
   FROM   rg a
   WHERE  a.rn <= 366
)

SELECT
  *
FROM
  rg
  LEFT JOIN (
    SELECT YEAR(saledate) as yr, DATEPART(dayofyear, saledate) as doy, count(*) as numsales 
    FROM sales 
    GROUP BY YEAR(saledate), DATEPART(dayofyear, saledate) 
  ) s ON rg.rn = s.doy

OPTION (MAXRECURSION 370);

您可以用 eg 替换空值（当天没有销售数据）AVG(COALESCE(numsales, 0))。您可能还需要一个 WHERE 子句来消除非闰年的第 366 天（例如 MODULO 将年份乘以 4，如果为 0，则仅执行 366 行）。

如果你只做一年，你可以在 sales 子查询中使用 where 子句来只给出相关记录；最有效的是使用范围 likeWHERE salesdate >= DATEFROMPARTS(YEAR(GetDate()), 1, 1) AND salesdate < DATEFROMPARTS(YEAR(GetDate()) + 1, 1, 1)而不是在每个销售日期调用函数来从中提取年份以与常数进行比较。如果只有一年，您也可以从选择/分组中删除 YEAR(salesdate)

如果你做了很多年，你可以让 rg 生成更多的行，或者（也许更简单）将它交叉连接到一个年份列表中，这样你就可以得到 366 行乘以 eg VALUES (2015),(2016),(2017),(2018),(2019),(2020)（并将年份从连接的销售部分也）

推荐阅读