实习期间DBeaver用到的语句_dbeaver查看视图语句-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_41829883/article/details/122300900

该博客探讨了如何使用SQL查询从ods_crd_ddt_external_dev表中找出具有多个非空字段的id行，并检测issue_id和version_id的重复插入。通过案例展示如何分组计数和排除信息技术无关信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查询最多字段不为空(非空列)的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 ;