sql技巧-构造分组条件

最新推荐文章于 2023-08-14 16:00:37 发布
最新推荐文章于 2023-08-14 16:00:37 发布
阅读量1.3k 收藏
点赞数 1
分类专栏： Oralce开发
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ashic/article/details/51570440
版权

create table PAPA(day DATE);
insert into PAPA (day) values (to_date('01-01-2016', 'dd-mm-yyyy'));
insert into PAPA (day) values (to_date('02-01-2016', 'dd-mm-yyyy'));
insert into PAPA (day) values (to_date('03-01-2016', 'dd-mm-yyyy'));
insert into PAPA (day) values (to_date('06-01-2016', 'dd-mm-yyyy'));
insert into PAPA (day) values (to_date('07-01-2016', 'dd-mm-yyyy'));
insert into PAPA (day) values (to_date('08-01-2016', 'dd-mm-yyyy'));
insert into PAPA (day) values (to_date('10-01-2016', 'dd-mm-yyyy'));
insert into PAPA (day) values (to_date('12-01-2016', 'dd-mm-yyyy'));
insert into PAPA (day) values (to_date('14-01-2016', 'dd-mm-yyyy'));
insert into PAPA (day) values (to_date('17-01-2016', 'dd-mm-yyyy'));
insert into PAPA (day) values (to_date('18-01-2016', 'dd-mm-yyyy'));
insert into PAPA (day) values (to_date('19-01-2016', 'dd-mm-yyyy'));
insert into PAPA (day) values (to_date('20-01-2016', 'dd-mm-yyyy'));
insert into PAPA (day) values (to_date('21-01-2016', 'dd-mm-yyyy'));
insert into PAPA (day) values (to_date('22-01-2016', 'dd-mm-yyyy'));
insert into PAPA (day) values (to_date('23-01-2016', 'dd-mm-yyyy'));
insert into PAPA (day) values (to_date('24-01-2016', 'dd-mm-yyyy'));
insert into PAPA (day) values (to_date('27-01-2016', 'dd-mm-yyyy'));
insert into PAPA (day) values (to_date('28-01-2016', 'dd-mm-yyyy'));
insert into PAPA (day) values (to_date('31-01-2016', 'dd-mm-yyyy'));
commit; 
  
  
  1. with t1 as 
  2. (select day,
  3.          count(*) over(order by day range between current row and interval '1' day following) cnt1
  4.      from papa),
  5. t2 as
  6. (select day,cnt1,lag(cnt1) over(order by day) cnt2 from t1),
  7. t3 as
  8. (select day,cnt1,cnt2,case when cnt2=1 or cnt2 is null then '1' else '0' end as kaishi from t2),
  9. t4 as
  10. (select day,sum(kaishi) over(order by day) sm from t3),
  11. t5 as
  12. (select day,row_number() over(partition by sm order by day) rn from t4)
  13. select day,case when rn<=6 then rn else mod(rn,6) end as jifen from t5;
  17. with t1 as 
  18. (select day,
  19.          count(*) over(order by day range between current row and interval '1' day following) cnt1
  20.      from papa)
  21. select * from t1;
  23. DAY             CNT1
  24. --------- ----------
  25. 01-JAN-16          2
  26. 02-JAN-16          2
  27. 03-JAN-16          1
  28. 06-JAN-16          2
  29. 07-JAN-16          2
  30. 08-JAN-16          1
  31. 10-JAN-16          1
  32. 12-JAN-16          1
  33. 14-JAN-16          1
  34. 17-JAN-16          2
  35. 18-JAN-16          2
  36. 19-JAN-16          2
  37. 20-JAN-16          2
  38. 21-JAN-16          2
  39. 22-JAN-16          2
  40. 23-JAN-16          2
  41. 24-JAN-16          1
  42. 27-JAN-16          2
  43. 28-JAN-16          1
  44. 31-JAN-16          1
  46. 找规律发现,上一行是1的,都是起始日
  48. with t1 as 
  49. (select day,
  50.          count(*) over(order by day range between current row and interval '1' day following) cnt1
  51.      from papa),
  52. t2 as
  53. (select day,cnt1,lag(cnt1) over(order by day) cnt2 from t1)
  54. select * from t2;
  56. DAY             CNT1       CNT2
  57. --------- ---------- ----------
  58. 01-JAN-16          2
  59. 02-JAN-16          2          2
  60. 03-JAN-16          1          2
  61. 06-JAN-16          2          1
  62. 07-JAN-16          2          2
  63. 08-JAN-16          1          2
  64. 10-JAN-16          1          1
  65. 12-JAN-16          1          1
  66. 14-JAN-16          1          1
  67. 17-JAN-16          2          1
  68. 18-JAN-16          2          2
  69. 19-JAN-16          2          2
  70. 20-JAN-16          2          2
  71. 21-JAN-16          2          2
  72. 22-JAN-16          2          2
  73. 23-JAN-16          2          2
  74. 24-JAN-16          1          2
  75. 27-JAN-16          2          1
  76. 28-JAN-16          1          2
  77. 31-JAN-16          1          1
  79. 于是lag一下,就确定了起始日,case when转换一下,1就是起始日，0就是连续日
  81. DAY             CNT1       CNT2 K
  82. --------- ---------- ---------- -
  83. 01-JAN-16          2            1
  84. 02-JAN-16          2          2 0
  85. 03-JAN-16          1          2 0
  86. 06-JAN-16          2          1 1
  87. 07-JAN-16          2          2 0
  88. 08-JAN-16          1          2 0
  89. 10-JAN-16          1          1 1
  90. 12-JAN-16          1          1 1
  91. 14-JAN-16          1          1 1
  92. 17-JAN-16          2          1 1
  93. 18-JAN-16          2          2 0
  94. 19-JAN-16          2          2 0
  95. 20-JAN-16          2          2 0
  96. 21-JAN-16          2          2 0
  97. 22-JAN-16          2          2 0
  98. 23-JAN-16          2          2 0
  99. 24-JAN-16          1          2 0
  100. 27-JAN-16          2          1 1
  101. 28-JAN-16          1          2 0
  102. 31-JAN-16          1          1 1
  104. with t1 as 
  105.   2  (select day,
  106.   3           count(*) over(order by day range between current row and interval '1' day following) cnt1
  107.   4       from papa),
  108.   5  t2 as
  109.   6  (select day,cnt1,lag(cnt1) over(order by day) cnt2 from t1),
  110.   7  t3 as
  111.   8  (select day,cnt1,cnt2,case when cnt2=1 or cnt2 is null then '1' else '0' end as kaishi from t2)
  112.   9  select day,sum(kaishi) over(order by day) sm from t3;
  114. DAY               SM
  115. --------- ----------
  116. 01-JAN-16          1
  117. 02-JAN-16          1
  118. 03-JAN-16          1
  119. 06-JAN-16          2
  120. 07-JAN-16          2
  121. 08-JAN-16          2
  122. 10-JAN-16          3
  123. 12-JAN-16          4
  124. 14-JAN-16          5
  125. 17-JAN-16          6
  126. 18-JAN-16          6
  127. 19-JAN-16          6
  128. 20-JAN-16          6
  129. 21-JAN-16          6
  130. 22-JAN-16          6
  131. 23-JAN-16          6
  132. 24-JAN-16          6
  133. 27-JAN-16          7
  134. 28-JAN-16          7
  135. 31-JAN-16          8
  137. 构造partition by条件, 这个sum() over()很巧妙,每行都是unbounded preceding到current row,由于连续是0，所以连续日和起始日的sum值是一样的，这样就创造了分组条件
  138. 有了分组条件就可以用row_number() over()编号。
  139. 需要注意的是大于6的，用mod

