SQL查询计算平均值的平均值

debugcn 发表于 Dev

二酸

我通读了几篇关于类似问题的帖子，但似乎找不到正确的解决方案。我尝试将计算添加到查询的几个部分，但没有成功。我确定我错过了一些简单的东西。

我在一家矿山工作，我被要求完成以下任务：

他们想知道过去 3 天内哪些卡车的平均有效载荷比所有其他卡车的平均有效载荷低 5% 以上。它还需要排除任何少于 10 辆装载的卡车

有3张表：

hist_loads（提供有效载荷和许多其他信息）
hist_exproot（提供与日期相关的信息，需要它来提供可读的日期）
hist_eqmtlist（需要它来确定特定的卡车型号，因为不是所有的卡车型号都必须包括在内）

以上所有表都由一个名为“shiftindex”的列连接在一起）

以下是我的查询：

    IF OBJECT_ID ('tempdb.dbo.#pave') IS NOT NULL DROP TABLE dbo.#pave;
IF OBJECT_ID ('tempdb.dbo.#pfinal') IS NOT NULL DROP TABLE dbo.#pfinal;

select  
    shiftdate
    ,truck
    ,AVG(measureton) as measureton
    ,COUNT(excav) as loads
into #pfinal
from hist_loads as hl
inner join hist_exproot as he on 
he.shiftindex = hl.shiftindex 
inner join hist_eqmtlist as heq on
heq.shiftindex = hl.shiftindex and 
hl.truck = heq.eqmtid
where hl.shiftindex between 
                            (select min(shiftindex) 
                            from hist_exproot 
                            where datepart(year,shiftdate) = datepart(year, getdate()-3)
                            and datepart(month,shiftdate) = datepart(month, getdate()-3)
                            and datepart(day,shiftdate) = datepart(day, getdate()-3))
        and 
                            (select max(shiftindex) 
                            from hist_exproot 
                            where datepart(year,shiftdate) = datepart(year, getdate()-1)
                            and datepart(month,shiftdate) = datepart(month, getdate()-1)
                            and datepart(day,shiftdate) = datepart(day, getdate()-1)) 

and eqmttype in ('Euclid EH4500','Euclid EH4500-W','Euclid EH4500-HW')
and expit like 1
and measureton not like 0

group by eqmttype, truck, shiftdate
having COUNT(excav) > 10

select  
    shiftdate
    ,AVG(measureton) as measureton
    ,COUNT(excav) as loads
into #pave
from hist_loads as hl
inner join hist_exproot as he on 
he.shiftindex = hl.shiftindex 
inner join hist_eqmtlist as heq on
heq.shiftindex = hl.shiftindex and 
hl.truck = heq.eqmtid
where hl.shiftindex between 
                            (select min(shiftindex) 
                            from hist_exproot 
                            where datepart(year,shiftdate) = datepart(year, getdate()-3)
                            and datepart(month,shiftdate) = datepart(month, getdate()-3)
                            and datepart(day,shiftdate) = datepart(day, getdate()-3))
        and 
                            (select max(shiftindex) 
                            from hist_exproot 
                            where datepart(year,shiftdate) = datepart(year, getdate()-1)
                            and datepart(month,shiftdate) = datepart(month, getdate()-1)
                            and datepart(day,shiftdate) = datepart(day, getdate()-1)) 

and eqmttype in ('Euclid EH4500','Euclid EH4500-W','Euclid EH4500-HW')
and measureton not like 0

group by eqmttype, shiftdate
having COUNT(excav) > 10

select ave.shiftdate, ave.measureton, final.truck
from #pave as ave
left join #pfinal as final on 
ave.shiftdate = final.shiftdate
where final.measureton < ave.measureton
order by truck

我的结果：

shiftdate           measureton         truck
2017-12-03 00:00:00 230.444365964912    DT01
2017-12-02 00:00:00 231.123014419048    DT04
2017-12-02 00:00:00 231.123014419048    DT07
2017-12-03 00:00:00 230.444365964912    DT07
2017-12-04 00:00:00 226.141955641026    DT07
2017-12-04 00:00:00 226.141955641026    DT10
2017-12-02 00:00:00 231.123014419048    DT13
2017-12-03 00:00:00 230.444365964912    DT14
2017-12-02 00:00:00 231.123014419048    DT15
2017-12-03 00:00:00 230.444365964912    DT17
2017-12-04 00:00:00 226.141955641026    DT17
2017-12-03 00:00:00 230.444365964912    DT20
2017-12-04 00:00:00 226.141955641026    DT20
2017-12-02 00:00:00 231.123014419048    DT22
2017-12-04 00:00:00 226.141955641026    DT24
2017-12-02 00:00:00 231.123014419048    DT25
2017-12-02 00:00:00 231.123014419048    DT27
2017-12-03 00:00:00 230.444365964912    DT27
2017-12-04 00:00:00 226.141955641026    DT28
2017-12-02 00:00:00 231.123014419048    DT30
2017-12-02 00:00:00 231.123014419048    DT31
2017-12-03 00:00:00 230.444365964912    DT31
2017-12-04 00:00:00 226.141955641026    DT32

