



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 都是存储过程的语法

关注 易学在线 公众号
每日更新大量优质技术文档
第一时间获知最新上架课程
与众多大数据猿分享与交流
DOIT.EDU 资料来自网络收集整理,如有侵权请告知