构造分组条件还有两种方法

  
  
  1. 方法2
  2. select day,case when day=lag(day) over(order by day)+1 then 0 else 1 end as fenzu from papa;
  4. DAY             FENZU
  5. ---------- ----------
  6. 2016-01-01          1
  7. 2016-01-02          0
  8. 2016-01-03          0
  9. 2016-01-06          1
  10. 2016-01-07          0
  11. 2016-01-08          0
  12. 2016-01-10          1
  13. 2016-01-12          1
  14. 2016-01-14          1
  15. 2016-01-17          1
  16. 2016-01-18          0
  17. 2016-01-19          0
  18. 2016-01-20          0
  19. 2016-01-21          0
  20. 2016-01-22          0
  21. 2016-01-23          0
  22. 2016-01-24          0
  23. 2016-01-27          1
  24. 2016-01-28          0
  25. 2016-01-31          1
  27. 思路是上一行+1=本行日期，那么就是连续登陆日，否则就是起始日
  29. 再套一层sum 构造分组依据
  30. with t1 as 
  31. (select day,case when day=lag(day) over(order by day)+1 then 0 else 1 end as fenzu from papa)
  32. select day,sum(fenzu) over(order by day) sm from t1;
  34. DAY                SM
  35. ---------- ----------
  36. 2016-01-01          1
  37. 2016-01-02          1
  38. 2016-01-03          1
  39. 2016-01-06          2
  40. 2016-01-07          2
  41. 2016-01-08          2
  42. 2016-01-10          3
  43. 2016-01-12          4
  44. 2016-01-14          5
  45. 2016-01-17          6
  46. 2016-01-18          6
  47. 2016-01-19          6
  48. 2016-01-20          6
  49. 2016-01-21          6
  50. 2016-01-22          6
  51. 2016-01-23          6
  52. 2016-01-24          6
  53. 2016-01-27          7
  54. 2016-01-28          7
  55. 2016-01-31          8
  56. 然后就和前面一样了，用row_number() over()
  59. 方法3
  60. select day,day-rownum from papa;
  62. DAY        DAY-ROWNUM
  63. ---------- ----------
  64. 2016-01-01 2015-12-31
  65. 2016-01-02 2015-12-31
  66. 2016-01-03 2015-12-31
  67. 2016-01-06 2016-01-02
  68. 2016-01-07 2016-01-02
  69. 2016-01-08 2016-01-02
  70. 2016-01-10 2016-01-03
  71. 2016-01-12 2016-01-04
  72. 2016-01-14 2016-01-05
  73. 2016-01-17 2016-01-07
  74. 2016-01-18 2016-01-07
  75. 2016-01-19 2016-01-07
  76. 2016-01-20 2016-01-07
  77. 2016-01-21 2016-01-07
  78. 2016-01-22 2016-01-07
  79. 2016-01-23 2016-01-07
  80. 2016-01-24 2016-01-07
  81. 2016-01-27 2016-01-09
  82. 2016-01-28 2016-01-09
  83. 2016-01-31 2016-01-11
  85. 思路是，因为rownum随行数递增，如果是连续日期，那么他们减rownum后得到的日期应该是相同的，这样直接就构造了分组条件，相同日期为一组
  87. select day,row_number() over(partition by fenzu order by day) rn from (
  88. select day,day-rownum fenzu from papa);
  90. DAY                RN
  91. ---------- ----------
  92. 2016-01-01          1
  93. 2016-01-02          2
  94. 2016-01-03          3
  95. 2016-01-06          1
  96. 2016-01-07          2
  97. 2016-01-08          3
  98. 2016-01-10          1
  99. 2016-01-12          1
  100. 2016-01-14          1
  101. 2016-01-17          1
  102. 2016-01-18          2
  103. 2016-01-19          3
  104. 2016-01-20          4
  105. 2016-01-21          5
  106. 2016-01-22          6
  107. 2016-01-23          7
  108. 2016-01-24          8
  109. 2016-01-27          1
  110. 2016-01-28          2
  111. 2016-01-31          1






