智慧医疗

门诊就诊人次
select a.year_id,count(a.count_id) as ORDER_ITEM_COST from (select substr(DLASTM,1,4) as year_id,CSFRMC,DJZRQ,count(1) as count_id from lqioc_ioc_ods.TBMZFYMX group by substr(DLASTM,1,4),CSFRMC,DJZRQ) a group by a.year_id having a.year_id=SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4);
住院就诊人次
select a.year_id,count(a.count_id) as ORDER_ITEM_COST  from (select substr(DJZRQ,1,4) as year_id,CXM,DLASTM,count(1) as count_id from lqioc_ioc_ods.TBQYZYFYMX group by substr(DJZRQ,1,4),CXM,DLASTM) a group by a.year_id having a.year_id=SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4);

门诊年龄分布（spark）

select '1-13岁门诊就诊量（人次）' as ORDER_ITEM,
sum(case when n.nl>0 and n.nl<14 then n.count_id else null end)  ORDER_ITEM_NUM
from
(select (SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)-substr(c.csfzh,7,4)) as nl, count(1) as count_id from (
select a.CSFRMC,a.DJZRQ,b.cjtzz,b.csfzh
from lqioc_ioc_ods.TBMZFYMX a
inner JOIN 
lqioc_ioc_ods.JKDAJBXXB  b
on a.CBRID=b.CBRID) c GROUP BY (SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)-substr(c.csfzh,7,4)))n
union all
select '14-19岁门诊就诊量（人次）' as ORDER_ITEM,
sum(case when n.nl>=14 and n.nl<=19 then n.count_id else null end)  ORDER_ITEM_NUM
from
(select (SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)-substr(c.csfzh,7,4)) as nl, count(1) as count_id from (
select a.CSFRMC,a.DJZRQ,b.cjtzz,b.csfzh
from lqioc_ioc_ods.TBMZFYMX a
inner JOIN 
lqioc_ioc_ods.JKDAJBXXB  b
on a.CBRID=b.CBRID) c GROUP BY (SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)-substr(c.csfzh,7,4)))n
union all
select '20-39岁门诊就诊量（人次）' as ORDER_ITEM,
sum(case when n.nl>=20 and n.nl<=39 then n.count_id else null end)  ORDER_ITEM_NUM
from
(select (SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)-substr(c.csfzh,7,4)) as nl, count(1) as count_id from (
select a.CSFRMC,a.DJZRQ,b.cjtzz,b.csfzh
from lqioc_ioc_ods.TBMZFYMX a
inner JOIN 
lqioc_ioc_ods.JKDAJBXXB  b
on a.CBRID=b.CBRID) c GROUP BY (SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)-substr(c.csfzh,7,4)))n
union all
select '40-59岁门诊就诊量（人次）' as ORDER_ITEM,
sum(case when n.nl>=40 and n.nl<=59 then n.count_id else null end) ORDER_ITEM_NUM
from
(select (SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)-substr(c.csfzh,7,4)) as nl, count(1) as count_id from (
select a.CSFRMC,a.DJZRQ,b.cjtzz,b.csfzh
from lqioc_ioc_ods.TBMZFYMX a
inner JOIN 
lqioc_ioc_ods.JKDAJBXXB  b
on a.CBRID=b.CBRID) c GROUP BY (SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)-substr(c.csfzh,7,4)))n
union all
select '60岁以上门诊就诊量（人次）' as ORDER_ITEM,
sum(case when n.nl>=60 then n.count_id else null end) ORDER_ITEM_NUM 
from
(select (SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)-substr(c.csfzh,7,4)) as nl, count(1) as count_id from (
select a.CSFRMC,a.DJZRQ,b.cjtzz,b.csfzh
from lqioc_ioc_ods.TBMZFYMX a
inner JOIN 
lqioc_ioc_ods.JKDAJBXXB  b
on a.CBRID=b.CBRID) c GROUP BY (SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)-substr(c.csfzh,7,4)))n;

住院年龄分布（spark）

