表在Oracle数据库中命名以开头和以结尾的分类

Question

让我用简单的例子来解释，基本上我们有一些复杂的表命名约定设计——至少在阶段模式中有4种模式。

总层数4

1. 舞台层
2. 基本数据存储层
3. EDW层
4. 集市层

以下所有模式都只属于STAGE层

舞台层-"DS_00"、"SC_00"，"SP_00"one_answers"T">

在这个模式中，当寻找搜索模式组合时，我需要考虑以"DS_00"、"DSC_00"、"BSP_00"one_answers"DT"开头的表(下面解释的模式)和以(T、W、W01、W02、W03、W1、W2、W3)结尾的表，因此该表必须分类为STANADARD_NAMEG_CONV else NON_STANDARD_NAMEG_CONV

T-技术W-工作台

模式1

DS_00_to_99(00_to_99-是用于标识从源加载表的系统的编号)DS_SYSTEM_NO_TABLENAME_ENDING系统编号-00_to_99TABLENAME-完整的表名ENDING-并非所有情况下表都分类为(T、W、W01、W02、W03、W1、W2、W3)因此，对于其他图案1，上述00至99的数字范围也是相同的。

模式2

DSC_00_to_99(00_to_99-是标识从源加载表的系统的编号)DSC_SYSTEM_NO_TABLENAME_ENDING因此，对于其他图案2，上述00至99的数字范围也是相同的。

模式3

DSP_00_to_99(00_to_99-是用于标识从源加载表的系统的编号)DS_SYSTEM_NO_TABLENAME_ENDING因此，对于其他图案3，上述00至99的数字范围也是相同的。

图案4

DT_ABC_FHSJDS_TABLENAME_ENDING因此数字范围不适用于这种模式

基本数据存储层-"DS">

在这个模式中，当寻找搜索模式组合时，我需要考虑以"DB"开头、以(D、F、L、T、W、W01、W02、W03、W1、W2、W3)结尾的表，因此该表必须分类为STANADARD_NAMEG_CONV else NON_STANDARD_NAMEG_CONV

EDW层-"DE">

在这个模式中，当寻找搜索模式组合时，我需要考虑以"DB"开头、以(D、F、L、T、W、W01、W02、W03、W1、W2、W3)结尾的表，因此，该表必须分类为STANADARD_NAMEG_CONV，否则为NON_STANDARD_NAMEG_CONV。在该层中，以TBD_开头的其他一些表必须归类为TO_be_DROPPED，TMP_必须归类为TEMPORARY_table

MART LAYER-"DM">

在这个模式中，当寻找搜索模式组合时，我需要考虑以"DB"开头、以(D、F、L、T、W、W01、W02、W03、W1、W2、W3、A、AD、AM、AQ、AY)结尾的表，因此，该表必须分类为STANADARD_NAMEG_CONV，否则为NON_STANDARD_NAMEG_CONV。在该层中，以TBD_开头的其他一些表必须归类为TO_be_DROPPED，TMP_必须归类为TEMPORARY_table

D-维度表
F-事实表
L-查找表
2T-技术表
1W-工作表
3A-聚合事实表
AD-每日聚合事实
AM-每月聚合事实
AQ-每季度聚合事实
AY-每年聚合事实

查询1-旧查询

SELECT owner,
object_name,
beginning,
ending,
--count(*),
CASE
WHEN ( beginning, ending ) IN (
( 'DS', 'T' ),
( 'DS', 'W' ),
( 'DS', 'W01' ),
( 'DS', 'W02' ),
( 'DS', 'W03' ),
( 'DS', 'W1' ),
( 'DS', 'W2' ),
( 'DS', 'W3' ),
( 'DB', 'D' ),
( 'DB', 'F' ),
( 'DB', 'L' ),
( 'DB', 'T' ),
( 'DB', 'W' ),
( 'DB', 'W01' ),
( 'DB', 'W02' ),
( 'DB', 'W03' ),
( 'DB', 'W1' ),
( 'DB', 'W2' ),
( 'DB', 'W3' ),
( 'DE', 'D' ),
( 'DE', 'F' ),
( 'DE', 'L' ),
( 'DE', 'T' ),
( 'DE', 'W' ),
( 'DE', 'W01' ),
( 'DE', 'W02' ),
( 'DE', 'W03' ),
( 'DE', 'W1' ),
( 'DE', 'W2' ),
( 'DE', 'W3' ),
( 'DA', 'D' ),
( 'DA', 'F' ),
( 'DA', 'L' ),
( 'DA', 'T' ),
( 'DA', 'W' ),
( 'DA', 'W01' ),
( 'DA', 'W02' ),
( 'DA', 'W03' ),
( 'DA', 'W1' ),
( 'DA', 'W2' ),
( 'DA', 'W3' ),
( 'DA', 'A' ),
( 'DA', 'AD' ),
( 'DA', 'AM' ),
( 'DA', 'AQ' ),
( 'DA', 'AY' )
)
THEN 'STANDARD_NAMING_CONVENTION'
WHEN object_name LIKE 'TBD%'
THEN 'TO_BE_DROPPED'
WHEN object_name LIKE 'TMP%'
THEN 'TEMPORARY_TABLE'
ELSE 'NON_STANDARD_NAMING_CONVENTION'
END AS table_classification
FROM   (
SELECT owner,       
object_name,
CASE first_separator
WHEN 0
THEN NULL
ELSE SUBSTR( object_name, 1, first_separator - 1 )
END AS beginning,
CASE last_separator
WHEN 0
THEN NULL
ELSE SUBSTR( object_name, last_separator + 1 )
END AS ending
FROM   (
SELECT owner,       
object_name,
INSTR( object_name, '_', 1 )  AS first_separator,
INSTR( object_name, '_', -1 ) AS last_separator
FROM   dba_objects
WHERE  owner in ('DI_STAGE','DI_BDS','DI_EDW','DI_MART')   
and    object_type='TABLE'
group by owner,object_name
order by owner desc 
)
);

