(3)添加计算字段,取最高点和最低点 (度量)
IF [CUST_CNT_MONTH] = [CUST_CNT_MAX] OR [CUST_CNT_MONTH] = [CUST_CNT_MIN]
THEN [CUST_CNT_MONTH] ELSE NULL END
(4)以计算字段为度量绘制一个图,选择"圆",然后点击双轴
可参考:各专业公司T+0产品客户情况统计及分析-注册时长(折线图)
3.分级别统计问题(以年龄分布为例说明)
(1)新增计算字段(维度)AGE_LEVEL(添加级别定义)
IF [AGE] < 18 THEN '<18'
ELSEIF [AGE] >= 18 AND [AGE] < 25 THEN '[18,25)'
ELSEIF [AGE] >= 25 AND [AGE] < 30 THEN '[25,30)'
ELSEIF [AGE] >= 30 AND [AGE] < 35 THEN '[30,35)'
ELSEIF [AGE] >= 35 AND [AGE] < 40 THEN '[35,40)'
ELSEIF [AGE] >= 40 AND [AGE] < 45 THEN '[40,45)'
ELSE '>=45' END
--默认取最新日期
IF [统计日期] = {MAX([统计日期])} THEN 'Last Date'
ELSE
STR(DATEPART('year',[统计日期]))+'-'+STR(DATEPART('month',[统计日期]))+'-'+STR(DATEPART('day',[统计日期]))
END
--如果统计一天24小时每个小时的数据量
--创建测试表
create table hour_test (add_hour date);
--循环插入测试数据
declare n number;
begin
n:=0;
while n<50000 loop
n :=n+1;
insert into hour_test
select * from (
select rownum rn,to_date('2017-01-01', 'yyyy-mm-dd hh:mi:ss')+ rownum/24 add_hour
from dual
connect by rownum<25
) a
where a.rn =(select round(dbms_random.value(1,24)) from dual);
end loop;
commit;
end ;
--查询结果
select to_char(add_hour,'hh24') hour,count(*) n from hour_test
group by to_char(add_hour,'hh24')
order by to_char(add_hour,'hh24');
Oracle中一个列转行例子
-- a 1|2|3
转成:
a 1
a 2
a 3
create table test( a1 varchar2(10),b1 varchar2(10));
insert into test(a1,b1) values('a','1,2,3');
commit;
--方法1`
select a1,regexp_substr(b1,'[^,]+',1,level)
from test
connect by level <= length(b1)-length(replace(b1,','))+1
--方法2
select a1,regexp_substr(b1,'[^,]+',1,level)
from test
connect by level <=length(regexp_replace(b1,'[^,]+')) +1
--方法3
select a1,regexp_substr(b1,'[^,]+',1,level)
from test
connect by level <=regexp_count(b1,',')+1