sql - 从case语句oracle sql中按Count捕获条件语句组
问题描述
嗨,我正在尝试按组捕获计数,但我需要的分组来自 case 语句中的人工列,我想知道如何实现这一点,下面是我正在使用的 sql 代码,这可能使用 WITH CTE 吗?还是有任何最简单的方法,下面是查询以及现有结果和预期结果
数据库 - Oracle 数据库 11g
询问
SELECT DISTINCT
EA.TGT_TBL,
REGEXP_SUBSTR(EA.TGT_TBL, '[0-9]+') AS SYSTEM_ID,
COUNT(*),
ROUND(100*RATIO_TO_REPORT(COUNT(*)) OVER (), 2) PERCENTAGE,
CASE WHEN EA.TGT_TBL LIKE '%DWH_STAGE.%' THEN 'STAGE'
WHEN EA.TGT_TBL LIKE '%DWH_STAGE_CST.%'THEN 'STAGE'
WHEN EA.TGT_TBL LIKE 'DEV_DWH.%' THEN 'STAGE'
WHEN EA.TGT_TBL LIKE 'BI_EDW.%' THEN 'EDW'
WHEN EA.TGT_TBL LIKE 'DEV_DWH_EDW.%' THEN 'EDW'
WHEN EA.TGT_TBL LIKE 'BI_EDW_CST.%' THEN 'EDW'
WHEN EA.TGT_TBL LIKE 'DEV_DWH_EDW_CST.%' THEN 'EDW'
WHEN EA.TGT_TBL LIKE 'BI_EDW_PREM2.%' THEN 'EDW'
WHEN EA.TGT_TBL LIKE 'DEV_DWH_EDW.%' THEN 'EDW'
WHEN EA.TGT_TBL LIKE 'BI_MART.%' THEN 'MART'
WHEN EA.TGT_TBL LIKE 'DEV_DWH_MART.%' THEN 'MART'
WHEN EA.TGT_TBL LIKE 'BI_MART_CST.%' THEN 'MART'
WHEN EA.TGT_TBL LIKE 'DEV_DWH_MART_CST.%' THEN 'MART'
WHEN EA.TGT_TBL LIKE 'SHP_MART.%' THEN 'MART'
WHEN EA.TGT_TBL LIKE 'BI_REPO.%' THEN 'REPO'
WHEN EA.TGT_TBL LIKE 'BI_PMART.%' THEN 'MART'
WHEN EA.TGT_TBL LIKE 'BI_META.%' THEN 'META'
WHEN EA.TGT_TBL LIKE 'BI_META_CST.%' THEN 'META'
WHEN EA.TGT_TBL LIKE 'DEV_DWH_META.%' THEN 'META'
WHEN EA.TGT_TBL LIKE 'BI_DFL.%' THEN 'DFL'
WHEN EA.TGT_TBL LIKE 'MPN_ETL_WORK.%' THEN 'VISA'
ELSE 'OTHER'
END AS SCHEMA_GROUP
FROM BI_ETL_AUD EA
WHERE 1=1
AND TRUNC(EA.SESS_BEG)=TRUNC(SYSDATE)
AND TRUNC(EA.SESS_END)=TRUNC(SYSDATE)
GROUP BY
EA.TGT_TBL,
REGEXP_SUBSTR(EA.TGT_TBL, '[0-9]+')
ORDER BY
EA.TGT_TBL,
REGEXP_SUBSTR(EA.TGT_TBL, '[0-9]+'),SYSTEM_ID,SCHEMA_GROUP
现有结果
TGT_TBL SYSTEM_ID SCHEMA_GROUP COUNT(*) PERCENTAGE
AB_01_SHP_ORDER 01 STAGE 10 0.1
BC_AB_PUR_STTS null EDW 200 2
GH_FND_AJD_DAA null MART 10 0.1
SD_HS_HHA_DHD null REPO 100 1
预期成绩
SCHEMA_GROUP COUNT(*) PERCENTAGE
STAGE 500 10
EDW 1500 30
MART 3000 60
REPO 250 05
请注意,我正在尝试从 SCHEMA_GROUP 获取分组(此列不在表中 - CASE 语句中使用的人工列)
解决方案
这是用于您的设置的 CTE 方法(有一点限制case
)
with ea as (
select
CASE WHEN EA.TGT_TBL LIKE '%DWH_STAGE.%' THEN 'STAGE'
-- cur for brevity
WHEN EA.TGT_TBL LIKE 'MPN_ETL_WORK.%' THEN 'VISA'
ELSE 'OTHER'
END AS SCHEMA_GROUP
FROM BI_ETL_AUD EA)
select
SCHEMA_GROUP,
COUNT(*),
ROUND(100*RATIO_TO_REPORT(COUNT(*)) OVER (), 2) PERCENTAGE
from ea
group by SCHEMA_GROUP;
这产生
SCHEM COUNT(*) PERCENTAGE
----- ---------- ----------
OTHER 3 75
VISA 1 25
有样本数据
create table BI_ETL_AUD as
select 'DEV_DWH_EDW.' TGT_TBL from dual union all
select 'DEV_DWH_EDW.' TGT_TBL from dual union all
select 'DEV_DWH_EDW.' TGT_TBL from dual union all
select 'MPN_ETL_WORK.' TGT_TBL from dual
或者,您始终可以使用单个查询,您需要在列中复制大小写准确且没有别名GROUP BY
:
select
CASE WHEN EA.TGT_TBL LIKE '%DWH_STAGE.%' THEN 'STAGE'
-- cur for brevity
WHEN EA.TGT_TBL LIKE 'MPN_ETL_WORK.%' THEN 'VISA'
ELSE 'OTHER'
END AS SCHEMA_GROUP,
COUNT(*),
ROUND(100*RATIO_TO_REPORT(COUNT(*)) OVER (), 2) PERCENTAGE
FROM BI_ETL_AUD EA
group by CASE WHEN EA.TGT_TBL LIKE '%DWH_STAGE.%' THEN 'STAGE'
-- cur for brevity
WHEN EA.TGT_TBL LIKE 'MPN_ETL_WORK.%' THEN 'VISA'
ELSE 'OTHER'
END
推荐阅读
- r - 在r中将数据帧转换为时间序列
- php - Laravel - 压缩数组
- kubernetes - 如何为与 GKE 上的 Kubernetes API 服务器通信的工作负载编写最小的出口 NetworkPolicy?
- python - 如何在 python 源代码中保护客户端证书信息
- parsing - 发生器在 prolog DCG 中不工作。我们如何更改 prolog 函数中的参数,以便它开始作为生成器工作
- machine-learning - 网格搜索随机森林“尚未安装 RandomForestClassifier 实例”
- python - 您如何找到 csv 文件中特定数字的平均值?
- office-ui-fabric - 如何有条件地更改详细信息列表中行的颜色?
- python - 本地到 AWS Lambda 调用有效,但 Lambda 到 Lambda 调用不起作用
- awk - 在 awk 中引用嵌套数组