时间线专题

时间轴类型题常见，难度中等以上，常使用 self join 或窗口函数，这里记录。

1. 和上年相比持平 / 上升 / 下降：

select s1.year,s1.sale,
case when s1.sale=s2.sale
    then '持平'
when s1.sale>s2.sale
    then '上升'
when s1.sale<s2.sale
    then '下降'
else null
end as '趋势'
from sales s1,sales s2
where s1.year-s2.year=1
order by s1.year;

---用 self join。

2. 移动累计：

select prc_date,prc_amt,
sum(prc_amt) over(order by prc_date rows between unbounded preceding and current row)
from accounts

select a1.prc_date,a1.prc_amt,sum(a2.prc_amt)
from accounts as a1,accounts as a2
where a1.prc_date >= a2.prc_date
group by a1.prc_date
order by a1.prc_date

---用窗口函数或 self join，前者更简洁。

力扣1308：

select gender,day,sum(sum_score_points) over(partition by gender order by day) as total
from
    (select gender,day,sum(score_points) as sum_score_points
    from Scores
    group by gender ,day) as tmp
order by gender ,day

---order by 后缺 rows between，默认 rows between unbounded preceding and current row。