select '1-13岁住院就诊量（人次）' as ORDER_ITEM,
sum(case when n.nl>0 and n.nl<14 then n.count_id else null end)  ORDER_ITEM_NUM
from
(select (SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)-substr(c.csfzh,7,4)) as nl, count(1) as count_id from (
select a.CSFRMC,a.DJZRQ,b.cjtzz,b.csfzh
from lqioc_ioc_ods.TBQYZYFYMX a
inner JOIN 
lqioc_ioc_ods.JKDAJBXXB  b
on a.CBRID=b.CBRID) c GROUP BY (SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)-substr(c.csfzh,7,4)))n
union all
select '14-19岁住院就诊量（人次）' as ORDER_ITEM,
sum(case when n.nl>=14 and n.nl<=19 then n.count_id else null end)  ORDER_ITEM_NUM
from
(select (SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)-substr(c.csfzh,7,4)) as nl, count(1) as count_id from (
select a.CSFRMC,a.DJZRQ,b.cjtzz,b.csfzh
from lqioc_ioc_ods.TBQYZYFYMX a
inner JOIN 
lqioc_ioc_ods.JKDAJBXXB  b
on a.CBRID=b.CBRID) c GROUP BY (SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)-substr(c.csfzh,7,4)))n
union all
select '20-39岁住院就诊量（人次）' as ORDER_ITEM,
sum(case when n.nl>=20 and n.nl<=39 then n.count_id else null end)  ORDER_ITEM_NUM
from
(select (SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)-substr(c.csfzh,7,4)) as nl, count(1) as count_id from (
select a.CSFRMC,a.DJZRQ,b.cjtzz,b.csfzh
from lqioc_ioc_ods.TBQYZYFYMX a
inner JOIN 
lqioc_ioc_ods.JKDAJBXXB  b
on a.CBRID=b.CBRID) c GROUP BY (SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)-substr(c.csfzh,7,4)))n
union all
select '40-59岁住院就诊量（人次）' as ORDER_ITEM,
sum(case when n.nl>=40 and n.nl<=59 then n.count_id else null end) ORDER_ITEM_NUM
from
(select (SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)-substr(c.csfzh,7,4)) as nl, count(1) as count_id from (
select a.CSFRMC,a.DJZRQ,b.cjtzz,b.csfzh
from lqioc_ioc_ods.TBQYZYFYMX a
inner JOIN 
lqioc_ioc_ods.JKDAJBXXB  b
on a.CBRID=b.CBRID) c GROUP BY (SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)-substr(c.csfzh,7,4)))n
union all
select '60岁以上住院就诊量（人次）' as ORDER_ITEM,
sum(case when n.nl>=60 then n.count_id else null end) ORDER_ITEM_NUM 
from
(select (SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)-substr(c.csfzh,7,4)) as nl, count(1) as count_id from (
select a.CSFRMC,a.DJZRQ,b.cjtzz,b.csfzh
from lqioc_ioc_ods.TBQYZYFYMX a
inner JOIN 
lqioc_ioc_ods.JKDAJBXXB  b
on a.CBRID=b.CBRID) c GROUP BY (SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)-substr(c.csfzh,7,4)))n;

近6个月就诊量趋势 门诊（spark）

select C.year_id,C.month_id,sum(C.COUNT_ID) FROM
(select SUBSTR(A.DJZSJ,1,4) AS year_id,SUBSTR(A.DJZSJ,6,2) AS month_id,A.CJGID,B.CJGMC,COUNT(1) AS COUNT_ID from 
(select DJZSJ,CJGID from lqioc_ioc_ods.TBMZJZXX where 
datediff(current_date,from_unixtime(unix_timestamp(DJZSJ,'yyyy-MM-dd HH:mm:ss'),'yyyy-MM-dd'))<=180) A 
LEFT JOIN
(SELECT DISTINCT CJGID,CJGMC FROM lqioc_ioc_ods.TBMZJZXX) B
ON A.CJGID=B.CJGID
GROUP BY SUBSTR(A.DJZSJ,1,4),SUBSTR(A.DJZSJ,6,2),A.CJGID,B.CJGMC) C
group by C.year_id,C.month_id;

