ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 682|回复: 8

[SQL] 求助sql, sum() 遇到0停住不合计

[复制链接]
认证徽章
论坛徽章:
1
弗兰奇
日期:2017-06-25 12:14:04
发表于 2017-6-15 01:07 | 显示全部楼层 |阅读模式
本帖最后由 wwwyibin518 于 2017-6-15 10:27 编辑

WITH t AS(
  SELECT 1 ID, 1 c1 FROM dual UNION ALL
  SELECT 2 ID, 1 c1 FROM dual UNION ALL
  SELECT 3 ID, 1 c1 FROM dual UNION ALL
  SELECT 4 ID, 1 c1 FROM dual UNION ALL
  SELECT 5 ID, 0 c1 FROM dual UNION ALL
  SELECT 6 ID, 0 c1 FROM dual UNION ALL
  SELECT 7 ID, 1 c1 FROM dual UNION ALL
  SELECT 8 ID, 0 c1 FROM dual UNION ALL
  SELECT 9 ID, 1 c1 FROM dual UNION ALL
  SELECT 10 ID, 1 c1 FROM dual
)
  SELECT t.id, t.c1
  FROM t
  ORDER BY t.id;

有ID, 和C1列, 想得到C2列这样的数据.该怎么写sql 啊.... (C1列的值,都是1 和0 ,想知道C1列连续有1值的有多少..,连续1的连续相加, 遇到0,变0.不继续相加)
想到用 leag() 和sum() 和 case when 组合..但是,努力了之后还是没能实现....


ID  C1  C2
1    1    1
2    1    2
3    1    3
4    1    4
5    0    0
6    0    0
7    1    1
8    0    0
9    1    1
10  1    2

【编辑补充】哈,不好意思啊,描述还不够详细.
C1列的0和1是随机的, C2列想得到的数据是 C1列按ID顺序遇到1时累加, 但是遇到0就停止且清0,遇到1继续累加.









论坛徽章:
479
状元
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09
发表于 2017-6-15 02:11 | 显示全部楼层
WITH t AS(
  SELECT 1 ID, 1 c1 FROM dual UNION ALL
  SELECT 2 ID, 1 c1 FROM dual UNION ALL
  SELECT 3 ID, 1 c1 FROM dual UNION ALL
  SELECT 4 ID, 1 c1 FROM dual UNION ALL
  SELECT 5 ID, 0 c1 FROM dual UNION ALL
  SELECT 6 ID, 0 c1 FROM dual UNION ALL
  SELECT 7 ID, 1 c1 FROM dual UNION ALL
  SELECT 8 ID, 0 c1 FROM dual UNION ALL
  SELECT 9 ID, 1 c1 FROM dual UNION ALL
  SELECT 10 ID, 1 c1 FROM dual
)
SELECT id,c1,SUM(c1) OVER(PARTITION BY grp ORDER BY id) s
  FROM (
        SELECT t.*
              ,ROW_NUMBER() OVER(ORDER BY id)-ROW_NUMBER() OVER(PARTITION BY c1 ORDER BY id) grp
          FROM t
       )
ORDER BY id;
  

使用道具 举报

回复
求职 : 数据库开发
论坛徽章:
16
优秀写手
日期:2014-02-27 06:00:13弗兰奇
日期:2017-07-04 09:16:01秀才
日期:2017-06-29 10:16:48乌索普
日期:2017-05-26 08:58:24娜美
日期:2017-05-18 16:07:23ITPUB15周年纪念
日期:2017-05-02 15:22:36妮可·罗宾
日期:2017-04-06 10:06:19处女座
日期:2016-03-10 09:03:26白羊座
日期:2015-10-09 16:42:50慢羊羊
日期:2015-06-15 21:49:18
发表于 2017-6-15 08:40 | 显示全部楼层
WITH t AS(
  SELECT 1 ID, 1 c1 FROM dual UNION ALL
  SELECT 2 ID, 1 c1 FROM dual UNION ALL
  SELECT 3 ID, 1 c1 FROM dual UNION ALL
  SELECT 4 ID, 1 c1 FROM dual UNION ALL
  SELECT 5 ID, 0 c1 FROM dual UNION ALL
  SELECT 6 ID, 0 c1 FROM dual UNION ALL
  SELECT 7 ID, 1 c1 FROM dual UNION ALL
  SELECT 8 ID, 0 c1 FROM dual UNION ALL
  SELECT 9 ID, 1 c1 FROM dual UNION ALL
  SELECT 10 ID, 1 c1 FROM dual
)
SELECT ID,C1,SUM(C1)OVER(PARTITION BY G ORDER BY ID) FROM
(SELECT ID,C1,SUM(DECODE(C1,0,1,0))OVER(ORDER BY ID) G FROM T);
懒得找规律的话,用递归或者model
WITH t AS(
  SELECT 1 ID, 1 c1 FROM dual UNION ALL
  SELECT 2 ID, 1 c1 FROM dual UNION ALL
  SELECT 3 ID, 1 c1 FROM dual UNION ALL
  SELECT 4 ID, 1 c1 FROM dual UNION ALL
  SELECT 5 ID, 0 c1 FROM dual UNION ALL
  SELECT 6 ID, 0 c1 FROM dual UNION ALL
  SELECT 7 ID, 1 c1 FROM dual UNION ALL
  SELECT 8 ID, 0 c1 FROM dual UNION ALL
  SELECT 9 ID, 1 c1 FROM dual UNION ALL
  SELECT 10 ID, 1 c1 FROM dual
),
t2(id,c1,c2) as
(select id,c1,c1 from t where id=1
union all
select t.id,t.c1,decode(t.c1,0,0,t.c1+t2.c2) from t2,t
where t.id=t2.id+1)
select * from t2;

