来自在以前的公司的学习。几个数据库算法,还不错,平时用的时候经常去找原来的资料,放到网上,方便自己寻找。数据库为DB2,也基本适应其他的数据库。
表关联原则:
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
WHERE A.RNK = 1;
抽样算法
#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;