Python社区  »  DATABASE

2020-11-09-Mysql(练习题第一套)

隐约喜欢萌萌哒 • 2 周前 • 25 次点击  

遗留问题 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'
image.png
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
Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/99151
 
25 次点击  
分享到微博