使用道具 举报

回复
论坛徽章:
395
阿斯顿马丁
日期:2014-01-03 13:53:52马上有对象
日期:2014-04-09 16:19:542014年世界杯参赛球队: 洪都拉斯
日期:2014-06-25 08:25:55itpub13周年纪念徽章
日期:2014-09-28 10:55:55itpub13周年纪念徽章
日期:2014-10-01 15:27:22itpub13周年纪念徽章
日期:2014-10-09 12:04:18马上有钱
日期:2014-10-14 21:37:37马上有钱
日期:2015-01-22 00:39:13喜羊羊
日期:2015-02-20 22:26:07懒羊羊
日期:2015-02-21 22:03:31
发表于 2017-6-15 09:28 | 显示全部楼层
chengccy2010 发表于 2017-6-15 08:40
WITH t AS(
  SELECT 1 ID, 1 c1 FROM dual UNION ALL
  SELECT 2 ID, 1 c1 FROM dual UNION ALL

比newkid的还短,还好懂

使用道具 举报

回复
认证徽章
论坛徽章:
1
弗兰奇
日期:2017-06-25 12:14:04
发表于 2017-6-15 10:10 | 显示全部楼层
本帖最后由 wwwyibin518 于 2017-6-15 10:12 编辑

WITH t AS(
  SELECT 1 ID, 0 c1 FROM dual UNION ALL
  SELECT 2 ID, 0 c1 FROM dual UNION ALL
  SELECT 3 ID, 1 c1 FROM dual UNION ALL
  SELECT 4 ID, 1 c1 FROM dual UNION ALL
  SELECT 5 ID, 1 c1 FROM dual UNION ALL
  SELECT 6 ID, 0 c1 FROM dual UNION ALL
  SELECT 7 ID, 1 c1 FROM dual UNION ALL
  SELECT 8 ID, 0 c1 FROM dual UNION ALL
  SELECT 9 ID, 1 c1 FROM dual UNION ALL
  SELECT 10 ID, 0 c1 FROM dual
)
  SELECT t.id, t.c1
  FROM t
  ORDER BY t.id;

【补充】哈,不好意思啊,描述还不够详细.
C1列的0和1是随机的, C2列想得到的数据是 C1列按ID顺序遇到1时累加, 但是遇到0就停止且清0,遇到1继续累加.

使用道具 举报

回复
认证徽章
论坛徽章:
1
弗兰奇
日期:2017-06-25 12:14:04
发表于 2017-6-15 10:22 | 显示全部楼层
本帖最后由 wwwyibin518 于 2017-6-15 10:26 编辑
newkid 发表于 2017-6-15 02:11
WITH t AS(
  SELECT 1 ID, 1 c1 FROM dual UNION ALL
  SELECT 2 ID, 1 c1 FROM dual UNION ALL

谢谢你的解答
有这个情况  当C1列 第一行第二行的值为0时, S列的数有点小问题.哈
不过是我没有描述清楚情况.

使用道具 举报

回复
认证徽章
论坛徽章:
1
弗兰奇
日期:2017-06-25 12:14:04
发表于 2017-6-15 10:25 | 显示全部楼层
chengccy2010 发表于 2017-6-15 08:40
WITH t AS(
  SELECT 1 ID, 1 c1 FROM dual UNION ALL
  SELECT 2 ID, 1 c1 FROM dual UNION ALL

谢谢你的解答.
提供 很好的清晰思路,学习了

使用道具 举报

回复
论坛徽章:
126
ITPUB元老
日期:2007-07-04 17:27:50会员2007贡献徽章
日期:2007-09-26 18:42:10现任管理团队成员
日期:2011-05-07 01:45:08优秀写手
日期:2015-01-09 06:00:14版主7段
日期:2015-07-16 02:10:00
发表于 2017-6-15 10:40 | 显示全部楼层



具体如下:

--测试一
SQL> WITH t AS(
  2    SELECT 1 ID, 1 c1 FROM dual UNION ALL
  3    SELECT 2 ID, 1 c1 FROM dual UNION ALL
  4    SELECT 3 ID, 1 c1 FROM dual UNION ALL
  5    SELECT 4 ID, 1 c1 FROM dual UNION ALL
  6    SELECT 5 ID, 0 c1 FROM dual UNION ALL
  7    SELECT 6 ID, 0 c1 FROM dual UNION ALL
  8    SELECT 7 ID, 1 c1 FROM dual UNION ALL
  9    SELECT 8 ID, 0 c1 FROM dual UNION ALL
10    SELECT 9 ID, 1 c1 FROM dual UNION ALL
11    SELECT 10 ID, 1 c1 FROM dual
12  )
13  select  id,
14          c1,
15          sum(c1) over(partition by rn order by id) c2
16  from
17  (select id,
18          c1,
19          max(rn) over(order by id) rn
20  from
21  (select id,
22          c1,
23          decode(c1, lag(c1) over(order by id), 0,row_number() over(order by id)) rn
24     from t))
25  ;

        ID         C1         C2
---------- ---------- ----------
         1          1          1
         2          1          2
         3          1          3
         4          1          4
         5          0          0
         6          0          0
         7          1          1
         8          0          0
         9          1          1
        10          1          2

10 rows selected

SQL>


--测试二
SQL> WITH t AS(
  2    SELECT 1 ID, 0 c1 FROM dual UNION ALL
  3    SELECT 2 ID, 0 c1 FROM dual UNION ALL
  4    SELECT 3 ID, 1 c1 FROM dual UNION ALL
  5    SELECT 4 ID, 1 c1 FROM dual UNION ALL
  6    SELECT 5 ID, 1 c1 FROM dual UNION ALL
  7    SELECT 6 ID, 0 c1 FROM dual UNION ALL
  8    SELECT 7 ID, 1 c1 FROM dual UNION ALL
  9    SELECT 8 ID, 0 c1 FROM dual UNION ALL
10    SELECT 9 ID, 1 c1 FROM dual UNION ALL
11    SELECT 10 ID, 0 c1 FROM dual
12  )
13  select  id,
14          c1,
15          sum(c1) over(partition by rn order by id) c2
16  from
17  (select id,
18          c1,
19          max(rn) over(order by id) rn
20  from
21  (select id,
22          c1,
23          decode(c1, lag(c1) over(order by id), 0,row_number() over(order by id)) rn
24     from t))
25  ;

        ID         C1         C2
---------- ---------- ----------
         1          0          0
         2          0          0
         3          1          1
         4          1          2
         5          1          3
         6          0          0
         7          1          1
         8          0          0
         9          1          1
        10          0          0

10 rows selected



使用道具 举报

回复
论坛徽章:
479
状元
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09
发表于 2017-6-15 21:56 | 显示全部楼层
wwwyibin518 发表于 2017-6-15 10:22
谢谢你的解答
有这个情况  当C1列 第一行第二行的值为0时, S列的数有点小问题.哈
不过是我没有描述清楚 ...

分组时少写了一列:

WITH t AS(
  SELECT 1 ID, 0 c1 FROM dual UNION ALL
  SELECT 2 ID, 0 c1 FROM dual UNION ALL
  SELECT 3 ID, 1 c1 FROM dual UNION ALL
  SELECT 4 ID, 1 c1 FROM dual UNION ALL
  SELECT 5 ID, 0 c1 FROM dual UNION ALL
  SELECT 6 ID, 0 c1 FROM dual UNION ALL
  SELECT 7 ID, 1 c1 FROM dual UNION ALL
  SELECT 8 ID, 0 c1 FROM dual UNION ALL
  SELECT 9 ID, 1 c1 FROM dual UNION ALL
  SELECT 10 ID, 1 c1 FROM dual
)
SELECT id,c1,SUM(c1) OVER(PARTITION BY c1,grp ORDER BY id) s
  FROM (
        SELECT t.*
              ,ROW_NUMBER() OVER(ORDER BY id)-ROW_NUMBER() OVER(PARTITION BY c1 ORDER BY id) grp
          FROM t
       )
ORDER BY id;

我这个方法是解决连续值分组的套路,这里的需求是只需关注某个值的连续出现,所以用3楼的方法更为简洁。
递归和MODEL的方法效率比较低。

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表