SQLZOO:White Christmas

1.The units are 10th of a degree Celcius. The columns are yr and dy for year and day of month. The next twelve columns are for January through to December.

Show the average daily temperature for August 10th 1964

SELECT m8/10 FROM hadcet
  WHERE yr=1964 AND dy=10

• 理解例表是以月为列名，年份与每月的第几日为排名，表格内容是当天平均气温（单位摄氏度）数值的10倍。题目要求是显示对应年月日的日平均气温，因此在where设置条件，在select中要将格内数值除以10。

2.Charles Dickens is said to be responsible for the tradition of expecting snow at Christmas Daily Telegraph. Show the temperature on Christmas day (25th December) for each year of his childhood. He was born in February 1812 - so he was 1 (more or less) in December 1812.

Show the twelve temperatures.

SELECT yr-1811 as age ,m12/10
FROM hadcet
  WHERE yr BETWEEN 1812 and 1812+11 AND dy=25

3.We declare a White Christmas if there was a day with an average temperature below zero between 21st and 25th of December.

For each age 1-12 show which years were a White Christmas. Show ‘White Christmas’ or ‘No snow’ for each age.

SELECT yr-1811 as age,case when min(m12)<0 then 'White Christmas' else 'No Snow' end
from hadcet
where dy between 21 and 25
and yr BETWEEN 1812 and 1812+11
group by yr

• 提交了样例代码后可以查看答案，答案的列名提示了用case when语句，可以提供一种寻找到白色圣诞节年份的思路。

4.A person’s White Christmas Count (wcc) is the number of White Christmases they were exposed to as a child (between 3 and 12 inclusive assuming they were born at the beginning of the year and were about 1 year old on their first Christmas).

Charles Dickens’s wcc was 8.

List all the years and the wcc for children born in each year of the data set. Only show years where the wcc was at least 7.

select yr yob,a+b+c+d+e+f+g+h+i+j wcc
from (select yr,
lead(a,2) over(order by yr) a,
lead(a,3) over(order by yr) b,
lead(a,4) over(order by yr) c,
lead(a,5) over(order by yr) d,
lead(a,6) over(order by yr) e,
lead(a,7) over(order by yr) f,
lead(a,8) over(order by yr) g,
lead(a,9) over(order by yr) h,
lead(a,10) over(order by yr) i,
lead(a,11) over(order by yr) j
from (select yr,case when min(m12)<0 then 1 else 0 end a
from hadcet
where dy between 21 and 25
group by yr)tmp1)tmp2
where a+b+c+d+e+f+g+h+i+j>=7

• 一开始不懂怎么把对应行插入到年份里去，所以不得已这么写了，后来我寻思这方法也太笨了吧，只是算十来行还好，要是算个百来行还不写代码写死？？然后上bing查了下，国内外关于这篇challenge的解析几乎没有，但总算找到一个解：

select x.yr yob,count(y.yr) wcc
from (select distinct yr
		from hadcet)x
left join (select yr 
			from hadcet 
			where dy between 21 and 25
			group by yr 
			having sum(m12<0)>0)y
on y.yr between x.yr+2 and x.yr+11
group by x.yr
having wcc>=7

• 思路：建立两个表，x表是所有年份，y是白色圣诞节的年份，然后利用left join结合between将单独年份（出生年份）对应的10年（3到12岁年份）中含白色圣诞节的行插入。最后在select中对y.yr进行统计即可得到wcc，输出结果用group by进行编组，并令wcc>=7。
• 学到了新的join方式，举一反三可以结合rank或rownumber等窗口函数用这种方法将一定区间的行对应到每一行去。
• 另外这个代码的取白色圣诞节年份的方式也很巧妙，比通过第三题得到的case when思路更简练。

5.Here are the average temperatures for August by decade. You decide.

SELECT ROUND(yr,-1) decade, ROUND(AVG(NULLIF(m8,-999))/10,1)
  FROM hadcet
GROUP BY ROUND(yr,-1)

• 这道题没让做解，只是展示了一个显示每个世纪的8月平均气温的案例。
  • 其中用nullif函数输出所有正常日期温度值，将非正常日期（如6月31日这种不存在于现实的日期，但事实上8月份不会出现这种日期）的温度值从表中的默认值-999更换为NULL，即排除了非正常值的影响。
  • 用round(yr,-1)巧妙地将所有年份改为了对应的世纪。