SQL案例记录：学习和工作中遇到的 SQL 常见思想

目录

一、实现累加

方法一 ：开窗

方法二：自连接

二、删除有重复的数据

三、连续问题

方法一：位移函数:

方法二：row_num()构造新列

四、基于连接的两表比较

五、分区间计数的思想

（一）行列转化

（二）多条件判断，分区间统计汇总

六、当月第一天

SqlServer中

Oracle中

七、找出字符出现的次数

---

        记录一下一些处理问题的 sql 案例，持续更新，有些可能很简单，不过编程重要的是处理问题的思路

一、实现累加

        建表语句：

Create table test1 (person_id int, x int);
Truncate table test1;
insert into test1 (person_id, x) values ('1', '10');
insert into test1 (person_id, x) values ('2', '20');
insert into test1 (person_id, x) values ('3', '30');
insert into test1 (person_id, x) values ('4', '40');
insert into test1 (person_id, x) values ('5', '50');

select * from test1

        是一个如下的表： 

方法一 ：开窗

select test1.*,sum(x) over(order by person_id ) from test1

        倒序排列：

select test1.*,sum(x) over(order by person_id desc) from test1

        用这种方式可以实现分组累计求和

方法二：自连接

表a.                            表b.

 

        在如 MySQL 不支持开窗函数时，可以用第二种方法——自连接，用表a控制求和的位置，用表b的x求和（只用一个表就统一都是截至某行的sum值了，必须要自连接）

select a.person_id,(
SELECT SUM (b.x) FROM test1 b WHERE b.person_id <= a.person_id )from test1 a

二、删除有重复的数据

        建表：

Create table If Not Exists Person (Id int, Email varchar(255))
Truncate table Person
insert into Person (id, email) values ('1', 'john@example.com')
insert into Person (id, email) values ('2', 'bob@example.com')
insert into Person (id, email) values ('3', 'john@example.com')

        ID为唯一标识 

大致思路： 

        使用 max() 或 min() 函数对其唯一标识处理，仅保留一条数据，若无唯一标识 ORACLE 中可以使用 ROWID，筛选出需要唯一保留的数据，其他删除

DELETE FROM PERSON  WHERE ID NOT IN 
    (SELECT MIN(ID) FROM PERSON GROUP BY EMAIL )

三、连续问题

        建表：

Create table Logs (id int, num int);
Truncate table Logs;
insert into Logs (id, num) values ('1', '1');
insert into Logs (id, num) values ('2', '1');
insert into Logs (id, num) values ('3', '1');
insert into Logs (id, num) values ('4', '2');
insert into Logs (id, num) values ('5', '1');
insert into Logs (id, num) values ('6', '2');
insert into Logs (id, num) values ('7', '2');

        ID为唯一标识

方法一：位移函数:

        lead()/lag() over()，若至少重复三次，则其按ID排序前一位和后一位均为相同数字，如下：

SELECT LOGS.*,
    lag(NUM,1) OVER(ORDER BY ID) as last,
    lead(NUM,1) OVER(ORDER BY ID) as next 
FROM LOGS

         再筛选出前后一位均和 num 相等的数据，进行去重，即可得到至少重复三次的数字

SELECT distinct num ConsecutiveNums  FROM(
  SELECT LOGS.*,
    lag(NUM,1) OVER(ORDER BY ID) last,
    lead(NUM,1) OVER(ORDER BY ID) next 
  FROM LOGS ) 
where num=last and num=next 

         位移函数还可以用来算移动平均哦

方法二：row_num()构造新列

        这里的假设需要ID是连续的

select 
    Id,Num,row_number() over(partition by Num order by id) as RK,
    id-row_number() over(partition by Num order by id)  des
from Logs

        先看为何这样构造，注意到，使用开窗函数对 num 分组后，由于 row_num() 从 1 开始连续的，因此若 num 也是连续出现的，那每组内 ID - RK 之间的值将一直相等，接下来在这个表里只需要筛选出差值出现次数大于等于3次即可

SELECT num,des,count(1) FROM (
select 
    Id,Num,row_number() over(partition by Num order by id) as RK,
    id-row_number() over(partition by Num order by id)  des
from Logs) group by num,des

        加上 count()>=3 的条件

        注意：若ID是从0开始的序列会有误差，此时将ID+1再作差即可~~

SELECT num ConsecutiveNums FROM (
  SELECT 
      Id,Num,row_number() over(partition by Num order by id) as RK,
      id-row_number() over(partition by Num order by id)  des
  FROM Logs) 
GROUP BY num,des HAVING count(1)>=3

        这是解决连续问题比较通用的思想，如顾客连续购买日期、连续登录等等问题均可用该方法解决

四、基于连接的两表比较

        两表做连接是 SQL 中最简单的处理之一，建表：

Create table A (X int);
Create table B (Y int);
Truncate table A;
Truncate table B;

insert into A (X) values (1);
insert into A (X) values (2);
insert into A (X) values (3);
insert into A (X) values (4);
insert into A (X) values (5);