查询2-最终查询-已工作

SELECT OWNER, 
OBJECT_NAME, 
REGEXP_REPLACE(OBJECT_NAME, '^([A-Z0-9$]{1,})_([A-Z0-9$]{1,})_.*','1_2')AS  BEGINNING, 
REGEXP_SUBSTR(OBJECT_NAME, '[^_]*$') AS ENDING, 
CASE 
WHEN REGEXP_LIKE(OBJECT_NAME, '^D(S[CP]?|T)_.+_(T|(W0?[123]?))$')
THEN 
'STANDARD_NAMING_CONVENTION' 
WHEN REGEXP_LIKE(OBJECT_NAME, '^DB_.+_(D|F|T|W|W1|W2|W3)$')
THEN 
'STANDARD_NAMING_CONVENTION' 
WHEN REGEXP_LIKE(OBJECT_NAME, '^DE_.+_(D|F|L|T|W|W01|W02|W03|W1|W2|W3)$')
THEN 
'STANDARD_NAMING_CONVENTION'
WHEN OBJECT_NAME LIKE 'TBD%' 
THEN 
'TO_BE_DROPPED' 
WHEN OBJECT_NAME LIKE 'TMP%' 
THEN 'TEMPORARY_TABLE' 
WHEN REGEXP_LIKE(OBJECT_NAME, '^DM_.+_(D|F|L|T|W|W01|W02|W03|W1|W2|W3|A|AD|AM|AQ|AY)$') 
THEN 
'STANDARD_NAMING_CONVENTION' 
ELSE 'NON_STANDARD_NAMING_CONVENTION' 
END 
AS TABLE_CLASSIFICATION 
FROM   DBA_OBJECTS 
WHERE  OWNER IN ('DI_STAGE', 'DI_BDS', 'DI_EDW', 'DI_MART') 
AND OBJECT_TYPE = 'TABLE' 
GROUP  BY OWNER, 
OBJECT_NAME 
ORDER  BY OWNER DESC, 
OBJECT_NAME; 

预期结果-匹配

SNO OWNER  OBJECT_NAME       BEGINNING ENDING  TABLE_CLASSIFICATION 
01  DI_BDS DB_PROD_DGGAA_D   DB_PROD   D       STANDARD_NAMING_CONVENTION
02  DI_BDS DB_CUST_DHHA_F    DB_CUST   F       STANDARD_NAMING_CONVENTION
03  DI_BDS DB_DHSHJA_HHSGS_T DB_DHSHJA T       STANDARD_NAMING_CONVENTION
04  DI_BDS DB_DHS_DHHA_W     DB_DHS    W       STANDARD_NAMING_CONVENTION
05  DI_BDS DB_GSG_DHHA_W01   DB_GSG    W01     STANDARD_NAMING_CONVENTION
06  DI_BDS DB_GFS_FHSH_W02   DB_GFS    W02     STANDARD_NAMING_CONVENTION
07  DI_BDS DB_FGS_FHS_W03    DB_FGS    W03     STANDARD_NAMING_CONVENTION
08  DI_BDS DB_DJJ_GSA_W1     DB_DJJ    W1      STANDARD_NAMING_CONVENTION
09  DI_BDS DB_DKS_SJ_W2      DB_DKS    W2      STANDARD_NAMING_CONVENTION
10  DI_BDS DB_DJA_DT_W3      DB_DJA    W3      STANDARD_NAMING_CONVENTION
11  DI_BDS DB_DHH_DG         DB_DHH    DG      NON_STANDARD_NAMING_CONV
12  DI_BDS DB_DNS_DRS_123    DB_DNS    123     NON_STANDARD_NAMING_CONV
13  DI_BDS DB_FHD_DRS_1      DB_FHD    1       NON_STANDARD_NAMING_CONV
14  DI_BDS DB_OKS_DRS_0      DB_OKS    0       NON_STANDARD_NAMING_CONV
15  DI_BDS DB_SKG_DRS_90     DB_SKG    90      NON_STANDARD_NAMING_CONV

最后工作

Answer 1

这似乎是一个模式匹配问题，但模式也取决于表所有者。我把它做成这样：