近6个月就诊量趋势 住院（spark）
select C.year_id,C.month_id,sum(C.COUNT_ID) FROM
(select SUBSTR(A.DRYSJ,1,4) AS year_id,SUBSTR(A.DRYSJ,6,2) AS month_id,A.CJGID,COUNT(1) AS COUNT_ID from 
(select DRYSJ,CJGID from lqioc_ioc_ods.TBQYZYBR where 
datediff(current_date,from_unixtime(unix_timestamp(DRYSJ,'yyyy-MM-dd HH:mm:ss'),'yyyy-MM-dd'))<=180) A 
LEFT JOIN
(SELECT DISTINCT CJGID FROM lqioc_ioc_ods.TBQYZYBR) B
ON A.CJGID=B.CJGID
GROUP BY SUBSTR(A.DRYSJ,1,4),SUBSTR(A.DRYSJ,6,2),A.CJGID) C
group by C.year_id,C.month_id;


门诊就诊区域(spark)


select substring_index(substring_index(c.cjtzz,'+',4),'+',-1) as xz,count(1) from (
select a.CSFRMC,a.DJZRQ,b.cjtzz,b.csfzh
from lqioc_ioc_ods.TBMZFYMX a
inner JOIN 
lqioc_ioc_ods.JKDAJBXXB  b
on a.CBRID=b.CBRID) c GROUP BY substring_index(substring_index(c.cjtzz,'+',4),'+',-1)
having xz in (
'获鹿镇',
'铜冶镇',
'上庄',
'寺家庄',
'李村镇',
'宜安镇',
'黄壁庄',
'大河镇',
'山尹村',
'石井乡',
'白鹿泉',
'上寨乡',
'开发区');

住院就诊区域（spark）

select substring_index(substring_index(c.cjtzz,'+',4),'+',-1) as xz,count(1) as count_id from (
select a.CSFRMC,a.DJZRQ,b.cjtzz,b.csfzh
from lqioc_ioc_ods.TBQYZYFYMX a
inner JOIN 
lqioc_ioc_ods.JKDAJBXXB  b
on a.CBRID=b.CBRID) c GROUP BY substring_index(substring_index(c.cjtzz,'+',4),'+',-1)
having xz in (
'获鹿镇',
'铜冶镇',
'上庄',
'寺家庄',
'李村镇',
'宜安镇',
'黄壁庄',
'大河镇',
'山尹村',
'石井乡',
'白鹿泉',
'上寨乡',
'开发区');


年度门诊就诊量排名Top5(报错语句)
数据倾斜原始语句：
select SUBSTR(a.DJZSJ,1,4) AS SY,a.CJGID,a.CJGMC,COUNT(1) as COUNT_ID2 from lqioc_ioc_ods.TBMZJZXX a GROUP BY SUBSTR(a.DJZSJ,1,4),a.CJGID,a.CJGMC;
更改之后：
select c.SY,c.CJGID,c.CJGMC,c.COUNT_ID,row_number()over(order by c.COUNT_ID desc) as row_id from 
(select SUBSTR(A.DJZSJ,1,4) AS SY,A.CJGID,B.CJGMC,COUNT(1) AS COUNT_ID from lqioc_ioc_ods.TBMZJZXX  A 
LEFT JOIN
(SELECT DISTINCT CJGID,CJGMC FROM lqioc_ioc_ods.TBMZJZXX) B
ON A.CJGID=B.CJGID
GROUP BY SUBSTR(A.DJZSJ,1,4),A.CJGID,B.CJGMC) c
where c.SY=SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4) and c.CJGMC is not null limit 5;

