测试模型后sql整理
最近测试了一个分析模型,没有页面,测试工作为数据库数据验证。
根据数据分析师提供的计算公式,查询数据库数据验证结果值。
mape=abs(si-sj)/∑sj_m
碰到了以下问题:
一、DBeaver工具Hana库表跨库查询
A表在user1账号下,B表在user2账号下,两表需关联,涉及到跨库查表。
开通权限后,直接加前缀查询即可。
例如:
SELECT * FROM user2.B
Hana连接后,加粗的表即为登录的账号。
二、数据库中存在每日重复数据,需要以月份维度来统计
1.对每日数据进行分组求和。
select date,name,sum(day_cnt) as num from user2.B where date>='2018-01-01' group by date,name
查询结果为:
2.截取date前7位数据,并且按月份求和。
- 用到了substr('HelloWorld',0,3)函数,先对字段进行截取。
select substr(date,0,7) AS date ,substr(name,0,2) as name,sum(day_cnt) as num --截取时间前7位,系列前2位 from uesr2.B where date>='2018-01-01' group by date,name
查询结果:
- 再按照月份求和。
SELECT name,date,sum (num) AS value FROM (select substr(date,0,7) AS date ,substr(name,0,2) as name,sum(day_cnt) as num from user2.B where report_date>='2018-01-01' group by date,name) WHERE name IN ('01','02','03') GROUP BY date,name
查询结果:
三、以上查询结果需要与其他表关联,或者作为公共部分
用到了with a as()临时结果集,如果有多个可以这样写with a as(),b as()
with a as(SELECT name,date,sum (num) AS value FROM (select substr(date,0,7) AS date ,substr(name,0,2) as name,sum(day_cnt) as num from user2.B where report_date>='2018-01-01' group by date,name) WHERE name IN ('01','02','03') GROUP BY date,name)
那么我们下次查询时,就可以:
SELECT * FROM a
四、计算公式
abs(a.value - b.value) absvalue
c.absvalue * 100 / b.zh --百分比
round(pjz, 2) mape --取两位小数
就可以实现了。