1321，餐馆营业额变化增长

1321，餐馆营业额变化增长

分析

下面的图片为表中数据：

第一步：求出每一天的营业额（即根据日期分组，然后求出每一天的营业额）

select
visited_on,
    sum(amount) sum_amount
from Customer
group by visited_on;

第二步：求出总营业额（即这一天和以前所有天的总营业额）

with t1 as (
    select
        visited_on,
        sum(amount) sum_amount
    from Customer
    group by visited_on
)select
    visited_on,
    sum(sum_amount) over(order by visited_on range between interval 6 day preceding and current row) amount
from t1;

第三步：求出近七天的平均营业额，保留两位小数

with t1 as (
    select
        visited_on,
    -- 每一天总营业额
        sum(amount) sum_amount
    from Customer
    group by visited_on
)
, t2 as (
    select
        visited_on,
        sum(sum_amount) over(order by visited_on range between interval 6 day preceding and current row) amount
    from t1
)
select
    visited_on,
    amount,
    -- 近七天平均营业额
    round(amount / 7, 2) average_amount
from t2
-- 筛选过滤 近七天
where datediff(visited_on, (select min(visited_on) from Customer)) >= 6;

总结

此题并无难度，认真分析即可

原文地址：https://blog.csdn.net/qq_54889094/article/details/143019355

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！