用postgresql特性简化group by 后取每组前n条记录的方法
发布时间:2020-12-13 16:44:19 所属栏目:百科 来源:网络整理
导读:需求: 求一段时间内各生产线前3名坏机的原因及坏机数. step 1,从将原始数据中抽取品质数据放于t96临时表 create temp table t96 on commit drop as select * from t96_pd_log where recseq between '791G0' and '792G0' and f96_op='PD-QC' ; 可以看下这个t9
需求: 求一段时间内各生产线前3名坏机的原因及坏机数. step 1,从将原始数据中抽取品质数据放于t96临时表 create temp table t96 on commit drop as select * from t96_pd_log where recseq between '791G0' and '792G0' and f96_op='PD-QC' ;可以看下这个t96中对坏机原因的统计情况: select f96_line,f96_result,sum(f96_qty) as qty from t96 group by f96_line,f96_result order by f96_line,qty desc step2,一条sql就能得到需求的结果,其中用了with这个短句,类似于临时表或子查询的意思了,但如果用了with定义一个子集,在接着Select里面可以多处引用. 下面SQL这个A子查询每条生产线用不良数倒序排,并每记录给一个行号 ... with udc_qty_byline as (select f96_line,f96_result ) SELECT f96_line,qty FROM ( SELECT *,ROW_NUMBER() OVER (PARTITION BY f96_line order by qty DESC) AS Row_ID FROM udc_qty_byline ) AS A WHERE Row_ID <=3 ORDER BY f96_line Postgresql ispowerful. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |