用sql统计每天每个客户的金额及截止到统计时的累计金额

qq_37845330 2017-05-03 09:40:20
date userid amount
2011/10/1 1:00 u1 20
2011/10/1 2:00 u1 52
2011/10/1 3:00 u2 14
2011/10/1 3:00 u4 58
2011/10/2 4:00 u1 47
2011/10/2 4:00 u2 95
2011/10/2 4:00 u4 56
2011/10/3 5:00 u1 58
2011/10/3 4:00 u2 69
我想得到：
date us amount 累计
2011/10/1 u1 72 72
2011/10/1 u2 14 86
2011/10/1 u4 58 144
2011/10/2 u1 47 191
2011/10/2 u2 95 286
2011/10/2 u4 56 342
2011/10/3 u1 58 400
2011/10/3 u2 69 469
在原始数据的基础上增加3条记录，原始数据记录变为：
date userid amount
2011/10/1 1:00 u1 20
2011/10/1 2:00 u1 52
2011/10/1 3:00 u2 14
2011/10/1 3:00 u4 58
2011/10/2 4:00 u1 47
2011/10/2 4:00 u2 95
2011/10/2 4:00 u4 56
2011/10/3 5:00 u1 58
2011/10/3 4:00 u2 69
2011/10/3 5:00 u2 78
2011/10/3 6:00 u2 32
2011/10/3 7:00 u4 20
通过sql统计得到
date us amount 累计
2011/10/1 u1 72 72
2011/10/1 u2 14 86
2011/10/1 u4 58 144
2011/10/2 u1 47 191
2011/10/2 u2 95 286
2011/10/2 u4 56 342
2011/10/3 u1 58 400
2011/10/3 u2 179 469
2011/10/3 u4 20 599


...全文
3607 18 打赏 收藏 转发到动态 举报
写回复
用AI写文章
18 条回复
切换为时间正序
请发表友善的回复…
发表回复
「已注销」 2018-05-10
  • 打赏
  • 举报
 回复
引用 12 楼 sinat_28984567 的回复:
[quote=引用 10 楼 qq_28581179 的回复:] 这样还不结贴，不尊重别人的劳动成果吗
[/quote] help me，3q
「已注销」 2018-05-10
  • 打赏
  • 举报
 回复



需求是获得每条记录的截止上期金额，这个好像和这个问题很相似，想问问
「已注销」 2018-05-10
  • 打赏
  • 举报
 回复
一楼的问题，如果要根据userid 分类怎么处理？ 也就是说 只累计与当前uid 一样的数据
wakl1207 2017-06-23
  • 打赏
  • 举报
 回复
顺势而为1 2017-05-05
  • 打赏
  • 举报
 回复
他的结贴率为0
qq_37845330 2017-05-05
  • 打赏
  • 举报
 回复
必须结贴 哈哈
二月十六 2017-05-05
  • 打赏
  • 举报
 回复
引用 10 楼 qq_28581179 的回复:
这样还不结贴，不尊重别人的劳动成果吗
二月十六 2017-05-05
  • 打赏
  • 举报
 回复 
--测试数据

if not object_id(N'Tempdb..#T') is null

	drop table #T

Go

 CREATE TABLE #T([date] datetime,userid NVARCHAR(100),amount INT)

Insert #T

select '2011/10/1 1:00',N'u1',20 union all

select '2011/10/1 2:00',N'u1',52 union all

select '2011/10/1 3:00',N'u2',14 union all

select '2011/10/1 3:00',N'u4',58 union all

select '2011/10/1 3:00',N'u5',75 union all

select '2011/10/1 4:00',N'u1',84 union all

select '2011/10/1 4:00',N'u2',12 union all

select '2011/10/1 4:00',N'u5',32 union all

select '2011/10/2 4:00',N'u4',56 union all

select '2011/10/2 4:00',N'u2',95 union all

select '2011/10/2 4:00',N'u5',47 union all

select '2011/10/3 5:00',N'u5',58 union all

select '2011/10/3 4:00',N'u6',69 union all

select '2011/10/3 4:00',N'u2',78 union all

select '2011/11/1 4:00',N'u5',52 union all

select '2011/11/2 4:00',N'u6',66 union all

select '2011/11/3 4:00',N'u4',69 union all

select '2011/11/4 4:00',N'u5',90 union all

select '2011/12/1 4:00',N'u6',30 union all

select '2011/12/1 4:00',N'u4',41 union all

select '2011/12/1 4:00',N'u7',80 union all

select '2011/12/1 4:00',N'u8',95 union all

select '2011/12/1 4:00',N'u5',41

Go

--测试数据结束

;

WITH    tempa

          AS ( SELECT   YEAR(date) AS nian ,

                        MONTH(date) AS yue ,

                        userid ,

                        SUM(amount) AS amount

               FROM     #T

               GROUP BY YEAR(date) ,

                        MONTH(date) ,

                        userid

             ),

        tempb

          AS ( SELECT   * ,

                        ROW_NUMBER() OVER ( ORDER BY nian, yue ) AS num

               FROM     tempa

             ),

        tempc

          AS ( SELECT   nian ,

                        yue ,

                        userid ,

                        amount ,

                        ( SELECT    SUM(amount)

                          FROM      tempb b

                          WHERE     b.num <= a.num

                        ) AS 累计

               FROM     tempb a

             )

    SELECT  nian ,

            yue ,

            a.userid ,

            a.amount ,

            a.累计 ,

            RTRIM(CONVERT(DECIMAL(10, 2), ( ( CONVERT(FLOAT, a.amount) )

                                            / ( SELECT  SUM(b.amount)

                                                FROM    tempc b

                                                WHERE   b.nian = a.nian

                                                        AND b.yue = a.yue

                                              ) ) * 100)) + '%' AS 同比当月金额

    FROM    tempc a;



逍遥清风_Frank 2017-05-05
  • 打赏
  • 举报
 回复
这样还不结贴，不尊重别人的劳动成果吗
qq_37845330 2017-05-05
  • 打赏
  • 举报
 回复
我懂了，这个是按照每个人每天统计的，按照年，月呢，就是第一列是年，第二列是相应的月，即同时按照年，月统计
二月十六 2017-05-04
  • 打赏
  • 举报
 回复
按照以前的写法，写的，没有后边改成这样nian yue的
--测试数据

if not object_id(N'Tempdb..#T') is null

	drop table #T

Go

 CREATE TABLE #T([date] datetime,userid NVARCHAR(100),amount INT)

Insert #T

select '2011/10/1 1:00',N'u1',20 union all

select '2011/10/1 2:00',N'u1',52 union all

select '2011/10/1 3:00',N'u2',14 union all

select '2011/10/1 3:00',N'u4',58 union all

select '2011/10/1 3:00',N'u5',75 union all

select '2011/10/1 4:00',N'u1',84 union all

select '2011/10/1 4:00',N'u2',12 union all

select '2011/10/1 4:00',N'u5',32 union all

select '2011/10/2 4:00',N'u4',56 union all

select '2011/10/2 4:00',N'u2',95 union all

select '2011/10/2 4:00',N'u5',47 union all

select '2011/10/3 5:00',N'u5',58 union all

select '2011/10/3 4:00',N'u6',69 union all

select '2011/10/3 4:00',N'u2',78 union all

select '2011/11/1 4:00',N'u5',52 union all

select '2011/11/2 4:00',N'u6',66 union all

select '2011/11/3 4:00',N'u4',69 union all

select '2011/11/4 4:00',N'u5',90 union all

select '2011/12/1 4:00',N'u6',30 union all

select '2011/12/1 4:00',N'u4',41 union all

select '2011/12/1 4:00',N'u7',80 union all

select '2011/12/1 4:00',N'u8',95 union all

select '2011/12/1 4:00',N'u5',41

Go

--测试数据结束

;WITH tempa AS (

SELECT  CONVERT(NVARCHAR(100), date, 23) AS date ,

        userid ,

        SUM(amount) AS amount

FROM    #T

GROUP BY CONVERT(NVARCHAR(100), date, 23) ,

        userid

),tempb AS (

SELECT *,ROW_NUMBER()OVER(ORDER BY date) AS num FROM tempa

),tempc AS(

SELECT  date ,

        userid ,

        amount ,

        ( SELECT    SUM(amount)

          FROM      tempb b

          WHERE     b.num <= a.num

        ) AS 累计

FROM    tempb a )

SELECT  a.date ,

        a.userid ,

        a.amount ,

        a.累计 ,

        RTRIM(CONVERT(DECIMAL(10, 2), ( ( CONVERT(FLOAT, a.amount) )

                                        / ( SELECT  SUM(b.amount)

                                            FROM    tempc b

                                            WHERE   b.date = a.date

                                          ) ) * 100)) + '%'

FROM    tempc a;


