遗留问题 select语句中的别名问题 group by 中的别名问题
# sql
-表 FACT_DAY_SC
-字段 DEPT_ID varchar(20) 电厂
TJSJ_DATE DATE 日期
FSL number 日发电量
create table FACT_DAY_SC(
DEPT_ID varchar(20),
TJSJ_DATE DATE,
FSL LONG
);
insert into FACT_DAY_SC values ('10001','2020-12-01',113);
insert into FACT_DAY_SC values ('10001','2019-02-01',1233);
insert into FACT_DAY_SC values ('10001','2020-03-01',1453);
insert into FACT_DAY_SC values ('10002','2020-04-01',113);
insert into FACT_DAY_SC values ('10001','2020-05-01',563);
insert into FACT_DAY_SC values ('10002','2020-06-01',4);
insert into FACT_DAY_SC values ('10001','2020-07-01',567);
insert into FACT_DAY_SC values ('10002','2020-08-01',4567);
insert into FACT_DAY_SC values ('10001','2020-09-01',1232);
insert into FACT_DAY_SC values ('10001','2020-10-01',456);
insert into FACT_DAY_SC values ('10001','2020-07-23',678);
insert into FACT_DAY_SC values ('10003','2020-09-2',1247);
insert into FACT_DAY_SC values ('10001','2020-01-2',579);
insert into FACT_DAY_SC values ('10003','2019-01-3',1257);
insert into FACT_DAY_SC values ('10001','2019-01-11',1269);
insert into FACT_DAY_SC values ('10003','2020-01-4',4793);
insert into FACT_DAY_SC values ('10003','2019-01-5',47234);
insert into FACT_DAY_SC values ('10003','2020-04-04',4793);
insert into FACT_DAY_SC values ('10003','2018-01-19',4456783);
insert into FACT_DAY_SC values ('10003','2020-12-01',47123);
1.写出SQL计算出每个单位,每个月的最高发电量
//问题:分析是否按照年来分类
// select substr(cast('2020-01-09' as Date ) , 1,7 ); substr() 函数能截取日期
//substr(string string, int a, int b);函数的理解
//string 需要截取的字符串
a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）
b 要截取的字符串的长度
select
DEPT_ID,
substr(DEPT_ID,,6,2) as result ,
max(FSL)
from FACT_DAY_SC
group by DEPT_ID , substr(TJSJ_DATE,6,2) ;
2.求取10001电厂,每日的最高发电量(显示到目前为只每日最高发电量)
//用聚合函数还是使用窗口函数
//好像不行???? 发现问题??? 我用的是long比较的是字典顺序?????
select
*,
max(FSL) over(ORDER BY TJSJ_DATE) as num
from
FACT_DAY_SC
where DEPT_ID ='10001';
//这个是比较的是前后日期最大的日电量
select
DEPT_ID,
TJSJ_DATE,
if(result > FSL , result , FSL) as '日最大用量'
from
(
select
DEPT_ID,
TJSJ_DATE,
FSL,
lead(FSL,1) over( order by FSL ) as result
from
(
select
DEPT_ID,
TJSJ_DATE,
FSL
from FACT_DAY_SC
where DEPT_ID = '10001'
order by TJSJ_DATE
) t1
) t2
3.基于2查询每次打破日最大用电量的日期和发电量
select
TJSJ_DATE,
FSL
from
(
select
DEPT_ID,
TJSJ_DATE,
FSL,
max(FSL) over(order by FSL ) as max_result
from
FACT_DAY_SC
where DEPT_ID = '10001'
) t1
where max_result = t1.FSL
4.查询日期2019-01-11日发电量topN 的电厂
//知识点 序列函数考虑并序
select
DEPT_ID
dense_rank() over(order by FSL )
from FACT_DAY_SC
where TJSJ_DATE = '2019-01-11'
5.计算本年有多少天的SQL?
//知识点回顾SQL中的日期函数 注意使用 cast(' 2020-09-09' as DATE)
datediff(date ,date)+1 比较两个日期之间相差多少天
date_add(date,number) 日期加number
date_sub(date ,number) 日期减number
select datediff('2020-12-31','2020-01-01') +1
6.计算 2019-01月 及 电厂10001 中天数超过平均发电量的天数
//知识点 使用切割函数 substr() 分组
//发现不允许这样用使用子查询
select
count(FSL > avg(FSL))
from
FACT_DAY_SC
where DEPT_ID = '1001' and substr(TJSJ_DATE ,1,7) = '2020-09'
// substr和子查询
select
count(FSL >(select
avg(FSL)
from
FACT_DAY_SC
where DEPT_ID = '10001'
group by DEPT_ID) )
from
FACT_DAY_SC
where DEPT_ID = '10001' and substr(TJSJ_DATE ,1,7) = '2020-09'
8.指标分析
create table TEST(
DEP_CODE varchar(10),
ITEM_CODE varchar(10),
ITEM_VALUE int
);
insert into TEST values ('单位一','A',100);
insert into TEST values ('单位二','A',200);
insert into TEST values ('单位一','B',300);
insert into TEST values ('单位二','B',300);
insert into TEST values ('单位一','C',350);
insert into TEST values ('单位二','C',300);
select
DEP_CODE,
ITEM_CODE,
ITEM_VALUE
from
TEST
UNION ALL
select
DEP_CODE,
'D' as ITEM_CODE,
sum(if(ITEM_CODE = 'C',-ITEM_VALUE,ITEM_VALUE)) as ITEM_VALUE
from
TEST
group by DEP_CODE
