Suzhou Urban Brain SQL statement--Calculate the travel share rate of a bus in an area

Last year wrote a relatively long SQL, although not how to write now. On the blog to commemorate a bit.

Calculate the share rate of bus driving, the logic is as follows

Morning 6 to 9 o'clock morning peak, night 16:30-19:30 late peak, the rest of the time flat peak.

According to the number of passengers travelling on the Od five-minute bus (the number is calculated in the previous table), according to the peak type, whether the weekday, the number of bus travel in an area. The number of buses in an area divided by the total number is the share of public transport.

drop table if exists BUS_QY_FDL_JC;
CREATE TABLE IF not EXISTS BUS_QY_FDL_JC (
RQ STRING,
LX STRING,
SJD STRING,
Qyid STRING,
QYLX STRING,
FDL DOUBLE
);
Insert Overwrite table BUS_QY_FDL_JC
Select Rq,lx,sjd,bh as QYID,QYLX,FDL from (


Select A.rq,a.lx,a.sjd,a.bh,a.qylx,round (b.jcrs/a.zrs,4) *100 as FDL from (

Select Rq,lx,sjd,bh,qylx,round (avg (RS), 3) as ZRS from (
Select substr (rq,1,6) as Rq,lx,sjd,bh,qylx,sum (RS) as RS from (
Select Rq,size as Qylx,bh,rs,
Case when Weekday (To_date (Concat (RQ,SJD, "xx"), "Yyyymmddhhmiss")) in (5,6) and then "nonworking days"
Else "Working days"
End as LX,
Case Time (substr (sjd,1,2) >=6 and substr (sjd,1,2) <9) then "early Peak"
When (sjd>=1630 and sjd<1930) then "late Peak"
Else "Ping Feng"
End as SJD
From Csdnsz_mda_prod.qy_od_5min) a group by RQ,LX,SJD,BH,QYLX) A2 GROUP by RQ,LX,SJD,BH,QYLX
) A Join
(
Select Rq,lx,sjd,bh,qylx,round (avg (RS), 3) as JCRs from (
Select substr (rq,1,6) as Rq,lx,sjd,bh,qylx,sum (RS) as RS from (
Select Rq,size as Qylx,bh,rs,
Case when Weekday (To_date (Concat (RQ,SJD, "xx"), "Yyyymmddhhmiss")) in (5,6) and then "nonworking days"
Else "Working days"
End as LX,
Case Time (substr (sjd,1,2) >=6 and substr (sjd,1,2) <9) then "early Peak"
When (sjd>=1630 and sjd<1930) then "late Peak"
Else "Ping Feng"
End as SJD
From Csdnsz_mda_prod.qy_cll_5min) a group by RQ,LX,SJD,BH,QYLX) A1 GROUP by RQ,LX,SJD,BH,QYLX
) b
On A.rq=b.rq and A.LX=B.LX and A.SJD =B.SJD and A.BH=B.BH and A.QYLX=B.QYLX


UNION ALL
Select A.rq,a.lx,a.sjd,a.bh,a.qylx,round (b.jcrs/a.zrs,4) *100 as FDL from (

Select Rq,lx,sjd,bh,qylx,round (avg (RS), 3) as ZRS from (
Select substr (rq,1,6) as Rq,lx,sjd,bh,qylx,sum (RS) as RS from (
Select Rq,size as Qylx,bh,rs,
Case when Weekday (To_date (Concat (RQ,SJD, "xx"), "Yyyymmddhhmiss")) in (5,6) and then "nonworking days"
Else "Working days"
End as LX,
"All day" as SJD
From Csdnsz_mda_prod.qy_od_5min) a group by RQ,LX,SJD,BH,QYLX) C GROUP by RQ,LX,SJD,BH,QYLX) a join
(

Select Rq,lx,sjd,bh,qylx,round (avg (RS), 3) as JCRs from (
Select substr (rq,1,6) as Rq,lx,sjd,bh,qylx,sum (RS) as RS from (
Select Rq,size as Qylx,bh,rs,
Case when Weekday (To_date (Concat (RQ,SJD, "xx"), "Yyyymmddhhmiss")) in (5,6) and then "nonworking days"
Else "Working days"
End as LX,
"All day" as SJD
From Csdnsz_mda_prod.qy_cll_5min) a group by RQ,LX,SJD,BH,QYLX) A3 GROUP by RQ,LX,SJD,BH,QYLX


) b
On A.rq=b.rq and A.LX=B.LX and A.SJD =B.SJD and a.bh=b.bh and A.QYLX=B.QYLX) t;

Suzhou Urban Brain SQL statement--Calculate the travel share rate of a bus in an area