最新面试题集锦（1）-紫光数智电力SQL分析

1.1

select
 DEPT_ID,
   year,
   month,
   FDL (
select
   DEPT_ID,
   year,
   month,
   FDL,
   row_number() over(partition by DEPT_ID, year, month order by FDL desc) as rows
   from (
     select
     DEPT_ID,
     year(TJSJ_DATR) as year,
     month(TJSJ_DATR) as month,
     FDL
     from
     FACT_DAY_SC
   )t
 )where rows = 1;

1.2

select
 DEPT_ID
 TJSJ_DATR
 if(leads>FDL,leads,FDL) as "历史最高发电量"
 (
   select
   DEPT_ID,
   TJSJ_DATR,
   FDL
   lead(FDL,1,null) over(partition by DEPT_ID order by FDL) as leads
   from
   (
     select
     DEPT_ID,
     TJSJ_DATR,
     FDL
     from
     FACT_DAY_SC
     where DEPT_ID = 1000000
     order by TJSJ_DATR 
   )t
 )t1

1.3

select
 DEPT_ID,
 TJSJ_DATR,
 FDL
 from
 (
   select
   DEPT_ID,
   TJSJ_DATR,
   FDL
   lead(FDL,1,null) over(partition by DEPT_ID order by FDL) as leads
   from
   (
     select
     DEPT_ID,
     TJSJ_DATR,
     FDL
     from
     FACT_DAY_SC
     where DEPT_ID = 1000000
     order by TJSJ_DATR 
   )t
 )t1 where leads>FDL;

1.4

 select
 dept_id,
 tjsj_date,
 fdl,
 from  fact_day_sc
 where tjsj_date='2011-8-1'
 order by fdl desc
 limit 3

1.5

 select date_diff(2020-12-31,2020-01-01)+1;

1.6

with tmp as (
 select
 dept_id,
 tjsj_date,
 fdl
 from fact_day_sc
 where dept_id='10000000' and month(tjsj_date)=8
 )
select 
 count(if a.fdl>b.avgfdl,1,null) as days
 from
 (select fdl from tmp ) a
 join
 (select  avg(fdl) as avgfdl from tmp ) b

1.7

存储过程调用    
 begin
 xproc(参数)
 end

函数调用
 select   函数(参数)  … from ….

1.8 1.9 1.10 都是存储过程的语法

关注 易学在线 公众号

每日更新大量优质技术文档
第一时间获知最新上架课程
与众多大数据猿分享与交流