如何消除多表关联带来的可能的对结果集的影响？
如下代码：
我要按多个维度统计多个指标，分别需要用主表关联多张表取得，
这里我不想分步去统计，于是写成了下面的多表关联的形式，
但是我总是纠结于多表关联可能带来的对结果集的影响
（典型的一个例子就是：附表有重复记录造成部分笛卡尔积将结果集放大）
求大虾指导如何精确判断多表关联是否产生逻辑上的影响？
我还想知道的一个是多表关联在逻辑上是否一定能代替分步统计？
SELECTA.LAN_CODE,
A.PPDOM_ID,
CASE WHENB.CUST_ID IS NOT NULLAND C.CUST_ID IS NULL THEN1
WHENC.CUST_ID IS NOT NULLAND B.CUST_ID IS NULL THEN2
WHENB.CUST_ID IS NOT NULLAND C.CUST_ID IS NOT NULL THEN3
ELSE 4ENDASCUST_TYPE,
COUNT(DISTINCT A.CUST_ID) AS CUST_CNT,
SUM (CASE WHEN A.SERV_STATE'F1R' AND A.STATE='F0A' AND A.OLD_FLAG 'F1R' AND B.STATE='F0A' AND B.OLD_FLAG 'F1R' AND B.STATE='F0A'AND B.OLD_FLAG=4096 THEN C.SERV_ID END) AS KD_CZ_CNT_4M,
COUNT(DISTINCT CASE WHENC.KD_TYPE LIKE '%包年%' THEN C.SERV_ID END) AS KD_CZ_CNT_YEAR,
COUNT(DISTINCT CASE WHENM.SERV_ID IS NOT NULL THEN C.SERV_ID END) AS KD_CZ_CNT_ITV,
COUNT(DISTINCT CASE WHEN D.MAIN_SERV_FLAG LIKE '1%' THEN D.SERV_ID END)GH_CZ_CNT,
COUNT(DISTINCT CASE WHEN D.MAIN_SERV_FLAG LIKE '1%' AND D.SERV_STATE'F1R' AND D.STATE='F0A' AND D.OLD_FLAG 'F1R' AND STATE='F0A' AND OLD_FLAG<2
)C ON A.SERV_ID = C.SERV_ID
LEFT JOIN (SELECT SERV_ID,SUM(COALESCE(CHARGE,0)) CHARGE,SUM(COALESCE(DURATION,0)) DURATION
FROM MK_LIST.PRD_CDMA_MID_NEW_MON_MID_KF_701_120201
GROUP BY SERV_ID) D
ON A.SERV_ID= D.SERV_ID
LEFT JOIN (
SELECT SERV_ID,COALESCE(SUM(INTERNET_DISCHARGE),0) INTERNET_DISCHARGE
FROM WH_VAD.TB_GROUP_SERV_ITEM_MON_701_120201
GROUP BY SERV_ID
) E ON A.SERV_ID = E.SERV_ID
LEFT JOIN WH_VAD.TB_MID_ITV_INFO_701_120201 M ON C.SERV_ID = M.SERV_ID
GROUP BYA.LAN_CODE,
A.PPDOM_ID,
CASE WHENB.CUST_ID IS NOT NULLAND C.CUST_ID IS NULL THEN1
WHENC.CUST_ID IS NOT NULLAND B.CUST_ID IS NULL THEN2
WHENB.CUST_ID IS NOT NULLAND C.CUST_ID IS NOT NULL THEN3
ELSE 4END
|
