SQL分析——常见问题十二

一、上下行空值替换

CREATE TABLE F0726 (
	LDate DATE NOT NULL, 
		Value1 INT NULL, 
		Value2 INT NULL
);

INSERT INTO F0726 VALUES('2020-11-25', 500 ,200);
INSERT INTO F0726 VALUES('2020-11-24', Null, 200);
INSERT INTO F0726 VALUES('2020-11-23', Null, 250);
INSERT INTO F0726 VALUES('2020-11-22', 300 ,Null);
INSERT INTO F0726 VALUES('2020-11-21', 200 ,320);

-- 要求：如果某列的字段在该日期为空值，查询结果显示为之前最接近日期的非空值

SELECT 
	A.LDate,
	(SELECT Value1 
	FROM F0726 B
	WHERE B.LDate<=A.LDate
	AND B.Value1 IS NOT NULL
	ORDER BY A.LDate
	LIMIT 1
	) Value1,
	(SELECT Value2 
	FROM F0726 B
	WHERE B.LDate<=A.LDate
	AND B.Value2 IS NOT NULL
	ORDER BY A.LDate
	LIMIT 1
) Value2
FROM F0726 A

二、连续登录问题

CREATE TABLE F0727 (
	UID VARCHAR(10),
	LOADTIME DATE 
);

INSERT INTO F0727 VALUES ('201', '2017/01/28');
INSERT INTO F0727 VALUES ('201', '2017/01/29');
INSERT INTO F0727 VALUES ('202', '2017/01/29');
INSERT INTO F0727 VALUES ('202', '2017/01/30');
INSERT INTO F0727 VALUES ('203', '2017/01/30');
INSERT INTO F0727 VALUES ('201', '2017/01/31');
INSERT INTO F0727 VALUES ('202', '2017/01/31');
INSERT INTO F0727 VALUES ('201', '2017/02/01');
INSERT INTO F0727 VALUES ('202', '2017/02/01');
INSERT INTO F0727 VALUES ('201', '2017/02/02');
INSERT INTO F0727 VALUES ('203', '2017/02/02');
INSERT INTO F0727 VALUES ('203', '2017/02/03');

-- 要求：查询各个用户最长的连续登录天数

select uid, max(cnt) cnt 
from (
	select uid, grp_no, count(*) cnt 
	from (
		select uid, loadtime,
			(datediff(loadtime, '1970-01-01') - row_number() over(partition by uid order by uid, loadtime)) grp_no
		from F0727
	) a 
	group by uid, grp_no 
) a 
group by uid 

三、存储过程创建日历 表

-- 使用SQL创建一张日历表，要求有年、月、日、星期及具体日期

CREATE TABLE CALENDAR_INFO
 (
		 COUNTRY VARCHAR(5),-- '国家'
		 CDR_CODE VARCHAR(5) ,-- '日历代码'
		 DATE_NAME VARCHAR(20) , -- '日期名称'
		 YEAR CHAR(4), -- '年'
		 MONTH CHAR(2), -- '月'
		 WEEK CHAR(6), -- '星期'
		 DAY CHAR(2), -- '日'
		 CDR_DATE CHAR(10), -- '日历日期'
		 CREATOR VARCHAR(32), -- '创建人'
		 CREATE_DATE DATETIME , -- '创建日期'
		 CREATE_INST VARCHAR(20), -- '创建机构'
		 DEL_FLAG CHAR(1) DEFAULT 0 , -- '删除标志' 0:不删除，1:已删除
		 TS DATETIME DEFAULT GETDATE() -- '时间戳'
 );


CREATE PROC PROC_CALENDAR(@YEAR INT )
--定义一个名称为PROC_CALENDAR，且带输入参数@YEAR的存储过程
AS
BEGIN
--AS BEGIN是语法要求
--定义4个不同类型的变量@i，@START_DATE,@END_DATE,@DATE_COUNT
DECLARE @i INT;
DECLARE @START_DATE VARCHAR(20);
DECLARE @END_DATE VARCHAR(20);
DECLARE @DATE_COUNT INT;
  SET @i=0;
--定义一年的开始日期，用CONCAT函数将年份和月份日期拼接起来
  SET @START_DATE= CONCAT(@YEAR, '-01-01');
--定义一年的结束日期,就是取下一年的1月1日
  SET @END_DATE = CONCAT(@YEAR+1,'-01-01');
--如果表已经存在要新建的日历，则先删除，防止重复写数据
  DELETE FROM CALENDAR_INFO WHERE YEAR = @YEAR;
--计算开始到结束日期一共有多少天，DATEDIFF函数计算日期的间隔天数
  SET @DATE_COUNT = DATEDIFF(DAY,@START_DATE,@END_DATE);

