显示每个组最大值的配置单元表

Question

给定的配置单元查询。

select field_name, sum(frequency) as total_sum, ontology_uri 
from feed_one.dd_instance 
where feed_name="mrna"
group by ontology_uri,field_name order by total_sum desc;

生成下表：

**field_name**          **total_sum**   **ontology_uri**
tcga_participant_barcode    1944    http://data.bioontology.org/ontologies/NCIT
gene                        1251    http://data.bioontology.org/ontologies/GEXO
protocol                    612     http://data.bioontology.org/ontologies/PR
gene                        378     http://data.bioontology.org/ontologies/OGG
geneid                      315     http://data.bioontology.org/ontologies/NCIT
gene                        315     http://data.bioontology.org/ontologies/RETO
cohort                      270     http://data.bioontology.org/ontologies/NCIT

**field_name**          **max(total_sum)**  **ontology_uri**
tcga_participant_barcode    1944    http://data.bioontology.org/ontologies/NCIT
gene                        1251    http://data.bioontology.org/ontologies/GEXO
protocol                    612     http://data.bioontology.org/ontologies/PR
geneid                      315     http://data.bioontology.org/ontologies/NCIT
cohort                      270     http://data.bioontology.org/ontologies/NCIT

select * from A
where (A.field_name, A.total_sum) IN
(select A.field_name, max(A.total_sum) as maximum
from 
(
select field_name, sum(frequency) as total_sum, ontology_uri 
from feed_one.dd_instance 
where feed_name="mrna"
group by ontology_uri,field_name order by total_sum desc
) as A
group by A.field_name);

Answer 1

select t1.field_name, max(t1.total_sum),t1.ontology_uri
from
(
   select field_name, sum(frequency) as total_sum, ontology_uri 
   from feed_one.dd_instance 
   where feed_name="mrna"
   group by ontology_uri,field_name
) t1
group by t1.field_name, t1.ontology_uri

使用row_number()和partition

select * from 
(
   select 
        field_name,
        ontology_uri,
        sum(frequency) over (partition by field_name,ontology_uri) as total_sum,
        row_number() over (partition by field_name,ontology_uri order by total_sum desc) as row_no
   from feed_one.dd_instance 
   where feed_name="mrna"
) res 
where res.row_no = 1