SQL进阶技巧：如何计算累计指标？

莫叫石榴姐

于 2024-10-09 08:00:00 发布

阅读量544

点赞数 11

分类专栏：收获不止一点文章标签：数据库 mysql hive sql 大数据数据分析

本文链接：https://blog.csdn.net/godlovedaniel/article/details/142768607

版权

收获不止一点专栏收录该内容

21 篇文章 4 订阅

订阅专栏

如果觉得本文对你有帮助，想进一步学习SQL语言这门艺术的，那么不妨也可以选择去看看我的博客专栏，部分内容如下：

数字化建设通关指南

专栏原价99，现在活动价59.9，按照阶梯式增长，直到恢复原价。

0 需求描述

 我们有如下的用户访问数据
    userId  visitDate   visitCount
    u01     2017/1/21        5
    u02     2017/1/23        6
    u03     2017/1/22        8
    u04     2017/1/20        3
    u01     2017/1/23        6
    u01     2017/2/21        8
    U02     2017/1/23        6
    U01     2017/2/22        4
 
要求使用SQL统计出每个用户的累积访问次数，如下表所示：
   
   用户id    月份    小计  累积
    u01     2017-01  11    11
    u01     2017-02  12    23
    u02     2017-01  12    12
    u03     2017-01  8     8
    u04     2017-01  3     3

1 数据准备

create table visit as(

  select stack (

    8,
    'u01',    '2017/1/21',    5 ,
    'u02',    '2017/1/23',    6 ,
    'u03',    '2017/1/22',    8 ,
    'u04',    '2017/1/20',    3 ,
    'u01',    '2017/1/23',    6 ,
    'u01',    '2017/2/21',    8 ,
    'u02',    '2017/1/23',    6 ,
    'u01',    '2017/2/22',    4

  ) as(user_id,visit_time,visit_cnt)

)；

2 问题分析

第一步：求出每个用户每月的访问次数

select user_id
     , date_format(regexp_replace(visit_time, '/', '-'), 'yyyy-MM') year_month
     , sum(visit_cnt)                                               visit_cnt
from visit
group by user_id, date_format(regexp_replace(visit_time, '/', '-'), 'yyyy-MM')

第二步：利用分析函数在步骤1的基础上求出每月的累计访问次数

with t1 as
         (select user_id
               , date_format(regexp_replace(visit_time, '/', '-'), 'yyyy-MM') year_month
               , sum(visit_cnt)                                               visit_cnt
          from visit
          group by user_id, date_format(regexp_replace(visit_time, '/', '-'), 'yyyy-MM'))

select user_id
     , year_month
     , visit_cnt
     , sum(visit_cnt) over (partition by user_id order by year_month) acc_visit_cnt
from t1