大数据中台-数据集

数据集你可以想象为一个EXCEL的二维表, 有行和列

数据源类型分为两种

#######  A 类数据源 ##########
比如你的原始数据库的格式是, 表名tb_name:
城市    户型    数量
长沙    A       12
长沙    A       23
上海    B       19

查询的sql: select 城市,户型,sum(数量) AS 数量 from tb_name group by 城市,户型
正常的查询的结果为:
[['长沙','A',35],
 ['上海','B ',19]]
由于生成的格式第一行是 [字符,字符,数值], 后台会自动进行转列动作, 
生成图表更容易使用格式:
[['Categroy','A','B'],
 ['长沙',     35, 0],
 ['上海',     0, 19]
 ]

#########  B类数据源 ##########
再比如我们有一个表的数据格式, 指标是展开的:
城市    A     B
长沙    10    12
上海    11    19
长沙    9     10

我们可以写的sql是:
select 城市, sum(A) as A, sum(B) as B from tb_name group by 城市
这样得到的结果是:
[['长沙',19,22],
 ['上海',11,19]]
可以发现这样和我们的标准格式差了一点, 再加上一个头部看看:
[['城市','A','B'],
 ['长沙', 19, 22],
 ['上海', 11, 19]]
是不是就和我们的标准格式一致了(此功能在数据集设定中有是否带头部)
再比如, 我们其实需要的格式是:
[['城市','长沙','上海'],
 ['A', 19, 11],
 ['B', 22, 19]]
这就是行列转化, 你可以直接使用函数处理
dataset = ds_transform(dataset)

多维度的数据透视

以上提到 A类数据源的情况, 格式都是: 
维度A   维度B  数据
但还有情况比如你有一个数据格式是:
维度A   维度B  维度C  数据
你需要在表格中将 A,B维度做维度, 但C做透视为指标名进行展示
由于我们的数据透视只支持"字符, 字符, 数值"的SQL写法, 
所以如果要多维, 我们需要做下转变, 可以写成:
select concat_ws(',',维度A,维度B) AS 维度
,维度C,SUM(数据) AS 度量 
from tablename group by 维度, 维度C
得到的数据样式
dataset=[['维度','C1','C2'....]
        ,['A1,B1',1,1...]
        ,['A2,B2',2,2...]]
最终在图形数据集处理中, 我们可以使用如下函数进行转化:
dataset = ds_split(dataset,',',['维度A','维度B'])
',' : 参数为分隔符
['维度A','维度B'] : 指第一个字段需要拆分的表头名称

最终得到的数据就是多维度透视
[['维度A','维度B','C1','C2'....]
,['A1','B1',1,1...]
,['A2','B2',2,2...]]

转化函数

对于一些特殊图形, 我们也可以使用dataset, 引入fun.js, 内置了几个转化函数

假设dataset的格式是, SQL = Select 维度1,维度2,数据 from xxxx, 生成的数据集如下
dataset = [['category','C1','C2'],
           ['R1',       12,  18],
           ['R2',       10,  17] ]

//移除数据集中的某几列
result=ds_remove_column(dataset,remove_list=[0]) 
 结果 =    [['R1','R2'],
           [12,  10],
           [18,  17] ]
//生成字典表示为key->[], 常用于定制化高的图形
result = ds_createMap(dataset)
结果 = {"category":['C1','C2'],
           "R1" : [12, 10],
           "R2" : [18,  17]}
         //取数方法 result['C1']
//获取列名list, 用于获取系列名
result = ds_rowname(dataset,start_row=1)
结果 = ['R1','R2']

//将数据集行列转化         
result = ds_transform(dataset)
结果  = [['category','R1','R2'],
           ['C1',       12,  10],
           ['C2',       18,  17] ]

关于数据刷新机制

• 间隔时间刷新, 如设定 间隔时间 为0.5, 则如果下一次访问时间超过30秒, 即触发数据刷新
• 固定时点刷新, 如设定 间隔时间 为-1, 则固定时点刷新, 即访问的时间超过固定的时点时, 触发数据刷新
• 强制刷新, 访问url加上&refresh=Y, 则强制刷新数据, 此刷新不会影响间隔或固定刷新的时间, 不会更新下次同步时间
• 后台刷新, 调用API链接, 可用于ETL完成后, 自动刷新数据, 会更新下次同步时间, 可以单独指定数据集或整个dashboard进行刷新, url:echart/refresh_ds?type=xxx&Token=xxx or dataset=xxx

设计原则

