数据库几点原则和几则数据算法

来自在以前的公司的学习。几个数据库算法，还不错，平时用的时候经常去找原来的资料，放到网上，方便自己寻找。数据库为DB2,也基本适应其他的数据库。

表关联原则：

1）主表应该是粒度最细的表；对于粒度比主表还细的从表，

需先将从表数据汇总到与主表相同粒度之后再进行关联。

2）与表关联时，需按从表的主键进行关联，保证关联该表后主表数据不会出现翻倍。

3）两个不同维度的表进行关联时，对于粒度较粗的从表，其数值字段不能直接参与sum()、avg()等汇总，因为与较细粒度的主表关联后，从表的数据已经翻倍了。

4）大表间关联时，关联条件中尽量不要出现对字段的函数操作，替代方案是先算好函数操作的结果并保存成单独的字段，后面作用该字段进行关联。


 

左关联原则：

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;