hivesql 查询最近七天 最近三十天，昨天各个原因的占比

hivesql 查询最近七天 最近三十天，昨天各个原因的占比
l_date 日期字段，vc 原因字段 ，base表字段
麻烦小哥哥写详细点

上面的sql是错的。只是做了个排序，没有进行指标的计算，而且这个根本没必要用开窗函数。
下面的sql应该是你想要的结果

SELECT tag, vc
    , num / sum(pv_total) OVER () AS rate
FROM (
    SELECT '最近7天' AS tag, vc, count(1) AS num
    FROM base
    WHERE to_date(l_date, 'yyyy-mm-dd') > to_date(sysdate - 7, 'yyyy-mm-dd')
    GROUP BY vc
) a
UNION ALL
SELECT tag, vc
    , num / sum(pv_total) OVER () AS rate
FROM (
    SELECT '最近30天' AS tag, vc, count(1) AS num
    FROM base
    WHERE to_date(l_date, 'yyyy-mm-dd') > to_date(sysdate - 30, 'yyyy-mm-dd')
    GROUP BY vc
) a
UNION ALL
SELECT tag, vc
    , num / sum(pv_total) OVER () AS rate
FROM (
    SELECT '昨天' AS tag, vc, count(1) AS num
    FROM base
    WHERE to_date(l_date, 'yyyy-mm-dd') > to_date(sysdate - 1, 'yyyy-mm-dd')
    GROUP BY vc
) a

select *
from (select l_date , vc,
row_number() over( order by l_date desc) as n
from base t group by l_date , vc ) a where a.n <= 7；
select *
from (select l_date , vc ,
row_number() over( order by l_date desc) as n
from base t group by l_date , vc ) a where a.n <= 30；

select vc from base where l_date>date_sub(now(),7)