年度住院就诊量排名Top5（spark）
select c.SY,c.CJGID,c.CJGMC,c.COUNT_ID,row_number()over(order by c.COUNT_ID desc) as row_id from 
(select SUBSTR(A.DRYSJ,1,4) AS SY,A.CJGID,B.CJGMC,COUNT(1) AS COUNT_ID from lqioc_ioc_ods.TBQYZYBR  A 
LEFT JOIN
(SELECT DISTINCT CJGID,CJGMC FROM lqioc_ioc_ods.TBMZJZXX) B
ON A.CJGID=B.CJGID
GROUP BY SUBSTR(A.DRYSJ,1,4),A.CJGID,B.CJGMC) c
where c.SY=SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4) and c.CJGMC is not null limit 5;

年度健康体检量排名Top5（spark）
select c.SY,c.CJGID,c.CJGMC,c.COUNT_ID,row_number()over(order by c.COUNT_ID desc) as row_id from 
(select SUBSTR(A.DJLSJ,1,4) AS SY,A.CJGID,B.CJGMC,COUNT(1) AS COUNT_ID from lqioc_ioc_ods.TBJKTJ A
LEFT JOIN
(SELECT DISTINCT CJGID,CJGMC FROM lqioc_ioc_ods.TBMZJZXX) B
ON A.CJGID=B.CJGID
GROUP BY SUBSTR(A.DJLSJ,1,4),A.CJGID,B.CJGMC) c
where c.SY=SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4) and c.CJGMC is not null limit 5;

门诊就诊疾病排名Top5（spark）
select c.year_id,c.month_id,c.CZDMC,c.COUNT_ID from 
(select a.year_id,a.month_id,a.CZDMC,a.COUNT_ID,row_number()over(partition by a.year_id,a.month_id order by a.COUNT_ID desc) as row_id from 
(select SUBSTR(b.DZDSJ,1,4) as year_id,SUBSTR(b.DZDSJ,6,2) as month_id,b.CZDMC,COUNT(1) as COUNT_ID from lqioc_ioc_ods.TBQYMZZD b group by SUBSTR(b.DZDSJ,1,4),SUBSTR(b.DZDSJ,6,2),b.CZDMC) a)c where c.row_id<6
and c.year_id=SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4) and c.month_id=SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),6,2);

住院就诊疾病排名Top5（spark）
select a.year_id,a.month_id,row_number()over(order by a.COUNT_ID desc) from 
(select SUBSTR(b.DRYSJ,1,4) as year_id,SUBSTR(b.DRYSJ,6,2) as month_id,regexp_replace(b.CZYJBMC,'\n|\t|\r',''),COUNT(1) as COUNT_ID from lqioc_ioc_ods.TBQYZYBR b group by SUBSTR(b.DRYSJ,1,4),SUBSTR(b.DRYSJ,6,2),regexp_replace(b.CZYJBMC,'\n|\t|\r',''))a
where a.year_id=SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4) and
a.month_id=SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),6,2);



年度门诊费用(分类)(spark)
select '自费应收费用（元）' as ORDER_ITEM,
sum(case when b.csfzl='自费' then b.fy else 0 end) as '自费'
from 
(select a.csfzl,a.year_id,sum(cast(a.MYSJE_NUM as bigint)) as fy from 
(select SUBSTR(DLASTM,1,4) as year_id,csfzl,regexp_replace(MYSJE,'[[^0-9.\\\u4e00-\\\u9fa5]]','') as MYSJE_NUM  from lqioc_ioc_ods.TBMZFYMX) a GROUP BY a.csfzl,a.year_id having a.year_id=SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)) b
union all
select '职工医保应收费用（元）' as ORDER_ITEM,
sum(case when b.csfzl in ('本院职工','职工医保','医保') then b.fy else 0 end) as '职工医保'
from 
(select a.csfzl,a.year_id,sum(cast(a.MYSJE_NUM as bigint)) as fy from 
(select SUBSTR(DLASTM,1,4) as year_id,csfzl,regexp_replace(MYSJE,'[[^0-9.\\\u4e00-\\\u9fa5]]','') as MYSJE_NUM  from lqioc_ioc_ods.TBMZFYMX) a GROUP BY a.csfzl,a.year_id having a.year_id=SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)) b
union all
select '城乡居民医保应收费用（元）' as ORDER_ITEM,
sum(case when b.csfzl in ('农村合作医疗','居民医保','城镇居民医保','农合') then b.fy else 0 end) as  '城乡居民医保'
from 
(select a.csfzl,a.year_id,sum(cast(a.MYSJE_NUM as bigint)) as fy from 
(select SUBSTR(DLASTM,1,4) as year_id,csfzl,regexp_replace(MYSJE,'[[^0-9.\\\u4e00-\\\u9fa5]]','') as MYSJE_NUM  from lqioc_ioc_ods.TBMZFYMX) a GROUP BY a.csfzl,a.year_id having a.year_id=SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)) b
union all
select '其他应收费用（元）' as ORDER_ITEM,
sum(case when b.csfzl in ('记账','普通','半费','公费','优惠') then b.fy else 0 end) as  '其他'
from 
(select a.csfzl,a.year_id,sum(cast(a.MYSJE_NUM as bigint)) as fy from 
(select SUBSTR(DLASTM,1,4) as year_id,csfzl,regexp_replace(MYSJE,'[[^0-9.\\\u4e00-\\\u9fa5]]','') as MYSJE_NUM  from lqioc_ioc_ods.TBMZFYMX) a GROUP BY a.csfzl,a.year_id having a.year_id=SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)) b;;



