一段SQL轻松实现数据库直接查询出TGI结果

当我们遇到要计算某些指标的TGI时,相对来说就比较复杂了,因为我们要分别计算出参与统计的父/子对象的分子和分母,并且还要用父子对象计算结果后的百分比再作为分子和分母来求出TGI的值

当遇到这样的情况,如何才能最有效的只通过SQL就实现结果的计算,而不用下载数据到EXCLE进行反复的操作加工呢?

一段SQL能轻松实现数据库直接查询TGI结果

假设我们有如下一个表结构,limit出前20条内容,我们得到里面的具体数据情况,我们需要计算一个具体的author_id对某个具体series_id的emo_result的TGI,由于emo_result有两个类型,一个是“negative”,另一个是“positive”,所以这时我们需要分别对他们进行计算,我们直接来看看具体的代码实例

select a.dim_month,a.series_id,a.author_name,a.influence ,a.cnt_1,b.cnt_2,c.cnt_3,d.cnt_4,(a.cnt_1/b.cnt_2)/(c.cnt_3/d.cnt_4) TGI from (select dim_month,series_id,author_name,cnt_1,influence from 
(select dim_month,series_id,author_name,count(object_id) cnt_1,max(influence) influence from db.tableA where dim_month > '2019-11-01' and dim_month <'2020-02-01'and series_id in ('835','135','3294','364') and emo_result = 'negative' group by dim_month ,series_id,author_name) innera 
where series_id = '135' and dim_month ='2019-11-30' order by influence desc limit 20) a
 left join 
(select dim_month,author_name,count(object_id) cnt_2 from db.tableA where dim_month > '2019-11-01' and dim_month <'2020-02-01' and series_id in ('835','135','3294','364') and emo_result = 'negative' group by dim_month,author_name) b
 on a.dim_month =b.dim_month 
 and a.author_name = b.author_name
 left join (
 select dim_month,series_id,count(object_id) cnt_3 from db.tableA where dim_month > '2019-11-01' and dim_month <'2020-02-01' and series_id in ('835','135','3294','364') and emo_result = 'negative' group by dim_month,series_id) c
 on a.dim_month = c.dim_month 
 and a.series_id = c.series_id
 left join (
 select dim_month,count(object_id) cnt_4 from db.tableA where dim_month > '2019-11-01' and dim_month <'2020-02-01' and emo_result = 'negative' and series_id in ('835','135','3294','364') group by dim_month) d
on c.dim_month = d.dim_month

通过上面的代码中“where series_id = ‘135’ and dim_month =’2019-11-30′ ”里的参数,我们就能得到不同的series_id在不同的月份作者negative的TGI,当然我们也可以将negative改为“positive”得到作者positive的TGI

在这里大家可能会发现一个比较特别的语句,即“order by influence desc limit 20”,这是按照influence 这个字段,降序输出前20条记录,如果我们不做这个限制,则会返回所有的查询结果,这条语句可以基于实际的业务需求进行增删

我来评几句
登录后评论

已发表评论数()

相关站点

热门文章