qq_37845330 2017-05-04
  • 打赏
  • 举报
 回复
大神，
date userid amount
2011/10/1 1:00 u1 20
2011/10/1 2:00 u1 52
2011/10/1 3:00 u2 14
2011/10/1 3:00 u4 58
2011/10/1 3:00 u5 75
2011/10/1 4:00 u1 84
2011/10/1 4:00 u2 12
2011/10/1 4:00 u5 32
2011/10/2 4:00 u4 56
2011/10/2 4:00 u2 95
2011/10/2 4:00 u5 47
2011/10/3 5:00 u5 58
2011/10/3 4:00 u6 69
2011/10/3 4:00 u2 78
2011/11/1 4:00 u5 52
2011/11/2 4:00 u6 66
2011/11/3 4:00 u4 69
2011/11/4 4:00 u5 90
2011/12/1 4:00 u6 30
2011/12/1 4:00 u4 41
2011/12/1 4:00 u7 80
2011/12/1 4:00 u8 95
2011/12/1 4:00 u5 41
结果是
nian yue userid amount 累计
2011 10 u1 156 156
2011 10 u2 199 355
2011 10 u4 114 469
2011 10 u5 212 681
2011 10 u6 69 750
2011 11 u4 69 819
2011 11 u5 142 961
2011 11 u6 66 1027
2011 12 u4 41 1068
2011 12 u5 41 1109
2011 12 u6 30 1139
2011 12 u7 80 1219
2011 12 u8 95 1314

同比是每个客户的金额/当月合计
nian yue userid amount 累计 同比当月金额
2011 10 u1 156 156 20.8%
2011 10 u2 199 355 26.5%
2011 10 u4 114 469 15.2%
2011 10 u5 212 681 28.3%
2011 10 u6 69 750 9.2%
2011 11 u4 69 819 5.3%
2011 11 u5 142 961 1 0.8%
2011 11 u6 66 1027 5.0%
2011 12 u4 41 1068 3.1%
2011 12 u5 41 1109 3.1%
2011 12 u6 30 1139 2.3%
2011 12 u7 80 1219 6.1%
2011 12 u8 95 1314 7.2%

二月十六 2017-05-04
  • 打赏
  • 举报
 回复
引用 5 楼 qq_37845330 的回复:
懂了，结贴，大神再回答个问题，用sql统计出每个月每个用户的当月金额，同比当月金额及环比当月金额，
额希望楼主能按照这个形式，再描述一下想要得到的结果。。。 date userid amount 2011/10/1 1:00 u1 20 2011/10/1 2:00 u1 52 2011/10/1 3:00 u2 14 2011/10/1 3:00 u4 58 2011/10/2 4:00 u1 47 2011/10/2 4:00 u2 95 2011/10/2 4:00 u4 56 2011/10/3 5:00 u1 58 2011/10/3 4:00 u2 69 我想得到： date us amount 累计 2011/10/1 u1 72 72 2011/10/1 u2 14 86 2011/10/1 u4 58 144 2011/10/2 u1 47 191 2011/10/2 u2 95 286 2011/10/2 u4 56 342 2011/10/3 u1 58 400 2011/10/3 u2 69 469
qq_37845330 2017-05-04
  • 打赏
  • 举报
 回复
懂了，结贴，大神再回答个问题，用sql统计出每个月每个用户的当月金额，同比当月金额及环比当月金额，
qq_37845330 2017-05-04
  • 打赏
  • 举报
 回复
？ 大神，还是昨天的数 date userid amount 2011/10/1 1:00 u1 20 2011/10/1 2:00 u1 52 2011/10/1 3:00 u2 14 2011/10/1 3:00 u4 58 2011/10/1 3:00 u5 75 2011/10/1 4:00 u1 84 2011/10/1 4:00 u2 12 2011/10/1 4:00 u5 32 2011/10/2 4:00 u4 56 2011/10/2 4:00 u2 95 2011/10/2 4:00 u5 47 2011/10/3 5:00 u5 58 2011/10/3 4:00 u6 69 2011/10/3 4:00 u2 78 2011/11/1 4:00 u5 52 2011/11/2 4:00 u6 66 2011/11/3 4:00 u4 69 2011/11/4 4:00 u5 90 2011/12/1 4:00 u6 30 2011/12/1 4:00 u4 41 2011/12/1 4:00 u7 80 2011/12/1 4:00 u8 95 2011/12/1 4:00 u5 41 最后为 nian yue userid amount 累计 2011 10 u1 156 156 2011 10 u2 199 355 2011 10 u4 114 469 2011 10 u5 212 681 2011 10 u6 69 750 2011 11 u4 69 819 2011 11 u5 142 961 2011 11 u6 66 1027 2011 12 u4 41 1068 2011 12 u5 41 1109 2011 12 u6 30 1139 2011 12 u7 80 1219 2011 12 u8 95 1314 我试了半天，还是不行
二月十六 2017-05-03
  • 打赏
  • 举报
 回复 