年度住院费用(分类)(spark)
select '自费应收费用（元）' as ORDER_ITEM,
sum(case when b.csfzl='自费' then b.fy else 0 end) as ORDER_ITEM_NUM
from 
(select a.csfzl,a.year_id,sum(cast(a.MYSJE_NUM as bigint)) as fy from 
(select SUBSTR(DLASTM,1,4) as year_id,csfzl,regexp_replace(MYSJE,'[[^0-9.\\\u4e00-\\\u9fa5]]','') as MYSJE_NUM  from lqioc_ioc_ods.TBQYZYFYMX) a GROUP BY a.csfzl,a.year_id having a.year_id=SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)) b
union all 
select '职工医保应收费用（元）' as ORDER_ITEM,
sum(case when b.csfzl='医保' then b.fy else 0 end) as ORDER_ITEM_NUM
from 
(select a.csfzl,a.year_id,sum(cast(a.MYSJE_NUM as bigint)) as fy from 
(select SUBSTR(DLASTM,1,4) as year_id,csfzl,regexp_replace(MYSJE,'[[^0-9.\\\u4e00-\\\u9fa5]]','') as MYSJE_NUM  from lqioc_ioc_ods.TBQYZYFYMX) a GROUP BY a.csfzl,a.year_id having a.year_id=SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)) b
union all 
select '城乡居民医保应收费用（元）' as ORDER_ITEM,
sum(case when b.csfzl='农合' then b.fy else 0 end) as  ORDER_ITEM_NUM
from 
(select a.csfzl,a.year_id,sum(cast(a.MYSJE_NUM as bigint)) as fy from 
(select SUBSTR(DLASTM,1,4) as year_id,csfzl,regexp_replace(MYSJE,'[[^0-9.\\\u4e00-\\\u9fa5]]','') as MYSJE_NUM  from lqioc_ioc_ods.TBQYZYFYMX) a GROUP BY a.csfzl,a.year_id having a.year_id=SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)) b
union all 
select '其他应收费用（元）' as ORDER_ITEM,
sum(case when b.csfzl in ('未定义','公费') then b.fy else 0 end) as  ORDER_ITEM_NUM
from 
(select a.csfzl,a.year_id,sum(cast(a.MYSJE_NUM as bigint)) as fy from 
(select SUBSTR(DLASTM,1,4) as year_id,csfzl,regexp_replace(MYSJE,'[[^0-9.\\\u4e00-\\\u9fa5]]','') as MYSJE_NUM  from lqioc_ioc_ods.TBQYZYFYMX) a GROUP BY a.csfzl,a.year_id having a.year_id=SUBSTR(FROM_UNIXTIME(UNIX_TIMESTAMP()),1,4)) b;