JCRS下载中心

-- dm1yj_tj SELECT UniqueID, GzRq, YjFlDm, YjFl, YjKs, ExamType, TjLb, KdRc, Kd_Jf_Rc, Kd_Wc_Rc, Kd_JfYc_Rc, Kd_ExamYc_Rc, AVG_KdSh_Sc, Time1_Wc_Rate, Time2_Wc_Rate, Time3_Wc_Rate, Time4_Wc_Rate, Time5_Wc_Rate, Time6_Wc_Rate, Yy_Gt_Rc, ZdYy_Xm_Cnt, DjRc, Dj_Wjc_Rc, JcRc, JcRs, Jc_Wwc_Rc, Avg_Dj_Jc_Sc, Dj_Jc_Rc, Dj_Jc_Rc1, Dj_Jc_Rc2, XmJe, Avg_XmJe, WcRc, Avg_Jc_Sh_Sc, Avg_Bg_Sh_Sc, Time1_Wc_Num, Time2_Wc_Num, Time3_Wc_Num, Time4_Wc_Num, Time5_Wc_Num, Time6_Wc_Num, Time_Wc_Den FROM dw3yj_tj WHERE GzRq>=CURRENT_DATE -INTERVAL 14 DAY -- dw3yj_tj SELECT a.UniqueID,a.GzRq,a.YjFlDm,a.YjFl,a.YjKs,a.ExamType,a.TjLb,a.KdRc,a.Kd_Jf_Rc,a.Kd_Wc_Rc,a.Kd_JfYc_Rc,a.Kd_ExamYc_Rc,a.AVG_KdSh_Sc,a.Time1_Wc_Rate,a.Time2_Wc_Rate,a.Time3_Wc_Rate,a.Time4_Wc_Rate,a.Time5_Wc_Rate ,a.Time6_Wc_Rate,a.Yy_Gt_Rc,a.ZdYy_Xm_Cnt ,c.DjRc,c.Dj_Wjc_Rc ,d.JcRc,d.JcRs,d.Jc_Wwc_Rc,d.Avg_Dj_Jc_Sc,d.Dj_Jc_Rc,d.Dj_Jc_Rc1,d.Dj_Jc_Rc2,d.XmJe,d.Avg_XmJe ,e.WcRc,e.Avg_Jc_Sh_Sc,e.Avg_Bg_Sh_Sc, a.Time1_Wc_Num, a.Time2_Wc_Num, a.Time3_Wc_Num, a.Time4_Wc_Num, a.Time5_Wc_Num, a.Time6_Wc_Num, a.Time_Wc_Den FROM dw2yj_kd_tj a LEFT JOIN dw2yj_dj_tj c ON a.GzRq=c.GzRq AND a.YjFlDm=c.YjFlDm AND a.YjKs=c.YjKs AND a.ExamType=c.ExamType AND a.TjLb=c.TjLb LEFT JOIN dw2yj_exam_tj d ON a.GzRq=d.GzRq AND a.YjFlDm=d.YjFlDm AND a.YjKs=d.YjKs AND a.ExamType=d.ExamType AND a.TjLb=d.TjLb LEFT JOIN dw2yj_bg_tj e ON a.GzRq=e.GzRq AND a.YjFlDm=e.YjFlDm AND a.YjKs=e.YjKs AND a.ExamType=e.ExamType AND a.TjLb=e.TjLb WHERE a.GzRq>=CURRENT_DATE -INTERVAL 14 DAY AND a.YjFlDm='01' UNION ALL SELECT a.UniqueID,a.GzRq,a.YjFlDm,a.YjFl,a.YjKs,a.ExamType,a.TjLb,a.KdRc,a.Kd_Jf_Rc,a.Kd_Wc_Rc,a.Kd_JfYc_Rc,a.Kd_ExamYc_Rc,a.AVG_KdSh_Sc,a.Time1_Wc_Rate,a.Time2_Wc_Rate,a.Time3_Wc_Rate,a.Time4_Wc_Rate,a.Time5_Wc_Rate ,a.Time6_Wc_Rate,a.Yy_Gt_Rc,a.ZdYy_Xm_Cnt ,c.DjRc,c.Dj_Wjc_Rc ,d.JcRc,d.JcRs,d.Jc_Wwc_Rc,d.Avg_Dj_Jc_Sc,d.Dj_Jc_Rc,d.Dj_Jc_Rc1,d.Dj_Jc_Rc2,d.XmJe,d.Avg_XmJe ,e.WcRc,e.Avg_Jc_Sh_Sc,e.Avg_Bg_Sh_Sc, a.Time1_Wc_Num, a.Time2_Wc_Num, a.Time3_Wc_Num, a.Time4_Wc_Num, a.Time5_Wc_Num, a.Time6_Wc_Num, a.Time_Wc_Den FROM dw2yj_kd_tj a LEFT JOIN dw2yj_dj_tj c ON a.GzRq=c.GzRq AND a.YjFlDm=c.YjFlDm AND a.YjKs=c.YjKs AND a.ExamType=c.ExamType LEFT JOIN dw2yj_exam_tj d ON a.GzRq=d.GzRq AND a.YjFlDm=d.YjFlDm AND a.YjKs=d.YjKs AND a.ExamType=d.ExamType LEFT JOIN dw2yj_bg_tj e ON a.GzRq=e.GzRq AND a.YjFlDm=e.YjFlDm AND a.YjKs=e.YjKs AND a.ExamType=e.ExamType WHERE a.GzRq>=CURRENT_DATE -INTERVAL 14 DAY AND a.YjFlDm='02' dm层显示的是开单人群效益分析数据,是日维度的,我现在需要周维度的dm层,也是从dw层抽取,请你给我建表语句和查询语句
08-02
SELECT UniqueID, CONCAT(YEAR(a.GzRq), '-W', LPAD(WEEK(a.GzRq, 3), 2, '0')) AS GzWeek, DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY AS WeekStartDate, DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY AS WeekEndDate, CONCAT( '第', LPAD( WEEK ( a.GzRq, 3 ), 2, '0' ), '周(', DATE_FORMAT( DATE_FORMAT( a.GzRq, '%Y-%m-%d' ) - INTERVAL WEEKDAY( a.GzRq ) DAY, '%Y-%m-%d' ), ' - ', DATE_FORMAT( DATE_FORMAT( a.GzRq, '%Y-%m-%d' ) - INTERVAL WEEKDAY( a.GzRq ) DAY + INTERVAL 6 DAY, '%Y-%m-%d' ), ')' ) AS WeekLabelWithDate, a.YjFlDm, a.YjFl, a.YjKs, a.ExamType, a.TjLb, SUM(a.KdRc) AS KdRc, SUM(a.Kd_Jf_Rc) AS Kd_Jf_Rc, SUM(a.Kd_Wc_Rc) AS Kd_Wc_Rc, SUM(a.Kd_JfYc_Rc) AS Kd_JfYc_Rc, SUM(a.Kd_ExamYc_Rc) AS Kd_ExamYc_Rc, ROUND(SUM(a.AVG_KdSh_Sc_Num) / NULLIF(SUM(a.AVG_KdSh_Sc_Den), 0), 4) AS AVG_KdSh_Sc, ROUND(SUM(a.Time1_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4) AS Time1_Wc_Rate, ROUND(SUM(a.Time2_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4) AS Time2_Wc_Rate, ROUND(SUM(a.Time3_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4) AS Time3_Wc_Rate, ROUND(SUM(a.Time4_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4) AS Time4_Wc_Rate, ROUND(SUM(a.Time5_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4) AS Time5_Wc_Rate, ROUND(SUM(a.Time6_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4) AS Time6_Wc_Rate, SUM(a.Yy_Gt_Rc) AS Yy_Gt_Rc, SUM(a.ZdYy_Xm_Cnt) AS ZdYy_Xm_Cnt, SUM(a.DjRc) AS DjRc, SUM(a.Dj_Wjc_Rc) AS Dj_Wjc_Rc, SUM(a.JcRc) AS JcRc, SUM(a.JcRs) AS JcRs, SUM(a.Jc_Wwc_Rc) AS Jc_Wwc_Rc, SUM(a.Dj_Jc_Rc1) AS Dj_Jc_Rc1, SUM(a.Dj_Jc_Rc2) AS Dj_Jc_Rc2, SUM(a.XmJe) AS XmJe, SUM(a.WcRc) AS WcRc, SUM(a.Time1_Wc_Num) AS Time1_Wc_Num, SUM(a.Time2_Wc_Num) AS Time2_Wc_Num, SUM(a.Time3_Wc_Num) AS Time3_Wc_Num, SUM(a.Time4_Wc_Num) AS Time4_Wc_Num, SUM(a.Time5_Wc_Num) AS Time5_Wc_Num, SUM(a.Time6_Wc_Num) AS Time6_Wc_Num, SUM(a.Time_Wc_Den) AS Time_Wc_Den, SUM(a.AVG_KdSh_Sc_Num) AS AVG_KdSh_Sc_Num, SUM(a.AVG_KdSh_Sc_Den) AS AVG_KdSh_Sc_Den FROM dw3yj_tj a WHERE a.GzRq >= CURRENT_DATE - INTERVAL 14 WEEK GROUP BY 这是我写的,你看看哪里需要修改
08-02
SELECT CONCAT(YEAR(a.GzRq), '-W', LPAD(WEEK(a.GzRq, 3), 2, '0')) AS GzWeek, DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY AS WeekStartDate, DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY AS WeekEndDate, CONCAT( '第', LPAD(WEEK(a.GzRq, 3), 2, '0'), '周(', DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY, '%Y-%m-%d'), ' - ', DATE_FORMAT(DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY, '%Y-%m-%d'), ')' ) AS WeekLabelWithDate, a.YjFlDm, a.YjFl, a.YjKs, a.ExamType, a.TjLb, SUM(a.KdRc) AS KdRc, SUM(a.Kd_Jf_Rc) AS Kd_Jf_Rc, SUM(a.Kd_Wc_Rc) AS Kd_Wc_Rc, SUM(a.Kd_JfYc_Rc) AS Kd_JfYc_Rc, SUM(a.Kd_ExamYc_Rc) AS Kd_ExamYc_Rc, ROUND(SUM(a.AVG_KdSh_Sc_Num) / NULLIF(SUM(a.AVG_KdSh_Sc_Den), 0), 4) AS AVG_KdSh_Sc, ROUND(SUM(a.Time1_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4) AS Time1_Wc_Rate, ROUND(SUM(a.Time2_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4) AS Time2_Wc_Rate, ROUND(SUM(a.Time3_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4) AS Time3_Wc_Rate, ROUND(SUM(a.Time4_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4) AS Time4_Wc_Rate, ROUND(SUM(a.Time5_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4) AS Time5_Wc_Rate, ROUND(SUM(a.Time6_Wc_Num) / NULLIF(SUM(a.Time_Wc_Den), 0), 4) AS Time6_Wc_Rate, SUM(a.Yy_Gt_Rc) AS Yy_Gt_Rc, SUM(a.ZdYy_Xm_Cnt) AS ZdYy_Xm_Cnt, SUM(a.DjRc) AS DjRc, SUM(a.Dj_Wjc_Rc) AS Dj_Wjc_Rc, SUM(a.JcRc) AS JcRc, SUM(a.JcRs) AS JcRs, SUM(a.Jc_Wwc_Rc) AS Jc_Wwc_Rc, SUM(a.Dj_Jc_Rc1) AS Dj_Jc_Rc1, SUM(a.Dj_Jc_Rc2) AS Dj_Jc_Rc2, SUM(a.XmJe) AS XmJe, SUM(a.WcRc) AS WcRc, SUM(a.Time1_Wc_Num) AS Time1_Wc_Num, SUM(a.Time2_Wc_Num) AS Time2_Wc_Num, SUM(a.Time3_Wc_Num) AS Time3_Wc_Num, SUM(a.Time4_Wc_Num) AS Time4_Wc_Num, SUM(a.Time5_Wc_Num) AS Time5_Wc_Num, SUM(a.Time6_Wc_Num) AS Time6_Wc_Num, SUM(a.Time_Wc_Den) AS Time_Wc_Den, SUM(a.AVG_KdSh_Sc_Num) AS AVG_KdSh_Sc_Num, SUM(a.AVG_KdSh_Sc_Den) AS AVG_KdSh_Sc_Den FROM dw3yj_tj a WHERE a.GzRq >= CURRENT_DATE - INTERVAL 14 WEEK GROUP BY a.YjFlDm, a.YjFl, a.YjKs, a.ExamType, a.TjLb, CONCAT(YEAR(a.GzRq), '-W', LPAD(WEEK(a.GzRq, 3), 2, '0')), DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY;我需要建表语句
08-02
SELECT UniqueID, CONCAT(YEAR(a.GzRq), '-W', LPAD(WEEK(a.GzRq, 3), 2, '0')) AS GzWeek, DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY AS WeekStartDate, DATE_FORMAT(a.GzRq, '%Y-%m-%d') - INTERVAL WEEKDAY(a.GzRq) DAY + INTERVAL 6 DAY AS WeekEndDate, CONCAT( '第', LPAD( WEEK ( a.GzRq, 3 ), 2, '0' ), '周(', DATE_FORMAT( DATE_FORMAT( a.GzRq, '%Y-%m-%d' ) - INTERVAL WEEKDAY( a.GzRq ) DAY, '%Y-%m-%d' ), CREATE TABLE dm_weekly_yj_tj ( GzWeek VARCHAR(10), WeekStartDate DATE COMMENT '本周起始日期(周一)', WeekEndDate DATE COMMENT '本周结束日期(周日)', WeekLabelWithDate VARCHAR(50) COMMENT '带日期的周标签,如:本周(2025-07-28 - 2025-08-03)', YjFlDm char(2) DEFAULT NULL COMMENT '医技分类代码', YjFl varchar(30) DEFAULT NULL COMMENT '医技分类', YjKs varchar(30) DEFAULT NULL COMMENT '医技科室', ExamType varchar(50) DEFAULT NULL COMMENT '检查类型', TjLb VARCHAR(50), DEFAULT NULL COMMENT '统计类型', KdRc int DEFAULT NULL COMMENT '开单人次', Kd_Jf_Rc int DEFAULT NULL COMMENT '当天开单缴费人次', Kd_Wc_Rc int DEFAULT NULL COMMENT '当天开单且检查完成人次', Kd_JfYc_Rc int DEFAULT NULL COMMENT '未及时缴费人次', Kd_ExamYc_Rc int DEFAULT NULL COMMENT '检查迟到人次', AVG_KdSh_Sc decimal(20,2) DEFAULT NULL COMMENT '当天开单-审核报告平均时长', Time1_Wc_Rate decimal(20,2) DEFAULT NULL COMMENT '1小时完成率', Time2_Wc_Rate decimal(20,2) DEFAULT NULL COMMENT '2小时完成率', Time3_Wc_Rate decimal(20,2) DEFAULT NULL COMMENT '11点前出报告率', Time4_Wc_Rate decimal(20,2) DEFAULT NULL COMMENT '16点前出报告率', Time5_Wc_Rate decimal(20,2) DEFAULT NULL COMMENT '当天完成率', Time6_Wc_Rate decimal(20,2) DEFAULT NULL COMMENT '非当天完成率', Yy_Gt_Rc int DEFAULT NULL COMMENT '预约非当天人次', ZdYy_Xm_Cnt int DEFAULT NULL COMMENT '主动预约项目数', DjRc int DEFAULT NULL COMMENT '登记人次', Dj_Wjc_Rc int DEFAULT NULL COMMENT '登记未检查人次', JcRc int DEFAULT NULL COMMENT '检查人次', JcRs int DEFAULT NULL COMMENT '检查人数', Jc_Wwc_Rc int DEFAULT NULL COMMENT '检查中人次', Dj_Jc_Rc1 int DEFAULT NULL COMMENT '登记-开始检查时长大于30分钟人次', Dj_Jc_Rc2 int DEFAULT NULL COMMENT '登记-开始检查时长大于60分钟人次', XmJe BIGINT DEFAULT NULL COMMENT '费用项目', WcRc BIGINT DEFAULT NULL COMMENT '完成检查人次', Time1_Wc_Num int DEFAULT NULL COMMENT '1小时完成率分子', Time2_Wc_Num int DEFAULT NULL COMMENT '2小时完成率分子', Time3_Wc_Num int DEFAULT NULL COMMENT '11点前出报告率分子', Time4_Wc_Num int DEFAULT NULL COMMENT '16点前出报告率分子', Time5_Wc_Num int DEFAULT NULL COMMENT '当天完成率分子', Time6_Wc_Num int DEFAULT NULL COMMENT '非当天完成率分子', Time_Wc_Den int DEFAULT NULL COMMENT '完成率分母', AVG_KdSh_Sc_Num DECIMAL(20,2) DEFAULT NULL COMMENT '当天出报告时长分子' , AVG_KdSh_Sc_Den int DEFAULT NULL COMMENT '当天出报告时长分母' ); You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT NULL COMMENT '统计类型', KdRc int DEFAULT N' at line 10
最新发布
08-02
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值