测试模型后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   --取两位小数

就可以实现了。