with test (owner, table_name) as
( select 'DWH_STAGE_LAYER',    'DS_WHATEVER' from dual union all
select 'DWH_STAGE_LAYER',    'DS_ANYTHING_F' from dual union all
select 'DWH_DATAMART_LAYER', 'DM_CONS_REV_F' from dual union all
select 'DWH_DATAMART_LAYER', 'DM_CONS_REV_F34' from dual union all
select 'DWH_DATAMART_LAYER', 'DM_ORDER_ENTRY_W' from dual union all
select 'DWH_DATAMART_LAYER', 'DM_ORDER_ENTRY_W12' from dual union all
select 'DWH_DATAMART_LAYER', 'DM_PROC_SALE_A' from dual union all
select 'DWH_DATAMART_LAYER', 'DM_PROC_SALE_A12' from dual union all
select 'DWH_DATAMART_LAYER', 'DM_PROC_SALE_AD' from dual union all
select 'DWH_DATAMART_LAYER', 'DM_PROC_SALE_AD11' from dual union all
select 'DWH_DATAMART_LAYER', 'DM_PROC_SALE_AM' from dual union all
select 'DWH_DATAMART_LAYER', 'DM_PROC_SALE_AQ' from dual union all
select 'DWH_DATAMART_LAYER', 'DM_PROC_SALE_AQ234' from dual union all
select 'DWH_DATAMART_LAYER', 'DM_WHATEVER_AQ' from dual union all
select 'DWH_EDW_LAYER',      'DM_WHATEVER_AQ' from dual union all
select 'DWH_EDW_LAYER',      'DE_EMP_POOL_T' from dual union all
select 'DWH_EDW_LAYER',      'DE_EMP_POOL_T33' from dual union all
select 'DWH_EDW_LAYER',      'DE_PROD_RECORD_D' from dual union all
select 'DWH_EDW_LAYER',      'DE_PROD_RECORD_D123' from dual
)
select t.owner, t.table_name
, case
when t.owner = 'DWH_STAGE_LAYER' and t.table_name like 'DS_%' escape '' then 'Y'
when t.owner = 'DWH_EDW_LAYER' and regexp_like(t.table_name,'^DE_.+_[DFLTW]$') then 'Y'
when t.owner = 'DWH_DATAMART_LAYER' and regexp_like(t.table_name,'^DM_.+_([DFLTW]|A[DMQY]?)$') then 'Y'
else 'N'
end as valid
from   test t
order by t.owner, t.table_name

有效DWH_DATAMART_LAYERDWH_DATAMART_LAYER

所有者	table_NAME
DWH_DATAMART_LAYER	DM_CONS_REV_F	Y
DWH_DATAMART_LAYER	DM_CONS_REV_F34	N
DWH_DATAMART_LAYER	DM_ORDER_ENTRY_W	Y
DWH_DATAMART_LAYER	DM_ORDER_ENTRY_W12	N
DWH_DATAMART_LAYER	DM_PROC_SALE_A	Y
DWH_DATAMART_LAYER
DWH_DATAMART_LAYER	DM_PROC_SALE_AD	Y
DWH_DATAMART_LAYER	DM_PROC_SALE_AD11	N
DWH_DATAMART_LAYER	DM_PROC_SALE_AM	Y
DWH_DATAMART_LAYER	DM_PROC_SALE_AQ234	N
DWH_EDW_LAYER	DE_EMP_POOL_T	Y
DWH_EDW_LAYER	DE_EMP_POOL_T33	N
DWH_EDW_LAYER	DE_PROD_RECORD_D	Y
DWH_EDW_LAYER	DE_PROD_RECORD_D123	N
DWH_EDW_LAYER	DM_WHATEVER_AQ	N
DWH_STAGE_LAYER	DS_ANYTHING_F	Y
DWH_STAGE_LAYER	DS_WHATEVER	Y

您似乎想要这样的东西：

SELECT owner,
object_name,
beginning,
ending,
CASE
WHEN ( beginning, ending ) IN (
( 'DS', 'INS' ),
( 'DE', 'D' ),
( 'DE', 'T' ),
( 'DM', 'F' ),
( 'DM', 'W' ),
( 'DM', 'A' ),
( 'DM', 'AD' ),
( 'DM', 'AM' ),
( 'DM', 'AQ' ),
( 'DM', 'AY' )
)
THEN 'STANDARD_NAMING_CONVENTION'
WHEN object_name LIKE 'TBD%'
THEN 'TO_BE_DROPPED'
ELSE 'NON_STANDARD_NAMING_CONVENTION'
END AS table_classification
FROM   (
SELECT owner,       
object_name,
CASE first_separator
WHEN 0
THEN NULL
ELSE SUBSTR( object_name, 1, first_separator - 1 )
END AS beginning,
CASE last_separator
WHEN 0
THEN NULL
ELSE SUBSTR( object_name, last_separator + 1 )
END AS ending
FROM   (
SELECT owner,       
object_name,
INSTR( object_name, '_', 1 )  AS first_separator,
INSTR( object_name, '_', -1 ) AS last_separator
FROM   dba_objects
WHERE  owner in ('DWH_STAGE_LAYER','DWH_EDW_LAYER','DWH_DATAMART_LAYER')  
AND    object_type='TABLE'
)
);