场景1：下游分析

最近在分析一些图片的标签，大概场景是有没有这个标签，图片数还剩多少，在没有标签1的情况下，如果没有标签2，还剩多少图片，没有标签2，3的情况下，如果没有标签1，还剩多少图片。

图片标签表的标识键是图片ID，一个图片可以被打很多标签，因此标识键会有很多行。如果按照传统方法：

select count(distinct case when label_id =xxx then id end),count(distinct case when label_id not in(xxx,xxx) then id end) from table 

这样很不方便扩展分析，如果要更换分析条件，那么就需要重跑一次分区表，一个分区接近200亿数据，计算非常耗时，用窗口函数的方法可以给标识键多行的情况下给每一个相同的键的所有行都打上正确或错误的条件，存储这张表，后续的探索性分析就可以不用在进行复杂逻辑计算，实现持续分析。

如t1表id是图片的id，col1表示图片的标签；t2表表示每一个图片id想要的目标标签idtar，如果t1表有对应的标签tar，就保留这个图片的所有信息。下面的代码is_top_label_calc=1就通过窗口函数筛选出了目标图片的所有行。

with t1 as (
select 'ab' as col1,'1' as id
union all
select 'cd' as col1,'1' as id
union all
select 'ab' as col1,'2' as id
union all
select 'cd' as col1,'3' as id
)
t2 as (
    select 'ab' as tar,'1' as id
    union all
    select 'ab' as tar,'2' as id
    union all
    select 'ab' as tar,'3' as id
    )
select * ,max(case when t1.col1=t2.tar then 1 else 0 end) over (partition by tar) as is_top_label_calc from t1 left join t2 on t1.id=t2.id;

如果不这么做，需要先join两张表，然后找到col1有等于tar的图片标识键，在从t1表中留下有这些键的所有行，这样子就多写了一步。

场景2：条件抽样

最近接到了一个需求，比如我们有一些电影资源要推送，电影资源有上市时间这个属性，比如2020年，2019年，现在我们需要抽取50部电影，要求：优先抽取在不同年份的电影。
这个需求可以用窗口函数实现，其原理如下面的SQL:先构建一个随机值，由于我们需要根据年份抽样，优先保证年份不一样的电影排序在前面。那么就需要为年份一样的电影打上一个序号，date_rank实现了根据时间进行排序，每一年的电影都会从序号1开始编号，overall_rank是一个完全随机的编号。那么下游的final_rank的排序来看，每一年的第一部都是1，此时为0，asc升序永远排在前面，保证了每年至少有一部电影选中后，后面再随机排序。 如果要保证每年有5部电影都优先选上，那就date_rank<=5 then 0 else 1 就可以了。

with ranked_rand as (
select 
    item_id,update_time,rand() as random_num from video
),
ranked_video as (
select
    item_id,
    update_time,
    random_num,
    row_number() over (partition by  update_time order by random_num) as date_rank,
    row_number() over (order by random_num) as overall_rank
from ranked_rand)
select
    item_id
from (
    select
        item_id,
        row_number() over ( order by case when date_rank = 1 then 0 else 1 end,overall_rank ) as final_rank
    from ranked_video ) t
    where final_rank <= 30