--测试数据

if not object_id(N'Tempdb..#T') is null

	drop table #T

Go

 CREATE TABLE #T([date] datetime,userid NVARCHAR(100),amount INT)

Insert #T

select '2011/10/1 1:00',N'u1',20 union all

select '2011/10/1 2:00',N'u1',52 union all

select '2011/10/1 3:00',N'u2',14 union all

select '2011/10/1 3:00',N'u4',58 union all

select '2011/10/2 4:00',N'u1',47 union all

select '2011/10/2 4:00',N'u2',95 union all

select '2011/10/2 4:00',N'u4',56 union all

select '2011/10/3 5:00',N'u1',58 union all

select '2011/10/3 4:00',N'u2',69

Go

--测试数据结束



;WITH tempa AS (

SELECT  CONVERT(NVARCHAR(100), date, 23) AS date ,

        userid ,

        SUM(amount) AS amount

FROM    #T

GROUP BY CONVERT(NVARCHAR(100), date, 23) ,

        userid

),tempb AS (

SELECT *,ROW_NUMBER()OVER(ORDER BY date) AS num FROM tempa

)

SELECT  date ,

        userid ,

        amount ,

        ( SELECT    SUM(amount)

          FROM      tempb b

          WHERE     b.num <= a.num

        ) AS 累计

FROM    tempb a


二月十六 2017-05-03
  • 打赏
  • 举报
 回复
引用 2 楼 qq_37845330 的回复:
谢谢啊，大神
不客气，记得结贴
qq_37845330 2017-05-03
  • 打赏
  • 举报
 回复
谢谢啊，大神
Hive求截止到当前日期累计消费金额或使用次数
说明 经常遇到这种类型的问题：求截止到当前日期（或在此日期之前），某用户的累计消费金额或者累计使用次数。 只要遇到截止到当前日期为止，就要想到窗口函数，因为最后求的是累积消费金额，就要使用sum(金额字段) over(partition by 用户 order by 日期字段)。 场景题 下面是力扣的一道SQL题，以此题为例： activity 表: 编写一个 SQL 查询，同时报告每组玩家和日期，以及玩家到目前为止玩了多少游戏。也就是说，在此日期之前玩家所玩的游戏总数。最终结果如下： SQL SELE
编写sql实现每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数
编写sql实现每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数
hive sql系列（二）——统计每个人每个月访问量和累计月访问量
每天分享一个sql，帮助大家找到sql的快乐 需求：统计每个月访问量和累计月访问量 建表语句 CREATE TABLE visit( userId string, visitDate string, visitCount INT ) ROW format delimited FIELDS TERMINATED BY "\t"; 数据 INSERT INTO TABLE visit VALUES ( 'u01', '2017/1/21', 5 ), ( 'u0...
SQL实现计算累计月份预算金额(默认指定到12月)
在报表数据统计的时候，用到存储过程来统计1到12月份的金额汇总，通过制定的月份累计，比如截止到12月份，就统计1到12月份的金额。这种写法可以这样实现 ，贴下代码 DECLARE @i INT,@NewMonth INT,@COLUMN VARCHAR(1000),@SumCOLUMN VARCHAR(2000),@sql VARCHAR(6000) SET @i=0;SET @COLUM...
截止到某天的汇总报表_SQL练习题43：牛客每天有很多人登录，请你统计一下牛客每个用户查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题。 不存在没有登录却刷题的情况，但是存在登录...
题目：牛客每天有很多人登录，请你统计一下牛客每个用户查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题。 不存在没有登录却刷题的情况，但是存在登录了没刷题的情况，不会存在刷题表里面，有提交代码没有通过的情况，但是会记录在刷题表里，只不过通过数目是0。有一个登录(login)记录表，简况如下:第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网。。...
community_281

590

社区成员

254,054

社区内容

发帖
与我相关
我的任务
社区描述
提出问题
其他 技术论坛（原bbs）
社区管理员
  • community_281
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章