但我只需要比平均有效载荷低 5% 以上的卡车。上面的结果似乎是过去 3 天所有卡车的平均有效载荷。3个数字之间似乎也有一些重复。

任何帮助，将不胜感激。

浅褐色的

看起来你很接近......没有你的实际数据无法确认，但让我们看看这个简化的查询是否适合你......

第一个查询，我预先计算只是基于仅日期字段收集数据，而不必单独分解年/月/日。

GetDate() 本身将返回当前日期和时间。为了得到日期，我正在运行 CONVERT(date, getdate())，所以 2017-12-05 08:22:15 am 被剥离到 2017-12-05。此外，通过执行 dateadd() -3 天会将其降低到 2017-12-02。where 子句现在 >= '2017-12-02' AND LESS than '2017-12-05' 包含 '2017-12-04 at 11:59:59 pm' 获得整个 12-04 天的覆盖您要求的整个 3 天。

您最初的计数 > 10，但说要丢弃任何少于 10 次的次数，因此我更改为 >= 10。您的其他条件保持不变。

因此，在对临时表的第一个查询结束时，仅给出合格卡车的相关 3 天的活动，并获取每辆卡车的平均值。

SELECT
      shiftdate,
      truck,
      AVG(measureton) as measureton,
      COUNT(excav) as loads
   into 
      #pTruckAvgs
   from
      hist_loads as hl
         inner join hist_exproot as he 
            ON hl.shiftindex = he.shiftindex
         inner join hist_eqmtlist as heq 
            ON hl.shiftindex = heq.shiftindex 
            and hl.truck = heq.eqmtid
   where 
          hl.shiftindex >= DateAdd( day, -3, CONVERT(date, getdate()) )
      and hl.shiftindex < CONVERT(date, getdate())
      and eqmttype in ('Euclid EH4500','Euclid EH4500-W','Euclid EH4500-HW')
      and expit like 1
      and measureton not like 0
   group by 
      eqmttype, 
      truck, 
      shiftdate
   having 
      COUNT(excav) >= 10;

现在，您有一个包含所有卡车及其各自平均值的临时表。您不需要根据日期等再次重新查询，只需从这个临时表中获取平均值（通过 QualAvgs 结果别名）。在这里，您正在寻找比平均值低 5% 的值，因此我取平均值 * .95，并获得低于所有平均值 95% 的所有卡车平均值。完成后删除临时表。

select
      TA.*,
      QualAvgs.AllAvg
    from
       #pTruckAvgs TA,
       ( select AVG(measureton) as AllAvg
            from #pTruckAvgs ) QualAvgs
    where
       TA.measureTon < ( QualAvgs.AllAvg * .95);


drop table #pTruckAvgs;

现在最后的想法...对于每天的平均值可能需要稍作调整...例如，如果恶劣的天气或其他不可预见的限制使每辆卡车在一天内变慢，您可能想要。

select
      TA.*,
      QualAvgs.AllAvg
    from
       #pTruckAvgs TA,
       ( select shiftdate,
                 AVG(measureton) as AllAvg
            from #pTruckAvgs 
            group by shiftdate ) QualAvgs
    where
           TA.measureTon < ( QualAvgs.AllAvg * .95)
       AND TA.ShiftDate = QualAvgs.ShiftDate;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。

编辑于2021-07-16

我来说两句

0条评论

登录后参与评论

来自分类Dev

Related 相关文章

文章

SQL查询计算平均值的平均值

SQL查询计算平均值的平均值

SQL计算平均值

计算平均值？

每个状态平均值的SQL查询计算列

用于计算平均值并插入表中的 SQL 查询

SQL-计算预测平均值

对平均值函数进行SQL查询

获取子查询SQL的平均值

SQL SELECT 查询总和 > 平均值

计算DateInterval的平均值

计算平均值C ++

计算Datediff的平均值

从EditTexts计算平均值

计算多行平均值

函数计算平均值

计算字段的平均值

JDBC计算平均值

MySQL查询平均值

Linq查询与平均值

Linq查询平均值

MySQL查询平均值

Linq查询与平均值

SQL 分组平均值

条件平均值（“平均值”）

条件平均值（“平均值”）

平均值的平均值

平均值计算SQL Server中的加权平均值

计算计算值的平均值

列表平均值

累计平均值