insert into B (Y) values (1);
insert into B (Y) values (3);
insert into B (Y) values (5);
insert into B (Y) values (7);
insert into B (Y) values (9);

SELECT * FROM A;
SELECT * FROM B

         左外连接

SELECT * FROM A left join b on a.x=b.y

        右外连接

SELECT * FROM A right join b on a.x=b.y

        全外连接

SELECT * FROM A FULL JOIN b on A.X=B.Y

        实际上，简单外连接可以检查仅其中一表有而另外一表没有的数据（表中含有空值的），如有无某个行为的用户，某商品有无购买记录，或模拟销售退款行为等等

五、分区间计数的思想

（一）行列转化

使用 SUM(CASE WHEN)或COUNT(CASE WHEN)，先判断，后计数

        假设现在有一表 A，id为主键，唯一，想看 X 落在哪个区间范围内

ID	WAREHOUSE	X
1	1001	200
2	1001	800
3	1002	600
4	1002	600
5	1003	3000

        步骤一：使用 0 和 1 判断是否处于区间内：

SELECT ID,WAREHOUSE,
CASE WHEN X>0 AND X<=500 THEN 1 ELSE O END [0,500],
CASE WHEN X>500 AND X<=1000 THEN 1 ELSE O END (500,1000],
CASE WHEN X>1000 AND X<=1500 THEN 1 ELSE O END (1000,1500]
FROM A

ID	WAREHOUSE	[0,500]	(500,1000]	(1000,1500]
1	1001	1	0	0
2	1001	0	1	0
3	1002	0	1	0
4	1002	0	1	0
5	1003	0	0	1

        步骤二：判断完了进行聚合统计ID，我们看看每个 WAREHOUSE 在每个区间内有多少人

SELECT WAREHOUSE,
SUM(CASE WHEN X>0 AND X<=500 THEN 1 ELSE O END) [0,500],
SUM(CASE WHEN X>500 AND X<=1000 THEN 1 ELSE O END) (500,1000],
SUM(CASE WHEN X>1000 AND X<=1500 THEN 1 ELSE O END) (1000,1500]
FROM A GROUP BY WAREHOUSE

WAREHOUSE	[0,500]	(500,1000]	(1000,1500]
1001	1	1	0
1002	0	2	0
1003	0	0	1

         该方法可用于统计落在不同区间样本的数量

（二）多条件判断，分区间统计汇总

        直接使用oracle中的emp表

select empno,sal from emp

        对薪酬判断，定级

select empno,sal,
case when sal>='500' and sal<'1500' then '一级'
  when sal>='1500' and sal<'2500' then '二级'
    else '管理级' end as "职位等级" from emp;

        结果：

        分区间汇总

select "职位等级",count(distinct empno) "人数" from 
 (select empno,sal,
  case when sal>='500' and sal<'1500' then '一级'
    when sal>='1500' and sal<'2500' then '二级'
      else '管理级' end as "职位等级" from emp ) g group by "职位等级";

六、当月第一天

        在进行自动化报表时有这样一个需求，即将日期时间限制在当月的一号至昨天

SqlServer中

GETDATE()、CURRENT_TIMESTAMP：

        SqlServer 中当前时间的函数

SELECT GETDATE()

DATEFROMPARTS(year,month,day)

        在 SQL Server 中用来从给定的年、月、日的值中返回一个日期，指定日期为1号即可

SELECT DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1);

Oracle中

        sysdate：Oracle 中的系统时间（用systimestamp可显示时间戳）

SELECT TRUNC( sysdate, 'mm') FROM DUAL;

        Trunc(date,format)：根据日期格式对源日期进行截取，格式：

• sysdate 当前系统时间,      --yyyy/mm/dd hh24:mi:ss
• trunc(sysdate) 当前日期,          --yyyy/mm/dd
• trunc(sysdate,'yyyy') 当年第一天, --yyyy/mm/dd
• trunc(sysdate,'mm') 当月第一天,   --yyyy/mm/dd
• trunc(sysdate,'dd') 当前日期（舍去时分秒）,     --yyyy/mm/dd
• trunc(sysdate,'hh24') 当前小时,   --yyyy/mm/dd hh24:mi:ss
• trunc(sysdate,'mi') 当前分钟,     --yyyy/mm/dd hh24:mi:ss
• trunc(sysdate,'year') 当年第一天, --yyyy/mm/dd
• trunc(sysdate,'month') 当月第一天,--yyyy/mm/dd
• trunc(sysdate,'day') 当前上周日,  --yyyy/mm/dd
• trunc(sysdate,'iw') 当前星期一,   --yyyy/mm/dd
• trunc(sysdate,'q') 当季度的第一天 --yyyy/mm/dd;

        Round() 同理，但 Round 是对时间进行四舍五入

七、找出字符出现的次数

        比如找出有几个空格，思路很简单，这串字符有空格的长度减去无空格的长度即可

select length(content_text)-length(replace(content_text,' ',''))
from user_content;

        将空格去掉，然后长度相减，即可，测试后暂时只确保能用于单个字符