需要分析一批数据,统计数据量。
数据很简单:唯一主键、多个类型、得分、插入日期时间等字段。但是不同类型组合统计数据量分外麻烦,在此做简单笔记记录。
通过随机数生成100000条测试数据。
CREATE TABLE demo AS
SELECT trunc(dbms_random.value(1,50000)) AS id,
to_char(sysdate + rownum / 24 / 3600, 'yyyy-mm-dd hh24:mi:ss') AS insertDateTime,
trunc(dbms_random.value(1,4)) AS type1,
trunc(dbms_random.value(1,4)) AS type2,
dbms_random.value(1,10) as score
FROM dual
CONNECT BY LEVEL <= 100000;
说明:只是设置了两个类型(type1、type2),同时根据得分(score)字段做筛选。
需求:统计类型1(type1)为1时,各个类型2(type2)中得分(score)数量大于4分的数据量各是多少。
难点:不同id的数据会出现多条数据,需要数据去重;数据去重前,需要筛选出符合要求的数据。
分析:筛选数据时只有两种情况,要么符合,要么不符合,可以通过状态(0,1)替换实际值;数据去重不能通过distinct
关键字,只能group by
分组。
开工:
-
筛选数据
通过case when then else end
对符合条件的数据做筛选SELECT t.id, t.type2, CASE WHEN t.type2 = 1 AND t.score >= 4 THEN 1 else 0 END ty21, CASE WHEN t.type2 = 2 AND t.score >= 4 THEN 1 else 0 END ty22, CASE WHEN t.type2 = 3 AND t.score >= 4 THEN 1 else 0 END ty23 FROM demo t WHERE t.type1 = 1;
-
去重
group by id
可以对数据去重,但是无法获取状态值,这时就需要max()
函数SELECT id, MAX(ty21) mty21, MAX(ty22) mty22, MAX(ty23) mty23 FROM (SELECT t.id, t.type2, CASE WHEN t.type2 = 1 AND t.score >= 4 THEN 1 else 0 END ty21, CASE WHEN t.type2 = 2 AND t.score >= 4 THEN 1 else 0 END ty22, CASE WHEN t.type2 = 3 AND t.score >= 4 THEN 1 else 0 END ty23 FROM demo t WHERE t.type1 = 1) GROUP BY id;
-
计数
添加筛选条件,统计数量SELECT COUNT(*) FROM (SELECT id, MAX(ty21) mty21, MAX(ty22) mty22, MAX(ty23) mty23 FROM (SELECT t.id, t.type2, CASE WHEN t.type2 = 1 AND t.score >= 4 THEN 1 else 0 END ty21, CASE WHEN t.type2 = 2 AND t.score >= 4 THEN 1 else 0 END ty22, CASE WHEN t.type2 = 3 AND t.score >= 4 THEN 1 else 0 END ty23 FROM demo t WHERE t.type1 = 1) GROUP BY id) WHERE mty21 > 0;