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

来自在以前的公司的学习。几个数据库算法，还不错，平时用的时候经常去找原来的资料，放到网上，方便自己寻找。数据库为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_CDVARCHAR(20)流水号(PK)USR_NBRVARCHAR(18)用户号码USR_ATR1VARCHAR(32)用户属性1USR_ATR2VARCHAR(32)用户属性2OPR_DTINTEGER操作日期

现需要按号码去重，每个号码取最后办理时记录。

流水号用户号码操作日期1001A201107011002A201110051003B201110061004C20111225     1005D201201011006B201204011007A20120612

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_DTINTEGER统计月份USR_NBRVARCHAR(18)用户号码(PK)GPRS_FLUXBIGINTGPRS流量(K)

表样本数据如下：

STAT_DTUSR_NBRGPRS_FLUX201207135********0201207134********1035201206138********55412201206139********11524201205150********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_DTRNK_NAMUSR_CNT2012072M以内1002012072-5M1202012075-10M30020120710-30M123020120730-70M1010

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;