针对同一个源数据中有多个度量值, 但需要在一起展示, 可以采用SQL union的方法如:

select 类别, '指标1', 度量值1 from tablename group by 类别
union all 
select 类别, '指标2', 度量值2 from tablename group by 类别

新版本已经可以支持以下写法,而不用再使用union的方法, 推荐:
select 类别, SUM(指标1) AS 指标1,SUM(指标2) AS 指标2 
from tablename group by 类别

针对自定义图形, 标准方法如下,具体应用样列参考

let dataset = __dataset__ //传入dataset
legend_label = ['R2','R1'] //legend_label的顺序可以指定
xlabel = dataset[0].slice(1) //x轴的标签列
dataset = ds_createMap(dataset) //转化成KV格式
name = legend_label[0] //第一个系列的名字,第二个则legend_label[1]
data = dataset[legend_label[0]] //对应的第一个系列的数据
//
//以下为可选转化
dataset = ds_transform(dataset) //可选, 当需要行列互转时
legend_label = ds_rowname(dataset) //可选, 自动获取legend

单个查询请限制在1秒以内, 如整个dashboard查询时长大于4秒, 需考虑采用自动刷新机制, 如总体查询时长大于10秒, 需考虑指标进行预计算, 如在源数据端建立指标表

数据权限与参数控制

• 关于权限, sql查询中加上'$username', 会自动替换成登录用户, 可以采用此做数据权限关联后台权限mapping表, 注意如采用参数控制, 缓存会失效, 请控制查询时间在1秒以内
• 关于参数设计, 支持在dataset中设置参数, 格式$参数名. 传入参数的方法有两种
• 从url传入&param={"参数名":"参数值"}
• 针对dashboard固定参数, 只需在其它设定中加入参数{"参数名":"参数值"}, 注意url中传入的参数可以update dashboard中的参数
• 另外也支持当有参数传入时候采用参数, 没有传入时忽略参数据查询语句

注意 /* ... */的写法, 当参数写在这个区间时, 如果外部没有传入参数, 
会自动忽略这一段代码, 这样对于钻取来说非常重要, 
初始时不设筛选全部显示, 点击其它图形时传入参数进行动态钻取
select xxx,xxx,xxx from tablename /* where xxxx = '$参数名' */ .... 
####常见应用
//如何实现在不传参数时默认参数是当前月份,小胡同学的贡献:
select xx from tablename where calmonth = 
(select case when '/*$calmonth*/'<> '' then '/*$calmonth*/' 
else from_unixtime(unix_timestamp(),'yyyyMM') end) --impala版本

select xx from tablename where /* calmonth = '$calmonth' and */ 
(calmonth =to_char(sysdate,'YYYYMM') 
/*or calmonth = '$calmonth'*/) --oracle版本

select xx from tablename where
/*--$calmonth*/ calmonth=to_char(sysdate,'YYYYMM')
/* calmonth = '$calmonth'*/   --最优雅的方式

//如何传多个参数
select xx from tablename where 1=1 /* and city = '$city' */ 
/* and calmonth ='$calmonth'*/

//如何将字段当成参数
select /*$calmonth,*/ city, count(1) as qty from tablename 
group by /*$calmonth,*/ city

//如何实现当传入月份时, 按城市,月份 的数据, 不传参数时只看按城市的总数
select /*月份 as $参数, */  城市, 度量 from tablename 
/* where 月份 = $参数*/
group by 城市  /*,月份 --  $参数*/

• 另外需要注意的是, 当设定参数后, 如果从url传入参数, 此dataset的缓存功能失效, 所以如果有大的查询, 建意采用中间表的方式设计
• 数据集中的默认参数

对于开发人员来说, 带参数的SQL调试不方便, 所以支持你在sql中写入默认参数
方法如下格式, 你可以在sql的开头写上 /* {xxxxxx} */, 
会默认在开发调试模式下取这些参数, 在用户模式下会忽略

/* {"月份":"202009","城市":"中山"} */
select xxx from table xxx

固定数据集

有一些场景, 比如已有一些固定的筛选器,或是需要测试用,或者Demo,或者其它图形需要用到一些共用的已确定好的数据 这样我们可以不需要通过查询数据库的方式, 而直接写入数据集, 支持数组和字典的格式 你只需要在数据集中起始写入 dataset= , 这样就是默认是固定数据

• 如何快速的输入固定数据集, 你可以通过直接从EXCEL复制,保存以后会自动生成:

 

固定数据集也支持之前提到传参数, 魔术方法, 缓存等所有数据集的功能