如何查询最近10天以上的站点 [英] How to query Sites with Last 10 Days above Temperature

问题描述

我有一个表（在MS SQL 2005上），它存储来自站点的每日报告，包括它们的温度。每天有几百个网站报道。

这样的事情：

 id网站Temp 
 ======= ============================= 
 12/16/2012 3:06:20 AM  9876   52  
 12/16/2012 4:02:28 AM  1234   66  
 12/17/2012 7:08:24 AM  2222  < span class =code-digit> 53  
 12/17/2012 1:00:14 PM  1234   65  
 12/17/2012 1:10:36 PM  9876   51  

我需要找到一种方法来查找过去10天超过一定温度的所有网站（让我们说60度。）然后我需要找到这10天内每个结果网站的平均温度。



有了这个问题我已经达到了他自己教授sql知识。非常感谢任何帮助。

解决方案

从mytable中选择不同的网站，其中temp> 60和id> = DATEADD（D，-10，DATEDIFF（D，0，GETDATE（）））



这是10天前的开始，并使用distinct，以便如果某个站点有多个结果，则只返回一个。

为此，您需要查找所有超过60的站点并随时取消所有站点小于或等于60.然后使用group by子句获取平均温度。

  SELECT  
网站，
平均（临时）
  FROM  TableName A 
  WHERE  temp>  60  和 id> = DATEADD（D，-10，DATEDIFF（D， 0 ，GETDATE（）））
  AND  不 存在（ SELECT 网站 FROM  TableName B < span class =code-keyword> WHERE  B.temp< =  60  和 a.Site = b.Site 和 B.id> = DATEADD（D，-10，DATEDIFF（D， 0 ，GETDATE（））））
  GROUP   BY 网站

请修复任何语法错误，我还没编译。

谢谢Christian和Rohit通过这个和学习经验帮助我！你们都是学者和先生们。



这两个查询产生类似的回报。



来自Christian：

  SELECT  site，avg（temp） AS  avgTemp 
  FROM  myTable  as  a 
  WHERE （
  SELECT  COUNT（*）
  FROM  myTable  as  b 
  WHERE  b.site = a.site  AND  b.temp>  50   AND  b.id> ; = DATEADD（D，-10，DATEDIFF（D， 0 ，GETDATE（））））>  9  
  AND  a.id> = DATEADD（D，-10，DATEDIFF（D） ， 0 ，GETDATE（）））
  GROUP   BY 网站
  ORDER   BY 网站

来自Rohit：

  SELECT 网站，avg（temp） as  avgTemp 
  FROM  myTable a 
  WHERE  temp>  55  和 id> = DATEADD（D，-10，DATEDIFF（D， 0 ，GETDATE（）））
  AND  不 存在（ SELECT  site  FROM  myTable B < span class =code-keyword> WHERE  B.temp< =  55  和 a.SID = b.SID 和 B.id> = DATEADD（D，-10，DATEDIFF（D， 0） ，GETDATE（））））
  GROUP   BY  site 
  计数（ Distinct  转换（ VARCHAR （ 10 ），id， 101 ）） >  9  
 订单  网站

I have a table (on MS SQL 2005) that stores daily reports from sites including their temperature. There are several hundred sites reporting daily.
Something like this:

id                      Site Temp
====================================
12/16/2012 3:06:20 AM   9876  52
12/16/2012 4:02:28 AM   1234  66
12/17/2012 7:08:24 AM   2222  53
12/17/2012 1:00:14 PM   1234  65
12/17/2012 1:10:36 PM   9876  51

I need to find a way to find all those sites that have had the past 10 days above a certain temperature (lets say 60 degrees.) And then i need to find the average temp of each of the resulting sites over those 10 days.

With this problem i have reached the wall of my self taught sql knowledge. Any assistance would be greatly appreciated.

解决方案

select distinct site from mytable where temp > 60 and id >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE()))

This gets the start of the day 10 days ago, and uses distinct so that if a site had more than one result, only one is returned.

For this you need find out all the site which are above 60 and negate all site which anytime had less than or equal to 60. Then take avg of temp with group by clause.

SELECT
   Site,
   Avg(Temp)
FROM TableName A
WHERE temp > 60 and id >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE()))
AND Not Exists (SELECT Site FROM TableName B WHERE B.temp <= 60 and a.Site = b.Site and B.id >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE())))
GROUP BY Site

Please fix any syntax error is there, i have not compiled it.

Thank you Christian and Rohit for helping me through this and the learning experience! You are both Scholars and Gentlemen.

Here are the two queries that yield similar returns.

From Christian:

SELECT site, avg(temp) AS avgTemp
FROM  myTable as a
WHERE (
  SELECT COUNT(*)
  FROM myTable as b
  WHERE b.site= a.site AND b.temp > 50 AND b.id >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE()))) > 9 
  AND a.id >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE()))
GROUP BY site
ORDER BY site

From Rohit:

SELECT site, avg(temp) as avgTemp
FROM myTable a
WHERE temp> 55 and id >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE()))
    AND Not Exists (SELECT site FROM myTable B WHERE B.temp <= 55 and a.SID = b.SID and B.id >= DATEADD(D , -10, DATEDIFF(D,0, GETDATE())))
GROUP BY site
Having Count(Distinct Convert(VARCHAR(10),id , 101))> 9
order by site

这篇关于如何查询最近10天以上的站点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！