1)主表应该是粒度最细的表;对于粒度比主表还细的从表,
需先将从表数据汇总到与主表相同粒度之后再进行关联。
2)与表关联时,需按从表的主键进行关联,保证关联该表后主表数据不会出现翻倍。
3)两个不同维度的表进行关联时,对于粒度较粗的从表,其数值字段不能直接参与sum()、avg()等汇总,因为与较细粒度的主表关联后,从表的数据已经翻倍了。
4)大表间关联时,关联条件中尽量不要出现对字段的函数操作,替代方案是先算好函数操作的结果并保存成单独的字段,后面作用该字段进行关联。
SELECT
union all 属性汇总
可以采用的方式如下:
1)需要确定源表是否充分的主表,对于非限制性的从表,
尽量采用左关联,以避免由于从表数据不全导致主表的部分数据漏统计。
2)左关联时,从表的限制条件不应出现在WHERE条件中( 限制从表的主键值为NULL除外),否则,会导致左关联变成内关联(若业务要求,则将左关联改成显式的内关联)。
例如:
A LEFT JOIN B ON A.CON1=B.CON1 WHERE B.CON2=1
3)关联表的顺序,先关联需要内关联的表,再关联需要左关联的表;先关联大表,再关联小表。
4)从左关联的表中取的数据,除了在on 语句里面,其它都要用value()函数填默认值。
例如:
SELECT A.CON1, VALUE(B.CON2,0) FROM A
LEFT JOIN B ON A.CON1=B.CON1
WHERE子句注意的方面
例子1:
Select * from ods.to_usr where cmcc_branch_cd= ‘GZ01' and usr_sts_cd =1
例子2:
Select * from ods.to_usr where usr_sts_cd=1 'and cmcc_branch_cd= ‘GZ01’
以上两个SQL中cmcc_branch_cd及usr_sts_cd两个字段都没进行索引,
所以执行的时候都是全表扫描,如果:第一条SQL的cmcc_branch_cd= ‘GZ01'条件在记录集内比率为99%,而usr_sts_cd=1的比率只为0.5%
按x去重法
KF2.DEMO_USR | ||
字段 | 数据类型 | 说明 |
SEQ_CD | VARCHAR(20) | 流水号(PK) |
USR_NBR | VARCHAR(18) | 用户号码 |
USR_ATR1 | VARCHAR(32) | 用户属性1 |
USR_ATR2 | VARCHAR(32) | 用户属性2 |
OPR_DT | INTEGER | 操作日期 |
现需要按号码去重,每个号码取最后办理时记录。
流水号 | 用户号码 | 操作日期 |
1001 | A | 20110701 |
1002 | A | 20111005 |
1003 | B | 20111006 |
1004 | C | 20111225 |
1005 | D | 20120101 |
1006 | B | 20120401 |
1007 | A | 20120612 |
SELECT
A.SEQ_CD, A.USR_NBR, A.USR_ATR1, A.USR_ATR2, A.OPR_DT
FROM (
SELECT A.SEQ_CD, A.USR_NBR, A.USR_ATR1,
A.USR_ATR2, A.OPR_DT,
ROW_NUMBER() OVER (
PARTITION BYA.USR_NBR
ORDER BY A.OPR_DT DESC) AS RNK
FROM KF2.DEMO_USR A
) A
#1建立临时表
DECLARE GLOBAL TEMPORARY TABLE SESSION.PICK_CNT(
CMCC_BRANCH_CD CHAR(4),
PICK_CNT INTEGER
) PARTITIONING KEY (CMCC_BRANCH_CD)
ON COMMIT PRESERVE ROWS
WITH REPLACE NOT LOGGED IN TMPSP;
#2插入临时表数据
INSERT INTO SESSION.PICK_CNT
VALUES
('GZ',1500),
('SZ',1000)
…...;
#3取数逻辑
SELECT A.CMCC_BRANCH_CD, A.USR_NBR, A.BRND_NAME
FROM (
SELECT T.CMCC_BRANCH_CD, T.USR_NBR, T.BRND_NAME,
ROW_NUMBER() OVER (PARTITION BY CMCC_BRANCH_CD
ORDER BY RAND_RSLT) AS NBR_RNK
FROM (
SELECT CMCC_BRANCH_CD, USR_NBR, BRND_NAME,
RAND() * 100000000 AS RAND_RSLT
FROM KF2.DEMO_USR
) T
) A
JOIN SESSION.PICK_CNT B ON
A.CMCC_BRANCH_CD = B.CMCC_BRANCH_CD
AND
A.NBR_RNK <= B.PICK_CNT
分层算法
假设有一张表,表结构如下:
KF2.USR_GPRS | ||
字段 | 数据类型 | 说明 |
STAT_DT | INTEGER | 统计月份 |
USR_NBR | VARCHAR(18) | 用户号码(PK) |
GPRS_FLUX | BIGINT | GPRS流量(K) |
表样本数据如下:
STAT_DT | USR_NBR | GPRS_FLUX |
201207 | 135******** | 0 |
201207 | 134******** | 1035 |
201206 | 138******** | 55412 |
201206 | 139******** | 11524 |
201205 | 150******** | 304136 |
…… | …… | …… |
现在分析GPRS的用户实用情况,需要分别统计流量为0,0~30M,30M及以上的
用户数量。
# CASE ……WHEN…..ELSE……END
SELECT STAT_DT,
CASE WHEN GPRS_FLUX=0 THEN ‘0流量’
WHEN GPRS_FLUX>0 AND GPRS_FLUX<=30*1024
THEN ‘0~30M’
ELSE ‘30M以上’ END,
COUNT(USR_NBR)
FROM KF2.USR_GPRS
GROUP BY STAT_DT,
CASE WHEN GPRS_FLUX=0 THEN ‘0流量’
WHEN GPRS_FLUX>0 AND GPRS_FLUX<=30*1024
THEN ‘0~30M’ ELSE ‘30M以上’ END
现在分析GPRS的用户实用情况,需要统计
GPRS分层(M):0-2|2-5|5-10|10-30|30-70|70-300|300-1000|1000以上,
并按流量从少到多排序。
#1 建立临时表
DECLARE GLOBAL TEMPORARY TABLE SESSION.GPRS_RNK(
RNK_CD INTEGER, --------分层ID,可用于排序
RNK_LOW DECIMAL(14,2), --------分层下限
RNK_HIGH DECIMAL(14,2), --------分层上限
RNK_NAM VARCHAR(32) --------分层名称,用于显示
)PARTITIONING KEY (RNK_CD) ON COMMIT PRESERVE ROWS
WITH REPLACE NOT LOGGED IN TMPSP;
#2 插入数据
INSERT INTO SESSION.GPRS_RNK VALUES
(1,-99999999,2,'2M以内'),
(2,2,5,'2-5M'),
(3,5,10,'5-10M'),
(4,10,30,'10-30M'),
(5,30,70,'30-70M'),
(6,70,300,'70-300M'),
(7,300,1000,'300-1000M'),
(8,1000,99999999,'1000M以上‘)
# 统计逻辑
SELECT STAT_DT,
VALUE(B.RNK_NAM, ‘2M以内’) AS RNK_NAM ,
COUNT(DISTINCT A.USR_NBR) AS USR_CNT
FROM KF2.USR_GPRS A
LEFT JOIN SESSION.GPRS_RNK B ON
A.CALL_FLUX > G.RNK_LOW * 1024 AND
A.CALL_FLUX <= G.RNK_HIGH * 1024
GROUP BY STAT_DT,VALUE(B.RNK_NAM,'2M以内'), VALUE(B.RNK_CD,1)
ORDER BY STAT_DT, VALUE(B.RNK_CD,1)
WITH UR;
STAT_DT | RNK_NAM | USR_CNT |
201207 | 2M以内 | 100 |
201207 | 2-5M | 120 |
201207 | 5-10M | 300 |
201207 | 10-30M | 1230 |
201207 | 30-70M | 1010 |
union all 属性汇总
假设有有三个表,各自有不同的属性字段,需要合并到一起:
TABLE_1 | 地市编码 | 品牌 | 属性B |
| 通话时长 | 收入 |
TABLE_2 | 地市编码 |
|
| 地市名称 |
|
|
TABLE_3 | 地市编码 |
|
|
| 通话时长 | 收入 |
最终效果:
TABLE_U | 地市编码 | 属性B | 地市名称 | 总通话时长 | 总收入 |
可以采用的方式如下:
#1
INSERT INTO TABLE_U
SELECT 地市, B, ‘-’, SUM(通话时长), SUM(收入)
FROM TABLE1 GROUP BY 地市
UNION ALL
SELECT 地市, ‘-’, ‘-’, 地市名称, 0, 0
FROM TABLE2
UNION ALL
SELECT 地市, ‘-’ , ‘-’, 通话时长, 收入
FROM TABLE3 ;
#2
SELECT 地市, MAX(B), MAX(地市名称), SUM(通话时长), SUM(收入)
FROM TABLE_U
GROUP BY 地市
WITH UR;