DM数据库SQL杂项技巧

1 查询

1.1 随机采样

1.1.1 ORDER BY RAND()

-- 随机排序后返回10行数据
select * from test.SALES ORDER BY RAND() LIMIT 10 ;

1.1.2 SAMPLE子句

-- 随机采样10%的记录返回，返回执行结果集不相同
select * from test.SALES SAMPLE(10) ;
-- 随机采样10%的记录返回，返回执行结果集相同（每次使用相同的种子值99）
select * from test.SALES SAMPLE(10) SEED(99) ;

2 分组

2.1 ROLLUP：对每个维度进行进行汇总

-- 按年【年月小计】、【年度合计】、【总合计】入职人数
SELECT YEAR(HIRE_DATE)  AS 入职年份,
       MONTH(HIRE_DATE) AS 入职月份,
       COUNT(*)         AS 入职人数
FROM DMHR.EMPLOYEE group by ROLLUP(YEAR(HIRE_DATE), MONTH(HIRE_DATE))
order by YEAR(HIRE_DATE) NULLS last, MONTH(HIRE_DATE) NULLS last;

2.2 CUBE：对所有组合进行分组

-- 按【年月小计】、【年合计】、【月合计】、【总合计】入职人数
SELECT YEAR(HIRE_DATE)  AS 入职年份,
       MONTH(HIRE_DATE) AS 入职月份,
       COUNT(*)         AS 入职人数
FROM DMHR.EMPLOYEE
GROUP BY CUBE (YEAR(HIRE_DATE), MONTH(HIRE_DATE))
ORDER BY YEAR(HIRE_DATE) NULLS LAST, MONTH(HIRE_DATE) NULLS LAST;

2.3 GROUPING SETS：指定多维度聚合合并

-- 按【年月】、【年度】合计（相比rollup，这里没有【总合计】，不需要时可节约资源）
SELECT YEAR(HIRE_DATE)  AS 入职年份,
       MONTH(HIRE_DATE) AS 入职月份,
       COUNT(*)         AS 入职人数
FROM DMHR.EMPLOYEE
GROUP BY GROUPING SETS 
( 
    (YEAR(HIRE_DATE), MONTH(HIRE_DATE)),
    YEAR(HIRE_DATE)
)
order by YEAR(HIRE_DATE) NULLS last, MONTH(HIRE_DATE) nulls last;

2.4 GROUPING：标示是否分组某列的派生行（并非数据本身是NULL）

-- 按年月小计、年度合计、总合计入职人数（分组派生行用小计、合计区分）
SELECT DECODE(GROUPING(YEAR(HIRE_DATE)), 1, '合计', YEAR(HIRE_DATE))                                                    AS 入职年份,
       CASE WHEN GROUPING(YEAR(HIRE_DATE)) = 0 AND GROUPING(MONTH(HIRE_DATE)) = 1 THEN '小计' ELSE MONTH(HIRE_DATE) END AS 入职月份,
       COUNT(*)                                                                                                       AS 入职人数
FROM DMHR.EMPLOYEE
GROUP BY ROLLUP (YEAR(HIRE_DATE), MONTH(HIRE_DATE))
ORDER BY YEAR(HIRE_DATE) NULLS LAST, MONTH(HIRE_DATE) NULLS LAST;

3 行列转换

原数据格式

-- 通过PIVOT将月份作为列显示
WITH EMP AS (
    SELECT YEAR(HIRE_DATE)  AS 年份,
           MONTH(HIRE_DATE) AS HIRE_MONTH,
           COUNT(*)         AS HIRE_CNT
    FROM DMHR.EMPLOYEE
    GROUP BY (YEAR(HIRE_DATE), MONTH(HIRE_DATE))
)
SELECT *
FROM EMP PIVOT (MAX(HIRE_CNT) FOR HIRE_MONTH IN (1,2,3,4,5,6,7,8,9,10,11,12))
ORDER BY 年份;  

4 临时函数

-- 定义一个生成根据下限和上限参数，返回一个整数值的【临时函数】，用以生成测试成绩表数据
WITH FUNCTION RAND_INT(P_LOW INT, P_HIGH INT) RETURN INT AS
BEGIN
    RETURN TRUNC(DBMS_RANDOM.VALUE(P_LOW, P_HIGH));
END;
SELECT DBMS_RANDOM.STRING('L', RAND_INT(5, 10)) AS 姓名,
       RAND_INT(1, 100)                         AS 语文,
       RAND_INT(1, 100)                         AS 数学,
       RAND_INT(1, 100)                         AS 外语
CONNECT BY LEVEL <=100;

5 MERGE INTO

5.1 例1：员工表部门名称冗余

-- 假如员工表为了提升效率，减少关联查询，加入部门名称字段
ALTER TABLE "DMHR"."EMPLOYEE"
    ADD COLUMN DEPARTMENT_NAME VARCHAR(50);
-- 利用部门表关联员工表，将部门名称更新到员工表
MERGE INTO "DMHR"."EMPLOYEE" E
USING "DMHR"."DEPARTMENT" D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHEN MATCHED THEN
    UPDATE
    SET E.DEPARTMENT_NAME=D.DEPARTMENT_NAME;

5.2 例2：产品PV统计表初始化与更新

-- 更新产品PV统计表中产品名称（假设产品名称可能会修改），如果不存在该商品，成初始化一条产品PV统计数据，PV值为0
MERGE INTO PRODUCTION.stat_product_pv S
USING PRODUCTION.PRODUCT P
ON S.PRODUCTID = P.PRODUCTID
WHEN MATCHED THEN
    UPDATE
    SET S.NAME=P.NAME
    WHEN NOT MATCHED THEN
INSERT (productid, name ,pv)VALUES(p.productid,p.name,0);

达梦技术社区：https://eco.dameng.com/