Fan_-_
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL:分组数据
TTYLXWT的博客
05-18 2859
分组数据： A. SQL Server Group By语句 Group By 从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集” 划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。 以下是 GROUP BY 子句的语法： 在此查询语法中， GROUP BY 子句为列中的每个值组合生成一个组。 SQL GROUP BY 语句 合计函数 (比如 SUM) 常常需要添加 GROUP BY 语句。 GROUP BY 语句 GRO.
SQL 分组条件深入剖析
terry_chengq的博客
12-18 1632
原文地址：http://c.raqsoft.com.cn/article/1576225048795?r=CGQ SQL 分组条件深入剖析 导读关键词:SQL分组 条件; SQL HAVING 条件; SQL 子查询 条件 问题 在 stackoverflow 网站上看到这样一个 SQL 分组条件的需求，需求看似挺简单，但能把 SQL 写正确对于新手来说也不容易，我们拿过来深入剖...
SQL分组指南
寒冰屋的专栏
06-30 1万+
这篇文章是SQL中Group By子句的指南。它可用于将相似的值合并到一行中。 使用SQL GROUP BY子句是将相似的值合并到一行中。该组是一组列。group by从具有相同列值的查询中的一个或多个返回单行。它的主要目的是与函数一起工作，例如SUM或COUNT，并提供一种汇总值的方法。...
SQL的分组查询
热门推荐
weixin_50179223的博客
04-07 2万+
SQL的分组查询 一、在SQL中Group By从字面的意思上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个‘数据集’划分成若干个‘小区域’，然后针对若干个‘小区域’进行数据处理。在此语法中group by子句为列中每一值组合生成一组。 例如：对成绩表(sc)中的学号(sno)进行分组 SELECT sno FROM sc GROUP BY sno 注意：使用GROUP BY 语法是分组，不是去重。需要去重的请使用DISTINCT，以下是去重的语法： SELECT DISTINCT s
SQL | 分组数据
掩月天权的博客
08-14 1536
SQL数据分组，where过滤结合group by分组
sql学习分享---分组数据
weixin_38359768的博客
02-18 2421
sql学习分享---分组数据
实验二SQL基本操作-数据查询的实现.docx
05-09
1. **熟悉SQL查询分析器的基本使用方法**：掌握如何利用SELECT、FROM、WHERE、GROUP BY、HAVING等子句来构造有效的SQL查询语句。 2. **掌握单表查询、连接查询及嵌套查询的技巧**：能够根据不同需求灵活运用这些查询...
实验四 SQL进行复杂查询-学生.docx
最新发布
05-24
### 实验四 SQL进行复杂查询知识点...以上实验步骤覆盖了SQL中的多种查询技巧和技术，包括简单的数据检索、复杂的多表连接、聚合函数的使用、分组与过滤、排序、字符串处理等，有助于深入理解和掌握SQL查询语言的应用。
sql-challenge
03-08
完成这些挑战不仅可以增强你的SQL编程技巧，还能让你更好地理解和应用数据库原理，这对于数据分析、数据科学以及任何涉及数据库管理的工作都是至关重要的。 通过实践这些挑战，你可以逐步掌握SQL的精髓，从而在实际...
Transact_SQL语句使用技巧大全
07-05
《Transact-SQL语句使用技巧大全》是针对SQL Server数据库管理系统中Transact-SQL语言的一份详尽指南。Transact-SQL是SQL Server的主要编程语言，用于查询、操作、管理和控制数据库。以下将深入探讨其中的一些关键...
Oracle SQL 官方文档
03-08
包括基本的查询构造、数据过滤、排序、分组、聚合函数以及如何处理日期和时间等。 学习这些官方文档可以帮助开发者深入理解Oracle SQL的全貌，有效编写高效的查询，设计和实施复杂的数据库解决方案。无论是初学者...
[SQL]对取出来的数据构造实验组和对照组的方法
TOMACAT的博客
07-25 2万+
select id from tb_name where id is not null and cast(id%100 as int) not between 50 and 59; --即抽取十分之一作为实验组  
SQL语言中的分组数据
weixin_54835055的博客
04-20 4250
（1）group by子句 group by 根据by指定的规则对数据进行分组。 分组：即将一个“数据集”划分成若干个“小区域”，再对若干个“小区域”进行数据处理。 语法： group by 子句为列中的每个值组合生成一个组。 group by子句的例子： （2）group by子句和聚合函数 ①group by子句通与聚合函数一起用于统计数据； ②聚合函数对组执行计算并返回每个组的唯一值； ...
SQL语言——分组数据概念及实例详细解读
kkkyzp的博客
11-26 2994
1 数据分组 从上一章知道，SQL聚集函数可用来汇总数据。这使我们能够对行进 行计数，计算和与平均数，获得最大和最小值而不用检索所有数据。 目前为止的所有计算都是在表的所有数据或匹配特定的WHERE子句的 数据上进行的。提示一下，下面的例子返回供应商1003提供的产品数目： 但如果要返回每个供应商提供的产品数目怎么办？或者返回只提供 单项产品的供应商所提供的产品，或返回提供10个以上产品的供应商怎 么办？ 这就是分组显身手的时候了。分组允许把数据分为多个逻辑组，以 便能对...
SQL基础九【分组数据】
weixin_45902800的博客
02-13 995
数据分组 使用分组可以将数据分为多个逻辑组，并对每个组进行聚集计算。 创建分组 分组是使用select语句的group by子句建立的。group by 可以认为是把一张表中的数据，按照分组字段的值，划分成了多个不同的子表 上面语句的含义：对表tb_order按order_status进行分组，返回不同的order_status，以及他们的数量。结果如下。 *group by子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致的进行数据分组。 *如果在group by子句中嵌套了分组，数据将..
mysql 两个分组条件_数据库关于group by 两个或以上条件的分析
weixin_39960710的博客
01-19 6816
首先group by 的简单说明:group by 一般和聚合函数一起使用才有意义,比如 count sum avg等,使用group by的两个要素:(1) 出现在select后面的字段 要么是聚合函数中的,要么是group by 中的.(2) 要筛选结果 可以先使用where 再用group by 或者先用group by 再用having下面看下 group by多个条件的分析:在SQL查询...
SQL Server数据分组
weixin_50160957的博客
04-28 1797
SQL Server数据分组 一、SQL Server Group By语句。 Group By语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。它的作用是通过一定的规则将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。 以下是 GROUP BY 子句的语法： SELECT select_list FROM table_name GROUP BY column_name1, column_name2 ,...; 在此查询语法中，
PLS_INTEGER、BINARY_INTEGER和SIMPLE_INTEGER区别
FaN()
08-07 3391
PLS_INTEGER PLS_INTEGER数据类型保存范围-2147483648到2147483647的有符整数。这种类型的值是通过底层硬件平台的原生整数格式来表示的。 设计PLS_INTEGER数据类型是为了运算速度。在oracle数据库10g以前，PLS_INTEGER是唯一的一个使用原生机器算法的整数类型。其他的所有数值烈性的数据类型都和NUMBER数
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值