--建立循环，条件是@i小于一年的天数
  WHILE @i < @DATE_COUNT
  BEGIN 
--开始循环插入数据
      INSERT INTO CALENDAR_INFO (COUNTRY,CDR_CODE,DATE_NAME,
YEAR,MONTH,WEEK,DAY,CDR_DATE,CREATOR,CREATE_DATE,CREATE_INST)
       SELECT
       'CN',
       'CN01',
      --CONVERT函数将@START_DATE转换成指定的格式YYYY-MM-DD
       CONVERT(CHAR(10),@START_DATE,111) DATE_NAME,
      --DATENAME获取@START_DATE的年份并用COVERT转换为字符格式
       CONVERT(CHAR(4),DATENAME(YEAR, @START_DATE)) YEAR,
      --DATENAME获取@START_DATE的月份并用COVERT转换为字符格式
       CONVERT(CHAR(2),DATENAME(MONTH, @START_DATE)) MONTH,
      --DATENAME获取@START_DATE的星期并用COVERT转换为字符格式
       CONVERT(CHAR(6),DATENAME(WEEKDAY, @START_DATE)) WEEK,
      --DATENAME获取@START_DATE的日期并用COVERT转换为字符格式
       CONVERT(CHAR(2),DATENAME(DAY, @START_DATE)) DAY,
       CONVERT(CHAR(10),@START_DATE,120) CDR_DATE,
       'SQL数据库开发',
       GETDATE() CREATE_DATE,
       'SQL数据库开发'
     
      --插入完成一条记录后，对@i进行自加1处理，开始插入下一条
      SET @i=@i+1;
      --更新@START_DATE的值，在原来的基础上使用DATEADD函数增加一天
      SET @START_DATE=CONVERT(CHAR(10),DATEADD(DAY, 1, @START_DATE),120);
  END 
END;

--调用存储过程
EXEC PROC_CALENDAR 2023;

--查询数据核实准确性
SELECT * FROM CALENDAR_INFO;

四、四种去重方法

create table T_orders ( 
	customer_id int, 
	order_id int, 
	orderdate date 
);  

insert into T_orders values (1,111,'2023-01-21'); 
insert into T_orders values (1,201,'2023-01-22'); 
insert into T_orders values (1,121,'2023-01-23'); 
insert into T_orders values (2,109,'2023-01-22'); 
insert into T_orders values (2,103,'2023-01-23'); 
insert into T_orders values (3,209,'2023-01-22'); 
insert into T_orders values (4,211,'2023-01-23'); 
insert into T_orders values (5,232,'2023-01-24');  

select * from T_orders;  

-- Q:统计有多少客户下单  

-- distinct 
select count(distinct customer_id) cnt 
from T_orders;  

-- group by  
select count(t.customer_id) cnt 
from ( 
	select customer_id 
	from T_orders 
	group by customer_id 
) t  

-- row_number() 
select sum(case when rn=1 then 1 else 0 end) cnt 
from ( 
	select 
		customer_id, 
		row_number() over (partition by customer_id order by orderdate) rn 
	from T_orders 
) t;  

-- union 
select count(customer_id) cnt 
from ( 
	select customer_id 
	from T_orders 
	union 
	select customer_id 
	from T_orders 
) t

五、三种分页方法(sql server)

CREATE TABLE TestTable ( 
	ID int 
);  

insert into TestTable values (1); 
insert into TestTable values (3); 
insert into TestTable values (4); 
insert into TestTable values (5); 
insert into TestTable values (7); 
insert into TestTable values (9); 
insert into TestTable values (11); 
insert into TestTable values (12); 
insert into TestTable values (13); 
insert into TestTable values (15); 
insert into TestTable values (16); 
insert into TestTable values (17); 
insert into TestTable values (19); 
insert into TestTable values (20); 
insert into TestTable values (24); 
insert into TestTable values (25); 
insert into TestTable values (22); 
insert into TestTable values (21); 
insert into TestTable values (20);   

-- Q:按ID的顺序获取第11-15条记录  

-- 方案一：利用NOT IN和SELECT TOP分页语句形式  

SELECT TOP 5 * 
FROM TestTable 
WHERE ID NOT IN (
	SELECT TOP 10 ID 
	FROM TestTable 
	ORDER BY ID 
) ORDER BY ID    

-- 方案二：利用ID大于多少和SELECT TOP分页语句形式  
SELECT TOP 5 * 
FROM TestTable 
WHERE ID > ( 
	SELECT MAX(id) 
	FROM  (
		SELECT TOP 10 id 
		FROM  TestTable 
		ORDER BY id
	) AS T
) ORDER BY ID   

