实习期间DBeaver用到的语句

查询最多字段不为空(非空列)的id行

select id , 
	case when controlling_document !='' then 1 else 0 end +
	case when environment !='' then 1 else 0 end +
--	case when escalated_to_tpl_counter !='' then 1 else 0 end +
--	case when escalated_to_upl_counter !='' then 1 else 0 end +
	case when found_in_series !='' then 1 else 0 end +
	case when found_in_sold_object !='' then 1 else 0 end +
--	case when gitcommitsreferenced !='' then 1 else 0 end +
	case when issuefunction !='' then 1 else 0 end +
--	case when leading_art !='' then 1 else 0 end +
--	case when original_story_points !='' then 1 else 0 end +
	case when solution_business_owner !='' then 1 else 0 end +
--	case when structure_index_monitor !='' then 1 else 0 end +
	case when structure_index_monitor !='' then 1 else 0 end as num
--	case when team !='' then 1 else 0 end as num
from ods_crd_ddt_external_dev.jira_ft_ods_issue_s3_di_pt vfoisdp order by num desc limit 10;

#查看stg表每个分区的数量

select dt, count(distinct id) from ods_crd_ddt_external_dev.vira_t_ods_issue_stg_di_pt vtoisdp group by dt order by dt;

根据issue_id分组并查询各种情况

select distinct count from 
(SELECT issue_id,COUNT(*)  FROM ods_crd_ddt_external_dev.vira_ft_ods_issue_version_s3_df vfoivsd GROUP BY issue_id) order by count desc; 

有两个字段重复插入，找出所有重复插入的数据

--找到issue_id,version_id重复插入多次的数据
select distinct issue_id from ods_crd_ddt_external_dev.vira_ft_ods_issue_version_s3_df vfoivsd where issue_id
in (select issue_id from ods_crd_ddt_external_dev.vira_ft_ods_issue_version_s3_df vfoivsd2 group by issue_id,version_id having count(*) > 1) order by issue_id,version_id ;