本文已参与「新人创作礼」活动，一起开启掘金创作之路。
最近的做题感觉:
有时候还是有些题目写的出来了,而且感觉手感比之前好多了.
但是还是有个问题,就是在做题目的时候会有点着急,没有把题目的具体问题看明白,或者在做的时候就是做着做着把题目给忘记了!!!
注意注意!这在真的比赛的时候很吃亏的!
35.对表连接之后的统计我觉得你有点欠缺。。
36.审题！！
select
distinct viewer_id as id
from views
group by viewer_id,view_date having count(distinct article_id)>=2
#注意,需要每个人都读了不同的文章,而不是一个文章读了多遍
order by id
37.窗口函数执行顺序
from → on→ join→
where → group by→聚合函数→having→窗口函数→
select +distinct→union→order by →limit
38.思路思路思路！
这题有点难,
在做连接的时候一定要注意,到底是什么主导了连接关系的发送生.
比如说在这里按道理来说左边是没有东西的,但是右边会有.
distinct做的是整一条记录的去重,而不是单个字段的去重
38.三处关键点
select distinct visited_on,sum_amount as amount,round(average_amount,2) as average_amount
from
(
select visited_on,
sum(amount) over(order by visited_on rows 6 preceding) as sum_amount,
avg(amount) over(order by visited_on rows 6 preceding) as average_amount #avg也可以用窗口
from(
select visited_on,sum(amount) as amount
from Customer
group by visited_on
)t1
)t2
where datediff(visited_on,(select min(visited_on)from Customer))>=6 #时间差和where内不能直接用min，因为是先执行where，才有聚合
39.构造表,实现左连接带null
select distinct spend_date, "desktop" as platform
from Spending
union
select distinct spend_date, "mobile" as platform
from Spending
union
select distinct spend_date, "both" as platform
from Spending
40.又看到一个人写的sum妙用
SELECT LEFT( trans_date, 7 ) AS month,
country,
count(*) AS trans_count,
sum( state = 'approved' ) AS approved_count, #计数用
sum( amount ) AS trans_total_amount,
sum( IF ( state = 'approved', amount, 0 ) ) AS approved_total_amount #统计所有值的总和用
FROM
transactions
GROUP BY
country, month
```