-- 方案三：利用SQL Server中的特性ROW_NUMBER进行分页   
SELECT * FROM ( 
	SELECT ROW_NUMBER() OVER(ORDER BY ID) AS rn,* 
	FROM TestTable 
) AS mytable  
where rn between 11 and 15  

-- 扩展：返回随机5条记录 
SELECT TOP 5 * 
FROM TestTable 
ORDER BY NEWID();

六、三种累加方法

create table Add_Num ( 
	OrderDate date, 
	Amount int 
);  

insert into Add_Num values ('2021-09-01',100); 
insert into Add_Num values ('2021-09-02',200); 
insert into Add_Num values ('2021-09-05',100); 
insert into Add_Num values ('2021-09-08',300); 
insert into Add_Num values ('2021-09-12',100); 
insert into Add_Num values ('2021-09-14',200);  

-- 要求:将表里的Amount数据按日期顺序进行累加。

-- 方法一：开窗函数 

SELECT  
	OrderDate,
	Amount,
	SUM(Amount) OVER(ORDER BY OrderDate) Amount  
FROM Add_Num   

-- 方法二：子查询 
SELECT 
	A.OrderDate, 
	( SELECT SUM(B.AMOUNT) 
		FROM ADD_NUM B 
		WHERE B.OrderDate <= A.OrderDate ) Amount 
FROM ADD_NUM A   

-- 方法三：笛卡尔积 
SELECT A.OrderDate, SUM(B.AMOUNT) Amount 
FROM ADD_NUM A 
CROSS JOIN ADD_NUM B 
WHERE B.OrderDate <= A.OrderDate 
GROUP BY A.OrderDate

七、两种行转列的方法

CREATE TABLE F0805A ( 
	ID INT, 
	机房 VARCHAR(20) 
); 

CREATE TABLE F0805B ( 
	ID INT, 
	机房ID INT, 
	主机名称 VARCHAR(20), 
	主机状态 INT 
);  

INSERT INTO F0805A VALUES (1,'机房A'); 
INSERT INTO F0805A VALUES (2,'机房B'); 
INSERT INTO F0805A VALUES (3,'机房C');  
INSERT INTO F0805B VALUES (1,1,'主机A',1); 
INSERT INTO F0805B VALUES (2,1,'主机B',1); 
INSERT INTO F0805B VALUES (3,1,'主机C',2); 
INSERT INTO F0805B VALUES (4,2,'主机D',0); 
INSERT INTO F0805B VALUES (5,2,'主机E',1);  

-- Q:希望可以看到每个机房的主机状态分别有多少个？   

-- 方法一：case when 
SELECT  
	A.机房, 
	SUM(CASE WHEN B.主机状态=0 THEN 1 ELSE 0 END) '0', 
	SUM(CASE WHEN B.主机状态=1 THEN 1 ELSE 0 END) '1', 
	SUM(CASE WHEN B.主机状态=2 THEN 1 ELSE 0 END) '2' 
FROM F0805A A 
LEFT JOIN F0805B B 
ON A.ID=B.机房ID 
GROUP BY A.机房   

-- 方法二：PIVOT 
SELECT * 
FROM ( 
	SELECT A.机房,B.主机名称,B.主机状态 
	FROM F0805A A 
	LEFT JOIN F0805B B 
	ON A.ID=B.机房ID 
) X PIVOT ( COUNT(主机名称) FOR 主机状态 IN ([0],[1],[2]) ) XX

八、如何每隔15分钟分组

CREATE TABLE F0810 ( 
	ID INT, 
	TIMES VARCHAR(50) 
);  

INSERT INTO F0810 VALUES (1,'2019-12-25 11:01'); 
INSERT INTO F0810 VALUES (2,'2019-12-25 11:03'); 
INSERT INTO F0810 VALUES (3,'2019-12-25 11:05'); 
INSERT INTO F0810 VALUES (4,'2019-12-25 11:09'); 
INSERT INTO F0810 VALUES (5,'2019-12-25 11:17'); 
INSERT INTO F0810 VALUES (6,'2019-12-25 11:19'); 
INSERT INTO F0810 VALUES (7,'2019-12-25 11:29'); 
INSERT INTO F0810 VALUES (8,'2019-12-25 11:37');  

-- Q:查询出每隔15分钟的记录数  

SELECT 
	FROM_UNIXTIME(t.unit_time * 900, 
	'%Y-%m-%d %H:%i:%s') Times,  
	t.cnt 
FROM ( 
	SELECT 
		CEIL(UNIX_TIMESTAMP(Times) / 900) as unit_time,  
		count(ID) cnt 
	FROM F0810  
	GROUP BY unit_time 
) t;