第一步：创建表，导入数据
#建表 create table click_tb(username string,month string,click int) row format delimited fields terminated by ','; #导入数据 load data local inpath ‘/root/test_data/click.txt’ into click_tb;第二步：求每个用户每个月的点击量
select username,month,sum(click) as click_count from click_tb group by username,month; +-----------+----------+--------------+--+ | username | month | click_count | +-----------+----------+--------------+--+ | A | 2015-01 | 33 | | A | 2015-02 | 10 | | B | 2015-01 | 30 | | B | 2015-02 | 15 | +-----------+----------+--------------+--+第三步：自己和自己内连接（求交集）
select * from (select username,month,sum(click) as click_count from click_tb group by username,month) A inner join (select username,month,sum(click) as click_count from click_tb group by username,month) B on A.username=B.username; +-------------+----------+----------------+-------------+----------+----------------+--+ | a.username | a.month | a.click_count | b.username | b.month | b.click_count | +-------------+----------+----------------+-------------+----------+----------------+--+ | A | 2015-01 | 33 | A | 2015-01 | 33 | | A | 2015-01 | 33 | A | 2015-02 | 10 | | A | 2015-02 | 10 | A | 2015-01 | 33 | | A | 2015-02 | 10 | A | 2015-02 | 10 | | B | 2015-01 | 30 | B | 2015-01 | 30 | | B | 2015-01 | 30 | B | 2015-02 | 15 | | B | 2015-02 | 15 | B | 2015-01 | 30 | | B | 2015-02 | 15 | B | 2015-02 | 15 | +-------------+----------+----------------+-------------+----------+----------------+--+第四步：求出最终所需结果
select a.username,a.month,min(a.click_count) as click_count,sum(b.click_count) as sum_count from (select username,month,sum(click) as click_count from click_tb group by username,month) a inner join (select username,month,sum(click) as click_count from click_tb group by username,month) b on A.username=B.username where b.month<=a.month group by a.username,a.month order by a.username,a.month; +-------------+----------+--------------+------------+--+ | a.username | a.month | click_count | sum_count | +-------------+----------+--------------+------------+--+ | A | 2015-01 | 33 | 33 | | A | 2015-02 | 10 | 43 | | B | 2015-01 | 30 | 30 | | B | 2015-02 | 15 | 45 | +-------------+----------+--------------+------------+--+